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: (view raw, whole thread or download thread mbox)
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:
> INSERT INTO some_table ( columns )
> VALUES ( values);
> SELECT CURRVAL('some_table_seq');
> 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
> 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.


Rory Campbell-Lange 

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-2017 The PostgreSQL Global Development Group