Column reference in "On Conflict" is ambiguous

Question

I created the following table:

CREATE TABLE IF NOT EXISTS public.publications (
    publication_id bigint NOT NULL DEFAULT nextval('eap_publications_publication_id_seq'::regclass),
    title character varying(1000) COLLATE pg_catalog."default" NOT NULL,
    author character varying(1000) COLLATE pg_catalog."default" NOT NULL,
    type integer[] NOT NULL,
    sys_publication_timestamp timestamp without time zone NOT NULL,
    tags integer[] NOT NULL,
    languages eap_control_vocabulary[],
    isbn character varying(255) COLLATE pg_catalog."default",
    url character varying(255) COLLATE pg_catalog."default",
    thumbnail_url character varying(255) COLLATE pg_catalog."default",
    target_audience integer[] NOT NULL,
    topic integer[] NOT NULL,
    related_projects integer[] NOT NULL,
    featured boolean,
    publication_status status,
    sys_creat_ip_address inet,
    sys_creat_timestamp timestamp without time zone NOT NULL,
    sys_modif_ip_address inet,
    sys_modif_timestamp timestamp without time zone,
    CONSTRAINT publications_pkey PRIMARY KEY (publication_id)
);

And I made the following function to UPSERT:

CREATE OR REPLACE FUNCTION public.upsert_publication(
    titlepub character varying(1000),
    authorpub character varying(1000),
    typepub integer[],  
    tagspub integer[],
    languagespub text,
    isbnpub character varying(255),
    urlpub character varying(255),
    thumbnail_urlpub character varying(255),
    target_audiencepub integer[],
    topicpub integer[],
    related_projectspub integer[],
    featuredpub boolean,
    publication_statuspub status,
    sys_creat_ip_addresspub inet,
    sys_modif_ip_addresspub inet)
    
    RETURNS jsonb
    LANGUAGE 'plpgsql' AS
$BODY$
    declare
        pub_timestamp timestamp without time zone;
        pub_creat_timestamp timestamp without time zone;
        pub_modif_timestamp timestamp without time zone;
    
    BEGIN
    
        pub_timestamp :=current_timestamp;
        pub_creat_timestamp :=current_timestamp;
        pub_modif_timestamp :=current_timestamp;

        INSERT INTO public.publications 
        VALUES (DEFAULT,titlepub, authorpub, typepub, pub_timestamp, tagspub, languagespub,isbnpub,urlpub,thumbnail_urlpub, 
        target_audiencepub, topicpub, related_projectspub, featuredpub, publication_statuspub, sys_creat_ip_addresspub, pub_creat_timestamp, sys_modif_ip_addresspub,pub_modif_timestamp )
        ON CONFLICT (title, author) 
        DO 
           UPDATE SET title=EXCLUDED.title, author=EXCLUDED.author, type=EXCLUDED.type, sys_publication_timestamp=current_timestamp, 
           tags=EXCLUDED.tags, languages=EXCLUDED.languages,isbn=EXCLUDED.isbn,url=EXCLUDED.url,thumbnail_url=EXCLUDED.thumbnail_url, 
           target_audience=EXCLUDED.target_audience, topic=EXCLUDED.topic, related_projects=EXCLUDED.related_projects, featured=EXCLUDED.featured,
           publication_status=EXCLUDED.publication_status, sys_creat_ip_address=EXCLUDED.sys_creat_ip_address, sys_creat_timestamp=current_timestamp, 
           sys_modif_ip_address=EXCLUDED.sys_modif_ip_address, sys_modif_timestamp=current_timestamp;

end;
$BODY$;

But every time I try to use it, I have the following error:

ERROR:  column reference "title" is ambiguous
LINE 4:   ON CONFLICT (title,author) 
                      ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.

How can I make this reference less ambiguous? I try to add the name of the table in front of the "title" in the ON CONFLICT but that's just a syntax error.

I change the name of the inputs parameters as suggested. This as solve my issue, thanks!

Answer 1

1. As pointed out by Frank Heikens, you can rename your variables to avoid the name collision between your variables and table columns. You seem to have resolved that already.
2. You could also use an alias for the table targeted by your insert: demo

   CREATE TABLE publications(
     title TEXT NOT NULL, 
     author TEXT NOT NULL, 
     another_column INT,
     UNIQUE (title,author) );
   
   CREATE OR REPLACE FUNCTION public.upsert_publications(
       title text,
       author text,
       another_column int)
   RETURNS jsonb LANGUAGE 'plpgsql' AS $BODY$
   BEGIN
     INSERT INTO public.publications AS target --here's the alias
     VALUES (title,author,another_column)
     ON CONFLICT ((target.title),(target.author))--alias, note the parentheses
   --left side of the SET assignment already assumes the target, 
   --don't use that target alias here, below, on the left of =
     DO UPDATE SET another_column = EXCLUDED.another_column;
     RETURN '{"award":"participation_award"}'::jsonb;
   END $BODY$;
   

