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

Re: Serial data type

From: "Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)at>
To: "Christian Rengstl *EXTERN*" <Christian(dot)Rengstl(at)klinik(dot)uni-regensburg(dot)de>,<pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: Serial data type
Date: 2007-11-30 12:58:26
Message-ID: D960CB61B694CF459DCFB4B0128514C2A0B0DA@exadv11.host.magwien.gv.at (view raw or flat)
Thread:
Lists: pgsql-jdbc
Christian Rengstl wrote:
> in my Java app at certain points INSERT queries are built dynamically,
> but now i am facing a problem when the target table contains a SERIAL
> field where NULL values are not allowed. Therefore I have two questions:
> 
> 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?

There's no really good implementation independent way, I think.

You can query the system catalogs with something like:

SELECT seq.relname
FROM pg_catalog.pg_depend dep    JOIN
     pg_catalog.pg_class tab     ON (dep.refobjid = tab.oid)     JOIN
     pg_catalog.pg_class seq     ON (dep.objid = seq.oid)        JOIN
     pg_catalog.pg_namespace sch ON (tab.relnamespace = sch.oid) JOIN
     pg_catalog.pg_attribute col ON (dep.refobjsubid = col.attnum)
WHERE sch.nspname = 'schema'  AND
      tab.relname = 'tabname' AND
      col.attname = 'colname' AND
      seq.relkind = 'S' AND dep.deptype = 'a';
This will return the name of the sequence for a serial column
and no row for another column.

Not nice, but maybe it is good enough for you.

> 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?

Because the default value is only used if you do not insert
anything into the column. What you try is to explicitly insert
a NULL into the field.

If you want the default value, omit the column in the list of
fields.

Yours,
Laurenz Albe

In response to

pgsql-jdbc by date

Next:From: Christian KindlerDate: 2007-11-30 14:07:44
Subject: prepared statement using postgres array
Previous:From: Dave CramerDate: 2007-11-30 11:53:32
Subject: Re: Serial data type

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