Is there a solution for "SELECT OR INSERT"

From: "Stefan Becker" <stefan(at)yukonho(dot)de>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: Is there a solution for "SELECT OR INSERT"
Date: 2010-11-28 23:30:47
Message-ID: 000001cb8f54$4a9e3c20$dfdab460$@de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

given a simply table table like this:

create table MyTabl
(id serial,
lname varchar,
primary key (id)
)

I am trying to return the Primary-Key for a given
row regardless of the wanted row already being
in the table.

using something like:

select coalesce
(
(select id from MyTabl where lname='wanted'),
(insert into MyTabl (lname) values ('wanted') returning id)
) as id;

the above doesn't allow the insert..returning even thow
a similar select statement would be okay.
Is there a way to get the ID row OR create a new one in
ONE single statement?

my best regards and thank-you
for your time....

Stefan

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Jorge Arenas 2010-11-29 05:38:48 subselect and left join not working?
Previous Message Mikhail V. Puzanov 2010-11-27 15:08:39 Re: atomic multi-threaded upsert