Postgres: difference between DEFAULT in CREATE TABLE and ALTER TABLE in database dump

Question

In database dump created with pg_dump, some tables have DEFAULTs in the CREATE TABLE statement, i.e.:

CREATE TABLE test (
  f1 integer DEFAULT nextval('test_f1_seq'::regclass) NOT NULL
);

But others have an additional ALTER statement:

ALTER TABLE ONLY test2 ALTER COLUMN f1 SET DEFAULT nextval('test2_f1_seq'::regclass);

What is the reason of this? All sequential fields were created with type SERIAL, but in the dump they look different, and I can't guess any rule for this.

any differences in whether or not these were set to primary keys? — Joe Love, Mar 05 '19 at 22:58
If some tables are used as FKs and some are not, that might be a difference.. creating the indexes after loading the data may be what it's trying to do for efficiency's sake — Joe Love, Mar 05 '19 at 22:59

score 0 · Answer 1 · answered Mar 06 '19 at 07:29

The difference must be that in the first case, the sequence is “owned” by the table column.

You can specify this dependency using the OWNED BY clause when you create a sequence. A sequence that is owned by a column will automatically be dropped when the column is.

If a sequence is implicitly created by using serial, it will be owned by the column.

Postgres: difference between DEFAULT in CREATE TABLE and ALTER TABLE in database dump

1 Answers1