如何通过 plpgsql 从 Postgres 获取表的主键？

小开

看一下 pg_constraint系统表。或者如果你喜欢坚持接近 SQL 标准的 information_schema.table_constraints视图。

作为一个完整的示例，使用带有“-E”选项并键入 \d <some_table>的 psql连接到数据库，您将看到描述表时使用的实际查询。

小开

为了简单介绍 SQL，可以使用以下命令列出主键列及其类型:

SELECT c.column_name, c.data_type
FROM information_schema.table_constraints tc
JOIN information_schema.constraint_column_usage AS ccu USING (constraint_schema, constraint_name)
JOIN information_schema.columns AS c ON c.table_schema = tc.constraint_schema
AND tc.table_name = c.table_name AND ccu.column_name = c.column_name
WHERE constraint_type = 'PRIMARY KEY' and tc.table_name = 'mytable';

小开

最佳答案

上面的查询非常糟糕，因为它非常慢。

如果需要模式，则查询如下所示

SELECT
pg_attribute.attname,
format_type(pg_attribute.atttypid, pg_attribute.atttypmod)
FROM pg_index, pg_class, pg_attribute, pg_namespace
WHERE
pg_class.oid = 'foo'::regclass AND
indrelid = pg_class.oid AND
nspname = 'public' AND
pg_class.relnamespace = pg_namespace.oid AND
pg_attribute.attrelid = pg_class.oid AND
pg_attribute.attnum = any(pg_index.indkey)
AND indisprimary

小开

注意列顺序与表的列顺序不同的索引。(即，如果主键使用列3、2和1)

下面的查询要复杂得多，但是以正确的顺序返回列。(删除‘ indisbasic’子句以获得表上所有索引的相同信息)

WITH ndx_list AS
(
SELECT pg_index.indexrelid
FROM pg_index, pg_class
WHERE pg_class.relname = 'test_indices_table'
AND pg_class.oid = pg_index.indrelid
AND pg_index.indisprimary
), ndx_cols AS
(
SELECT pg_class.relname AS index_name, UNNEST(i.indkey) AS col_ndx, i.indisunique, i.indisprimary
FROM pg_class, pg_index i
WHERE pg_class.oid = i.indexrelid
AND pg_class.oid IN (SELECT indexrelid FROM ndx_list)
)
SELECT ndx_cols.index_name, ndx_cols.indisunique, ndx_cols.indisprimary,
a.attname, format_type(a.atttypid, a.atttypmod), a.attnum
FROM pg_class c, pg_attribute a
JOIN ndx_cols ON (a.attnum = ndx_cols.col_ndx)
WHERE c.oid = 'test_indices_table'::regclass
AND a.attrelid = c.oid

小开

使用 generate_subscripts保留列顺序(基于 @ Paul Draper 的回答) :

SELECT
a.attname,
format_type(a.atttypid, a.atttypmod)
FROM
pg_attribute a
JOIN (SELECT *, GENERATE_SUBSCRIPTS(indkey, 1) AS indkey_subscript FROM pg_index) AS i
ON
i.indisprimary
AND i.indrelid = a.attrelid
AND a.attnum = i.indkey[i.indkey_subscript]
WHERE
a.attrelid = 'your_table'::regclass
ORDER BY
i.indkey_subscript

小开

以下 SQL声明适用于我:

SELECT a.attname
FROM   pg_index i
JOIN   pg_attribute a ON a.attrelid = i.indrelid
AND a.attnum = ANY(i.indkey)
WHERE  i.indrelid = 'tablename'::regclass
AND    i.indisprimary;

它直接取自给你。

小开

这将为您提供约束列表以及定义。

SELECT
conrelid::regclass AS table_from,
conname,
pg_get_constraintdef ( c.oid )
FROM
pg_constraint c
JOIN pg_namespace n ON n.oid = c.connamespace
WHERE
contype IN ( 'f', 'p ' )
AND conrelid::regclass::TEXT IN ( 'foo' )


ORDER BY
conrelid::regclass::TEXT,
contype DESC

小开

您实际上只需要两个系统表:

Pg _ 約束-告诉您哪些列(按数字)属于主键
Pg _ tribute-将列号转换为列名

注意: 系统表可能会在 PostgreSQL 版本之间变化，但这种情况并不经常发生(实际上非常罕见，如果有的话)。与使用 information _ schema 不同。Table _ 約束，您不需要任何特殊的权限，只需要在表上进行选择。(这在 Postgres 10.6中进行了测试)

SELECT string_agg(a.attname, ', ') AS pk
FROM
pg_constraint AS c
CROSS JOIN LATERAL UNNEST(c.conkey) AS cols(colnum) -- conkey is a list of the columns of the constraint; so we split it into rows so that we can join all column numbers onto their names in pg_attribute
INNER JOIN pg_attribute AS a ON a.attrelid = c.conrelid AND cols.colnum = a.attnum
WHERE
c.contype = 'p' -- p = primary key constraint
AND c.conrelid = '<schemaname>.<tablename>'::REGCLASS; -- regclass will type the name of the object to its internal oid

小开

\d tablename

将提供主键信息以及其他与表相关的信息，如所有列、它们的类型、相关索引、约束、规则、触发器等。您可能不需要所有这些信息，但它是获得所有细节的最快方法，一目了然，看到更多细节给你。

它返回的结果是这样的:

    Table "public.tablename"
Column |  Type   | Modifiers
--------+---------+-----------
col1   | text    | not null
col2   | numeric |
col3   | text    |
col4   | text    |
col5   | numeric |
Indexes:
"tablename_pkey" PRIMARY KEY, btree (col1)

小开

SELECT a.attname AS name, format_type(a.atttypid, a.atttypmod) AS type
FROM
pg_class AS c
JOIN pg_index AS i ON c.oid = i.indrelid AND i.indisprimary
JOIN pg_attribute AS a ON c.oid = a.attrelid AND a.attnum = ANY(i.indkey)
WHERE c.oid = 'example'::regclass

产出:

 name |  type
------+--------
id   | bigint