1

Let's say I want to change the data type of all columns of one type to another. How would I go about doing this, for example converting all bigints to ints.

0

In psql, you'd run something like this which will output a bunch of commands.

SELECT format(
  'ALTER TABLE %I.%I.%I ALTER COLUMN %I SET DATA TYPE int;',
  table_catalog,
  table_schema,
  table_name,
  column_name
)
FROM information_schema.columns
WHERE data_type = 'bigint'
  AND table_schema NOT LIKE 'pg_%'
  AND lower(table_schema) <> 'information_schema'
  AND is_updatable = 'YES';

You can spot check them and if you're good to go execute them with \gexec.

|improve this answer|||||
  • 1
    You can also wrap a similar query up in a PL/PgSQL DO block with EXECUTE. – Craig Ringer Jul 28 '17 at 3:45
  • @CraigRinger feel free to add it in too, Thanks for the additions, I almost feel like asking another question why lower(table_schema) rather than just table_schema, but I'm 100% sure you did it right and I made the mistake, or what other pg_% schemas there are – Evan Carroll Jul 28 '17 at 4:03
  • 1
    You also need to omit any views that might have columns of this type by adding AND is_updatable='YES' to the WHERE – ffox003 Jul 31 '17 at 7:50
  • @ffox003 fixed. =) – Evan Carroll Aug 1 '17 at 16:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for?Browse other questions tagged or ask your own question.