How to set Ignore Duplicate Key in Postgresql while table creation itself

Question

I am creating a table in Postgresql 9.5 where id is the primary key. While inserting rows in the table if anyone tries to insert duplicate id, i want it to get ignored instead of raising exception. Is there any way such that i can set this while table creation itself that duplicate entries get ignored.

There are many techniques to resolve duplicate insertion issue while writing insertion query i.e. using ON CONFLICT DO NOTHING, or using WHERE EXISTS clause etc. But i want to handle this at table creation end so that the person writing insertion query doesn't need to bother any.

Creating RULE is one of the possible solution. Are there other possible solutions? Maybe something like this:

`CREATE TABLE dbo.foo (bar int PRIMARY KEY WITH (FILLFACTOR=90, IGNORE_DUP_KEY = ON))`

Although exact this statement doesn't work on Postgresql 9.5 on my machine.

AFAIK, there's no such possibility. I don't think I would like the person or process inserting data not to be notified in any way when its data is simply ignored or discarded. — joanolo
– joanolo, Commented Aug 1, 2017 at 8:20

Vao Tsun · Accepted Answer · 2017-08-01 08:19:16Z

1

add a trigger before insert or rule on insert do instead - otherwise has to be handled by inserting query. both solutions will require more resources on each insert.

Alternative way to use function with arguments for insert, that will check for duplicates, so end users will use function instead of INSERT statement.

WHERE EXISTS sub-query is not atomic btw - so you can still have exception after check...

9.5 ON CONFLICT DO NOTHING is the best solution still

answered Aug 1, 2017 at 8:19

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to set Ignore Duplicate Key in Postgresql while table creation itself

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related