From: | "Andrus" <kobruleht2(at)hot(dot)ee> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Using autogenerated primary key in transaction |
Date: | 2010-01-02 16:40:11 |
Message-ID: | 3083D1B79E6F431CB25707E5AAC97DB6@andrusnotebook |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
INSERT generates SERIAL primary key column value automatically.
How to save this value for use in same transaction ?
Only way I have found is to create temporary table.
To emulate real database table for testcase I used command:
create temp table test ( vmnr serial primary key, name text ) on commit
drop;
Then I tried
CREATE TEMP TABLE savedkey ON COMMIT DROP AS
INSERT INTO test ( name)
select 'Scott'
RETURNING vmnr;
and
CREATE TEMP TABLE savedkey ON COMMIT DROP AS
SELECT * FROM (INSERT INTO test ( name)
select 'Scott'
RETURNING vmnr) dummy;
but got syntax errors.
lastval() in infected by triggers and nextval() requires hard-coded sequence
name.
How to get autogenerated primary key for >=8.0 servers ?
If this is not possible then at least for
PostgreSql versions which support ON COMMIT DROP and RETURNING clauses
?
Andrus.
From | Date | Subject | |
---|---|---|---|
Next Message | Andrus | 2010-01-02 16:46:40 | SELECT does not find table created by itself |
Previous Message | Dimitri Fontaine | 2010-01-02 10:44:42 | Re: pg_dump excluding tables content but not table schema |