YOU MUST BE LOGGED INTO CURRENT DATABASE ! ALL RESULTS OF THESE SCRIPTS MUST BE EXECUTED TO CORRECT WORK OF CRM !
Don run this script on the root level
The script will generate ALTER commands, paste them into the editor and bulk-run. Skip the rows containing _pkey or _key
SELECT string_agg (format ('ALTER TABLE %s ALTER COLUMN %I TYPE text' , a.attrelid::regclass, a.attname), E';\n') FROM pg_attribute a JOIN pg_class c ON c.oid = a.attrelid JOIN pg_namespace n ON n.oid = c.relnamespace WHERE a.atttypid = 'varchar'::regtype AND NOT a.attisdropped -- no dropped columns AND a.attnum > 0 -- no system columns (redundant check) AND c.relname NOT LIKE '%_pkey%' AND c.relname NOT LIKE '%_key%' -- not tested should avoid rows with _pkey or _key AND format_type(a.atttypid, a.atttypmod) LIKE ('character varying(%');
The script will enable module for generating uuid in database
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
The script will generate ALTER commands setting default value uuid for PK with uuid data type, paste them into the editor and bulk-run.
SELECT string_agg (format ('ALTER TABLE %s ALTER COLUMN %I SET DEFAULT uuid_generate_v4()' , a.attrelid::regclass, a.attname), E';\n') FROM pg_attribute a JOIN pg_class c ON c.oid = a.attrelid JOIN pg_namespace n ON n.oid = c.relnamespace WHERE a.atttypid = 'uuid'::regtype AND NOT a.attisdropped -- no dropped columns AND a.attnum > 0 -- no system columns (redundant check) AND c.relname NOT LIKE '%_pkey%' -- skip rows with 'pkey' AND a.attname = 'Id'; -- select only PK
The script will generate ALTER commands setting default value as current day for all columns named 'HistoryDate' or 'CreatedDate', paste them into the editor and bulk-run.
SELECT string_agg (format ('ALTER TABLE %s ALTER COLUMN %I SET DEFAULT CURRENT_TIMESTAMP' , a.attrelid::regclass, a.attname), E';\n') FROM pg_attribute a JOIN pg_class c ON c.oid = a.attrelid JOIN pg_namespace n ON n.oid = c.relnamespace WHERE NOT a.attisdropped -- no dropped columns AND a.attnum > 0 -- no system columns (redundant check) AND c.relname NOT LIKE '%_pkey%' -- skip rows with 'pkey' AND a.attname = 'CreatedDate' -- select cols named 'CreatedDate' OR a.attname = 'HistoryDate'; -- select cols named 'HistoryDate' OR a.attname = 'UploadedDate'; -- select cols named 'HistoryDate'