I'm getting column "my_column" contains null values' when adding a composite primary key

user_10585064 0
Viewed 43

Is it not supposed to delete null values before altering the table? I'm confused...
My query looks roughly like this:

BEGIN;
    DELETE FROM my_table
      WHERE my_column IS NULL;

    ALTER TABLE my_table DROP CONSTRAINT my_table_pk;
    ALTER TABLE my_table ADD PRIMARY KEY (id, my_column);

    -- this is to repopulate the data afterwards
    INSERT INTO my_table (name, other_table_id, my_column)
    SELECT
        ya.name,
        ot.id,
        my_column
    FROM other_table ot
    
    LEFT JOIN yet_another ya
        ON ya.id = ot."fileId"
        
    WHERE NOT EXISTS (
        SELECT 
            1
        FROM my_table mt
      WHERE  ot.id = mt.other_table_id AND ot.my_column = mt.my_column
  ) AND  my_column IS NOT NULL;
COMMIT;

sorry for naming

database-migration postgresql
1 Answers

There are two possible explanations:

  • A concurrent session inserted a new row with a NULL value between the start of the DELETE and the start of ALTER TABLE.

    To avoid that, lock the table in SHARE mode before you DELETE.

  • There is a row where id has a NULL value.

user_64643080
Related