pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedINSERT [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