Re: Serial data type

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