Обсуждение: Newbie wonder...

Please, bear with me, as this is my first post here.

(1) I have a 2 table database, result of a conversion from Access. This has
been made by an amateur, as one of the tables should be at least 3 related
tables, bunch of redundant data,and the other one 2.

I know I could create a table as the result of a request, so that I could
isolate these redundant data, but what I don't know is how I would in the
same time update the original table to put the ID of the matching ROW
number in the newly created table, instead of the redundant data ?

Should I create a stored procedure for that, and if yes, how ? Or should I
do that in 2 passes, sequentially ?

(2) How should I go to create a sequence for an existing table? For all
futures data entry, after this conversion, I want the unique ID for each
row to come from a sequence, but if I know how to create a table using
serial, I am not sure how to modify one for this.

Thanks,
Bernard

Bernard,

If you are simply doing a one-time convert of an old database schema to a 
new one, simply load the old tables into postgres and then use SQL commands 
to insert the data into the new tables.  For a sequence on the "existing 
table," you can do as above and load the old table or just use copy with the 
column names of all columns except the column with the serial values--these 
will be auto-incremented.

Sean

----- Original Message ----- 
From: "Bernard Grosperrin" <bernard@bgsoftfactory.com>
To: <pgsql-sql@postgresql.org>
Sent: Tuesday, March 08, 2005 6:25 PM
Subject: [SQL] Newbie wonder...


> Please, bear with me, as this is my first post here.
>
> (1) I have a 2 table database, result of a conversion from Access. This 
> has
> been made by an amateur, as one of the tables should be at least 3 related
> tables, bunch of redundant data,and the other one 2.
>
> I know I could create a table as the result of a request, so that I could
> isolate these redundant data, but what I don't know is how I would in the
> same time update the original table to put the ID of the matching ROW
> number in the newly created table, instead of the redundant data ?
>
> Should I create a stored procedure for that, and if yes, how ? Or should I
> do that in 2 passes, sequentially ?
>
> (2) How should I go to create a sequence for an existing table? For all
> futures data entry, after this conversion, I want the unique ID for each
> row to come from a sequence, but if I know how to create a table using
> serial, I am not sure how to modify one for this.
>
> Thanks,
> Bernard
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>               http://archives.postgresql.org
> 

If you want to add a SERIAL field to an existing table, create a sequence  
and then create an integer field with default nextval(seq) and postgres  
will fill it automatically. The order in which it will fill it is not  
guaranteed though !
However, you might also like to de-dupe your data once it's in the  
additional tables, thus you might need more complicated measures.


> (2) How should I go to create a sequence for an existing table? For all
> futures data entry, after this conversion, I want the unique ID for each
> row to come from a sequence, but if I know how to create a table using
> serial, I am not sure how to modify one for this.
>
> Thanks,
> Bernard
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org
>

On Mon, 2005-03-14 at 10:02 +0100, PFC wrote:
>     If you want to add a SERIAL field to an existing table, create a sequence  
> and then create an integer field with default nextval(seq) and postgres  
> will fill it automatically. The order in which it will fill it is not  
> guaranteed though !

With 8.0, Alter Table supports most complicated commands including the
addition of SERIAL directly.

alter table abc add column bob serial NOT NULL UNIQUE;

--