From: | Nikolay Samokhvalov <samokhvalov(at)gmail(dot)com> |
---|---|
To: | PostgreSQL-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Sequences/defaults and pg_dump |
Date: | 2006-02-07 11:33:56 |
Message-ID: | e431ff4c0602070333n300d0d94t9e845706228559df@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Martijn van Oosterhout | 2006-02-07 11:56:49 | Re: Sequences/defaults and pg_dump |
Previous Message | Richard Huxton | 2006-02-07 10:39:05 | Re: strange query runtime |
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2006-02-07 11:40:53 | Re: Need Help In arrays |
Previous Message | Simon Riggs | 2006-02-07 09:40:37 | Re: [Bizgres-general] WAL bypass for INSERT, UPDATE and |