SQL 获取 Postgres 中表列的默认值？

Get the default values of table columns in Postgres?

提问by sjchen

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:

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

_{**Editor's note: I fixed the table definition as it has no impact on the question.}

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

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

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

我需要一个查询，它会以某种格式告诉我默认integer值为 2、text“我是默认值”和moretext“我也是默认值”。查询结果可以包括任何其他没有默认值的列的任何值，即，unimportant对我的目的不重要，根本不重要。

回答by Peter Eisentraut

Use the information schema:

使用信息模式：

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)

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

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

回答by Erwin Brandstetter

System catalogsare the source of truth in Postgres:

系统目录是 Postgres 的真实来源：

SELECT pg_get_expr(d.adbin, d.adrelid) AS default_value
FROM   pg_catalog.pg_attribute    a
LEFT   JOIN pg_catalog.pg_attrdef d ON (a.attrelid, a.attnum) = (d.adrelid,  d.adnum)
WHERE  NOT a.attisdropped           -- no dropped (dead) columns
AND    a.attnum   > 0               -- no system columns
AND    a.attrelid = 'myschema.mytable'::regclass
AND    a.attname  = 'mycolumn';

• Related answer on dba.SE discussing information schema vs. system catalogs

• dba.SE 上讨论信息模式与系统目录的相关答案

LEFT JOINguarantees a result as long as the column exists. If there is no default you get NULL- which happens to be the defaultdefault. And almost always correct. But see:

LEFT JOIN只要列存在就保证结果。如果您没有获得默认值NULL- 这恰好是默认默认值。而且几乎总是正确的。但请看：

• How to use default value of data type as column default?

• 如何使用数据类型的默认值作为列默认值？

To excludecolumns without default, use JOINinstead and be prepared to get no row occasionally.

要排除没有默认值的列，请JOIN改用并准备偶尔不获取行。

The special cast ::regclassconsiders the current setting for search_path. With names that aren't schema-qualified (which you should, to be sure!), you may or may not get the expected result. See rebuttal below. More in the manual.Related:

特殊演员会::regclass考虑 的当前设置search_path。对于不符合模式限定的名称（当然，您应该这样做！），您可能会也可能不会得到预期的结果。见下面的反驳。手册中有更多内容。有关的：

• How does the search_path influence identifier resolution and the "current schema"

• search_path 如何影响标识符解析和“当前模式”

No need to include pg_classonce we have the OID of the table. Faster without.

pg_class一旦我们有了表的 OID，就不需要包括了。没有更快。

Why the first answer wouldn't do

为什么第一个答案不行

@Zohaib's queryis almost but not quite right. There are a couple of issues. I copied it here for future reference. Do not use this:

@Zohaib 的查询几乎但不完全正确。有几个问题。我把它复制到这里以备将来参考。不要使用这个：

SELECT adsrc as default_value
 FROM pg_attrdef pad, pg_atttribute pat, pg_class pc
 WHERE pc.relname='your_table_name'
     AND pc.oid=pat.attrelid AND pat.attname='your_column_name'
     AND pat.attrelid=pad.adrelid AND pat.attnum=pad.adnum

• Copied from some blog. Good that it's mentioned, but the source should be added. People reading that blog need to be warned.

• Typo in pg_atttribute- fixed easily.

• Doesn't return any rows, if there is no default specified for the requested column. Better make that a LEFT JOIN pg_attrdef ON .., so you alwaysget a resulting row if the column exists. It will be NULL, if there is no default, which is actually the correct result because NULListhe default then.

• If you remove attnamefrom the WHERE clause, you only get values for columns that actually have a default value. Not for others. And you need to add attnameto the SELECT list or you will not know for which column.

• The query would also return the default of a column that is already dropped, which is wrong. Read about the details in the manual.

