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

42P10 invalid column reference 는?

PostgreSQL 에러 코드 42P10은 invalid column reference로, 쿼리 내에서 컬럼 참조가 유효하지 않을 때 발생합니다. 주로 GROUP BY, ORDER BY, WINDOW 함수, 또는 집계 함수와 함께 사용되는 쿼리에서 컬럼을 잘못 참조할 때 나타납니다. 이 에러는 SQL 표준 규칙을 위반하거나 PostgreSQL의 컬럼 참조 규칙을 올바르게 따르지 않을 때 트리거됩니다.

---

주요 발생 원인

1. WINDOW 함수에서의 잘못된 컬럼 참조

WINDOW 함수(윈도우 함수)를 사용할 때 PARTITION BY 또는 ORDER BY 절에 존재하지 않거나 잘못된 컬럼을 참조하면 이 에러가 발생합니다. 특히 서브쿼리나 CTE(Common Table Expression) 내부에서 외부 쿼리의 컬럼을 윈도우 함수의 파티션 기준으로 사용하려 할 때 흔히 발생합니다. PostgreSQL은 윈도우 함수의 범위 내에서 참조 가능한 컬럼을 엄격하게 제한하기 때문에, 현재 쿼리 레벨에서 접근할 수 없는 컬럼을 사용하면 반드시 이 에러를 마주하게 됩니다.

2. GROUP BY 절에서의 부적절한 컬럼 참조

SELECT 절에 포함된 비집계 컬럼이 GROUP BY 절에 명시되지 않거나, 반대로 GROUP BY에 존재하지 않는 컬럼 별칭(alias)을 사용할 때 발생합니다. PostgreSQL은 일부 다른 DBMS와 달리 GROUP BY 절에서 SELECT 절의 별칭을 직접 참조하는 것을 허용하지 않는 경우가 있으며, 이로 인해 컬럼 참조 오류가 발생할 수 있습니다. 또한 복잡한 서브쿼리에서 외부 쿼리의 컬럼을 내부 GROUP BY에서 참조하려 할 때도 에러가 발생할 수 있습니다.

3. 잘못된 컬럼 참조를 사용하는 복잡한 조인 쿼리

여러 테이블을 조인할 때 동일한 이름의 컬럼이 여러 테이블에 존재하거나, 테이블 별칭 없이 모호하게 컬럼을 참조하는 경우 이 에러가 발생할 수 있습니다. 특히 LATERAL 조인이나 서브쿼리를 사용하는 복잡한 쿼리에서 스코프(scope)를 벗어난 컬럼을 참조하면 PostgreSQL은 해당 컬럼을 인식하지 못하고 에러를 반환합니다. 외부 쿼리의 컬럼이 내부 서브쿼리에서 참조될 때의 규칙을 정확히 이해하고 있어야 이러한 문제를 예방할 수 있습니다.

---

해결 방법

원인 1 해결: WINDOW 함수 컬럼 참조 수정

문제가 되는 쿼리 예시:

-- 에러 발생: 외부 쿼리 컬럼을 윈도우 함수 내에서 잘못 참조
SELECT
    employee_id,
    department_id,
    salary,
    AVG(salary) OVER (PARTITION BY d.department_name ORDER BY salary) AS avg_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

해결된 쿼리:

-- 올바른 방법: 조인된 테이블의 컬럼을 명확하게 참조
SELECT
    e.employee_id,
    e.department_id,
    e.salary,
    AVG(e.salary) OVER (
        PARTITION BY e.department_id
        ORDER BY e.salary
    ) AS avg_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

-- CTE를 활용한 안전한 윈도우 함수 사용
WITH employee_data AS (
    SELECT
        e.employee_id,
        d.department_name,
        e.salary
    FROM employees e
    JOIN departments d ON e.department_id = d.department_id
)
SELECT
    employee_id,
    department_name,
    salary,
    AVG(salary) OVER (
        PARTITION BY department_name
        ORDER BY salary
    ) AS avg_salary
FROM employee_data;

원인 2 해결: GROUP BY 절 컬럼 참조 수정

문제가 되는 쿼리 예시:

-- 에러 발생: SELECT 절의 별칭을 GROUP BY에서 직접 사용
SELECT
    department_id,
    EXTRACT(YEAR FROM hire_date) AS hire_year,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id, hire_year;  -- hire_year 별칭 참조 불가

해결된 쿼리:

-- 올바른 방법 1: GROUP BY에 실제 표현식을 사용
SELECT
    department_id,
    EXTRACT(YEAR FROM hire_date) AS hire_year,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id, EXTRACT(YEAR FROM hire_date);

