psycopg2.errors.UndefinedColumn: column excluded.number does not exist

Question

У меня есть две таблицы в двух схемах в одной БД:

CREATE TABLE IF NOT EXISTS target_redshift.dim_collect_projects (
            project_id BIGINT NOT NULL UNIQUE,
            project_number BIGINT,
            project_name VARCHAR(300) NOT NULL,
            connect_project_id BIGINT NOT NULL,
            project_desc VARCHAR(5000) NOT NULL,
            project_type VARCHAR(50) NOT NULL,
            project_status VARCHAR(100),
            project_path VARCHAR(32768),
            language_code VARCHAR(10),
            country_code VARCHAR(10),
            timezone VARCHAR(10),
            project_created_at TIMESTAMP WITHOUT TIME ZONE,
            project_modified_at TIMESTAMP WITHOUT TIME ZONE,
            date_created TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW(),
            date_updated TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW()
        );

CREATE TABLE IF NOT EXISTS source_redshift.dim_collect_projects (
            id BIGINT NOT NULL UNIQUE,
            number BIGINT,
            name VARCHAR(300) NOT NULL,
            connect_project_id BIGINT NOT NULL,
            description VARCHAR(5000) NOT NULL,
            type VARCHAR(50) NOT NULL,
            status VARCHAR(100),
            path VARCHAR(32768),
            language VARCHAR(10),
            country VARCHAR(10),
            timezone VARCHAR(10),
            created TIMESTAMP WITHOUT TIME ZONE NULL DEFAULT NOW(),
            modified TIMESTAMP WITHOUT TIME ZONE NULL DEFAULT NOW()
        );

Мне нужно скопировать данные из второй таблицы в первую.

Делаю так:

INSERT INTO target_redshift.dim_collect_projects AS t
        SELECT id, number, name, connect_project_id, description,
        type, status, path, language, country, timezone, created,
        modified
        FROM source_redshift.dim_collect_projects
        ON CONFLICT (project_id)
        DO UPDATE SET
        (t.project_number, t.project_name, t.connect_project_id, t.project_desc,
        t.project_type, t.project_status, t.project_path, t.language_code,
        t.country_code, t.timezone, t.project_created_at, t.project_modified_at,
        t.date_created, t.date_updated) = (EXCLUDED.number, EXCLUDED.name, EXCLUDED.connect_project_id,
         EXCLUDED.description, EXCLUDED.type, EXCLUDED.status,
         EXCLUDED.path, EXCLUDED.language, EXCLUDED.country,
         EXCLUDED.timezone, EXCLUDED.created, EXCLUDED.modified, t.date_created, NOW())

Получаю следующую ошибку:

psycopg2.errors.UndefinedColumn: column excluded.number does not exist
LINE 12:         t.date_created, t.date_updated) = (EXCLUDED.number, ...

Answer 1

Имя excluded, представляющее данные новой строки, оперирует именами полей уже целевой таблицы, а не select'а, которым эти строки формировались.

melkij=> create table tbl_foo (id int primary key, dest_col int);
CREATE TABLE
melkij=> create table tbl_bar (id int primary key, src_col int);
CREATE TABLE
melkij=> insert into tbl_foo (id, dest_col) select id, src_col from tbl_bar 
on conflict (id) do update set dest_col = excluded.src_col;
ERROR:  column excluded.src_col does not exist
СТРОКА 1: ...tbl_bar on conflict (id) do update set dest_col = excluded.s...
                                                               ^
melkij=> insert into tbl_foo (id, dest_col) select id, src_col from tbl_bar 
on conflict (id) do update set dest_col = excluded.dest_col;
INSERT 0 0

У вас в таблице target_redshift.dim_collect_projects нет поля с именем number, потому обращение к EXCLUDED.number становится ошибкой. Верно будет excluded.project_number

Answer 2

В общем сделал:

INSERT INTO dim_collect_projects_1 AS t (project_id, project_number, project_name, connect_project_id, project_desc,
        project_type, project_status, project_path, language_code,
        country_code, timezone, project_created_at, project_modified_at)
        SELECT s.id, s.number, s.name, s.connect_project_id, s.description,
        s.type, s.status, s.path, s.language, s.country, s.timezone, s.created,
        s.modified
        FROM dim_collect_projects_2 AS s
        ON CONFLICT (project_id)
        DO UPDATE SET
        (project_number, project_name, connect_project_id, project_desc,
        project_type, project_status, project_path, language_code,
        country_code, timezone, project_created_at, project_modified_at,
        date_updated) = (EXCLUDED.project_number,
        EXCLUDED.project_name, EXCLUDED.connect_project_id,
        EXCLUDED.project_desc, EXCLUDED.project_type, EXCLUDED.project_status,
        EXCLUDED.project_path, EXCLUDED.language_code, EXCLUDED.country_code,
        EXCLUDED.timezone, EXCLUDED.project_created_at,
        EXCLUDED.project_modified_at, NOW())
        WHERE t.project_number != EXCLUDED.project_number
        OR t.project_name != EXCLUDED.project_name
        OR t.connect_project_id != EXCLUDED.connect_project_id
        OR t.project_desc != EXCLUDED.project_desc
        OR t.project_type != EXCLUDED.project_type
        OR t.project_status != EXCLUDED.project_status
        OR t.project_path != EXCLUDED.project_path
        OR t.language_code != EXCLUDED.language_code
        OR t.country_code != EXCLUDED.country_code
        OR t.timezone != EXCLUDED.timezone
        OR t.project_created_at != EXCLUDED.project_created_at
        OR t.project_modified_at != EXCLUDED.project_modified_at;

БЛОГ НА HUSL

psycopg2.errors.UndefinedColumn: column excluded.number does not exist

Ответы (2 шт):