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

Re: Serial data type

From: Christian Schröder <cs(at)deriva(dot)de>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Serial data type
Date: 2007-11-30 11:07:05
Message-ID: 474FEED9.2060000@deriva.de (view raw or flat)
Thread:
Lists: pgsql-jdbc
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


In response to

Responses

pgsql-jdbc by date

Next:From: Dave CramerDate: 2007-11-30 11:53:32
Subject: Re: Serial data type
Previous:From: Kris JurkaDate: 2007-11-30 11:01:30
Subject: Re: Serial data type

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