3. Instead of listing columns that are included in a constraint you expect to be violated, you can target the actual constraint:

   CREATE TABLE publications(
     title TEXT NOT NULL, 
     author TEXT NOT NULL, 
     another_column INT,
     CONSTRAINT publications_title_author_key UNIQUE (title,author) );
   -- ...
   INSERT INTO public.publications --alias not strictly necessary
   VALUES (title,author,another_column)
   ON CONFLICT ON CONSTRAINT publications_title_author_key
   DO UPDATE SET another_column=EXCLUDED.another_column;
   

4. There's no need to repeat the conflicting columns/values in your set list of on conflict do update; they are already the same in the incoming row compared to what you had in the table, which is why there's a conflict. No need to overwrite with the same value.
5. You will get a pretty self-explanatory ERROR: column "languages" is of type eap_control_vocabulary[] but expression is of type text. Redefine the function to accept that type for languagespub, or make it a text[] and cast it to that type.
6. You might have some more steps that you're not showing in that function, but given what you've shown, a plain lanugage SQL, a procedure or even a prepared statement might be more adequate. demo

Answer 2

There is a lot of noise. And the return type makes no sense. This should work:

CREATE OR REPLACE FUNCTION public.upsert_publication(
       titlepub varchar(1000),
       authorpub varchar(1000),
       typepub integer[],  
       tagspub integer[],
       languagespub eap_control_vocabulary[],
       isbnpub varchar(255),
       urlpub varchar(255),
       thumbnail_urlpub varchar(255),
       target_audiencepub integer[],
       topicpub integer[],
       related_projectspub integer[],
       featuredpub boolean,
       publication_statuspub status,
       sys_creat_ip_addresspub inet,
       sys_modif_ip_addresspub inet
   )
  RETURNS public.publications 
  LANGUAGE sql AS
$func$
INSERT INTO public.publications 
        title   , author   , type   , sys_publication_timestamp, tags   , languages   , isbn   , url   , thumbnail_url   , target_audience   , topic   , related_projects   , featured   , publication_status   , sys_creat_ip_address   , sys_creat_timestamp, sys_modif_ip_address   , sys_modif_timestamp)
VALUES (titlepub, authorpub, typepub,         CURRENT_TIMESTAMP, tagspub, languagespub, isbnpub, urlpub, thumbnail_urlpub, target_audiencepub, topicpub, related_projectspub, featuredpub, publication_statuspub, sys_creat_ip_addresspub,   CURRENT_TIMESTAMP, sys_modif_ip_addresspub,   CURRENT_TIMESTAMP)
ON CONFLICT (title, author) DO UPDATE
SET   (         title,          author,          type, sys_publication_timestamp,          tags,          languages,          isbn,          url,          thumbnail_url,          target_audience,          topic,          related_projects,          featured,          publication_status,          sys_creat_ip_address, sys_creat_timestamp,          sys_modif_ip_address, sys_modif_timestamp)
=     (EXCLUDED.title, EXCLUDED.author, EXCLUDED.type,         CURRENT_TIMESTAMP, EXCLUDED.tags, EXCLUDED.languages, EXCLUDED.isbn, EXCLUDED.url, EXCLUDED.thumbnail_url, EXCLUDED.target_audience, EXCLUDED.topic, EXCLUDED.related_projects, EXCLUDED.featured, EXCLUDED.publication_status, EXCLUDED.sys_creat_ip_address,   CURRENT_TIMESTAMP, EXCLUDED.sys_modif_ip_address,   CURRENT_TIMESTAMP)
RETURNING *;
$func$;

The naming conflicts have already been resolved by using distinct input parameter names. See:

• INSERT INTO ... RETURNING - ambiguous column reference
• How to return result of a SELECT inside a function in PostgreSQL?

I don't see the need for PL/pgSQL. Nor the need for additional variables.

I strongly advise to spell out target column names for the persisted INSERT statement. Don't implicitly rely on the full list of table columns. This breaks easily with any changes to table columns later. (And possibly goes unnoticed.) Then you also don't need DEFAULT in the VALUES expression. Columns that are not targeted receive their column default per default.

The columns making up the UNIQUE constraint that conflicts are unchanged by definition, so you could omit those in the UPDATE part. But that doesn't make any difference. Postgres writes a new row version anyway. Same cost.

I formatted for easy visual control.