INSERT Issues

"Rod Taylor" <rod.taylor@inquent.com> writes:

INSERT INTO table [ ( column [, ...] ) ]
{ DEFAULT VALUES | VALUES ( expression [, ...] ) | SELECT query }

The documentation is wrong here, not the code. SQL92 defines the syntax
as

<insert statement> ::=
INSERT INTO <table name> <insert columns and source>

<insert columns and source> ::=
[ <left paren> <insert column list> <right paren> ] <query expression>
| DEFAULT VALUES

<insert column list> ::= <column name list>

regards, tom lane

On Thu, Apr 05, 2001 at 07:16:49PM -0400, Rod Taylor wrote:

CREATE TABLE junk (
col SERIAL PRIMARY KEY
);

INSERT INTO junk (col) DEFAULT VALUES;

INSERT INTO junk DEFAULT VALUES:

Second insert works, first one fails.

INSERT INTO table [ ( column [, ...] ) ]
{ DEFAULT VALUES | VALUES ( expression [, ...] ) | SELECT query }

The column list should just be ignored correct?

Hmm, the BNF from SQL1992 actually is:

<insert statement> ::=
INSERT INTO <table name>
<insert columns and source>

<insert columns and source> ::=
[ <left paren> <insert column list> <right paren> ]
<query expression>
| DEFAULT VALUES

<insert column list> ::= <column name list>

So the grammar is right to reject your first example.

According to the rules for <insert statement>:

2) An <insert columns and source> that specifies DEFAULT VALUES is
equivalent to an <insert columns and source> that specifies a
<query expression> of the form

VALUES (DEFAULT, . . . )

where the number of "DEFAULT" entries is equal to the number of
columns of T.

So the proper spelling of your first version is:

INSERT INTO junk (col) VALUES (DEFAULT);

Does that work for you?

Ross

create table junk (col SERIAL);

INSERT INTO junk (col) VALUES (DEFAULT);
ERROR: parser: parse error at or near "DEFAULT";