pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedNewbie wonder...
4+ messages / 4 participants
[nested] [flat]
* Newbie wonder...
@ 2005-03-08 23:25 Bernard Grosperrin <bernard@bgsoftfactory.com>
0 siblings, 2 replies; 4+ messages in thread
From: Bernard Grosperrin @ 2005-03-08 23:25 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Newbie wonder...
@ 2005-03-14 02:50 Sean Davis <sdavis2@mail.nih.gov>
parent: Bernard Grosperrin <bernard@bgsoftfactory.com>
1 sibling, 0 replies; 4+ messages in thread
From: Sean Davis @ 2005-03-14 02:50 UTC (permalink / raw)
To: bernard@bgsoftfactory.com; pgsql-sql
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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Newbie wonder...
@ 2005-03-14 09:02 PFC <lists@boutiquenumerique.com>
parent: Bernard Grosperrin <bernard@bgsoftfactory.com>
1 sibling, 1 reply; 4+ messages in thread
From: PFC @ 2005-03-14 09:02 UTC (permalink / raw)
To: bernard@bgsoftfactory.com; pgsql-sql
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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Newbie wonder...
@ 2005-03-14 13:56 Rod Taylor <pg@rbt.ca>
parent: PFC <lists@boutiquenumerique.com>
0 siblings, 0 replies; 4+ messages in thread
From: Rod Taylor @ 2005-03-14 13:56 UTC (permalink / raw)
To: PFC <lists@boutiquenumerique.com>; +Cc: pgsql-sql; bernard@bgsoftfactory.com
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;
--
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-03-14 13:56 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-08 23:25 Newbie wonder... Bernard Grosperrin <bernard@bgsoftfactory.com>
2005-03-14 02:50 ` Sean Davis <sdavis2@mail.nih.gov>
2005-03-14 09:02 ` PFC <lists@boutiquenumerique.com>
2005-03-14 13:56 ` Rod Taylor <pg@rbt.ca>
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