QUery to find all `varchar` columns in the database and return query strings that can be run to change them to `text` types Raw

generate_text_alters.sql

WITH targets AS (
  SELECT 'ALTER TABLE "'||table_name||'"' as "table",
          string_agg(
            'ALTER COLUMN "'||column_name||'" TYPE text'||CASE
            WHEN character_maximum_length IS NULL THEN ''
            WHEN character_maximum_length >= 255 THEN ''
            ELSE
              E',\n  ADD CONSTRAINT '||column_name||E'_length \n    '||
              'CHECK (LENGTH("'||column_name||'") <= '||character_maximum_length||') '||
              'NOT VALID'
            END
            , E',\n  ' ORDER BY column_name) as "changes"
  FROM information_schema.columns
  WHERE table_schema = 'public'
    AND data_type = 'character varying'
  GROUP BY 1
  ORDER BY 1
)

SELECT "table" || E'\n  ' || "changes" || E';\n' as query
FROM targets
;

output.sql

ALTER TABLE "users"
  ALTER COLUMN "email" TYPE text,
  ALTER COLUMN "encrypted_password" TYPE text,
  ADD CONSTRAINT encrypted_password_length 
    CHECK (LENGTH("encrypted_password") <= 128) NOT VALID,
  ALTER COLUMN "first_name" TYPE text,
  ALTER COLUMN "last_name" TYPE text,
  ALTER COLUMN "salt" TYPE text,
  ADD CONSTRAINT salt_length 
    CHECK (LENGTH("salt") <= 128) NOT VALID,
  ALTER COLUMN "title" TYPE text;

-- ...