• Most importantly: the query can give completely wrongresults, as it does not take the schema name into account. There can be any number of table1.col1in a postgres database: in various schemas. If more than one have a default, you get multiple values. If the column you have in mind does not have a default, but another one in another schema does, you will be fooled and never know it.

• 复制自某博客。很好，它被提到，但应该添加来源。需要警告阅读该博客的人。

• 输入错误pg_atttribute- 轻松修复。

• 如果没有为请求的列指定默认值，则不返回任何行。最好将其设为 a LEFT JOIN pg_attrdef ON ..，因此如果该列存在，您总是会得到一个结果行。如果没有默认值，它将是 NULL，这实际上是正确的结果，因为NULL是默认值。

• 如果attname从 WHERE 子句中删除，则只能获得实际具有默认值的列的值。不是为了别人。并且您需要添加attname到 SELECT 列表中，否则您将不知道是哪一列。

• 该查询还将返回已删除的列的默认值，这是错误的。阅读手册中的详细信息。

• 最重要的是：查询可能会给出完全错误的结果，因为它没有考虑架构名称。table1.col1postgres 数据库中可以有任意数量的：在各种模式中。如果不止一个有默认值，你会得到多个值。如果您想到的列没有默认值，但另一个模式中的另一个有默认值，您将被愚弄并且永远不知道它。

To sum it up: C/P from some blog without insight went dangerously wrong.

总结一下：来自一些没有洞察力的博客的 C/P 出现了危险的错误。

回答by user9645

I liked Erwin's Answerbut was having some difficulties:

我喜欢Erwin 的答案，但遇到了一些困难：

1. Sometimes I would get column names "........pg.dropped.30.......", etc. even with the NOT a.attisdroppedqualification. It was not consistent, I wound up having to loop through the results and check the column names.
2. The returned default value sometimes had a cast attached, e.g. 'I am default'::character varyingwhich did not work when they are being stuffed into web form input values. I could not think of a good way to remove the cast suffix without doing something like .replace(/::.*/, '')which is not robust enough. I bet Erwin can figure out some magical way to EVAL()the returned value and use the a.atttypidcolumn to get the right data type.

1. 有时我会得到列名“........pg.dropped.30.......”等，即使有NOT a.attisdropped资格。它不一致，我不得不遍历结果并检查列名。
2. 返回的默认值有时会附加一个强制转换，例如'I am default'::character varying，当它们被塞入 Web 表单输入值时，它不起作用。如果不做一些.replace(/::.*/, '')不够健壮的事情，我想不出一个删除演员后缀的好方法。我敢打赌 Erwin 可以找出EVAL()返回值的某种神奇方法，并使用该a.atttypid列来获取正确的数据类型。

So I went back to what I was doing before:

所以我又回到了我之前做的事情：

BEGIN;
INSERT INTO mytable DEFAULT VALUES RETURNING *;
ROLLBACK;

One thing to note here is that any SERIALcolumns will get incremented. It likely does not matter so long as it is unique if it is just a table index. Otherwise it is a deal breaker.

这里要注意的一件事是任何SERIAL列都会增加。如果它只是一个表索引，那么它可能并不重要，只要它是唯一的。否则，这是一个交易破坏者。

The other thing to watch out for is any TRIGGER AFTER/BEFORE INSERTwhich will fire even though the INSERTgets rolled back (I think any changes the trigger function makes will get rolled back though.)

另一件需要注意的事情是TRIGGER AFTER/BEFORE INSERT即使INSERT回滚也会触发的任何事情（我认为触发器函数所做的任何更改都会回滚。）

回答by Morris de Oryx

I'm spending some time trying to get to grips with the pg_catalog tables. I'm assuming the names and such come back from the days when letters had to be carved individually and were, consequently, very expensive ;-) Anyway, that's all a fact of life. I wanted to get the default value for a column and ran into this thread. I saw a mention of wanting to have Erwin Brandstetter's code as a function. I've wrapped it up and figured I'd add it to the archives:

