在PostgreSQL中选择字段的数据类型。

Question

如何从Postgres表中获取特定字段的数据类型？ 例如，我有以下表格， student_details ( stu_id integer, stu_name varchar(30 ), joined_date timestamp );

使用字段名称或其他方式，我需要获取特定字段的数据类型。是否有可能实现？

- user260040

---

也被问到并回答了 https://dev59.com/7GIj5IYBdhLWcg3wflMy - Piotr Findeisen

Answer 1

你可以从information_schema中获取数据类型（这里引用的是8.4文档，但这不是一个新特性）：

=# select column_name, data_type from information_schema.columns
-# where table_name = 'config';
    column_name     | data_type 
--------------------+-----------
 id                 | integer
 default_printer_id | integer
 master_host_enable | boolean
(3 rows)

- Wayne Conrad

---

太简单了，太好了！现在我可以替换当前查询，该查询长度为310个字符（不包括表名），有4个表连接，不具备模式感知能力，代价高昂，并且返回'int4'和其他类型而不是整数。谢谢！ - some

PostgreSQL允许您在多个模式中拥有相同的表名（甚至是相同的表）。编写WHERE子句的强大方法考虑到了这种可能性：where table_catalog = ? and table_schema = ? and table_name = ?; 但是，这个information_schema视图并没有考虑DDL可能使用了domains。 - Mike Sherrill 'Cat Recall'

这不会给出数组的类型，因此必须与 pg_typeof 一起使用。 - Frankie Drake

你应该在使用varchar和类似类型时使用character_maximum_length。此外，你可能想要获取数字精度... 这应该显示：select column_name, data_type, coalesce(character_maximum_length, numeric_precision) as precision from information_schema.columns where table_name = 'record'; - Nux

如果你想按顺序获取它们，可以像这样添加ORDER BY ordinal_position，就像这样SELECT column_name, data_type FROM information_schema.columns where table_name = '{table_name}' ORDER BY ordinal_position;。 - undefined

Answer 2

你可以使用pg_typeof()函数，它也适用于任意值。

SELECT pg_typeof("stu_id"), pg_typeof(100) from student_details limit 1;

- Nathan Villaescusa

---

这会针对表中的每个记录返回一行。如果您有数百万条记录，请不要运行它。 - Saarang

如果您需要确定计算的类型，这个方法非常实用。例如，SELECT pg_typeof( date_part( 'year', now() ) ) AS expr，它的结果可能与您预期的不同。请注意，这并非解释，而是翻译原文的简要概括。 - Leo Orientis

这里的聪明之处在于 pg_typeof 可以用于存储过程输出的字段，对于这些字段，后台表格（如果存在）是未知/不清楚的。示例： select state, qstart, pg_typeof(qstart) as ty_qstart from listconn()。 在这种情况下，information_schema 没有什么用处。 - JL Peyret

@JLPeyret 没错！我希望我能多次点赞。 - MrR

虽然不显示varchar的字符数，但只显示“character varying”。您可以从information_schema.columns获取该信息。 - Nux

根据关于LIMIT的文档，"查询优化器在生成查询计划时会考虑LIMIT"，因此它很可能不会检查所有行。 - undefined

Answer 3

尝试以下请求：

SELECT column_name, data_type FROM information_schema.columns WHERE 
table_name = 'YOUR_TABLE' AND column_name = 'YOUR_FIELD';

- Romulo Freires

---

表名为'YOUR_TABLE'且列名为'YOUR_FIELD'； - haitham

如果您有多个具有相同名称的表格，则在请求中包含table_schema可能是必要的：table_schema='YOUR_SCHEMA' AND table_name='YOUR_TABLE' AND column_name='YOUR_FIELD' - Lars Ladegaard

Answer 4

运行 psql -E 然后输入 \d student_details

- user80168

Answer 5

如果您喜欢'Mike Sherrill'的解决方案，但不想使用psql，我使用了这个查询来获取丢失的信息：

select column_name,
case 
    when domain_name is not null then domain_name
    when data_type='character varying' THEN 'varchar('||character_maximum_length||')'
    when data_type='numeric' THEN 'numeric('||numeric_precision||','||numeric_scale||')'
    else data_type
end as myType
from information_schema.columns
where table_name='test'

结果为：

column_name |     myType
-------------+-------------------
 test_id     | test_domain
 test_vc     | varchar(15)
 test_n      | numeric(15,3)
 big_n       | bigint
 ip_addr     | inet

