CODEWITHSUNDEEP
sqlpostgresqlinsert
Nikhil Agarwal
Nikhil Agarwal

Reputation: 88

How to insert distinct and semi-distinct entries into table?

I have a table table1 having 2 columns user_name and book_name.

Now I want to insert entries such that the same user cannot have the same book name in another entry.

I was trying to do:

INSERT INTO table1(user_name, book_name) VALUES('random_user', 'random_book')
ON CONFLICT (user_name, book_name ) DO NOTHING;

But this was returning me a Postgres error 42P10.

Also, I want to know about how to enter entries in a table where it's one column may be the same but others should be different. For example, A table having three fields user_name, book_name, quantity in which a user can have the same book in different entries only if its quantity is different.

Upvotes: 2

Views: 39

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

You probably just need to define the unique constraint or index:

create table table1 (
    user_name text,
    book_name text,
    unique (user_name, book_name)
);

INSERT INTO table1(user_name, book_name)
    VALUES('random_user', 'random_book')
    ON CONFLICT (user_name, book_name ) DO NOTHING;

Here is a db<>fiddle.

Of course, this assumes that you are using Postgres 9.5 or later.

Upvotes: 1

Related Questions