Thread: What is bad in this query ?

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' .




Thanks 
-- 
Rado Petrik <r.p@szm.sk>

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

On Fri, 2003-05-02 at 09:21, 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;

The INSERT is bad.

Try something like:
INSERT INTO user (name)SELECT name FROM user WHERE name = 'rado';


--
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

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