У меня есть проект, и мне нужен запрос для получения всех атрибутов столбцов (имя столбца, позиция, тип данных, а не нуль и комментарии) все это с использованием имени таблицы.
Я получил имя столбца, тип данных позиции и не нуль? с этим запросом:
SELECT column_name, data_type, ordinal_position, is_nullable
FROM information_schema."columns"
WHERE "table_name"='TABLE-NAME'
Но мне нужны комментарии!
Вот запрос к системному каталогу, который должен получить все, что вам нужно (с бонусным полем первичного ключа, брошенным бесплатно).
SELECT DISTINCT
a.attnum as num,
a.attname as name,
format_type(a.atttypid, a.atttypmod) as typ,
a.attnotnull as notnull,
com.description as comment,
coalesce(i.indisprimary,false) as primary_key,
def.adsrc as default
FROM pg_attribute a
JOIN pg_class pgc ON pgc.oid = a.attrelid
LEFT JOIN pg_index i ON
(pgc.oid = i.indrelid AND i.indkey[0] = a.attnum)
LEFT JOIN pg_description com on
(pgc.oid = com.objoid AND a.attnum = com.objsubid)
LEFT JOIN pg_attrdef def ON
(a.attrelid = def.adrelid AND a.attnum = def.adnum)
WHERE a.attnum > 0 AND pgc.oid = a.attrelid
AND pg_table_is_visible(pgc.oid)
AND NOT a.attisdropped
AND pgc.relname = 'TABLE_NAME' -- Your table name here
ORDER BY a.attnum;
Что бы вернуть результаты, например:
num | name | typ | notnull | comment | primary_key
-----+-------------+-----------------------------+---------+---------------------+-------------
1 | id | integer | t | a primary key thing | t
2 | ref | text | f | | f
3 | created | timestamp without time zone | t | | f
4 | modified | timestamp without time zone | t | | f
5 | name | text | t | | f
NOT NULLCOMMENT, определенный для столбцаPRIMARY KEYПостроено на ответе @Chris:
SELECT a.attnum
,a.attname AS name
,format_type(a.atttypid, a.atttypmod) AS typ
,a.attnotnull AS notnull
,coalesce(p.indisprimary, FALSE) AS primary_key
,f.adsrc AS default_val
,d.description AS col_comment
FROM pg_attribute a
LEFT JOIN pg_index p ON p.indrelid = a.attrelid AND a.attnum = ANY(p.indkey)
LEFT JOIN pg_description d ON d.objoid = a.attrelid AND d.objsubid = a.attnum
LEFT JOIN pg_attrdef f ON f.adrelid = a.attrelid AND f.adnum = a.attnum
WHERE a.attnum > 0
AND NOT a.attisdropped
AND a.attrelid = 'schema.tbl'::regclass -- table may be schema-qualified
ORDER BY a.attnum;
Но:
В базе данных имена таблиц не уникальны и, следовательно, также не входят в системный каталог. Возможно, вам понадобится схема, чтобы получить имя.
Используйте условие a.attrelid = 'tbl'::regclass как условие. Таким образом, вы можете передать myschema.mytbl как имя и устранить неоднозначность. Тогда нет необходимости присоединяться к pg_class вообще в этом случае.
Кроме того, видимость автоматически проверяется на regclass, и нет необходимости в pg_table_is_visible().
A первичный ключ может охватывать несколько столбцов. Я позабочусь об этом, присоединившись к pg_index на a.attnum = ANY(p.indkey). indkey имеет тип int2vecor, который является частным случаем int2[], который используется только в каталогах.
Я нашел psql -E полезным для этого класса проблем.
Специализированный запрос, подобный этому, может сломаться после основного обновления версии. Postgres не гарантирует, что таблицы каталогов остаются стабильными. Чрезвычайно маловероятно, что основные элементы меняются, но чем сложнее и сложнее ваш запрос, тем больше вероятность. Вместо этого вы можете использовать информационную схему, которая стандартизирована, но также сравнительно медленная.