ereport(ERROR,

(errcode(ERRCODE_NOT_NULL_VIOLATION),

errmsg("column \"%s\" contains null values",

NameStr(attr->attname)),

errmsg("column \"%s\" of relation \"%s\" contains null values",

NameStr(attr->attname),

RelationGetRelationName(oldrel)),

errtablecol(oldrel, attn + 1)));

}

}

if (!ExecCheck(con->qualstate, econtext))

ereport(ERROR,

(errcode(ERRCODE_CHECK_VIOLATION),

errmsg("check constraint \"%s\" is violated by some row",

con->name),

errmsg("check constraint \"%s\" of relation \"%s\" is violated by some row",

con->name,

RelationGetRelationName(oldrel)),

errtableconstraint(oldrel, con->name)));

break;

case CONSTR_FOREIGN:

if (tab->validate_default)

ereport(ERROR,

(errcode(ERRCODE_CHECK_VIOLATION),

errmsg("updated partition constraint for default partition would be violated by some row")));

errmsg("updated partition constraint for default partition \"%s\" would be violated by some row",

RelationGetRelationName(oldrel))));

else

ereport(ERROR,

(errcode(ERRCODE_CHECK_VIOLATION),

errmsg("partition constraint is violated by some row")));

errmsg("partition constraint of relation \"%s\" is violated by some row",

RelationGetRelationName(oldrel))));

}

/* Write the tuple out to the new relation */

if (!ExecCheck(exprstate, econtext))

ereport(ERROR,

(errcode(ERRCODE_CHECK_VIOLATION),

errmsg("check constraint \"%s\" is violated by some row",

NameStr(constrForm->conname)),

errmsg("check constraint \"%s\" of relation \"%s\" is violated by some row",

NameStr(constrForm->conname),

RelationGetRelationName(rel)),

errtableconstraint(rel, NameStr(constrForm->conname))));

ResetExprContext(econtext);

ereport(ERROR,

(errcode(ERRCODE_NOT_NULL_VIOLATION),

errmsg("null value in column \"%s\" violates not-null constraint",

NameStr(att->attname)),

errmsg("null value in column \"%s\" of relation \"%s\" violates not-null constraint",

NameStr(att->attname),

RelationGetRelationName(orig_rel)),

val_desc ?errdetail("Failing row contains %s.",val_desc) :0,

errtablecol(orig_rel,attrChk)));

}

-- should fail with not-null constraint violation

BEGIN;

COPY forcetest (a, b, c) FROM STDIN WITH (FORMAT csv, FORCE_NULL(b), FORCE_NOT_NULL(c));

ERROR: null value in column "b" violates not-null constraint

ERROR: null value in column "b"of relation "forcetest"violates not-null constraint

DETAIL: Failing row contains (3, null, , null, null).

CONTEXT: COPY forcetest, line 1: "3,,"""

ROLLBACK;

create table parted_notnull_inh_test (a int default 1, b int not null default 0) partition by list (a);

create table parted_notnull_inh_test1 partition of parted_notnull_inh_test (a not null, b default 1) for values in (1);

insert into parted_notnull_inh_test (b) values (null);

ERROR: null value in column "b" violates not-null constraint

ERROR: null value in column "b"of relation "parted_notnull_inh_test1"violates not-null constraint

DETAIL: Failing row contains (1, null).

-- note that while b's default is overriden, a's default is preserved

\d parted_notnull_inh_test1

b | text | | |

INSERT INTO test_like_id_2 (b) VALUES ('b2');

ERROR: null value in column "a" violates not-null constraint

ERROR: null value in column "a"of relation "test_like_id_2"violates not-null constraint

DETAIL: Failing row contains (null, b2).

SELECT * FROM test_like_id_2; -- identity was not copied

a | b

INSERT INTO nulltest values ('a', NULL, 'c', 'd', 'c');

ERROR: domain dnotnull does not allow null values

INSERT INTO nulltest values ('a', 'b', NULL, 'd', 'c');

ERROR: null value in column "col3" violates not-null constraint

ERROR: null value in column "col3"of relation "nulltest"violates not-null constraint

DETAIL: Failing row contains (a, b, null, d, c).

INSERT INTO nulltest values ('a', 'b', 'c', NULL, 'd'); -- Good

-- Test copy

COPY nulltest FROM stdin; --fail

ERROR: null value in column "col3" violates not-null constraint

ERROR: null value in column "col3"of relation "nulltest"violates not-null constraint

DETAIL: Failing row contains (a, b, null, d, d).

CONTEXT: COPY nulltest, line 1: "ab\Ndd"

COPY nulltest FROM stdin; --fail

, col8 ddef5

);

insert into defaulttest(col4) values(0); -- fails, col5 defaults to null

ERROR: null value in column "col5" violates not-null constraint

ERROR: null value in column "col5"of relation "defaulttest"violates not-null constraint

DETAIL: Failing row contains (3, 12, 5, 0, null, 88, 8000, 12.12).

alter table defaulttest alter column col5 drop default;

insert into defaulttest default values; -- succeeds, inserts domain default

-- We used to treat SET DEFAULT NULL as equivalent to DROP DEFAULT; wrong

alter table defaulttest alter column col5 set default null;

insert into defaulttest(col4) values(0); -- fails

ERROR: null value in column "col5" violates not-null constraint

ERROR: null value in column "col5"of relation "defaulttest"violates not-null constraint

DETAIL: Failing row contains (3, 12, 5, 0, null, 88, 8000, 12.12).

alter table defaulttest alter column col5 drop default;

insert into defaulttest default values;

INSERT INTO gtest20a (a) VALUES (10);

INSERT INTO gtest20a (a) VALUES (30);

ALTER TABLE gtest20a ADD CHECK (b < 50); -- fails on existing row

ERROR: check constraint "gtest20a_b_check" is violated by some row

ERROR: check constraint "gtest20a_b_check"of relation "gtest20a"is violated by some row

CREATE TABLE gtest20b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED);

INSERT INTO gtest20b (a) VALUES (10);

INSERT INTO gtest20b (a) VALUES (30);

ALTER TABLE gtest20b ADD CONSTRAINT chk CHECK (b < 50) NOT VALID;

ALTER TABLE gtest20b VALIDATE CONSTRAINT chk; -- fails on existing row

ERROR: check constraint "chk" is violated by some row

ERROR: check constraint "chk"of relation "gtest20b"is violated by some row

-- not-null constraints

CREATE TABLE gtest21a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) STORED NOT NULL);

INSERT INTO gtest21a (a) VALUES (1); -- ok

INSERT INTO gtest21a (a) VALUES (0); -- violates constraint

ERROR: null value in column "b" violates not-null constraint

ERROR: null value in column "b"of relation "gtest21a"violates not-null constraint

DETAIL: Failing row contains (0, null).

CREATE TABLE gtest21b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) STORED);

ALTER TABLE gtest21b ALTER COLUMN b SET NOT NULL;

INSERT INTO gtest21b (a) VALUES (1); -- ok

INSERT INTO gtest21b (a) VALUES (0); -- violates constraint

ERROR: null value in column "b" violates not-null constraint

ERROR: null value in column "b"of relation "gtest21b"violates not-null constraint

DETAIL: Failing row contains (0, null).

ALTER TABLE gtest21b ALTER COLUMN b DROP NOT NULL;

INSERT INTO gtest21b (a) VALUES (0); -- ok now

ALTER TABLE itest4 ALTER COLUMN a DROP IDENTITY IF EXISTS; -- noop

NOTICE: column "a" of relation "itest4" is not an identity column, skipping

INSERT INTO itest4 DEFAULT VALUES; -- fails because NOT NULL is not dropped

ERROR: null value in column "a" violates not-null constraint

ERROR: null value in column "a"of relation "itest4"violates not-null constraint

DETAIL: Failing row contains (null, ).

ALTER TABLE itest4 ALTER COLUMN a DROP NOT NULL;

INSERT INTO itest4 DEFAULT VALUES;

ALTER TABLE itest8

ADD COLUMN f22 int NOT NULL,

ALTER COLUMN f22 ADD GENERATED ALWAYS AS IDENTITY;

ERROR: column "f22" contains null values

ERROR: column "f22"of relation "itest8"contains null values

TABLE itest8;

f1 | f2 | f3 | f4 | f5

----+----+----+----+----

ERROR: duplicate key value violates unique constraint "covering"

DETAIL: Key (c1, c2)=(1, 2) already exists.

INSERT INTO tbl SELECT 1, NULL, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;

ERROR: null value in column "c2" violates not-null constraint

ERROR: null value in column "c2"of relation "tbl"violates not-null constraint

DETAIL: Failing row contains (1, null, 3, (4,4),(4,4)).

INSERT INTO tbl SELECT x, 2*x, NULL, NULL FROM generate_series(1,300) AS x;

explain (costs off)

ERROR: duplicate key value violates unique constraint "tbl_pkey"

DETAIL: Key (c1, c2)=(1, 2) already exists.

INSERT INTO tbl SELECT 1, NULL, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;

ERROR: null value in column "c2" violates not-null constraint

ERROR: null value in column "c2"of relation "tbl"violates not-null constraint

DETAIL: Failing row contains (1, null, 3, (4,4),(4,4)).

INSERT INTO tbl SELECT x, 2*x, NULL, NULL FROM generate_series(1,10) AS x;

DROP TABLE tbl;