using schema-qualified names in INSERTs
Does PostgreSQL support INSERT syntax of this kind -
insert into table (table.col1, table.col2, table.col3) values('one', 'two',
'three')?
Trying it out generates an error. It works when the 'table' bit is removed from
the column names.
F Church
On Mon, Feb 13, 2006 at 01:33:31PM +0000, Frank Church wrote:
Does PostgreSQL support INSERT syntax of this kind -
insert into table (table.col1, table.col2, table.col3) values('one', 'two',
'three')?Trying it out generates an error. It works when the 'table' bit is removed from
the column names.
No, and frankly, I can't see why it should. I mean, it's obvious that
you're inserting into "table", so why does it need to be mentioned
again for each and every column?
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them.
Frank Church wrote:
Does PostgreSQL support INSERT syntax of this kind -
insert into table (table.col1, table.col2, table.col3) values('one', 'two',
'three')?Trying it out generates an error. It works when the 'table' bit is removed from
the column names.
What would this do for you? What would it mean if I wrote?
INSERT INTO table1 (table2.col1, table3.col2) VALUES (...)
The list of columns can only apply to the table you've just named -
that's the only thing that makes sense.
--
Richard Huxton
Archonet Ltd
The list of columns can only apply to the table you've just named -
that's the only thing that makes sense.
That's true for a SELECT from a single table, too, but the qualified
syntax is allowed there. Also, this is allowed:
# update testtab set b = testtab.b * 3;
But this isn't:
# update testtab set testtab.b = testtab.b * 3;
Arguably a tad inconsistent. I don't know what the OP's rationale was,
but I can imagine that allowing the syntax on INSERT might simplify the
generation of SQL in certain circumstances. There's also the Principle
of Least Surprise, etc.
- John D. Burger
MITRE
"John D. Burger" <john@mitre.org> writes:
Arguably a tad inconsistent. I don't know what the OP's rationale was,
but I can imagine that allowing the syntax on INSERT might simplify the
generation of SQL in certain circumstances. There's also the Principle
of Least Surprise, etc.
There's also something called the SQL standard, which forbids both of
those syntaxes.
regards, tom lane
... I can imagine that allowing the syntax on INSERT might simplify
the
generation of SQL in certain circumstances. There's also the
Principle
of Least Surprise, etc.There's also something called the SQL standard, which forbids both of
those syntaxes.
Ah - I should have checked that.
I sometimes have trouble understanding the edicts of the wise ones -
anyone know what the rationale for this is?
- John D. Burger
MITRE
"John D. Burger" <john@mitre.org> writes:
I sometimes have trouble understanding the edicts of the wise ones -
anyone know what the rationale for this is?
I'm not sure whether the SQL spec authors foresaw this (or maybe even
have added it themselves in SQL2003), but the main reason why not allow
table-qualification of INSERT and UPDATE targets is that qualification
in this context should mean sub-fields of composite-type columns. We
do support the latter, since 8.0 I think. If we tried to support both
we'd have ambiguity problems.
regards, tom lane