I have two tables. The first will contain recent records that are used on a
daily basis. The other table is identical but only exists to archive old
entries from the first table. I would like to know if there is a way in
postgreSQL to select all the old records from the first table and
automatically drop them into the second table.

I messed around with the 'INSERT INTO' SQL command, but this assumes that
the table you are 'selecting into' does not exist and you want to create
that table. My problem is that the table exists and I just want to append
the information to the archive table.

Is there a simple way to do this in SQL with PostgreSQL or am I going to
have to bring my result set out into a scripting language manipulate it
there and them do a series of INSERTS to update the archive table? This
option seems slow but perhaps the only option open to me at this point.

Any sugestions?

Alan



---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

"Alan T. Miller" <amiller (AT) hollywood101 (DOT) com> writes:
I think you are looking for INSERT ... SELECT. See the INSERT man page
or any book about SQL.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

Thanks, just what I needed

Alan


----- Original Message -----
From: "Tom Lane" <tgl (AT) sss (DOT) pgh.pa.us>
To: "Alan T. Miller" <amiller (AT) hollywood101 (DOT) com>
Cc: <pgsql-novice (AT) postgresql (DOT) org>
Sent: Tuesday, January 27, 2004 7:28 PM
Subject: Re: [NOVICE] SELECT INTO but append not create new table - How?




---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly