Skip site navigation (1) Skip section navigation (2)

Re: Serial data type

From: Dave Cramer <pg(at)fastcrypt(dot)com>
To: Christian Schröder <cs(at)deriva(dot)de>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Serial data type
Date: 2007-11-30 11:53:32
Message-ID: 0F50756B-C295-4488-96F5-4FBFE73EB383@fastcrypt.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
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


In response to

pgsql-jdbc by date

Next:From: Albe LaurenzDate: 2007-11-30 12:58:26
Subject: Re: Serial data type
Previous:From: Christian SchröderDate: 2007-11-30 11:07:05
Subject: Re: Serial data type

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group