Re: Serial data type
От | Christian Schröder |
---|---|
Тема | Re: Serial data type |
Дата | |
Msg-id | 474FEED9.2060000@deriva.de обсуждение исходный текст |
Ответ на | Serial data type ("Christian Rengstl" <Christian.Rengstl@klinik.uni-regensburg.de>) |
Ответы |
Re: Serial data type
(Dave Cramer <pg@fastcrypt.com>)
|
Список | pgsql-jdbc |
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
В списке pgsql-jdbc по дате отправления: