Insert into without null values-CodePudding

I don't understand why even if I set the non-null select to do the insert I still get the error that I am trying to enter null values. As a context I am trying to insert two columns from another table into a new table.

CREATE TABLE t.tb_c    
(
    id  INT NOT NULL,
    itm CHARACTER(5) NOT NULL,
    des CHARACTER(120) NOT NULL,

    CONSTRAINT pk_item PRIMARY KEY (item_id)
);

INSERT INTO t.tb_c (itm)
    SELECT itm 
    FROM erp.tb_d
    WHERE itm IS NOT NULL;



 INSERT INTO t.tb_c (des)
        SELECT des
        FROM erp.tb_d
        WHERE des IS NOT NULL;

I get this error:

SQLState: 23502
Short Description: AN UPDATE INSERT OR SET VALUE IS NULL BUT THE OBJECT COLUMN CANNOT CONTAIN NULL VALUES

ERROR: the null value for the «des» violates the restriction not null
DETAIL: The row is (90002, AA111, null).
SQL state: 23502

CodePudding user response：

Try with this insert:

INSERT INTO t.tb_c (itm, des)
SELECT itm, des
        FROM erp.tb_d
        WHERE des IS NOT NULL AND itm IS NOT NULL;

CodePudding user response：

alter table t.tb_c  alter column itm  drop not null;
alter table t.tb_c  alter column des  drop not null;

change id from int to serial follow=> Changing primary key int type to serial

ALTER TABLE t.tb_c ALTER COLUMN itm SET DEFAULT '';
ALTER TABLE t.tb_c ALTER COLUMN des  SET DEFAULT '';

change id from int to serial follow=> Changing primary key int type to serial