Thread: 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; --