Postgres Unique Constraint on two columns: Integer and Boolean

Question

I would like to find a way to store multiple addresses for one subject in my database, with only one default address per subject.

To cut a long story short, lets say we have a table:

CREATE TABLE test
(
  id integer NOT NULL,
  active boolean NOT NULL,
  CONSTRAINT pk_id PRIMARY KEY (id)
)

For each id in the table, there must be at most 1 true active value.

How could I achieve this?

Thanks.

Actually you don't have to do anything since id being the primary key makes it unique so there cannot be two rows with the same ID. — ThiefMaster
– ThiefMaster, Commented Jun 13, 2012 at 12:37
If you don't want id as primary then you will need to write a trigger: postgresql.org/docs/9.1/static/sql-createtrigger.html but thats probably not what you want. — Adam Gent
– Adam Gent, Commented Jun 13, 2012 at 12:51

kworr · Accepted Answer · 2018-03-08 14:29:40Z

18

Partial index is what you want:

create unique index some_index on test (id) where active;

As @ThiefMaster states primary index should be removed.

edited Mar 8, 2018 at 14:29

answered Jun 13, 2012 at 12:37

kworr

3,6941 gold badge23 silver badges34 bronze badges

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

3 Comments

greatkalu Over a year ago

Thanks. That is fine because my PK is the address PK in this table. This also works: CREATE UNIQUE INDEX some_index ON test USING btree (active, id) WHERE active;

kworr Over a year ago

You should notice that the only thing you have really changed in your query is adding active to index. While active is always true for all fields that a subject for indexing you just added some bloat.

kworr Over a year ago

PK is just a sum of UNIQUE and NOT NULL constraints.

Community · Accepted Answer · 2020-06-20 09:12:55Z

4

In this Question’s case it's not needed as explained previously.

But FYI, you can set constraints to 2 or more (non-PrimaryKey) columns with different types. For example:

ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (integer_column, boolean_column);

For more info, see the Postgres doc for Constraints, the "Unique Constraints" section. To quote:

If a unique constraint refers to a group of columns, the columns are listed separated by commas…

This specifies that the combination of values in the indicated columns is unique across the whole table, though any one of the columns need not be (and ordinarily isn't) unique.

Note: A partial index sets an index built over a subset of a table, which has a completely different purpose.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Sep 9, 2014 at 21:41

Emanuel Canha

4415 silver badges11 bronze badges

1 Comment

Hossein Over a year ago

I think with your solution he cant have multiple non active addresses.

Collectives™ on Stack Overflow

Postgres Unique Constraint on two columns: Integer and Boolean

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related