0

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
atiruz
  • 2,782
  • 27
  • 36
  • Please show us the CREATE TABLE of the tables in question, the complete ALTER TABLE ***and*** the complete error message. –  Jul 12 '12 at 16:22
  • The string "does not work" is not a built-in error message in PostgreSQL. –  Jul 12 '12 at 16:33

3 Answers3

1

To answer your question:

Is possible to change the data type of an INTEGER column to NUMERIC directly in pg_attribute?

No, it is not. Data types integer and numeric are not binary compatible. It is generally a very bad idea to try and mess with the system catalogs. You will most certainly break your database. SQL DML statements are the tools to use when you want to change the design of your database.

@a_horse already commented on the limitation in PostgreSQL.

There is the workaround like @a_horse describes - I use pgAdmin to collect the DDL scripts for dropping and recreating the views. psql works, too.

And there is another, simpler method - if you can afford it: Dump the database, hack the dump and restore it. This way you only have to change one line in the dump, the column definition of documento.folio (integer values fit into numeric columns just fine).

But you need exclusive access to the database for the duration of operation, i.e. all other access is blocked for the time. So, that's basically only for small databases or databases in development.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 1
    For smaller databases, I suggest dumping the database in two parts: --schema-only (relatively small, typically only a few thousand lines), and --data-only (could be big, but you don't need to touch it) You can even test the changes by importing it into a new (temp) database, this will enable you to chase and fix all the dependancies. – wildplasser Jul 12 '12 at 17:57
1

Try with this:

DROP DROP DROP

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

CREATE CREATE CREATE...

Sure works fine

Victor
  • 26
  • 2
0

This is a very annoying limitation in PostgreSQL: you cannot alter a column definition of a table where the column is used in a view.

Unfortunately the only workaround is to first drop all views referencing that column, then do the alter table, then re-create the views.