How to prevent duplicate insert with insert return in postgres?

Question

I'm trying to create some rules for my DB to prevent duplicate inserts, but since we're also hibernate, the .save() is called insert xxx RETURNING *

I can't seem to make the insert rule play well with the insert returning.

CREATE TABLE test ( aaa int, bbb int);

insert into test VALUES(1, 2);

CREATE RULE "testRule1" AS ON INSERT TO  test
  WHERE EXISTS(SELECT 1 FROM test 
                WHERE (aaa, bbb)=(NEW.aaa, NEW.bbb))
  DO INSTEAD NOTHING;

If I try to insert with just 1 conditional rule

insert into test VALUES(1, 2);
ERROR:  cannot perform INSERT RETURNING on relation "test"
HINT:  You need an unconditional ON INSERT DO INSTEAD rule with a RETURNING clause.

CREATE RULE "testRule2" AS ON INSERT TO  test
  DO INSTEAD INSERT INTO test VALUES(new.*)
  RETURNING test.*;

If I try to insert with both conditional and unconditional rules

insert into test VALUES(1, 2);
ERROR:  infinite recursion detected in rules for relation "test"

Is this impossible to achieve with my setup?

Answer 1

The correct way to avoid inserting duplicates is to define the fields as UNIQUE (or PRIMARY KEY)

CREATE TABLE Test (
aaa integer,
bbb integer,
val text, /* The reason why I added this field is explained later */
UNIQUE (aaa,bbb)
)

Inserting will fail with an error code if a tuple already exists (receiving error codes from a database is a good thing, as I explained recently here).

If it is unacceptable for you to have errors or if you want to insert multiple records in a single insert without having to care which one violates the UNIQUE constraint, the correct syntax is to use the ON CONFLICT clause.

INSERT INTO Test values (1,1,'Some value')
ON CONFLICT DO NOTHING

Basically the first record with a unique tuple will be inserted. That is compatible even if the query itself tries to insert duplicates such as the case below:

INSERT INTO Test values (2,2,'Will be inserted'), (2,2,'Will not be inserted')
ON CONFLICT DO NOTHING

Of course, this will also allow you to have the RETURNING clause in the same query.