Обсуждение: INSERT [IGNORE] INTO TABLE

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

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

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 createsuch procedure. I would appreciate Your help.
                        Petr Jezek

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

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 FOUNDTHEN     RETURN ''f'';
ELSEINSERT INTO USERS(username,password)    values(USR,PWORD);    RETURN ''t'';
END IF;

END;'
LANGUAGE 'plpgsql';

Regards.
Najm

>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
onein 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,
missingnon-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

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,
Pennsylvania19026