Question 257678 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Is it possible to make a unique index with id when active= 1?

I want to ask you it is possible to unique fields when a column is 1 ?

I have a table sales defined like this:

CREATE TABLE sales 
(
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    p_id UUID REFERENCES product(id),
    percentage SMALLINT,
    expires timestamp NOT NULL,
    active SMALLINT DEFAULT 1,
    created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
)

I want to say when p_id exists and active is 1, then I want to avoid that anyone can insert again this.

When active is 0, then it possible to insert product id with active = 1

Tags:

2

Answers


  1. 0

    If you want to create an unique index based on the combination if p_id and active you could do something like:

    CREATE UNIQUE INDEX name_of_the_index
ON sales (p_id)
WHERE active = 1;

    With that index no two rows with the same p_id and active = 1 can be inserted into your sales table.

    Check out the doc here: https://www.postgresql.org/docs/current/indexes-unique.html

    Login or Signup to reply.
  2. 0

    I want to say when p_id exists and active is 1 then I want to avoid that anyone can insert again this. When active is 0 then it possible to insert product id with active = 1

    Yes, it is possible.

    1/ You can create unique indexes. It can also ensure that no two rows in a table can have the same value in the specified column(s)

    CREATE UNIQUE INDEX indexname 
ON sales (p_id) 
WHERE active = 1;

    You can check out the doc here: https://www.postgresql.org/docs/current/indexes-partial.html

    2/ You can create unique constraint. It can ensure that no two rows in a table can have the same value in the specified column(s):

    ALTER TABLE sales
ADD CONSTRAINT UQ_sales_p_id UNIQUE (p_id);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search