tipo - modificar datos de una tabla en postgresql

Alterar el conjunto de columnas no nula falla

Question

Considere la siguiente tabla con aproximadamente 10M filas

CREATE TABLE user
(
id bigint NOT NULL,
...
CONSTRAINT user_pk PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
)

Entonces apliqué el siguiente cambio

ALTER TABLE USER ADD COLUMN BUSINESS_ID VARCHAR2(50);
--OK
UPDATE USER SET BUSINESS_ID = ID; //~1500 sec
--OK
ALTER TABLE USER ALTER COLUMN BUSINESS_ID SET NOT NULL;
ERROR: column "business_id" contains null values
SQL state: 23502

Esto es muy extraño ya que la columna id (que se ha copiado en la columna business_id) no puede contener valores nulos ya que es la clave principal, pero para estar seguro de que la verifiqué

select count(*) from USER where BUSINESS_ID is null
--0 records

Sospecho que esto es un error, solo me pregunto si me falta algo trivial.

Answer 1

La única explicación lógica sería un INSERT concurrente .
(Utilizando tbl lugar de la palabra reservada user como nombre de tabla).

ALTER TABLE tbl ADD COLUMN BUSINESS_ID VARCHAR2(50);
--OK
UPDATE tbl SET BUSINESS_ID = ID; //~1500 sec
--OK
 HERE
ALTER TABLE tbl ALTER COLUMN BUSINESS_ID SET NOT NULL;

Para evitar esto, use en su lugar:

ALTER TABLE tbl
ADD COLUMN BUSINESS_ID VARCHAR(50) DEFAULT ''''; -- or whatever is appropriate
...

Entonces puede terminar con un valor predeterminado en algunas filas. Es posible que desee comprobar.

O ejecute todo como bloque de transacción y tome un bloqueo exclusivo para asegurarse:

BEGIN;
LOCK tbl;
ALTER ...
UPDATE ...
ALTER ...
COMMIT;

Answer 2

No puedes hacer eso en la misma transacción. Añade tu columna y actualízala. Luego, en una transacción separada, establezca la restricción no nula.

Answer 3

Tal vez quiere un valor predeterminado? Postgresql docs en ALTER :

Para agregar una columna, use un comando como este:

ALTER TABLE products ADD COLUMN description text;

La nueva columna se rellena inicialmente con el valor predeterminado que se proporcione ( nulo si no especifica una cláusula DEFAULT ).

Asi que,

ALTER TABLE USER ALTER COLUMN BUSINESS_ID SET DEFAULT="",
ALTER COLUMN BUSINESS_ID SET NOT NULL;