获取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 
);

我需要一个以某种格式告诉我默认值<$ c $的查询c>整数为2，文本为我是默认， 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屋！