Hello PostgreSQL's CREATE DOMAIN documentation (section Notes) describes a way how one can add NULL's to a column that has a domain with the NOT NULL constraint. https://www.postgresql.org/docs/current/sql-createdomain.html
To me it seems very strange and amounts to a bug because it defeats the purpose of domains (to be a reusable assets) and constraints (to avoid any bypassing of these). Oracle 23c added the support of domains (https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/create-domain.html). I tested the same scenario both in PostgreSQL and Oracle (https://www.oracle.com/database/free/) and found out that in these situations Oracle does not allow NULL's to be added to the column. I do not know as to whether the behaviour that is implemented in PostgreSQL is specified by the standard. However, if it is not the case, then how it could be that Oracle can but PostgreSQL cannot. Best regards Erki Eessaar The scenario that I tested both in PostgreSQL (16) and Oracle (23c). *********************************** /*PostgreSQL 16*/ CREATE DOMAIN d_name VARCHAR(50) NOT NULL; CREATE TABLE Product_state_type (product_state_type_code SMALLINT NOT NULL, name d_name, CONSTRAINT pk_product_state_type PRIMARY KEY (product_state_type_code), CONSTRAINT ak_product_state_type_name UNIQUE (name)); CREATE TABLE Product (product_code INTEGER NOT NULL, name d_name, product_state_type_code SMALLINT NOT NULL, CONSTRAINT pk_product PRIMARY KEY (product_code), CONSTRAINT fk_product_product_state_type FOREIGN KEY (product_state_type_code) REFERENCES Product_state_type(product_state_type_code) ON UPDATE CASCADE); INSERT INTO Product_state_type (product_state_type_code, name) VALUES (1, (SELECT name FROM Product_state_type WHERE FALSE)); /*Insertion succeeds, name is NULL!*/ INSERT INTO Product (product_code, name, product_state_type_code) SELECT 1 AS product_code, Product.name, 1 AS product_state_type_code FROM Product_state_type LEFT JOIN Product USING (product_state_type_code); /*Insertion succeeds, name is NULL!*/ /*Oracle 23c*/ CREATE DOMAIN d_name AS VARCHAR2(50) NOT NULL; CREATE TABLE Product_state_type (product_state_type_code NUMBER(4) NOT NULL, name d_name, CONSTRAINT pk_product_state_type PRIMARY KEY (product_state_type_code), CONSTRAINT ak_product_state_type_name UNIQUE (name)); CREATE TABLE Product (product_code NUMBER(8) NOT NULL, name d_name, product_state_type_code NUMBER(4) NOT NULL, CONSTRAINT pk_product PRIMARY KEY (product_code), CONSTRAINT fk_product_product_state_type FOREIGN KEY (product_state_type_code) REFERENCES Product_state_type(product_state_type_code)); INSERT INTO Product_state_type (product_state_type_code, name) VALUES (1, (SELECT name FROM Product_state_type WHERE FALSE)); /*Fails. Error report - SQL Error: ORA-01400: cannot insert NULL into ("SYSTEM"."PRODUCT_STATE_TYPE"."NAME") Help: https://docs.oracle.com/error-help/db/ora-01400/ 01400. 00000 - "cannot insert NULL into (%s)" *Cause: An attempt was made to insert NULL into previously listed objects. *Action: These objects cannot accept NULL values.*/ INSERT INTO Product_state_type (product_state_type_code, name) VALUES (1, 'Active'); INSERT INTO Product (product_code, name, product_state_type_code) SELECT 1 AS product_code, Product.name, 1 AS product_state_type_code FROM Product_state_type LEFT JOIN Product USING (product_state_type_code); /*Fails. SQL Error: ORA-01400: cannot insert NULL into ("SYSTEM"."PRODUCT"."NAME") Help: https://docs.oracle.com/error-help/db/ora-01400/ 01400. 00000 - "cannot insert NULL into (%s)" *Cause: An attempt was made to insert NULL into previously listed objects. *Action: These objects cannot accept NULL values.*/
