0

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

2

1 Answer 1

4

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?

7
  • I changed it to profileid, id_user, still throwing the same error.
    – codeninja
    Nov 10, 2017 at 16:33
  • Did the error change to [column "id_user" specified in USING clause does not exist in left table]?
    – SQLDevDBA
    Nov 10, 2017 at 16:36
  • Now it says right table instead. ERROR: column "id_user" specified in USING clause does not exist in right table
    – codeninja
    Nov 10, 2017 at 16:37
  • 1
    Doesn't the JOIN USING (col1) clause require both the left and right tables to have "col1" in their definitions? And by that same exact name? The right table (FIRST_INSERT) has no id_user column in the definition.
    – SQLDevDBA
    Nov 10, 2017 at 16:39
  • But I'm confused here: Which is left table and which is right table?
    – codeninja
    Nov 10, 2017 at 16:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.