Re: How to append records into a file which has serial unique IDs?

On Apr 5, 2006, at 3:37 AM, James Long wrote:

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

Here is an example using a function rather than a temp table.

create table foo (
a serial primary key,
b text,
c integer
);

create or replace function duplicate_foo(rec foo)
returns integer as $$
begin
rec.a := nextval(pg_get_serial_sequence('foo', 'a'));
insert into foo values (rec.*);
return rec.a;
end;
$$ language plpgsql;

insert into foo values (default, 'one', 100);
insert into foo values (default, 'two', 200);

select * from foo;
a | b | c
---+-----+-----
1 | one | 100
2 | two | 200
(2 rows)

-- duplicate all rows, or you could use a where clause to limit the
duplicated rows:

select duplicate_foo(foo) from foo;
duplicate_foo
---------------
3
4

select * from foo;
a | b | c
---+-----+-----
1 | one | 100
2 | two | 200
3 | one | 100
4 | two | 200
(4 rows)

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL