Maybe it was discussed already, but I think it's very strange behavior
and things should be changed (please correct me if I'm wrong)
Suppose we have database containing only one simple table:
***
template1=# CREATE DATABASE testseq;
template1=# \c testseq
testseq=# CREATE TABLE test(id SERIAL, data TEXT);
NOTICE: CREATE TABLE will create implicit sequence "test_id_seq" for
serial column "test.id"
CREATE TABLE
***
Look at the table 'test':
***
testseq=# \d test
Table "public.test"
Column | Type | Modifiers
--------+---------+----------------------------------------------------------
id | integer | not null default (nextval('test_id_seq'::regclass))
data | text |
***
So, if we don't know the history we cannot understand that id is of
type SERIAL. We think as it's INTEGER with DEFAULT
'nextval('test_id_seq'::regclass)' [as expression]
This is the question #1 - how I can distinguish pure SERIAL and
INTEGER with corresponding DEFAULT setting?
Then... Imagine that we should use sequence in some other manner. For example:
***
ALTER TABLE test ALTER COLUMN id SET DEFAULT nextval('test_id_seq') * 10;
***
All is OK:
***
testseq=# \d test
Table "public.test"
Column | Type | Modifiers
--------+---------+----------------------------------------------------------
id | integer | not null default (nextval('test_id_seq'::regclass) * 10)
data | text |
***
... and it works as is supposed to do.
But after simple dump&restore procedure (I use 'pg_dump -U ns testseq
> ~/testseq.dump' and then in psql - '\i /home/ns/testseq.dump') we
have:
***
testseq=# \d test;
Table "public.test"
Column | Type | Modifiers
--------+---------+---------------------------------------------------
id | integer | not null default nextval('test_id_seq'::regclass)
data | text |
***
In dump file I see SERIAL as the type for test.id ...
So, the question #2 (the main Q): why pg_dump didn't dump my expression?
For me as end-user this is very-very strange and I consider it as
'gotcha' of PosgreSQL.
--
Best regards,
Nikolay
