Thread: default values

In transcribing Jose's reference page docs, I've come across examples
where Jose has exposed deficiencies in Postgres' support of SQL92. I've
fixed several (easier than transcribing more words :) and I've run into
a bit of trouble on the latest one with the

  CREATE TABLE tablename DEFAULT VALUES

statement. I changed the parser to allow this syntax and just use a nil
pointer for the column list. Everything works OK except that the first
column's default value is not assigned correctly.

Any ideas on where to look? I'll probably commit the changes to gram.y
anyway, since it almost works. Examples below...

                        - Tom

postgres=> create table t (x text default 'default',
postgres->  i int default 1);
CREATE
postgres=> insert into t default values;
INSERT 143693 1
postgres=> select * from t;
x|i
-+-
 |1
(1 row)

postgres=> insert into t (i) values (2);
INSERT 143694 1
postgres=> select * from t;
x      |i
-------+-
       |1
default|2
(2 rows)

postgres=> create table t3 (x text default 'default',
postgres->  i int default 1, j int default 2);
CREATE
postgres=> insert into t3 default values;
INSERT 143709 1
postgres=> select * from t3;
x|i|j
-+-+-
 |1|2
(1 row)

> In transcribing Jose's reference page docs, I've come across examples
> where Jose has exposed deficiencies in Postgres' support of SQL92. I've
> fixed several (easier than transcribing more words :) and I've run into
> a bit of trouble on the latest one with the
>
>   CREATE TABLE tablename DEFAULT VALUES
>
> statement. I changed the parser to allow this syntax and just use a nil
> pointer for the column list. Everything works OK except that the first
> column's default value is not assigned correctly.
>
> Any ideas on where to look? I'll probably commit the changes to gram.y
> anyway, since it almost works. Examples below...
>

I thought you did the default values additions?  Isn't it all done in
parser/analyze.c?  My guess is that is is looking at the number of
attributes specified in the target list, while in your case you really
want it look at the number of entries NOT in the target list.


>                         - Tom
>
> postgres=> create table t (x text default 'default',
> postgres->  i int default 1);
> CREATE
> postgres=> insert into t default values;
> INSERT 143693 1
> postgres=> select * from t;
> x|i
> -+-
>  |1
> (1 row)
>
> postgres=> insert into t (i) values (2);
> INSERT 143694 1
> postgres=> select * from t;
> x      |i
> -------+-
>        |1
> default|2
> (2 rows)
>
> postgres=> create table t3 (x text default 'default',
> postgres->  i int default 1, j int default 2);
> CREATE
> postgres=> insert into t3 default values;
> INSERT 143709 1
> postgres=> select * from t3;
> x|i|j
> -+-+-
>  |1|2
> (1 row)
>
>


--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

> I thought you did the default values additions?  Isn't it all done in
> parser/analyze.c?  My guess is that is is looking at the number of
> attributes specified in the target list, while in your case you really
> want it look at the number of entries NOT in the target list.

I just helped rewrite the gram.y parts of the default value grammar to
do full parsing on the clause. All of the hard work (and real knowledge)
was Vadim's...

                     - Tom

> >   CREATE TABLE tablename DEFAULT VALUES
> I thought you did the default values additions?

Sheesh. Of course, what I meant was

  INSERT INTO table DEFAULT VALUES

Sorry for the misinformation :(

                   - Tom

Thomas G. Lockhart wrote:
>
> In transcribing Jose's reference page docs, I've come across examples
> where Jose has exposed deficiencies in Postgres' support of SQL92. I've
> fixed several (easier than transcribing more words :) and I've run into
> a bit of trouble on the latest one with the
>
>   CREATE TABLE tablename DEFAULT VALUES
>
> statement. I changed the parser to allow this syntax and just use a nil
> pointer for the column list. Everything works OK except that the first
> column's default value is not assigned correctly.
>
> Any ideas on where to look? I'll probably commit the changes to gram.y
> anyway, since it almost works. Examples below...

DEFAULT is handled in parser/analyze.c:transformInsertStmt()...

Vadim