how to get column size and type through my database in PostgreSQL

I've changed column length manually in my previous database.

But after creating new database via HQL it's creating varchar(255) and I need to make it longer.

I need to find which table's column should I change?

I can find it manually but now I have about 200 tables and I need a query to d开发者_JS百科o this.

How can I get the column type and its length in Postgres using a SQL query?

---

The INFORMATION_SCHEMA tables will help you here:

select *
from INFORMATION_SCHEMA.COLUMNS

You can examine the table_name, column_name, data_type and character_maximum_length columns in the result set.

---

Stumbled across this old post. Based on RedFilter's answer here is the query for the original question:

select table_name, 
       column_name 
from INFORMATION_SCHEMA.COLUMNS 
where data_type = 'character varying' 
  and character_maximum_length = 200

putting it together with the alter table syntax of:

ALTER TABLE X ALTER COLUMN Y TYPE text;

You can generate all the commands you need by running this query:

select 'ALTER TABLE ' || table_name || ' ALTER COLUMN ' || column_name || ' TYPE text;'  
from INFORMATION_SCHEMA.COLUMNS 
where data_type = 'character varying' 
  and character_maximum_length = 200;

Hope this helps someone in the future, or at least saves them some time!