X-Original-To: pgsql-sql@postgresql.org
Received: from spampd.localdomain (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 481D047640D
	for <pgsql-sql@postgresql.org>; Fri,  2 May 2003 16:46:58 -0400 (EDT)
Received: from web13801.mail.yahoo.com (web13801.mail.yahoo.com
	[216.136.175.11])
	by postgresql.org (Postfix) with SMTP id 3E8AE476404
	for <pgsql-sql@postgresql.org>; Fri,  2 May 2003 16:46:53 -0400 (EDT)
Message-ID: <20030502204654.17011.qmail@web13801.mail.yahoo.com>
Received: from [216.173.173.66] by web13801.mail.yahoo.com via HTTP;
	Fri, 02 May 2003 13:46:54 PDT
Date: Fri, 2 May 2003 13:46:54 -0700 (PDT)
From: Chris Gamache <cgg007@yahoo.com>
Reply-To: cgg007@yahoo.com
Subject: Re: What is bad in this query ?
To: pgsql-sql@postgresql.org
In-Reply-To: <Pine.LNX.4.44.0305021705440.19233-100000@matrix.gatewaynet.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Spam-Status: No, hits=-24.7 required=5.0
	tests=BAYES_01,EMAIL_ATTRIBUTION,FROM_ENDS_IN_NUMS,IN_REP_TO,
	QUOTED_EMAIL_TEXT,REPLY_WITH_QUOTES,UPPERCASE_25_50
	autolearn=ham	version=2.50
X-Spam-Level: 
X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp)
X-Archive-Number: 200305/32
X-Sequence-Number: 13197

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