pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
INSERT [IGNORE] INTO TABLE
7+ messages / 5 participants
[nested] [flat]

* INSERT [IGNORE] INTO TABLE
@ 2000-10-31 16:05  Petr Jezek <jez20um@axpsu.fpf.slu.cz>
  0 siblings, 1 reply; 7+ messages in thread

From: Petr Jezek @ 2000-10-31 16:05 UTC (permalink / raw)
  To: pgsql-sql


	If I try to insert some row which in the table already exist the
	postgre don't insert it and tell some error. Everything ok.
	I'll insert only if there is not the same (same key) record,
	else don't insert and don't tell me errors about it.
	In MySQL is a switch [IGNORE]. 

				Thanx for help

						Petr Jezek




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* RE: INSERT [IGNORE] INTO TABLE
@ 2000-10-31 16:31  Edmar Wiggers <edmar@brasmap.com>
  parent: Petr Jezek <jez20um@axpsu.fpf.slu.cz>
  0 siblings, 2 replies; 7+ messages in thread

From: Edmar Wiggers @ 2000-10-31 16:31 UTC (permalink / raw)
  To: Petr Jezek <jez20um@axpsu.fpf.slu.cz>; pgsql-sql

> 	If I try to insert some row which in the table already exist the
> 	postgre don't insert it and tell some error. Everything ok.
> 	I'll insert only if there is not the same (same key) record,
> 	else don't insert and don't tell me errors about it.
> 	In MySQL is a switch [IGNORE].

Not a good feature for me. What do you do if the record already exists?
Update it?

Check existence and then insert or update. If you want, I guess you could
wrap that inside a stored procedure.




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* RE: INSERT [IGNORE] INTO TABLE
@ 2000-10-31 17:04  Petr Jezek <jez20um@axpsu.fpf.slu.cz>
  parent: Edmar Wiggers <edmar@brasmap.com>
  1 sibling, 1 reply; 7+ messages in thread

From: Petr Jezek @ 2000-10-31 17:04 UTC (permalink / raw)
  To: Edmar Wiggers <edmar@brasmap.com>; +Cc: pgsql-sql

On Tue, 31 Oct 2000, Edmar Wiggers wrote:

> > 	If I try to insert some row which in the table already exist the
> > 	postgre don't insert it and tell some error. Everything ok.
> > 	I'll insert only if there is not the same (same key) record,
> > 	else don't insert and don't tell me errors about it.
> > 	In MySQL is a switch [IGNORE].
> 
> Not a good feature for me. What do you do if the record already exists?
> Update it?
> 
> Check existence and then insert or update. If you want, I guess you could
> wrap that inside a stored procedure.
> 
	Well, that's a good idea :-) but I'm not sure about how to create
	such procedure. I would appreciate Your help.

							Petr Jezek




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* RE: INSERT [IGNORE] INTO TABLE
@ 2000-10-31 17:19  Edmar Wiggers <edmar@brasmap.com>
  parent: Petr Jezek <jez20um@axpsu.fpf.slu.cz>
  0 siblings, 0 replies; 7+ messages in thread

From: Edmar Wiggers @ 2000-10-31 17:19 UTC (permalink / raw)
  To: Petr Jezek <jez20um@axpsu.fpf.slu.cz>; +Cc: pgsql-sql

> > Check existence and then insert or update. If you want, I guess
> you could
> > wrap that inside a stored procedure.
> >
> 	Well, that's a good idea :-) but I'm not sure about how to create
> 	such procedure. I would appreciate Your help.

Quite frankly, I'm not sure either. I'm just starting with PostgreSQL, what
I really know is Oracle. I believe it would be something like

...
[declare routine, with one argument per record field]
...
if exists(select 1 from table_name where [field_key] = [arg_key]) then
  update table_name
   set field1 = arg1,...,fieldn = argn
   where field_key = arg_key;
else
  insert into table_name (field1,...,fieldn) values (arg1,...,argn);
end if;
...

The syntax is probably off, but that's the idea. I used that several times
on oracle.




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* RE: INSERT [IGNORE] INTO TABLE
@ 2000-10-31 19:42  K Parker <kparker@eudoramail.com>
  0 siblings, 0 replies; 7+ messages in thread

From: K Parker @ 2000-10-31 19:42 UTC (permalink / raw)
  To: pgsql-sql

>Not a good feature for me.

Me neither, but...

>Check existence and then insert or update.

