PostgreSQL 42P10 오류 원인과 해결 방법 완벽 가이드

42P10 invalid column reference 는?

PostgreSQL 에러 코드 42P10은 invalid column reference로, 주로 ON CONFLICT 절이나 GROUP BY, ORDER BY, 또는 윈도우 함수에서 잘못된 컬럼 참조가 발생할 때 나타납니다. 이 에러는 특히 INSERT ... ON CONFLICT 구문에서 충돌 대상(conflict target)으로 지정한 컬럼이 실제 유니크 인덱스나 기본 키와 일치하지 않을 때 자주 발생합니다. 쿼리 작성자가 존재하지 않는 컬럼명을 참조하거나, 문맥상 허용되지 않는 위치에서 컬럼을 참조하려 할 때도 동일한 에러가 발생합니다.

---

주요 발생 원인

1. ON CONFLICT 절에서 잘못된 컬럼 참조

INSERT ... ON CONFLICT (column_name) DO UPDATE 구문을 사용할 때, ON CONFLICT 뒤에 명시한 컬럼이 실제로 유니크 제약(UNIQUE constraint) 또는 기본 키(PRIMARY KEY)에 포함되지 않은 경우 이 에러가 발생합니다. PostgreSQL은 충돌 감지를 위해 반드시 유니크 인덱스나 기본 키를 기반으로 한 컬럼을 요구하며, 일반 컬럼을 충돌 대상으로 지정하면 42P10 에러를 반환합니다. 실무에서 가장 빈번하게 접하는 원인이므로 테이블 스키마를 먼저 확인하는 습관이 필요합니다.

2. 윈도우 함수(Window Function)에서 잘못된 컬럼 참조

OVER() 절 내의 PARTITION BY 또는 ORDER BY에서 쿼리의 SELECT 리스트에 존재하지 않거나, 해당 스코프에서 접근할 수 없는 컬럼을 참조할 때 발생합니다. 예를 들어 서브쿼리 내부에서 외부 쿼리의 컬럼을 윈도우 함수의 파티션 기준으로 사용하려 할 때 참조 범위(scope) 문제로 에러가 발생할 수 있습니다. 윈도우 함수는 컬럼 참조 스코프에 매우 엄격하므로 항상 현재 쿼리 레벨에서 접근 가능한 컬럼만 사용해야 합니다.

3. GROUP BY 또는 ORDER BY에서 잘못된 별칭(Alias) 또는 컬럼 참조

GROUP BY나 ORDER BY에서 SELECT 절에 정의된 별칭(alias)을 잘못 참조하거나, 집계 함수 내부의 컬럼을 그룹핑 기준으로 다시 참조하려 할 때 발생할 수 있습니다. PostgreSQL은 특정 맥락에서 SELECT 별칭을 GROUP BY에서 인식하지만, 서브쿼리나 복합 표현식에서는 허용하지 않는 경우가 있어 혼란을 야기합니다. 이러한 상황에서는 별칭 대신 원래 표현식을 명시적으로 작성하거나 서브쿼리를 활용하는 것이 안전합니다.

---

해결 방법

원인 1 해결: ON CONFLICT 절의 올바른 컬럼 지정

먼저 테이블의 유니크 제약이나 기본 키를 확인합니다.

-- 테이블의 인덱스 및 제약 확인
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'your_table';

-- 잘못된 예 (non_unique_column이 유니크 제약이 없는 경우)
INSERT INTO products (id, name, price)
VALUES (1, 'Widget', 9.99)
ON CONFLICT (name) DO UPDATE  -- 42P10 에러 발생: name에 유니크 제약 없음
SET price = EXCLUDED.price;

-- 올바른 예 1: 기본 키(id)를 충돌 대상으로 지정
INSERT INTO products (id, name, price)
VALUES (1, 'Widget', 9.99)
ON CONFLICT (id) DO UPDATE
SET price = EXCLUDED.price;

-- 올바른 예 2: 유니크 제약이 있는 컬럼 사용
ALTER TABLE products ADD CONSTRAINT products_name_unique UNIQUE (name);

INSERT INTO products (id, name, price)
VALUES (1, 'Widget', 9.99)
ON CONFLICT (name) DO UPDATE
SET price = EXCLUDED.price;

-- 올바른 예 3: ON CONFLICT ON CONSTRAINT 사용
INSERT INTO products (id, name, price)
VALUES (1, 'Widget', 9.99)
ON CONFLICT ON CONSTRAINT products_name_unique DO UPDATE
SET price = EXCLUDED.price;

원인 2 해결: 윈도우 함수의 올바른 컬럼 참조

