SELECT CONCAT (COALESCE(id_street), COALESCE(bld_no)) FROM iota_cmac

gives a normal result

 '9031546460179 025'

When trying to insert this value into the empty id_bld field of the same table:

 INSERT INTO iota_cmac.bld_54646(id_bld) SELECT CONCAT (COALESCE(id_street), COALESCE(bld_no)) FROM iota_cmac.bld_54646;

gives an error message:

 ERROR: null value in column "bld_code" violates not-null constraint DETAIL: Failing row contains (9031546460179 025 , null, null, null, null, null, null, null, null, null, null, null, null, null, null). SQL state: 23502

Question: where are the other fields of the table, if CONCAT selects only two specified fields?
Indicating the value of CONCAT does not change anything, the error is exactly the same.

 INSERT INTO iota_cmac.bld_54646(id_bld) SELECT CONCAT (COALESCE(id_street), COALESCE(bld_no)) FROM iota_cmac.bld_54646 VALUES (CONCAT);

as I understand this statement, the INSERT command should insert the CONCAT result in the id_bld field: '9031546460179 025' and stop the execution, and she continues to check the remaining fields. In this case, all the fields are FILLED, not empty, as written in the error.

If anyone can clarify, I would be grateful

  • When you write insert you create a new row in the table. And in this line only 1 field is filled, id_bld . You have a restriction on the table, which says that the bld_code field cannot be empty. Maybe you wanted to update ? - Viktorov
  • Thank you, I understood the error, correctly: UPDATE iota_cmac.bld_54646 SET (id_bld) = (SELECT CONCAT (COALESCE (id_street), COALESCE (bld_no)) FROM iota_cmac.bld_54646); - Boris Grebnevskyi
  • @Viktorov I don’t know how to give you a plus to karma here))) ... thanks for the hint - Boris Grebnevskyi

0