Re: Serial data type

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