Collected from the PG bugs email list.
|Reported by||Maxim Votyakov|
Body of first available message related to this bug follows.
The following bug has been logged on the website: Bug reference: 16150 Logged by: Maxim Votyakov Email address: (redacted) PostgreSQL version: 12.0 Operating system: Windows Description: Hi guys Today I found a big problem, I can write a valid UPDATE which put NULL value in non-null column. Let's create a domain, two tables and insert 1 row into them: create domain d_text_not_null as text default '' not null; create table person (name d_text_not_null); create table town (name d_text_not_null); insert into person(name) values ('test'); insert into town(name) values ('test2'); select * from person; -- 1 row with value test Now let's try to update person name to null. It failed and it is correct: update person set name = null; -- SQL Error : ERROR: domain d_text_not_null does not allow null values But now let's try to update person name to with sub-query. It updates a row without any problem: update person set name = (select name from town where name = 'wrong'); -- 1 row updated Let's check data and see the result: select * from person; -- 1 row with value NULL in non-null column !!! I think it is very dangerous and important bug and it has to be fixed to not damage data in databases - if I backup/restore such tables they become empty.
|2019-12-05 09:17:34+00||PG Bug reporting form||BUG #16150: UPDATE set NULL value in non-null columns|
|2019-12-05 11:35:49+00||Mahendra Singh||Re: BUG #16150: UPDATE set NULL value in non-null columns|
|2019-12-05 15:18:10+00||Tom Lane||Re: BUG #16150: UPDATE set NULL value in non-null columns|