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

Question

I have two tables in two different schemas on one database:

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()
        );

I need to copy data from the second table to the first.

Do it so:

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())

And AirFlow send an error:

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

Answer 1

You need to use the target_redshift.dim_collect_projects field names for the excluded.* fields e.g. excluded.project_number. The target table is the controlling one for the column names as that is where the data insert is being attempted.

UPDATE

Using an example table from my test database:

\d animals
                      Table "public.animals"
 Column |          Type          | Collation | Nullable | Default 
--------+------------------------+-----------+----------+---------
 id     | integer                |           | not null | 
 cond   | character varying(200) |           | not null | 
 animal | character varying(200) |           | not null | 
Indexes:
    "animals_pkey" PRIMARY KEY, btree (id)

\d animals_target
                      Table "public.animals_target"
    Column     |          Type          | Collation | Nullable | Default 
---------------+------------------------+-----------+----------+---------
 target_id     | integer                |           | not null | 
 target_cond   | character varying(200) |           |          | 
 target_animal | character varying(200) |           |          | 
Indexes:
    "animals_target_pkey" PRIMARY KEY, btree (target_id)

insert into 
   animals_target  
select 
  * 
from 
   animals 
ON CONFLICT 
   (target_id) 
DO UPDATE SET 
  (target_id, target_cond, target_animal) = 
  (excluded.target_id, excluded.target_cond, excluded.target_animal);

NOTE: No use of table alias for the table being inserted into.

The target table is the one the data is being inserted into. The attempted INSERT is into its columns so the they are the ones that are being potentially excluded.

Answer 2

Did it:

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;