Thread: Simple Question??

Here's a question that maybe you can answer easily...

If I am using a sequence (seq_a) in table_a

...then "create table_b as select * from table_a"

...does table_b start using that sequence (seq_a), starting at the point
where table_a left off??

I have a 1GB table that I want to optimize by reordering it, and I will want
the new table to use the same sequence to generate the primary key. I will
then drop the original table and rename the table_b to table_a.

Any clues?

Tim Perdue
tim@perdue.net

"archiver" <archiver@db.geocrawler.com> writes:
> If I am using a sequence (seq_a) in table_a
> ...then "create table_b as select * from table_a"
> ...does table_b start using that sequence (seq_a), starting at the point
> where table_a left off??

I assume you are wondering about what happens to a column declared as
SERIAL in table_a?

I have not experimented (and really, the most reliable way to check this
sort of thing is to experiment on a small test case).  But remember that
what SERIAL really means is INT4 DEFAULT NEXTVAL('some_sequence')
(plus the system goes off and creates the sequence for you).  My guess
is that the result of the SELECT will simply show the column type as
INT4, which means that table_b will have a plain INT4 column.  There's
no way that I know of for information about column constraints and
default values to propagate through the result of a SELECT.

So if you do it that way, I'd expect table_b to reproduce the current
values of the serial-number column from table_a, but it'd just be a
static snapshot; the column wouldn't act like a serial number anymore.

What you should do instead is to explicitly create table_b using the
properties you want, which in particular would mean saying INT4 DEFAULT
NEXTVAL('table_a's_sequence') rather than just SERIAL for this column.
That way table_b will continue to generate serial numbers from the
same sequence table_a was using.  Then transfer the data with something
like INSERT INTO ... SELECT FROM.  (Personally I'd probably use a COPY
out followed by COPY back in; I just like having backup copies of info
outside the database when I'm making major rearrangements...)

> I have a 1GB table that I want to optimize by reordering it, and I will want
> the new table to use the same sequence to generate the primary key. I will
> then drop the original table and rename the table_b to table_a.

Just out of curiosity, what makes you think that reordering the table
will "optimize" anything?  I have a feeling that what you really need to
be worrying about is creating appropriate indexes, or some such.

            regards, tom lane

"archiver" <archiver@db.geocrawler.com> writes:
> If I am using a sequence (seq_a) in table_a
> ...then "create table_b as select * from table_a"
> ...does table_b start using that sequence (seq_a), starting at the point
> where table_a left off??

I assume you are wondering about what happens to a column declared as
SERIAL in table_a?

I have not experimented (and really, the most reliable way to check this
sort of thing is to experiment on a small test case).  But remember that
what SERIAL really means is INT4 DEFAULT NEXTVAL('some_sequence')
(plus the system goes off and creates the sequence for you).  My guess
is that the result of the SELECT will simply show the column type as
INT4, which means that table_b will have a plain INT4 column.  There's
no way that I know of for information about column constraints and
default values to propagate through the result of a SELECT.

So if you do it that way, I'd expect table_b to reproduce the current
values of the serial-number column from table_a, but it'd just be a
static snapshot; the column wouldn't act like a serial number anymore.

What you should do instead is to explicitly create table_b using the
properties you want, which in particular would mean saying INT4 DEFAULT
NEXTVAL('table_a's_sequence') rather than just SERIAL for this column.
That way table_b will continue to generate serial numbers from the
same sequence table_a was using.  Then transfer the data with something
like INSERT INTO ... SELECT FROM.  (Personally I'd probably use a COPY
out followed by COPY back in; I just like having backup copies of info
outside the database when I'm making major rearrangements...)

> I have a 1GB table that I want to optimize by reordering it, and I will want
> the new table to use the same sequence to generate the primary key. I will
> then drop the original table and rename the table_b to table_a.

Just out of curiosity, what makes you think that reordering the table
will "optimize" anything?  I have a feeling that what you really need to
be worrying about is creating appropriate indexes, or some such.

            regards, tom lane