我花了一些时间试图掌握 pg_catalog 表。我假设这些名字和这样的东西从字母必须单独雕刻的时代回来了，因此，非常昂贵;-) 无论如何，这都是生活中的事实。我想获得一列的默认值并遇到了这个线程。我看到有人提到想要将 Erwin Brandstetter 的代码作为函数。我已经把它包起来并想我会把它添加到档案中：

CREATE OR REPLACE FUNCTION data.column_default (qualified_name text, column_name text)
  RETURNS text
 AS $$

SELECT d.adsrc AS default_value -- A human-readable representation of the default value, already typed as text.
FROM   pg_catalog.pg_attribute a
LEFT   JOIN pg_catalog.pg_attrdef d ON (a.attrelid, a.attnum)
                                     = (d.adrelid,  d.adnum)
WHERE  NOT a.attisdropped   -- no dropped (dead) columns
AND    a.attnum > 0         -- no system columns
AND    a.attrelid = qualified_name::regclass
AND    a.attname = column_name;

$$ LANGUAGE sql;

ALTER FUNCTION data.column_default (qualified_name text, column_name text) OWNER TO user_bender;

I'm using the code (probably in an inhumane manner) like so:

我正在使用代码（可能以不人道的方式），如下所示：

select pg_class.relnamespace::regnamespace as schema_name,
        attrelid::regclass as parent_name,
        attname,
       format_type(atttypid, atttypmod) as data_type,
       column_default(attrelid::regclass::text,attname),
       attnum

 from pg_attribute
 left join pg_class on (pg_class.oid = pg_attribute.attrelid::regclass)

From here I figure I'll write a view, etc., once I know better what I'm really after. For now, I'm using a CTE as a temporary view:

从这里开始，我想我会写一个视图等，一旦我更好地知道我真正想要的是什么。现在，我使用 CTE 作为临时视图：

with attributes as
(select pg_class.relnamespace::regnamespace as schema_name,
        attrelid::regclass as parent_name,
        attname,
       format_type(atttypid, atttypmod) as data_type,
       column_default(attrelid::regclass::text,attname),
       attnum

 from pg_attribute
 left join pg_class on (pg_class.oid = pg_attribute.attrelid::regclass)
)

select *
  from attributes
 where parent_name::text = 'sales'

Corrections, improvements, and suggestions all welcome...I'm just getting my feet wet in pg_catalog.

欢迎更正、改进和建议......我刚刚在 pg_catalog 中弄湿了我的脚。

回答by Zohaib

 SELECT adsrc as default_value
 FROM pg_attrdef pad, pg_atttribute pat, pg_class pc
 WHERE pc.relname='your_table_name'
     AND pc.oid=pat.attrelid AND pat.attname='your_column_name'
     AND pat.attrelid=pad.adrelid AND pat.attnum=pad.adnum

I found this query for postgresql on one of the blogs. you can try this to check if it works. To get values of all coumns, you can try removing AND pat.attname='your_column_name'from where clause.

我在其中一个博客上找到了对 postgresql 的这个查询。你可以试试这个来检查它是否有效。要获取所有参数的值，您可以尝试AND pat.attname='your_column_name'从 where 子句中删除。

回答by francs

you can just type " \d table_name" command , then It will displays some information about the

table, such as the default value of a column.

表，例如列的默认值。

--create table

--创建表

skytf=> CREATE TABLE mytable (
skytf(>     a integer DEFAULT 2,
skytf(>     b varchar(64)  DEFAULT 'I am default',
skytf(>     c varchar(64)  DEFAULT 'I am also default'
skytf(> );
CREATE TABLE

--show table information

--显示表信息

skytf=> \d mytable
                              Table "skytf.mytable"
 Column |         Type          |                   Modifiers                    
--------+-----------------------+------------------------------------------------
 a      | integer               | default 2
 b      | character varying(64) | default 'I am default'::character varying
 c      | character varying(64) | default 'I am also default'::character varying