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