-- 잘못된 예: 스코프 밖 컬럼 참조
SELECT
    order_id,
    ROW_NUMBER() OVER (PARTITION BY customer_name ORDER BY order_date) AS rn
    -- customer_name이 현재 SELECT 리스트나 FROM 테이블에 없을 경우 에러
FROM orders;

-- 올바른 예: 접근 가능한 컬럼만 사용
SELECT
    o.order_id,
    o.customer_id,
    c.customer_name,
    ROW_NUMBER() OVER (PARTITION BY o.customer_id ORDER BY o.order_date) AS rn
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

-- CTE를 활용한 안전한 윈도우 함수 사용
WITH ranked_orders AS (
    SELECT
        order_id,
        customer_id,
        order_date,
        amount,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
    FROM orders
)
SELECT *
FROM ranked_orders
WHERE rn = 1;

원인 3 해결: GROUP BY에서의 올바른 컬럼 참조

-- 잘못된 예: 집계 표현식 내부 컬럼을 GROUP BY에 재사용
SELECT
    department_id,
    SUM(salary) AS total_salary,
    AVG(bonus) AS avg_bonus
FROM employees
GROUP BY department_id, AVG(bonus);  -- 42P10 에러: 집계함수를 GROUP BY에 사용 불가

-- 올바른 예 1: 원시 컬럼만 GROUP BY에 사용
SELECT
    department_id,
    SUM(salary) AS total_salary,
    AVG(bonus) AS avg_bonus
FROM employees
GROUP BY department_id;

-- 올바른 예 2: 서브쿼리를 이용해 별칭 참조 문제 해결
SELECT *
FROM (
    SELECT
        department_id,
        SUM(salary) AS total_salary,
        AVG(bonus) AS avg_bonus
    FROM employees
    GROUP BY department_id
) dept_summary
WHERE total_salary > 100000
ORDER BY avg_bonus DESC;

---

예방 방법

1. 스키마 설계 시 제약 조건 문서화 및 \d 명령 습관화

ON CONFLICT 구문을 사용하기 전에 항상 \d table_name 또는 pg_indexes, information_schema.table_constraints를 통해 유니크 제약 및 기본 키를 먼저 확인하는 습관을 들이세요. 실무에서는 특히 마이그레이션 이후 제약 조건이 변경되는 경우가 많으므로, CI/CD 파이프라인에서 스키마 검증 스크립트를 자동으로 실행하도록 구성하면 사전에 에러를 방지할 수 있습니다.

-- 테이블의 모든 제약 조건 확인 쿼리
SELECT
    tc.constraint_name,
    tc.constraint_type,
    kcu.column_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
    ON tc.constraint_name = kcu.constraint_name
WHERE tc.table_name = 'your_table'
ORDER BY tc.constraint_type, kcu.column_name;

2. 복잡한 쿼리는 CTE로 단계별 분리하여 작성

복잡한 윈도우 함수나 집계 쿼리를 작성할 때는 하나의 긴 쿼리 대신 CTE(Common Table Expression)를 활용해 단계별로 분리하면 컬럼 참조 스코프를 명확히 할 수 있고, 에러 발생 시 원인을 빠르게 파악할 수 있습니다. 각 CTE 단계를 독립적으로 실행하며 중간 결과를 검증하는 방식은 42P10뿐만 아니라 다양한 쿼리 오류를 사전에 예방하는 효과적인 실무 전략입니다.

---

관련 에러

• 42703 (undefined_column): 쿼리에서 존재하지 않는 컬럼 이름을 참조할 때 발생합니다. 42P10과 유사하지만, 42703은 컬럼 자체가 테이블에 없는 경우이고 42P10은 문맥상 허용되지 않는 참조를 의미합니다.
• 42P19 (invalid_recursion): 재귀 CTE에서 잘못된 참조가 발생할 때 나타나는 에러로, 구조적 쿼리 작성 시 함께 주의해야 합니다.
• 42803 (grouping_error): GROUP BY 없이 집계 함수와 일반 컬럼을 혼용할 때 발생하며, 42P10과 함께 집계 쿼리 작성 시 자주 마주치는 에러입니다.
• 23505 (unique_violation): ON CONFLICT 없이 유니크 제약 위반 시 발생하는 에러로, 42P10을 수정한 후에 ON CONFLICT 로직이 올바르게 동작하는지 검증 과정에서 연관되어 나타날 수 있습니다.

---

본 포스트는 AI가 생성한 기술 가이드입니다. 운영 환경 적용 전 충분한 검토를 권장합니다.