Re: Serial data type

[Dave Cramer <pg(at)fastcrypt(dot)com>]

Christian,

You can use the keyword DEFAULT to get the default value in

 insert into sertest (id) values (DEFAULT);

You can also do

insert into sertest(id) values (nextval('sertest_id_seq'));

Dave
On 30-Nov-07, at 6:07 AM, Christian Schröder wrote:

> Christian Rengstl wrote:
> > 1) How can I find out if there is a serial field in a table, as
> > getColumnType() in ResultSetMetaData does not return a field  
> > indicating
> > SERIAL as far as I could see?
> Creating a column with type serial is simply a shortcut for creating  
> an integer column and setting up a sequence which generates the  
> default value (see chapter 8.1.4 in den PostgreSQL docs):
>
> chschroe=# create temp table temp(x serial);
> NOTICE:  CREATE TABLE will create implicit sequence "temp_x_seq" for  
> serial column "temp.x"
> CREATE TABLE
> chschroe=# \d temp
>                      Table "pg_temp_7.temp"
> Column |  Type   |                    Modifiers
> --------+---------+--------------------------------------------------
> x      | integer | not null default nextval('temp_x_seq'::regclass)
>
>
> > 2) Why does it not work to issue a query like INSERT INTO
> > x(serial_field) VALUES(NULL) as the default of the "serial_field" is
> > nextval() anyway?
> PostgreSQL behaves different from e.g. MySQL. When you set a column  
> to "null" in MySQL and this column has a default value this default  
> is instead inserted in the column. In PostgreSQL this leads to an  
> error. (I don't know which is compliant to the standard, but I  
> assume PostgreSQL's behaviour is correct.)
> If you want a column to get its default value you have to omit it in  
> the insert statement. Of course, this only makes sense if your table  
> has more than this column. Consider the following example:
>
> chschroe=# create temp table temp(x serial, foo text);
> NOTICE:  CREATE TABLE will create implicit sequence "temp_x_seq" for  
> serial column "temp.x"
> CREATE TABLE
> chschroe=# insert into temp(foo) values ('bar');
> INSERT 0 1
> chschroe=# select * from temp;
> x | foo
> ---+-----
> 1 | bar
> (1 row)
>
> This works fine, whereas the following doesn't work at all:
>
> chschroe=# insert into temp values (null, 'bar');
> ERROR:  null value in column "x" violates not-null constraint
>
> So it's not a jdbc problem, but a general misunderstanding in the  
> way PostgreSQL handles default values.
>
> Regards,
>   Christian
>
> --
> Deriva GmbH                         Tel.: +49 551 489500-42
> Financial IT and Consulting         Fax:  +49 551 489500-91
> Hans-Böckler-Straße 2                  http://www.deriva.de
> D-37079 Göttingen
>
> Deriva CA Certificate: http://www.deriva.de/deriva-ca.cer
>
>
> ---------------------------(end of  
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>              http://archives.postgresql.org