Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [206.210.65.6]) by hub.org (8.9.2/8.9.1) with ESMTP id NAA87635 for ; Sun, 28 Feb 1999 13:20:32 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id NAA07461 for ; Sun, 28 Feb 1999 13:20:01 -0500 (EST) To: pgsql-sql@hub.org Subject: Re: Simple Question?? In-reply-to: Your message of Sat, 27 Feb 1999 23:00:33 -0500 (EST) <199902280400.XAA13644@hub.org> Date: Sun, 28 Feb 1999 13:20:01 -0500 Message-ID: <7459.920226001@sss.pgh.pa.us> From: Tom Lane "archiver" 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