ereport(ERROR,

(errcode(ERRCODE_NOT_NULL_VIOLATION),

errtablecol(oldrel, attn + 1)));

}

}

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

ereport(ERROR,

(errcode(ERRCODE_CHECK_VIOLATION),

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

break;

case CONSTR_FOREIGN:

if (tab->validate_default)

ereport(ERROR,

(errcode(ERRCODE_CHECK_VIOLATION),

else

ereport(ERROR,

(errcode(ERRCODE_CHECK_VIOLATION),

}

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

if (!ExecCheck(exprstate, econtext))

ereport(ERROR,

(errcode(ERRCODE_CHECK_VIOLATION),

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

ResetExprContext(econtext);

ereport(ERROR,

(errcode(ERRCODE_NOT_NULL_VIOLATION),

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));

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);

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');

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');

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

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

CONTEXT: COPY nulltest, line 1: "a b \N d d"

COPY nulltest FROM stdin; --fail

, col8 ddef5

);

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

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

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

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

-- 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

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

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

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;

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;

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;

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;

-- Confirm PRIMARY KEY adds NOT NULL constraint to child table

CREATE TEMP TABLE z (b TEXT, PRIMARY KEY(aa, b)) inherits (a);

INSERT INTO z VALUES (NULL, 'text'); -- should fail

DETAIL: Failing row contains (null, text).

-- Check inherited UPDATE with all children excluded

create table some_tab (a int, b int);

create table c1(f3 int) inherits(p1,p2);

insert into c1 values(1,-1,2);

alter table p2 add constraint cc check (f2>0); -- fail

alter table p2 add check (f2>0); -- check it without a name, too

delete from c1;

insert into c1 values(1,1,2);

alter table p2 add check (f2>0);

--

create table inserttest (col1 int4, col2 int4 NOT NULL, col3 text default 'testing');

insert into inserttest (col1, col2, col3) values (DEFAULT, DEFAULT, DEFAULT);

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

insert into inserttest (col2, col3) values (3, DEFAULT);

insert into inserttest (col1, col2, col3) values (DEFAULT, 5, DEFAULT);

UPDATE t1 SET c2 = 1; -- fail, but row not shown

ERROR: duplicate key value violates unique constraint "t1_pkey"

INSERT INTO t1 (c1, c2) VALUES (null, null); -- fail, but see columns being inserted

DETAIL: Failing row contains (c1, c2) = (null, null).

INSERT INTO t1 (c3) VALUES (null); -- fail, but see columns being inserted or have SELECT

DETAIL: Failing row contains (c1, c3) = (null, null).

INSERT INTO t1 (c1) VALUES (5); -- fail, but see columns being inserted or have SELECT

DETAIL: Failing row contains (c1) = (5).

UPDATE t1 SET c3 = 10; -- fail, but see columns with SELECT rights, or being modified

ERROR: new row for relation "t1" violates check constraint "t1_c3_check"

(1 row)

INSERT INTO reloptions_test VALUES (1, NULL), (NULL, NULL);

DETAIL: Failing row contains (null, null).

VACUUM reloptions_test;

SELECT pg_relation_size('reloptions_test') > 0;

(1 row)

INSERT INTO reloptions_test VALUES (1, NULL), (NULL, NULL);

DETAIL: Failing row contains (null, null).

VACUUM reloptions_test;

SELECT pg_relation_size('reloptions_test') = 0;

INSERT INTO serialTest1 VALUES ('bar');

INSERT INTO serialTest1 VALUES ('force', 100);

INSERT INTO serialTest1 VALUES ('wrong', NULL);

DETAIL: Failing row contains (wrong, null).

SELECT * FROM serialTest1;

f1 | f2

CREATE TABLE vac_truncate_test(i INT NOT NULL, j text)

WITH (vacuum_truncate=true, autovacuum_enabled=false);

INSERT INTO vac_truncate_test VALUES (1, NULL), (NULL, NULL);

DETAIL: Failing row contains (null, null).

VACUUM (TRUNCATE FALSE) vac_truncate_test;

SELECT pg_relation_size('vac_truncate_test') > 0;

INSERT INTO PRIMARY_TBL VALUES (4, 'three');

INSERT INTO PRIMARY_TBL VALUES (5, 'one');

INSERT INTO PRIMARY_TBL (t) VALUES ('six');

DETAIL: Failing row contains (null, six).

SELECT '' AS four, * FROM PRIMARY_TBL;

four | i | t

INSERT INTO PRIMARY_TBL VALUES (4, 'three');

INSERT INTO PRIMARY_TBL VALUES (5, 'one');

INSERT INTO PRIMARY_TBL (t) VALUES ('six');

DETAIL: Failing row contains (null, six).

SELECT '' AS three, * FROM PRIMARY_TBL;

three | i | t