At least once a week I seem to be responding to this exact same suggestion.  Somebody _please_ tell me if I'm the only one in the whole world who worries about race conditions?  What's wrong with just making sure there's a proper unique key, and then just inserting the row?  The returned error message will tell you if a failure is due to duplicate key, missing non-null column, or an actual disaster.



Join 18 million Eudora users by signing up for a free Eudora Web-Mail account at http://www.eudoramail.com



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: INSERT [IGNORE] INTO TABLE
@ 2000-11-01 17:56  Najm Hashmi <najm@mondo-live.com>
  parent: Edmar Wiggers <edmar@brasmap.com>
  1 sibling, 1 reply; 7+ messages in thread

From: Najm Hashmi @ 2000-11-01 17:56 UTC (permalink / raw)
  To: Edmar Wiggers <edmar@brasmap.com>; pgsql-sql

Edmar Wiggers wrote:

> >       If I try to insert some row which in the table already exist the
> >       postgre don't insert it and tell some error. Everything ok.
> >       I'll insert only if there is not the same (same key) record,
> >       else don't insert and don't tell me errors about it.
> >       In MySQL is a switch [IGNORE].
>
> Not a good feature for me. What do you do if the record already exists?
> Update it?
>
> Check existence and then insert or update. If you want, I guess you could
> wrap that inside a stored procedure.

Hi, here is an example of using  function using pl/pgsql for inserting and
checking whether an instance exists or not.....
CREATE FUNCTION add_new_user(text,text) RETURNS bool AS' DECLARE
oldUser RECORD;
USR ALIAS FOR $1;
PWORD ALIAS FOR $2;

BEGIN
SELECT INTO oldUser *
FROM users
where username=USR AND password= PWORD;
IF FOUND
 THEN
      RETURN ''f'';
ELSE
 INSERT INTO USERS(username,password)
     values(USR,PWORD);
     RETURN ''t'';
END IF;

END;'
LANGUAGE 'plpgsql';

Regards.
Najm




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: INSERT [IGNORE] INTO TABLE
@ 2000-11-02 03:38  Bruce Momjian <pgman@candle.pha.pa.us>
  parent: Najm Hashmi <najm@mondo-live.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Bruce Momjian @ 2000-11-02 03:38 UTC (permalink / raw)
  To: Najm Hashmi <najm@mondo-live.com>; +Cc: Edmar Wiggers <edmar@brasmap.com>; pgsql-sql

The proper fix for this is subtransactions, which we will have someday. 
You do the INSERT in a subtransaction.


> Edmar Wiggers wrote:
> 
> > >       If I try to insert some row which in the table already exist the
> > >       postgre don't insert it and tell some error. Everything ok.
> > >       I'll insert only if there is not the same (same key) record,
> > >       else don't insert and don't tell me errors about it.
> > >       In MySQL is a switch [IGNORE].
> >
> > Not a good feature for me. What do you do if the record already exists?
> > Update it?
> >
> > Check existence and then insert or update. If you want, I guess you could
> > wrap that inside a stored procedure.
> 
> Hi, here is an example of using  function using pl/pgsql for inserting and
> checking whether an instance exists or not.....
> CREATE FUNCTION add_new_user(text,text) RETURNS bool AS' DECLARE
> oldUser RECORD;
> USR ALIAS FOR $1;
> PWORD ALIAS FOR $2;
> 
> BEGIN
> SELECT INTO oldUser *
> FROM users
> where username=USR AND password= PWORD;
> IF FOUND
>  THEN
>       RETURN ''f'';
> ELSE
>  INSERT INTO USERS(username,password)
>      values(USR,PWORD);
>      RETURN ''t'';
> END IF;
> 
> END;'
> LANGUAGE 'plpgsql';
> 
> Regards.
> Najm
> 
> 


-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026



^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2000-11-02 03:38 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-10-31 16:05 INSERT [IGNORE] INTO TABLE Petr Jezek <jez20um@axpsu.fpf.slu.cz>
2000-10-31 16:31 ` Edmar Wiggers <edmar@brasmap.com>
2000-10-31 17:04   ` Petr Jezek <jez20um@axpsu.fpf.slu.cz>
2000-10-31 17:19     ` Edmar Wiggers <edmar@brasmap.com>
2000-11-01 17:56   ` Najm Hashmi <najm@mondo-live.com>
2000-11-02 03:38     ` Bruce Momjian <pgman@candle.pha.pa.us>
2000-10-31 19:42 RE: INSERT [IGNORE] INTO TABLE K Parker <kparker@eudoramail.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