Учитывая имя таблицы, как мне извлечь список столбцов первичного ключа и их типы данных из функции plpgsql?
Как получить первичный ключ таблицы из Postgres через plpgsql?
Ответ 1
Вышеприведенный запрос очень плохой, поскольку он очень медленный.
Я бы порекомендовал эту официальную версию:
http://wiki.postgresql.org/wiki/Retrieve_primary_key_columns
Если необходима схема, запрос следующий:
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
Ответ 2
Чтобы обеспечить прямую часть 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';
Ответ 3
Следующий 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;
Это прямо взято отсюда.
Ответ 4
Посмотрите на pg_constraint
. Или information_schema.table_constraints
, если вы предпочитаете придерживаться стандарта SQL.
Для полного примера подключитесь к БД с помощью psql
с опцией -E и введите \d <some_table>
- вы увидите фактические запросы, используемые при описании таблицы.
Ответ 5
Остерегайтесь индексов, где порядок столбцов отличается от порядка столбцов таблицы. (то есть, если первичный ключ использовал столбцы 3, 2 и 1)
Следующий запрос намного сложнее, но возвращает столбцы в правильном порядке. (Удалите предложение "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
Ответ 6
Сохранение порядка столбцов с помощью 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
Ответ 7
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
Ответ 8
Для этого вам нужны только 2 системные таблицы:
- pg_constraint - сообщает, какие столбцы (по номеру) принадлежат первичному ключу
- pg_attribute - переводит номера столбцов в имена столбцов
Примечание: системные таблицы могут меняться между версиями 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
Ответ 9
\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)