- Fil

---

在我的数据库中，如果character_maximum_length、numeric_precision、numeric_scale包含NULL值，那么myType将为空。因此，我们应该为这3个字段添加COALESCE函数。 - tom10271

Answer 6

信息模式视图和pg_typeof()返回不完整的类型信息。其中，psql 提供了最精确的类型信息。（楼主可能不需要如此精确的信息，但应该知道其局限性。）

create domain test_domain as varchar(15);

create table test (
  test_id test_domain, 
  test_vc varchar(15), 
  test_n numeric(15, 3), 
  big_n bigint,
  ip_addr inet
);

使用psql和\d public.test正确显示了数据类型test_domain的使用、varchar(n)列的长度以及numeric(p, s)列的精度和刻度。

sandbox=# \d public.test
             Table "public.test"
 Column  |         Type          | Modifiers
---------+-----------------------+-----------
 test_id | test_domain           |
 test_vc | character varying(15) |
 test_n  | numeric(15,3)         |
 big_n   | bigint                |
 ip_addr | inet                  |

对information_schema视图的此查询根本不显示test_domain的使用情况。它也不报告varchar(n)和numeric(p, s)列的详细信息。

select column_name, data_type 
from information_schema.columns 
where table_catalog = 'sandbox'
  and table_schema = 'public'
  and table_name = 'test';

列名      |     数据类型
-------------+-------------------
 test_id     | 变长字符串类型
 test_vc     | 变长字符串类型
 test_n      | 数值类型
 big_n       | 大整数类型
 ip_addr     | IP地址类型

你可以通过连接其他 information_schema 视图或直接查询系统表来获取所有这些信息。使用 psql -E 可能会有所帮助。

函数 pg_typeof() 正确显示了对 test_domain 的使用，但不会报告 varchar(n) 和 numeric(p, s) 列的细节。

select pg_typeof(test_id) as test_id, 
       pg_typeof(test_vc) as test_vc,
       pg_typeof(test_n) as test_n,
       pg_typeof(big_n) as big_n,
       pg_typeof(ip_addr) as ip_addr
from test;

   测试ID   |      测试VC       | 测试N  | 大N    | IP地址
-------------+-------------------+---------+--------+---------
 测试域名    | 变长字符串类型    | 数字型  | 大整型 | 网际网路协议地址

- Mike Sherrill 'Cat Recall'

Answer 7

从information_schema中提取数据类型是可能的，但不方便（需要使用case语句连接几个列）。相反，可以使用内置函数format_type来完成此操作，但它适用于在pg_attribute中可见但在information_schema中不可见的内部类型标识符。例如：

SELECT a.attname as column_name, format_type(a.atttypid, a.atttypmod) AS data_type
FROM pg_attribute a JOIN pg_class b ON a.attrelid = b.relfilenode
WHERE a.attnum > 0 -- hide internal columns
AND NOT a.attisdropped -- hide deleted columns
AND b.oid = 'my_table'::regclass::oid; -- example way to find pg_class entry for a table

参考https://gis.stackexchange.com/a/97834的内容。

- Piotr Findeisen

---

为了后代，使用pg10将b.relfilenode替换为b.oid。 - tswaters

Answer 8

https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-PATTERNS

\gdesc 显示当前查询缓冲区结果的描述信息（即列名和数据类型）。实际上并不执行查询，但是如果查询包含某种语法错误，则会按照正常方式报告该错误。

如果当前查询缓冲区为空，则显示最近发送的查询的描述信息。

因此，您可以使用 table student_details limit 0 \gdesc 命令来输出占用更少空间的结果，而不是使用 \d 命令。

- jian

Answer 9

有很多方法可以找到特定字段或表中所有字段的数据类型。以下是所有方法的列表。

- 使用pg_typeof()函数 - 使用\d命令 - 使用SELECT语句 - 使用information_schema

使用pg_typeof()函数 pg_typeof()函数是一个内置函数，它接受列名作为参数并返回数据类型。

示例

SELECT pg_typeof(bike_launch_date)
FROM public.bike_details
LIMIT 1;

为了避免重复，将LIMIT设置为1。

使用\d命令

运行以下命令，并附上表名，以获取所有列的数据类型和其他信息。

示例

\d table_name;

要进一步探索更多剩余的方法或更好地理解，请阅读以下文章：如何在PostgreSQL中检查列类型。

- hamza._.ghouri