Insert without duplicates ?

[bluegroper]

How to insert a record making sure there's no duplicate ?

INSERT INTO invoices (invoiceid, billed) VALUES ('12345', 'TRUE')
WHERE NOT EXISTS (SELECT * FROM invoices WHERE invoices.invoiceid = '12345')

I keeps getting an error message and can't figure out how to do this ?
Syntax error at or near "WHERE"

Much TIA's

 

[bluegroper]

Or this variation on the same theme

IF EXISTS (SELECT * FROM invoices WHERE invoiceid = '12345')
UPDATE invoices SET billed = 'TRUE' WHERE invoiceid = '12345'
ELSE
INSERT INTO invoices (invoiceid, billed) VALUES ('12345', 'TRUE')
END IF

:-(

 

[feherke]

Hi

Maybe :

[b]insert[/b] [b]into[/b] invoices (invoiceid,billed)
[b]select[/b] [i]'12345'[/i],[i]'true'[/i] [b]where[/b] [b]not[/b] [b]exists[/b] ([b]select[/b] 1 [b]from[/b] invoices [b]where[/b] invoiceid=[i]'12345'[/i])

Regarding the second form, needs a stored procedure :

[b]create[/b] [b]or[/b] [b]replace[/b] [b]function[/b] putinvoice(varchar(10),varchar(5))
[b]returns[/b] boolean [b]as[/b] '
[b]declare[/b]
  _invoiceid [b]alias[/b] [b]for[/b] $1;
  _billed [b]alias[/b] [b]for[/b] $2;
[b]begin[/b]
  [b]update[/b] invoices [b]set[/b] billed=_billed [b]where[/b] invoiceid=_invoiceid;
  [b]if[/b] [b]not[/b] [b]found[/b] [b]then[/b]
    [b]insert[/b] [b]into[/b] invoices (invoiceid,billed) [b]values[/b] (_invoiceid,_billed);
  [b]end[/b] [b]if[/b];
  [b]return[/b] true;
[b]end[/b];
' [b]language[/b] plpgsql;

Feherke.

http://rootshell.be/~feherke/

 

[bluegroper]

Still trying to get this working.

DROP TABLE invoices;
CREATE TABLE invoices (
	invoiceid VARCHAR(5), 
	billed BOOLEAN,
	CONSTRAINT idkey PRIMARY KEY (invoiceid)
);
INSERT INTO invoices (invoiceid, billed)
VALUES ('12345', 'true') WHERE NOT EXIST (SELECT 1 FROM INVOICES WHERE invoiceid = '12345');

ERROR: syntax error at or near "WHERE"
SQL state: 42601
Character: 213

Grateful for any more clues.
Hoping to keep this simple rather than use stored procedure.

 

[bluegroper]

Sorry, I meant SELECT not VALUES

INSERT INTO invoices (invoiceid, billed)
SELECT ('12345', 'true') WHERE NOT EXIST (SELECT 1 FROM INVOICES WHERE invoiceid = '12345');

throws error as above.
TIA's for clues

 

[bluegroper]

Ok, finally got it.
For completeness, and maybe to help anybuddy else who hits this problem, this seems to work reliably.
Hope others can avoid repeating my mistake, just make new ones.

insert into invoices (invoiceid, billed) (select '12345' as invoiceid, 'TRUE' as billed where
not exists (select 1 from invoices where invoiceid='12345'));