获取 Postgres 中表列的默认值? [英] Get the default values of table columns in Postgres?

问题描述

我正在寻找一种方法来运行查询以查找 Postgres 中表的列的默认值.例如，如果我使用以下查询创建了一个表:

I'm looking for a way to run a query to find the default values of the columns of a table in Postgres. For example, if I made a table with the following query:

_{**编者注:我修正了表定义，因为它对问题没有影响.}

CREATE TABLE mytable (
    integer int DEFAULT 2,
    text varchar(64) DEFAULT 'I am default',
    moretext varchar(64) DEFAULT 'I am also default',
    unimportant int 
);

我需要一个查询，以某种格式告诉我integer 的默认值是2，text 是'I am default'，而moretext 是我也是默认的".查询结果可以包含任何其他没有默认值的列的任何值，即 unimportant 对我的目的来说并不重要，根本不重要.

I need a query that would tell me, in some format, that the default for integer is 2, text is 'I am default', and moretext is 'I am also default'. The query result can include any value for any other column that doesn't have a default, i.e., unimportant is unimportant for my purposes and doesn't matter at all.

推荐答案

使用信息架构:

SELECT column_name, column_default
FROM information_schema.columns
WHERE (table_schema, table_name) = ('public', 'mytable')
ORDER BY ordinal_position;

 column_name │             column_default             
─────────────┼────────────────────────────────────────
 integer     │ 2
 text        │ 'I am default'::character varying
 moretext    │ 'I am also default'::character varying
 unimportant │ 
(4 rows)

根据模式命名，这应该适用于任何 SQL 数据库系统.

Up to the schema naming, this should work in any SQL database system.

这篇关于获取 Postgres 中表列的默认值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！