CODEWITHSUNDEEP
sqlpostgresql
Andrey
Andrey

Reputation: 1759

Dirty way to alter table to change column type in Postgres

I have the situation I have a big table(a few millions of records) I need to update one column data type from varchar (250) to text. However here is the trick, there are multiple views created based on this table, these views are also used in a few other views and a few procedures, so deleting cascade and just simply recreating it is not an option. Is there any other tricky/dirty way to change it?

Upvotes: 0

Views: 896

Answers (1)

Dmitrii Karasev
Dmitrii Karasev

Reputation: 11

Main questions:

  1. How long can you carry out maintenance.
  2. How much space does this table take up? And how much free space is left on the tablespace?

--to get a list of dependent FUCTIONS/PROCEDURES use something like this:

SELECT n.nspname, p.proname, p.prosrc
FROM pg_proc p 
LEFT JOIN pg_namespace n ON n.oid = p.pronamespace 
WHERE p.prosrc ilike '%Table_xxx%';

--you can also check object dependency in pgAdmin

--to get a list of dependent VIEWS use something like this:

SELECT table_schema, table_name, pg_get_viewdef(table_schema||'.'||table_name, true)
FROM information_schema.tables 
WHERE table_type = 'VIEW'
AND pg_get_viewdef(table_schema||'.'||table_name, true) ilike '%Table_xxx%';

--so now you have a list of dependent objects. you can test your updates.

--Don`t forget about DCL (you need to test it or just use pgAdmin):

SELECT 'GRANT '||privilege_type||' ON '||table_schema||'.'||table_name||' TO '||grantee||';'
FROM information_schema.role_table_grants 
WHERE table_name='Table_xxx';

--next step. (if you have enough maintenance time:)

--in SINNGLE TRANSACTION:

--drop dependent objects

--change column type

ALTER TABLE Table_xxx ALTER COLUMN c_xxx TYPE text;

--recreate dependent objects

--restore DCL

--next step. (if you need to modify a lot of columns and you have enought space and time) you can:

--create temporary transformed table:

--stop your ETL-processes.

--in SINNGLE TRANSACTION:

--make transformations:

-- drop dependent objects

CREATE TABLE Table_xxx_tmp AS SELECT c_1, c_xxx::text, c_n FROM Table_xxx;

-- replace tables:

ALTER TABLE TABLE Table_xxx rename to Table_xxx_old;
ALTER TABLE TABLE Table_xxx_tmp rename to Table_xxx;

--recreate dependent objects (+triggers etc)

--4th restore DCL

--start your ETL-processes.

--but the MAIN THING here: think with your head.

Upvotes: 1

Related Questions