Обсуждение: Serial data type
Hi everyone, 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? 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? Thanks! Christian Rengstl M.A. Klinik und Poliklinik für Innere Medizin II Kardiologie - Forschung Universitätsklinikum Regensburg B3 1.388 Franz-Josef-Strauss-Allee 11 93053 Regensburg Tel.: +49-941-944-7230
On Fri, 30 Nov 2007, 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? getColumnType returns a value from java.sql.Types, which doesn't have SERIAL so there is no way to return it. ResultSetMetaData.getColumnTypeName() should probably return serial, but it doesn't at the moment. ResultSetMetaData.isAutoIncrement() will work as will DatabaseMetaData.getColumns(). > 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? > When you've explicitly supplied a value (NULL in this case) the default is not used. Otherwise there would be no way to set a column to NULL that had a default value. Kris Jurka
Christian Rengstl wrote:
> 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?
>
Creating a column with type serial is simply a shortcut for creating an
integer column and setting up a sequence which generates the default
value (see chapter 8.1.4 in den PostgreSQL docs):
chschroe=# create temp table temp(x serial);
NOTICE: CREATE TABLE will create implicit sequence "temp_x_seq" for serial column "temp.x"
CREATE TABLE
chschroe=# \d temp
Table "pg_temp_7.temp"
Column | Type | Modifiers
--------+---------+--------------------------------------------------
x | integer | not null default nextval('temp_x_seq'::regclass)
> 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?
>
PostgreSQL behaves different from e.g. MySQL. When you set a column to
"null" in MySQL and this column has a default value this default is
instead inserted in the column. In PostgreSQL this leads to an error. (I
don't know which is compliant to the standard, but I assume PostgreSQL's
behaviour is correct.)
If you want a column to get its default value you have to omit it in the
insert statement. Of course, this only makes sense if your table has
more than this column. Consider the following example:
chschroe=# create temp table temp(x serial, foo text);
NOTICE: CREATE TABLE will create implicit sequence "temp_x_seq" for serial column "temp.x"
CREATE TABLE
chschroe=# insert into temp(foo) values ('bar');
INSERT 0 1
chschroe=# select * from temp;
x | foo
---+-----
1 | bar
(1 row)
This works fine, whereas the following doesn't work at all:
chschroe=# insert into temp values (null, 'bar');
ERROR: null value in column "x" violates not-null constraint
So it's not a jdbc problem, but a general misunderstanding in the way
PostgreSQL handles default values.
Regards,
Christian
--
Deriva GmbH Tel.: +49 551 489500-42
Financial IT and Consulting Fax: +49 551 489500-91
Hans-Böckler-Straße 2 http://www.deriva.de
D-37079 Göttingen
Deriva CA Certificate: http://www.deriva.de/deriva-ca.cer
Christian,
You can use the keyword DEFAULT to get the default value in
insert into sertest (id) values (DEFAULT);
You can also do
insert into sertest(id) values (nextval('sertest_id_seq'));
Dave
On 30-Nov-07, at 6:07 AM, Christian Schröder wrote:
> Christian Rengstl wrote:
>> 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?
>>
> Creating a column with type serial is simply a shortcut for creating
> an integer column and setting up a sequence which generates the
> default value (see chapter 8.1.4 in den PostgreSQL docs):
>
> chschroe=# create temp table temp(x serial);
> NOTICE: CREATE TABLE will create implicit sequence "temp_x_seq" for
> serial column "temp.x"
> CREATE TABLE
> chschroe=# \d temp
> Table "pg_temp_7.temp"
> Column | Type | Modifiers
> --------+---------+--------------------------------------------------
> x | integer | not null default nextval('temp_x_seq'::regclass)
>
>
>> 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?
>>
> PostgreSQL behaves different from e.g. MySQL. When you set a column
> to "null" in MySQL and this column has a default value this default
> is instead inserted in the column. In PostgreSQL this leads to an
> error. (I don't know which is compliant to the standard, but I
> assume PostgreSQL's behaviour is correct.)
> If you want a column to get its default value you have to omit it in
> the insert statement. Of course, this only makes sense if your table
> has more than this column. Consider the following example:
>
> chschroe=# create temp table temp(x serial, foo text);
> NOTICE: CREATE TABLE will create implicit sequence "temp_x_seq" for
> serial column "temp.x"
> CREATE TABLE
> chschroe=# insert into temp(foo) values ('bar');
> INSERT 0 1
> chschroe=# select * from temp;
> x | foo
> ---+-----
> 1 | bar
> (1 row)
>
> This works fine, whereas the following doesn't work at all:
>
> chschroe=# insert into temp values (null, 'bar');
> ERROR: null value in column "x" violates not-null constraint
>
> So it's not a jdbc problem, but a general misunderstanding in the
> way PostgreSQL handles default values.
>
> Regards,
> Christian
>
> --
> Deriva GmbH Tel.: +49 551 489500-42
> Financial IT and Consulting Fax: +49 551 489500-91
> Hans-Böckler-Straße 2 http://www.deriva.de
> D-37079 Göttingen
>
> Deriva CA Certificate: http://www.deriva.de/deriva-ca.cer
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
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