From: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-sql@hub.org
Subject: Re: Simple Question??
Date: Sun, 28 Feb 1999 13:20:01 -0500
Message-ID: <7459.920226001@sss.pgh.pa.us> (raw)
In-Reply-To: <199902280400.XAA13644@hub.org>
"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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, pgsql-sql@hub.org
Subject: Re: Simple Question??
In-Reply-To: <7459.920226001@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox