Question 195507 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Insert Into On Conflict With a Where Clause

I have a SQL query that looks like this:

INSERT INTO testing.names
(
    id, 
    first_name,
    active_status
)
-------------------------------------
SELECT 
    stage.id,
    stage.first_name,
    stage.active_status
FROM 
    testing.name stage
    ON CONFLICT ON CONSTRAINT names_pkey DO UPDATE
SET 
    active_status = excluded.active_status
where
    active_status <> excluded.active_status

Problem: Error Message

active_status <> excluded.active_status is ambiguous.

Does anyone have a suggestion? For some reason, it says active_status is ambiguous, but only in the location of the where clause? I know I can use an alias, but I’m not sure how to in this case.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Looks like I can do something like this:

    INSERT INTO testing.names <place alias here>
(
    id, 
    first_name,
    active_status
)
-------------------------------------
SELECT 
    stage.id,
    stage.first_name,
    stage.active_status
FROM 
    testing.name stage
    ON CONFLICT ON CONSTRAINT names_pkey DO UPDATE
SET 
    active_status = excluded.active_status
where
    <alias from insert>.active_status <> excluded.active_status

  2. 0

    Yea, there are 2 relations in play at that point, "names" and "excluded". So it is ambiguous. You could use an alias if you want, but you don’t need to. You can just the table name directly (without the schema).

    where names.active_status <> excluded.active_status

    For the SET, only columns in the target relation can be updated, so in that case there is no ambiguity.

    You could argue that the only alternative interpretation is excluded.active_status <> excluded.active_status and that that doesn’t make sense so it is not really ambiguous. But trying to adopt that reasoning would be too clever by half. Just but in the qualification and be done with it.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search