Thread: 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/
> 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