Re: Serial data type

["Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)at>]

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