pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedWhat is bad in this query ?
4+ messages / 4 participants
[nested] [flat]
* What is bad in this query ?
@ 2003-05-02 13:21 Rado Petrik <r.p@szm.sk>
2003-05-02 14:32 ` Re: What is bad in this query ? Rod Taylor <rbt@rbt.ca>
2003-05-02 19:07 ` Re: What is bad in this query ? Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 2 replies; 4+ messages in thread
From: Rado Petrik @ 2003-05-02 13:21 UTC (permalink / raw)
To: pgsql-sql
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>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: What is bad in this query ?
2003-05-02 13:21 What is bad in this query ? Rado Petrik <r.p@szm.sk>
@ 2003-05-02 14:32 ` Rod Taylor <rbt@rbt.ca>
1 sibling, 0 replies; 4+ messages in thread
From: Rod Taylor @ 2003-05-02 14:32 UTC (permalink / raw)
To: Rado Petrik <r.p@szm.sk>; +Cc: pgsql-sql
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
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: What is bad in this query ?
2003-05-02 13:21 What is bad in this query ? Rado Petrik <r.p@szm.sk>
@ 2003-05-02 19:07 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2003-05-02 20:46 ` Re: What is bad in this query ? Chris Gamache <cgg007@yahoo.com>
1 sibling, 1 reply; 4+ messages in thread
From: Achilleus Mantzios @ 2003-05-02 19:07 UTC (permalink / raw)
To: Rado Petrik <r.p@szm.sk>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: What is bad in this query ?
2003-05-02 13:21 What is bad in this query ? Rado Petrik <r.p@szm.sk>
2003-05-02 19:07 ` Re: What is bad in this query ? Achilleus Mantzios <achill@matrix.gatewaynet.com>
@ 2003-05-02 20:46 ` Chris Gamache <cgg007@yahoo.com>
0 siblings, 0 replies; 4+ messages in thread
From: Chris Gamache @ 2003-05-02 20:46 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-05-02 20:46 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-05-02 13:21 What is bad in this query ? Rado Petrik <r.p@szm.sk>
2003-05-02 14:32 ` Rod Taylor <rbt@rbt.ca>
2003-05-02 19:07 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2003-05-02 20:46 ` Chris Gamache <cgg007@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox