Thread: extra syntax on INSERT

I just realized that INSERT allows us to have more syntax than the
manual said.  I wonder if we want to elimiate it or keep it with more
documentation on the INSERT statment?

Here is the INSERT synopsis we have in 7.2 documentation.
==========
INSERT INTO table [ ( column [, ...] ) ]
    { DEFAULT VALUES | VALUES ( expression [, ...] ) | SELECT query }

Assume we have,
CREATE TABLE t1 (a1 int, a2 int);
CREATE TABLE t2 (a3 int, a4 int);

INSERT INTO t2 VALUES(2, 0);
INSERT INTO t2 VALUES(2,1);

====  postgres allows to have something like ====

INSERT INTO t1 VALUES(1, 0 AS "Oops");
INSRET INTO t1 VALUES(t2.*);

===================

For the first one, I believe that is due to reusing the definition of
target_list/target_el.  I didn't dig in to see how PostgreSQL handle the
second case.  At least the INSRET synopsis does not cover this case.


--
Regards,
LM Liu

Limin Liu <limin@pumpkinnet.com> writes:
> I just realized that INSERT allows us to have more syntax than the
> manual said.  I wonder if we want to elimiate it or keep it with more
> documentation on the INSERT statment?

This will likely go away when we get around to upgrading INSERT to the
full SQL spec --- certainly I'd feel no compunction about removing any
non-SQL syntax that happens to be supported now, if it gets in the way
of spec compliance.

In short, no I don't want to document it, because I don't want people
to start relying on it.

> For the first one, I believe that is due to reusing the definition of
> target_list/target_el.

Yes.  There's not a lot of difference in the implementations of
INSERT ... VALUES and INSERT ... SELECT, at the moment.

            regards, tom lane

On Thu, 31 May 2001, Tom Lane wrote:

> Limin Liu <limin@pumpkinnet.com> writes:
> > I just realized that INSERT allows us to have more syntax than the
> > manual said.  I wonder if we want to elimiate it or keep it with more
> > documentation on the INSERT statment?
>
> This will likely go away when we get around to upgrading INSERT to the
> full SQL spec --- certainly I'd feel no compunction about removing any
> non-SQL syntax that happens to be supported now, if it gets in the way
> of spec compliance.

Are you talking about allowing multiple rows in one insert, like this?

INSERT into foo VALUES ((1, 2, 3), (4, 5, 6), (7, 8, 9))

That would be a nice feature to have, and I think it's consistent with
SQL-92.
--
Tod McQuillin