Hosting Provided By

Re: [JDBC] Serial data type

From: Albe Laurenz <laurenz.albe(at)wien.gv.at>
Date: Fri Nov 30 2007 - 07:58:26 EST

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.

Do you need help?

Yours,
Laurenz Albe

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match

Received on Fri Nov 30 07:59:19 2007

This message: [ Message body ]
Next message: Christian Kindler: "[JDBC] prepared statement using postgres array"
Previous message: Dave Cramer: "Re: [JDBC] Serial data type"
In reply to: Christian Rengstl: "[JDBC] Serial data type"

Contemporary messages sorted: [ By Date ] [ By Thread ] [ By Subject ] [ By Author ] [ By messages with attachments ]

This archive was generated by hypermail 2.1.8 : Wed Jun 18 2008 - 23:40:42 EDT