-- 올바른 방법 2: 서브쿼리로 먼저 별칭을 정의 후 GROUP BY 사용
SELECT
    department_id,
    hire_year,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary
FROM (
    SELECT
        department_id,
        EXTRACT(YEAR FROM hire_date) AS hire_year,
        salary
    FROM employees
) AS subquery
GROUP BY department_id, hire_year;

원인 3 해결: 조인 쿼리에서의 모호한 컬럼 참조 수정

문제가 되는 쿼리 예시:

-- 에러 발생: 모호한 컬럼 참조 (두 테이블 모두 department_id 보유)
SELECT
    department_id,
    employee_name,
    department_name
FROM employees
JOIN departments USING (department_id)
ORDER BY department_id, salary DESC;
-- LATERAL 서브쿼리에서 외부 컬럼을 잘못 참조하는 경우
SELECT
    e.employee_id,
    recent_orders.*
FROM employees e,
LATERAL (
    SELECT order_id, amount
    FROM orders
    WHERE orders.employee_id = employee_id  -- 모호한 참조
    ORDER BY order_date DESC
    LIMIT 5
) AS recent_orders;

해결된 쿼리:

-- 올바른 방법: 테이블 별칭으로 명확한 컬럼 참조
SELECT
    e.department_id,
    e.employee_name,
    d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
ORDER BY e.department_id, e.salary DESC;

-- LATERAL 조인에서 외부 컬럼 명확하게 참조
SELECT
    e.employee_id,
    recent_orders.*
FROM employees e,
LATERAL (
    SELECT order_id, amount
    FROM orders o
    WHERE o.employee_id = e.employee_id  -- 명확한 외부 참조
    ORDER BY o.order_date DESC
    LIMIT 5
) AS recent_orders;

---

예방 방법

1. 항상 테이블 별칭과 완전한 컬럼 참조 사용하기

복잡한 쿼리를 작성할 때는 반드시 모든 컬럼 앞에 테이블 별칭을 붙이는 습관을 들이세요. 이를 통해 컬럼 참조의 모호성을 제거하고, 어떤 테이블의 컬럼인지 명확히 알 수 있어 유지보수성도 높아집니다. 팀 내 SQL 코딩 컨벤션으로 “모든 컬럼에 테이블 별칭 필수” 규칙을 도입하면 이 에러를 사전에 방지할 수 있습니다.

-- 나쁜 예: 별칭 없이 컬럼 참조
SELECT employee_id, department_name
FROM employees JOIN departments ON department_id = department_id;

-- 좋은 예: 항상 테이블 별칭 사용
SELECT e.employee_id, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

2. CTE(Common Table Expression)를 활용한 쿼리 구조화

복잡한 집계 쿼리나 윈도우 함수를 사용하는 경우, CTE를 활용하여 쿼리를 단계별로 분리하세요. 각 CTE 단계에서 컬럼을 명확하게 정의하고 명명하면 이후 단계에서 컬럼 참조 오류를 방지할 수 있으며, 쿼리 디버깅도 훨씬 쉬워집니다. 특히 윈도우 함수와 GROUP BY를 함께 사용하는 복잡한 분석 쿼리에서 CTE는 필수적인 패턴입니다.

-- CTE로 단계별 쿼리 구조화 예시
WITH
base_data AS (
    SELECT
        e.employee_id,
        e.department_id,
        d.department_name,
        e.salary,
        e.hire_date
    FROM employees e
    JOIN departments d ON e.department_id = d.department_id
),
aggregated AS (
    SELECT
        department_id,
        department_name,
        AVG(salary) AS avg_salary,
        COUNT(*) AS headcount
    FROM base_data
    GROUP BY department_id, department_name
),
ranked AS (
    SELECT
        department_id,
        department_name,
        avg_salary,
        headcount,
        RANK() OVER (ORDER BY avg_salary DESC) AS salary_rank
    FROM aggregated
)
SELECT *
FROM ranked
WHERE salary_rank <= 5;

---

관련 에러

• 42803 (grouping_error): GROUP BY 절에 포함되지 않은 컬럼이 SELECT 절에 사용될 때 발생하며, 42P10과 함께 자주 나타나는 에러입니다.
• 42P09 (ambiguous_column): 조인 쿼리에서 동일한 이름의 컬럼이 여러 테이블에 존재할 때 발생하는 모호한 컬럼 참조 에러입니다.
• 42703 (undefined_column): 존재하지 않는 컬럼을 참조할 때 발생하며, 오타나 잘못된 테이블 참조로 인해 발생하는 경우가 많습니다.
• 42P01 (undefined_table): 잘못된 테이블 참조로 인해 컬럼 참조 자체가 불가능해지는 상황에서 함께 발생할 수 있습니다.

---

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