Re: How to append records into a file which has serial - Mailing list pgsql-novice

On Sat, 8 Apr 2006 08:09:32 -0700
James Long <pgsql-novice@museum.rain.com> threw this fish to the penguins:

> 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.

This worked for me:

newschm3=> create table foo(x text,y text,a serial);
newschm3=> insert into foo (x,y) values ('the','red');
INSERT 0 1
newschm3=> insert into foo (x,y) values ('rain','green');
INSERT 0 1
newschm3=> insert into foo (x,y) values ('in','blue');
INSERT 0 1
newschm3=> create temp table bar as select * from foo where y='red';
SELECT
newschm3=> alter table bar drop column a;
newschm3=> insert into foo select * from bar;
INSERT 0 1

newschm3=> select * from foo;
  x   |   y   |  a
------+-------+----
 the  | red   |  1
 rain | green |  2
 in   | blue  |  3
 the  | red   |  4

You don't need to know the field names "x" and "y", just the
serial field name "a".

Is this what you meant?

[BTW, it's always good practice to include the exact version of postgres
and your platform in questions, to save extra round-trips like "Are you
using version 8.1.2.3 of postgres?  It has a bug that makes your
fingernails turn green...".]

-- George Young

> 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 -----
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org
>


--
"Are the gods not just?"  "Oh no, child.
What would become of us if they were?" (CSL)