SQL 如何通过我在 PostgreSQL 中的数据库获取列大小和类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4471734/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to get column size and type through my database in PostgreSQL
提问by ?zbek
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.
但是在通过 HQL 创建新数据库后，它正在创建varchar(255)，我需要让它更长。
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 do this.
我可以手动找到它，但现在我有大约 200 个表，我需要一个查询来做到这一点。
How can I get the column type and its length in Postgres using a SQL query?
如何使用 SQL 查询在 Postgres 中获取列类型及其长度？
回答by RedFilter
The INFORMATION_SCHEMAtables will help you here:
该INFORMATION_SCHEMA表将在这里帮助你：
select *
from INFORMATION_SCHEMA.COLUMNS
You can examine the table_name, column_name, data_typeand character_maximum_lengthcolumns in the result set.
您可以检查table_name，column_name，data_type并character_maximum_length在结果集中的列。
回答by Tom Gerken
Stumbled across this old post. Based on RedFilter's answer here is the query for the original question:
偶然发现了这个旧帖子。基于 RedFilter 的回答，这里是对原始问题的查询：
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!
希望这对将来的人有所帮助，或者至少为他们节省一些时间！
