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