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@matrix.gatewaynet.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@matrix.gatewaynet.com
>         mantzios@softlab.ece.ntua.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