How do I create a DOMAIN that accept NULL?

Question

I can't seem to figure out how to get a custom domain to accept a NULL value. Notice, for shits and giggles I've tried it many different ways:

DROP SCHEMA census CASCADE;
CREATE SCHEMA census;
-- FIRST FOUR METHODS: NULL before CHECK, in CHECK, and NULL after CHECK
CREATE DOMAIN census.sex AS text NULL CHECK ( VALUE IN ('M', 'F', 'NULL') OR VALUE IS NULL ) NULL;
CREATE TABLE census.names (
  name       text
  , freq     int
  , cumfreq  float
  , rank     float
  , is_last  bool
  , sex      census.sex NULL -- fourth way
  , PRIMARY KEY ( is_last, sex, name )
);

But, still no luck...

# \d census.names;
          Table "census.names"
 Column  |       Type       | Modifiers 
---------+------------------+-----------
 name    | text             | not null
 freq    | integer          | 
 cumfreq | double precision | 
 rank    | double precision | 
 is_last | boolean          | not null
 sex     | census.sex       | not null
Indexes:
    "names_pkey" PRIMARY KEY, btree (is_last, sex, name)

More amusingly, the docs say

NULL

Values of this domain are allowed to be null. This is the default.

This clause is only intended for compatibility with nonstandard SQL databases. Its use is discouraged in new applications.

Without the clause it still says NOT NULL.

$ psql --version
psql (PostgreSQL) 9.1.1
contains support for command-line editing

score 3 · Accepted Answer · answered Jan 13 '12 at 17:19

3

As usual:

Post a descriptive problem on StackOverflow
Paste link in irc://irc.freenode.net/#postgresql
Get a guru to answer question within seconds.

11:17 < RhodiumToad> EvanCarroll: it's the primary key that does it - all columns of a primary key are not null

He knows all.

answered Jan 13 '12 at 17:19

Evan Carroll

78,363
46
261
468

2

Right, I was in the process of crafting that same answer. – Erwin Brandstetter Jan 13 '12 at 17:22
Have a comment upvote -- I just didn't want anyone to waste time writing the answer if I already had it. – Evan Carroll Jan 13 '12 at 17:26
I am not complaining, you did the right thing. Just wanted to confirm. – Erwin Brandstetter Jan 13 '12 at 18:29

score 1 · Answer 2 · answered Mar 21 '12 at 13:41

Works here (pg 9.1):

        -- Domain with only values 1 ... 9 allowed,
        -- used for both the {x,y,z} coordinates and the cell values.
CREATE DOMAIN one_nine
        AS INTEGER
        CHECK (value >= 1 AND value <= 9)
        ;

CREATE DOMAIN one_nine_notnull
        AS INTEGER NOT NULL
        CHECK (value >= 1 AND value <= 9)
        ;

CREATE TABLE all_numbers
        (val one_nine_notnull PRIMARY KEY
        );
INSERT INTO all_numbers(val)
SELECT nn FROM generate_series(1,9) AS nn;

        -- Table containing exactly one sudoku puzzle.
        -- The zzz coordinate (the "box number") is formally redundant
        -- (since it is functionally dependant on {xxx,yyy})
DROP TABLE IF EXISTS sudoku3 CASCADE;
CREATE TABLE sudoku3
        ( yyy one_nine_notnull
        , xxx one_nine_notnull 
        , zzz one_nine_notnull
        , val one_nine
        );
INSERT INTO sudoku3 (yyy,xxx,zzz,val) VALUES ( NULL, 3, 1, 4);
INSERT INTO sudoku3 (yyy,xxx,zzz,val) VALUES ( 1, 3, 1, 14);

        -- Conveniance view with 3-width horizontal and vertical bands
CREATE VIEW v_sudoku AS (
        SELECT yyy,xxx,zzz
        , val
        , (xxx+2)/ 3 as x3
        , (yyy+2)/ 3 as y3
        FROM sudoku3
        )
        ;

        -- First constraint: (x,y) is unique
ALTER TABLE sudoku3 ADD PRIMARY KEY (xxx,yyy);

        -- Three constraints for unique values for {rows,columns,boxes}
CREATE UNIQUE INDEX sudoku_xv ON sudoku3 (xxx,val);
CREATE UNIQUE INDEX sudoku_yv ON sudoku3 (yyy,val);
CREATE UNIQUE INDEX sudoku_zv ON sudoku3 (zzz,val);

Output:

drop cascades to type w00t.one_nine_notnull
drop cascades to table w00t.all_numbers
drop cascades to table w00t.sudoku3
drop cascades to view w00t.v_sudoku
drop cascades to view w00t.sudoku_row
drop cascades to view w00t.valid_moves
drop cascades to view w00t.low_hanging_fruit
drop cascades to view w00t.magic_moves
drop cascades to function w00t.sudoku_magic()
DROP SCHEMA
CREATE SCHEMA
SET
CREATE DOMAIN
CREATE DOMAIN
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "all_numbers_pkey" for table "all_numbers"
CREATE TABLE
INSERT 0 9
NOTICE:  table "sudoku3" does not exist, skipping
DROP TABLE
CREATE TABLE
ERROR:  domain one_nine_notnull does not allow null values
ERROR:  value for domain one_nine violates check constraint "one_nine_check"
CREATE VIEW
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "sudoku3_pkey" for table "sudoku3"
ALTER TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
INSERT 0 81
...

How do I create a DOMAIN that accept NULL?

2 Answers2