Search code examples
postgresqltypesalter-tablecatalogdml

Change data type of a column with dependencies from integer to numeric

I need to change a data type in a table, but run into a dependency error. Is possible to change the data type of an INTEGER column to NUMERIC directly in pg_attribute?

I've tried with ALTER TABLE and it does not work:

CREATE TABLE documento (
  iddocumento SERIAL, 
  idtipodocumento INTEGER NOT NULL, 
  folio INTEGER NOT NULL, 
  CONSTRAINT pk_documento PRIMARY KEY(iddocumento)
);

ALTER TABLE documento ALTER COLUMN folio TYPE NUMERIC(10,0);

The error:

ERROR:  cannot alter type of a column used by a view or rule
DETAIL:  rule _RETURN on view vw_xxx depends on column "folio"

The column has many dependencies.

Solution

  • Try with this:

    DROP DROP DROP

ALTER TABLE documento ALTER COLUMN folio TYPE NUMERIC(10,0);

CREATE CREATE CREATE...

    Sure works fine