postgresql获取主键、索引键等信息
时间: 2024-01-06 16:03:54 浏览: 74
要获取 PostgreSQL 数据库中表的主键、索引键等信息,您可以使用以下查询语句:
获取主键信息:
```sql
SELECT
tc.table_schema,
tc.table_name,
kc.column_name,
kc.ordinal_position
FROM
information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kc ON tc.constraint_name = kc.constraint_name
WHERE
tc.constraint_type = 'PRIMARY KEY'
AND tc.table_schema = 'your_schema_name'
AND tc.table_name = 'your_table_name'
ORDER BY
tc.table_schema,
tc.table_name,
kc.ordinal_position;
```
获取索引键信息:
```sql
SELECT
t.relname AS table_name,
i.relname AS index_name,
a.attname AS column_name,
ix.indisunique AS is_unique,
ix.indisprimary AS is_primary
FROM
pg_class t,
pg_class i,
pg_index ix,
pg_attribute a
WHERE
t.oid = ix.indrelid
AND i.oid = ix.indexrelid
AND a.attrelid = t.oid
AND a.attnum = ANY(ix.indkey)
AND t.relkind = 'r'
AND t.relname = 'your_table_name'
ORDER BY
t.relname,
i.relname;
```
请将 `your_schema_name` 替换为您的模式名称,将 `your_table_name` 替换为您的表名称。这些查询语句将返回给定表的主键、索引键等相关信息,包括列名称、索引名称、是否唯一和是否是主键。
相关推荐
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)