Search Postgresql Archives

Sequences/defaults and pg_dump

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux