CODEWITHSUNDEEP
postgresql
Sarvar Nishonboyev
Sarvar Nishonboyev

Reputation: 13090

Postgresql conditional check

I've a users table:

id
type
merchant_id
agent_id
...

I want to add a check constraint using the following conditions:

if type == 'MERCHANT' then merchant_id is not null
if type == 'AGENT' then agent_id is not null

How this constraint is implemented?

Update:

I forgot to mention an extra requirement. the user can only have an agent_id or merchant_id.

Upvotes: 1

Views: 47

Answers (2)

Harshit Rastogi
Harshit Rastogi

Reputation: 2112

You can check constraints in CREATE TABLE command:

DROP TABLE IF EXISTS users;
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    type VARCHAR (50),
    merchant_id INT (50),
    CONSTRAINT if_attribute_then_field_is_not_null 
      CHECK ( (NOT attribute) OR (field IS NOT NULL) ) 
);

OR in Alter Table command:

ALTER TABLE users 
ADD CONSTRAINT if_attribute_then_field_is_not_null 
      CHECK ( (NOT attribute) OR (field IS NOT NULL) )
);

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521123

You may add the following check constraints to the create table statement:

CREATE TABLE users (
    id INTEGER,
    type VARCHAR(55),
    merchant_id INTEGER,
    agent_id INTEGER,
    ...,
    CHECK ((type <> 'MERCHANT' OR merchant_id IS NOT NULL) AND
           (type <> 'AGENT' OR agent_id IS NOT NULL))
)

Upvotes: 2

Related Questions