Re: What is bad in this query ?

You want to insert into "user" a user named "rado" only if there is not a user
named rado in existence already, right?

Method 1:

data=# CREATE TABLE user (
data(# name varchar(50),
data(# CONSTRAINT user_name_pkey PRIMARY KEY (name)
data(# ) WITH OIDS;
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'user_name_pkey'
for table 'user'
CREATE
data=# BEGIN;
BEGIN
data=# INSERT INTO user VALUES ('rado');
INSERT 183655716 1
data=# COMMIT;
COMMIT
data=# BEGIN;
BEGIN
data=# INSERT INTO user VALUES ('rado');
ERROR: Cannot insert a duplicate key into unique index user_name_pkey
data=# COMMIT;
COMMIT
data=# SELECT * FROM user;
name
------
rado
(1 row)

data=#

OR

Method 2 (after creating the table above and inserting 'rado'):

data=# INSERT INTO user (name) SELECT 'rado' FROM user WHERE name <> 'rado';
INSERT 0 0
adddata=# INSERT INTO user (name) SELECT 'odar' FROM user WHERE name <> 'odar';
INSERT 183655788 1

(Method 2 fails silently...)

HTH,

CG

--- Achilleus Mantzios <achill(at)matrix(dot)gatewaynet(dot)com> wrote:
> On 2 May 2003, Rado Petrik wrote:
>
> > Hi,
> >
> > What is bad in this query ?
> >
> > SELECT CASE
> > WHEN EXIST(SELECT * FROM user WHERE name=rado)
> > THEN (INSERT INTO user VALUES('rado'))
> > END;
> >
> > PostgreSQL return
> >
> > ERROR: parser: parse error at or near "INTO"
> >
> > table user have only one column 'name' .
>
> What do you want to do in the first place??
> Have N+1 rows with name='rado' if N>0 ???
>
> >
> >
> >
> >
> > Thanks
> >
> >
>
> --
> ==================================================================
> Achilleus Mantzios
> S/W Engineer
> IT dept
> Dynacom Tankers Mngmt
> Nikis 4, Glyfada
> Athens 16610
> Greece
> tel: +30-210-8981112
> fax: +30-210-8981877
> email: achill(at)matrix(dot)gatewaynet(dot)com
> mantzios(at)softlab(dot)ece(dot)ntua(dot)gr
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html

__________________________________
Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo.
http://search.yahoo.com