PostgreSQL ON CONFLICT column reference is ambiguous, but how to specify table column?

user_4618456 0
Viewed 263

A section of a PL/pgSQL function looks like this:

    INSERT INTO assistant.accesstoken
    (
        submission_id,
        token,
        expires
    )
    VALUES
    (
        v_submission_id,
        assistant.pseudo_encrypt(v_submission_id),
        CURRENT_TIMESTAMP + v_token_duration
    )
    ON CONFLICT (submission_id)
    DO UPDATE SET
        expires = CURRENT_TIMESTAMP + v_token_duration
    RETURNING token
    INTO v_accesstoken;

...and gives me a complaint:

psycopg.errors.AmbiguousColumn: column reference "submission_id" is ambiguous
LINE 13:     ON CONFLICT (submission_id)
                         ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.
QUERY:  INSERT INTO assistant.accesstoken
    (
        submission_id,
        token,
        expires
    )
    VALUES
    (
        v_submission_id,
        assistant.pseudo_encrypt(v_submission_id),
        CURRENT_TIMESTAMP + v_token_duration
    )
    ON CONFLICT (submission_id)
    DO UPDATE SET
        expires = CURRENT_TIMESTAMP + v_token_duration
    RETURNING token
CONTEXT:  PL/pgSQL function assistant.evaluation_begin(character varying,character varying) line 115 at SQL statement

There is no variable named submission_id. Name only exists as a column name.

Unfortunately, it seems to be an error to specify table for the submission_id:

ERROR:  syntax error at or near ")"
LINE 140:     ON CONFLICT (accesstoken.submission_id)

How do I fix this then? How to prefix conflict_target for ON CONFLICT in a way that it is accepted?

Additional, can someone point me to a source where I could learn how variables and ON CONFLICT can go together? How would an INSERT be interested on a "conflict" in a PL/pgSQL variable when inserting a row, and what would it even mean? I am genuinely curious...

Does PostgreSQL have overzealous checking or something? There are no variables, anywhere ever in this application, that would not be prefixed with in_, out_, inout_, r_ or v_, ...meaning that there is no ambiguity of this kind that I could see.

plpgsql postgresql
1 Answers

Looks like this was closed during the night, so I won't bother with this topic much more than just try to write this open a little better:

In the comments, klin provided a link 37.5.9. SQL Functions Returning TABLE which explains why this happened, however the question remains unanswered: How does one prefix the conflict_target to tell PostgreSQL that it is a column, not a variable?

Furthermore, the linked "duplicate question" does not deal with the above. I thought this site wanted to accumulate knowledge and information - now this hasty and incorrect closure makes it seem like a support ticket handling (workaround found, close it). Well, if this is what you want of this site, then that's what you do.

Description of the issue. Because returning a TABLE is equivalent of returning a SETOF, the "column" names are then just variable names in the local scope, it seems (as opposed to be in a namespace of the anonymous table). Thus, the definition of the return TABLE which had submission_id (as shown below) was the cause of the ambiguity and renaming that, UPSERT's ON CONFLICT column / conflict_target is no longer ambiguous. (May have felt like a bug, but its not...)

CREATE OR REPLACE FUNCTION
assistant.evaluation_begin(
    in_course_id        VARCHAR,
    in_uid              VARCHAR
)
    RETURNS TABLE (
        submission_id   INTEGER,
        accesstoken     INTEGER
    )
    LANGUAGE PLPGSQL
    SECURITY DEFINER
    VOLATILE
    CALLED ON NULL INPUT
AS $$

This information hopefully allows others to see this condition (should they experience a similar issue) and this will certainly help working around it, even if doesn't provide the syntax/tools to maintain the call interface intact.

The second question also remains unanswered: In what way can a variable be a valid ON CONFLICT target and what does it even mean? It could be that such a thing doesn't even exist, and might just be because the same error string is used in all ambiguity cases. I personally suspect that this is the case.

But as this is closed now, we'll leave it at "problem solved, workaround found" state. Thank You for klin - the link was very helpful!

user_46184560
Related