Please pardon any duplication, but the previous two posts do not
appear to have made it to the list.
----- Forwarded message -----
Subject: How to append records into a file which has serial unique IDs?
To: pgsql-novice@postgresql.org
Date: Wed, 5 Apr 2006 00:37:48 -0700 (PDT)
X-Mailer: ELM [version 2.5 PL8]
Suppose I have a table "foo" with columns a b and c. A is a serial unique key.
What's a good way to copy a record to a temp table, and then append it
back in as a new record, automatically assigning a new serial key?
It appears that:
create temp table bar as select (*) from foo;
insert into foo (*) select (*) from bar;
isn't the way to do it.
If I explicitly name fields in the CREATE, INSERT and SELECT
clauses (so as to exclude field a), then I have to know and
maintain a list of each and every field and this becomes a
maintenance headache any time the column names change in foo.
I could live with a solution that required me to know just
the name of the one field I want to exclude, such as
create temp table temp as select (* except a) from foo;
insert into foo (* except a) select (* except a) from bar;
I have done a fair amount of old-school DOS database stuff, but
am rather new to SQL, so if this is a simple question, then that's
why I'm asking on the novice list! :)
But surely, there must be a nice general solution to this, since
it would seem to happen in a *large* number of master/transaction
situations, where a ledger of transactions is posted, and then the
posted transactions are archived into a history file, before clearing
the batch to make way for the next batch. Suppose both the batch
file and the history file use unique serial IDs, and each table's
IDs are numbered separately -- one has to be able to append all fields
except the serial from a number of rows, while simultaneously
generating new serials for each of the rows appended....
Once I figure out how, I would create a php subroutine with
parameterized table names and field name(s) to exclude on insert, and
then be able to easily append serialized records with no other
knowledge of the underlying structure of the specific table being
operated upon.
I just can't find a clean way to do it. Suggestions are appreciated.
Thanks, and regards from Portland,
Jim Long
----- End forwarded message -----
