PgSQL-NULL值处理-两个令人兴奋的功能

CREATE TABLE test (
    id serial PRIMARY KEY,
    value TEXT UNIQUE
);


INSERT INTO test (value) VALUES ('a');


-- This fails on the duplicate:
--   ERROR:  duplicate key value violates unique constraint "test_value_key"
--   DETAIL:  Key (value)=(a) already exists.
INSERT INTO test (value) VALUES ('a');


-- But this does not:
INSERT INTO test (value) VALUES (null);
INSERT INTO test (value) VALUES (null);


SELECT * from test;
 id | value
----+-------
  1 | a
  3 |
  4 |
(3 rows)

CREATE TABLE test (
    id serial PRIMARY KEY,
    value TEXT UNIQUE NULLS NOT DISTINCT
);


-- Now this fails on the second insert:
--   ERROR:  duplicate key value violates unique constraint "test_value_key"
--   DETAIL:  Key (value)=(null) already exists.
INSERT INTO test (value) VALUES (null);
INSERT INTO test (value) VALUES (null);

CREATE TABLE notifications (
    id serial,
    company_id INT REFERENCES companies (id),
    order_id INT REFERENCES orders (id),
    user_id INT REFERENCES users (id)
);


INSERT INTO notifications (company_id) VALUES (100);
INSERT INTO notifications (order_id) VALUES (200);


SELECT * from notifications;


 id | company_id | order_id | user_id
----+------------+----------+---------
  1 |        100 |          |
  2 |            |      200 |
(2 rows)

ALTER TABLE notifications
ADD CONSTRAINT notifications_reference
CHECK (company_id IS NOT NULL OR order_id IS NOT NULL OR user_id IS NOT NULL);

INSERT INTO notifications (company_id, order_id, user_id)
VALUES (NULL, 300, 400);


SELECT * from notifications;


 id | company_id | order_id | user_id
----+------------+----------+---------
  1 |        100 |          |
  2 |            |      200 |
  3 |            |      300 |     400
(3 rows)

ALTER TABLE notifications
ADD CONSTRAINT notifications_reference
CHECK (num_nonnulls(company_id, order_id, user_id) = 1);


-- Now we get an error on insert if there is more than one value:
--   ERROR:  new row for relation "notifications" violates check constraint "notifications_reference"
--   DETAIL:  Failing row contains (3, null, 300, 400).
INSERT INTO notifications (company_id, order_id, user_id)
VALUES (NULL, 300, 400);


-- Or if there are no values:
--   ERROR:  new row for relation "notifications" violates check constraint "notifications_reference"
--   DETAIL:  Failing row contains (4, null, null, null).
INSERT INTO notifications (company_id, order_id, user_id)
VALUES (NULL, NULL, NULL);