SQL 如何通过 plpgsql 从 Postgres 获取表的主键？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1214576/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I get the primary key(s) of a table from Postgres via plpgsql?
提问by jsight
Given a table name, how do I extract a list of primary key columns and their datatypes from a plpgsql function?
给定表名，如何从 plpgsql 函数中提取主键列及其数据类型的列表？
采纳答案by user3094383
The query above is very bad as it is really slow.
上面的查询非常糟糕，因为它真的很慢。
I would recommend this official version:
我会推荐这个官方版本：
http://wiki.postgresql.org/wiki/Retrieve_primary_key_columns
http://wiki.postgresql.org/wiki/Retrieve_primary_key_columns
if schema is needed the query is as follows
如果需要架构，查询如下
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
回答by Jamie Love
To provide a straight bit of SQL, you can list the primary key columns and their types with:
要提供简单的 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';
回答by BullyWiiPlaza
回答by Milen A. Radev
Take a look at pg_constraintsystem table. Or information_schema.table_constraintsview if you prefer to stick close to the SQL standard.
看一下pg_constraint系统表。或者information_schema.table_constraints查看您是否更喜欢接近 SQL 标准。
For a complete example connect to a DB using psqlwith the "-E" option and type \d <some_table>- you'll see the actual queries used in describing a table.
有关使用psql“-E”选项和类型连接到数据库的完整示例，\d <some_table>您将看到用于描述表的实际查询。
回答by Joe Samanek
You really only need 2 system tables for that:
你真的只需要 2 个系统表：
- pg_constraint - tells you which columns (by number) belong to the primary key
- pg_attribute - translates the column numbers to column names
- pg_constraint - 告诉您哪些列（按编号）属于主键
- pg_attribute - 将列号转换为列名
Note: the system tables might change between PostgreSQL versions, but it doesn't happen often (actually very rarely if at all). And unlike using the information_schema.table_constraints, you don't need any special permissions, just select on the table. (This was tested in Postgres 10.6)
注意：系统表可能会在 PostgreSQL 版本之间发生变化，但并不经常发生（实际上很少发生，如果有的话）。与使用 information_schema.table_constraints 不同，您不需要任何特殊权限，只需在表上选择即可。（这是在 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
回答by Konrad
\d tablename
will give you the primary key info along with other table related information such as all columns, their types, associated indexes, constraints, rules, triggers etc. You probably don't need all that information, but it is the fastest way to get all details at a glance, see more details here.
将为您提供主键信息以及其他与表相关的信息，例如所有列、它们的类型、关联索引、约束、规则、触发器等。您可能不需要所有这些信息，但这是获取所有信息的最快方法详细信息一目了然，请在此处查看更多详细信息。
It returns something like this:
它返回如下内容：
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)
回答by user2553316 Ns
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
回答by jamesvl
Beware of indexes where the column order differs from the table's column order. (i.e. if the primary key used columns 3, 2, and 1)
注意列顺序与表的列顺序不同的索引。（即，如果主键使用第 3、2 和 1 列）
The following query is much more complex, but returns the columns in the proper order. (Remove the 'indisprimary' clause to get the same info for all indexes on a table)
以下查询要复杂得多，但会以正确的顺序返回列。（删除“indisprimary”子句以获取表上所有索引的相同信息）
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
回答by snipsnipsnip
Preserving column order using generate_subscripts:
使用generate_subscripts以下方法保留列顺序：
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
