Re: Serial data type

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