From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Jeremy Finzel <finzelj(at)gmail(dot)com> |
Cc: | PostgreSQL mailing lists <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: UPSERT on view does not find constraint by name |
Date: | 2019-10-18 13:22:00 |
Message-ID: | 29453.1571404920@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Jeremy Finzel <finzelj(at)gmail(dot)com> writes:
> test=# CREATE TEMP TABLE foo (id int primary key);
> CREATE TABLE
> test=# CREATE VIEW bar AS SELECT * FROM foo;
> NOTICE: view "bar" will be a temporary view
> CREATE VIEW
> ...
> test=# INSERT INTO bar (id)
> VALUES (1)
> ON CONFLICT ON CONSTRAINT foo_pkey
> DO NOTHING;
> ERROR: constraint "foo_pkey" for table "bar" does not exist
> test=# INSERT INTO bar (id)
> VALUES (1)
> ON CONFLICT (id)
> DO NOTHING;
> INSERT 0 0
> Of interest are the last 2 statements above. ON CONFLICT on the constraint
> name does not work, but it does work by field name. I'm not saying it
> *should* work both ways, but I'm more wondering if this is
> known/expected/desired behavior.
The first case looks perfectly normal to me: there is no "foo_pkey"
constraint associated with the "bar" view. It is interesting that
the second case drills down to find there's an underlying constraint,
but that seems like a bit of a hack :-(.
Poking at it a little more closely, it seems like the first case
involves a parse-time constraint lookup, while the second case
postpones the lookup to plan time, and so the second case works
because the view has already been expanded into a direct reference
to the underlying table. Maybe it wasn't good to do those cases
differently. I can't get too excited about it though.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Tomas Vondra | 2019-10-18 13:44:52 | Re: Add Change Badges to documentation |
Previous Message | Colin Watson | 2019-10-18 13:21:30 | Backport "WITH ... AS MATERIALIZED" syntax to <12? |