Re: INSERT Issues

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
columnsand 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>
thatspecifies 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