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?

--
Rod Taylor

There are always four sides to every story: your side, their side, the
truth, and what really happened.
Thu, 5 Apr 2001 19:16:49 -0400
"Rod Taylor" <rod.taylor@inquent.com>
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

Fri, 06 Apr 2001 10:53:26 -0400
Tom Lane <tgl@sss.pgh.pa.us>
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 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

Fri, 6 Apr 2001 09:54:25 -0500
"Ross J. Reedstrom" <reedstrm@rice.edu>
Re: INSERT Issues
create table junk (col SERIAL);

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

>  INSERT INTO junk (col) VALUES (DEFAULT);
> 
> Does that work for you?
> 
> Ross
> 


Fri, 6 Apr 2001 11:17:56 -0400
"Rod Taylor" <rod.taylor@inquent.com>
Re: INSERT Issues