Postgresql – ERROR: column X specified in USING clause does not exist in left table

postgresqlpostgresql-9.6

I have a rather complex query I want to execute.
The data on table import_temp_table is imported from a CSV file coming from Google Datastore and CouchDB.

The table looks like this:

CREATE TABLE public.import_temp_table
(
    profileid text COLLATE pg_catalog."default",
    terms_accepted text COLLATE pg_catalog."default",
    lastname text COLLATE pg_catalog."default",
    firstname text COLLATE pg_catalog."default",
    picture_serving_url text COLLATE pg_catalog."default",
    is_active text COLLATE pg_catalog."default",
    is_passport_active text COLLATE pg_catalog."default",
    language text COLLATE pg_catalog."default",
    created text COLLATE pg_catalog."default",
    modified text COLLATE pg_catalog."default",
    passport_completion_level text COLLATE pg_catalog."default",
    email text COLLATE pg_catalog."default",
    about_me text COLLATE pg_catalog."default",
    uni_code text COLLATE pg_catalog."default",
    metadata text COLLATE pg_catalog."default"
)

I want to execute a query that performs 3 simultaneous inserts into the following tables:

User

CREATE TABLE public."user"
(
    id character varying COLLATE pg_catalog."default" NOT NULL DEFAULT nextval('user_id_seq'::regclass),
    first_name character varying COLLATE pg_catalog."default" NOT NULL,
    last_name character varying COLLATE pg_catalog."default" NOT NULL,
    email character varying COLLATE pg_catalog."default" NOT NULL,
    description character varying COLLATE pg_catalog."default",
    status integer,
    picture text COLLATE pg_catalog."default",
    city character varying COLLATE pg_catalog."default",
    cover_picture_url text COLLATE pg_catalog."default",
    cover_picture_file_name text COLLATE pg_catalog."default",
    cover_picture_container text COLLATE pg_catalog."default",
    birthday date,
    type character varying COLLATE pg_catalog."default",
    institution_id integer,
    language_id integer,
    created timestamp with time zone,
    modified timestamp with time zone,
    CONSTRAINT user_pkey PRIMARY KEY (id),
    CONSTRAINT user_email_key UNIQUE (email),
    CONSTRAINT user_institution_id_fkey FOREIGN KEY (institution_id)
        REFERENCES public.institution (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT user_language_id_fkey FOREIGN KEY (language_id)
        REFERENCES public.language (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

Passport

CREATE TABLE public.passport
(
    id integer NOT NULL DEFAULT nextval('passport_id_seq'::regclass),
    public boolean,
    status integer,
    completion_level character varying COLLATE pg_catalog."default",
    user_id character varying COLLATE pg_catalog."default",
    application_id integer,
    terms_accepted boolean,
    CONSTRAINT passport_pkey PRIMARY KEY (id),
    CONSTRAINT passport_application_id_fkey FOREIGN KEY (application_id)
        REFERENCES public.application (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT passport_user_id_fkey FOREIGN KEY (user_id)
        REFERENCES public."user" (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

Misc

CREATE TABLE public.misc
(
    id integer NOT NULL DEFAULT nextval('misc_id_seq'::regclass),
    key character varying COLLATE pg_catalog."default",
    value json,
    user_id character varying COLLATE pg_catalog."default",
    type character varying COLLATE pg_catalog."default",
    CONSTRAINT misc_pkey PRIMARY KEY (id),
    CONSTRAINT misc_user_id_fkey FOREIGN KEY (user_id)
        REFERENCES public."user" (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

And the query is the following:

WITH data AS (
    SELECT
        profileid as id_user,
        cast(terms_accepted as boolean) as terms_accepted_passport,
        lastname as last_name_user,
        firstname as first_name_user,
        picture_serving_url as picture_user,
        is_active as status_user,
        is_passport_active as status_passport,
        language as language_id_user,
        to_timestamp(created, 'YYYY-MM-DD HH24:MI:SS') as created_user,
        to_timestamp(modified, 'YYYY-MM-DD HH24:MI:SS') as modified_user,
        passport_completion_level as completion_level_passport,
        email as email_user,
        about_me as description_user,
        uni_code as institution_id_user,
        to_json(metadata) as metadata_misc
    FROM import_temp_table
)
, FIRST_INSERT AS(
    /*REMEMBER TO PUT AFFECTED FIELDS SINCE NOT ALL ARE BEING USED*/
    INSERT INTO public.user (
        id,
        first_name,
        last_name,
        email,
        description,
        status,
        picture,
        type,
        institution_id,
        language_id,
        created,
        modified
    )
    SELECT
        id_user,
        first_name_user,
        last_name_user,
        email_user,
        description_user,
        CASE status_user
            WHEN LOWER('true') THEN 1
            WHEN LOWER('false') THEN 2
        END AS status_user,
        picture_user,
        'client',
        CASE institution_id_user
            WHEN '100' THEN 4
        END AS institution_id_user,
        CASE
            WHEN LOWER(language_id_user) = 'english'    THEN 11
            WHEN LOWER(language_id_user) = 'spanish'    THEN 12
            WHEN LOWER(language_id_user) = 'portuguese' THEN 13
        END AS language_id_user,
        created_user,
        modified_user
    FROM data ON CONFLICT (id) DO NOTHING
    RETURNING id as current_user_id
)
, SECOND_INSERT AS(
    INSERT INTO passport (
        public,
        status,
        completion_level,
        user_id,
        terms_accepted
    )
    SELECT
        TRUE,
        CASE status_passport
            WHEN LOWER('true') THEN 1
            WHEN LOWER('false') THEN 2
        END AS status_passport,
        completion_level_passport,
        current_user_id,
        terms_accepted_passport
    FROM data
    JOIN FIRST_INSERT USING (current_user_id)
    RETURNING current_user_id
)
INSERT INTO misc (
    key, value, user_id
)
SELECT
    'metadata',
    metadata_misc,
    current_user_id
FROM data
JOIN FIRST_INSERT USING (current_user_id)
JOIN SECOND_INSERT USING (current_user_id)

The error the console on PG Admin throws me is the following: ERROR: column "current_user_id" specified in USING clause does not exist in left table

Any idea what am I doing wrong? What is this left table? PostgreSQL version is 9.6

Best Answer

The left Table is "data" in this scenario.

The error seems to be coming from this query:

SELECT
    TRUE,
    CASE status_passport
        WHEN LOWER('true') THEN 1
        WHEN LOWER('false') THEN 2
    END AS status_passport,
    completion_level_passport,
    current_user_id,
    terms_accepted_passport
FROM data
JOIN FIRST_INSERT USING (current_user_id)
RETURNING current_user_id

The CTE definition of the "data" table does not have current_user_id.

The "Left" table is usually the table right after the FROM keyword.

Not sure if it's actually trying to get current_user_id from your "right" table though (FIRST_INSERT_USING). Looks like you may have to fully qualify it to avoid confusion?

Related Question