Skip site navigation (1) Skip section navigation (2)

Re: return column id from insert

From: Rory Campbell-Lange <mail(at)campbell-lange(dot)net>
To: Josh Berkus <josh(at)agliodbs(dot)com>
Cc: Rory Campbell-Lange <mail(at)campbell-lange(dot)net>,pgsql-novice(at)postgresql(dot)org
Subject: Re: return column id from insert
Date: 2002-11-12 17:51:33
Message-ID: 20021112175133.GA18608@campbell-lange.net (view raw or flat)
Thread:
Lists: pgsql-novice
Hi Josh

Thanks very much for your email. Sorry about the column id faux pas.

On 12/11/02, Josh Berkus (josh(at)agliodbs(dot)com) wrote:
> > The issue is this:
> > I need to make a database insert and then make an image with the
> > resulting column id number, which is provided by a sequence. 
> 
> I think you mean "primary key" or "row id", not "column id".   There is
> no "column id".

Primary key is what I meant.
> 
> > This has to be a single action, otherwise I could fall into a race
> > condition with another insert going on concurrently.
> 
> No, you can't.  PostgreSQL sequences are guarenteed 100% unique
> regardless of concurrent updates.   Simply put:
> 
> BEGIN TRANSACTION;
> INSERT INTO some_table ( columns )
> VALUES ( values);
> SELECT CURRVAL('some_table_seq');
> COMMIT TRANSACTION;
> 
> Easy, no?

Yeah. I guess what I meant to say was that if the cgi program grabbed an
id and then tried to use that to make an explicit record or expected
that to be the value of a subsequently inserted record there is no
guarantee of that id being accurate. Using transactions eliminates this
problem.
> 
> Now please go read the online docs, or better yet, buy an introductory
> PostgreSQL book.   There's a lot to learn.

Thanks. I'll have a good look at my Momjian and DBI books again tonight.

Cheers
Rory

-- 
Rory Campbell-Lange 
<rory(at)campbell-lange(dot)net>
<www.campbell-lange.net>

In response to

pgsql-novice by date

Next:From: Devinder K RajputDate: 2002-11-12 19:33:31
Subject: Re: SQL statement to copy a result set to create
Previous:From: Josh BerkusDate: 2002-11-12 17:37:07
Subject: Re: return column id from insert

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group