Получить значения столбцов таблицы по умолчанию в Postgres?
Я ищу способ запустить запрос, чтобы найти значения по умолчанию столбцов таблицы в Postgres. Например, если я сделал таблицу со следующим запросом:
** Примечание редактора: я исправил определение таблицы, поскольку оно не влияет на вопрос.
CREATE TABLE mytable (
integer int DEFAULT 2,
text varchar(64) DEFAULT 'I am default',
moretext varchar(64) DEFAULT 'I am also default',
unimportant int
);
Мне нужен запрос, который скажет мне, в каком-то формате, что значение по умолчанию для integer
равно 2, text
равно "Я по умолчанию", а moretext
- "Я тоже по умолчанию". Результат запроса может включать любое значение для любого другого столбца, у которого нет значения по умолчанию, т.е. unimportant
не имеет значения для моих целей и вообще не имеет значения.
Ответы
Ответ 1
Используйте информационную схему:
SELECT column_name, column_default
FROM information_schema.columns
WHERE (table_schema, table_name) = ('public', 'mytable')
ORDER BY ordinal_position;
column_name │ column_default
─────────────┼────────────────────────────────────────
integer │ 2
text │ 'I am default'::character varying
moretext │ 'I am also default'::character varying
unimportant │
(4 rows)
До наименования схемы это должно работать в любой системе баз данных SQL.
Ответ 2
Запрос @Zohaib почти, но не совсем правильный. Есть пара вопросов.
Я скопировал его в свой ответ для дальнейшего использования. Не используйте это:
SELECT adsrc as default_value
FROM pg_attrdef pad, pg_atttribute pat, pg_class pc
WHERE pc.relname='your_table_name'
AND pc.oid=pat.attrelid AND pat.attname='your_column_name'
AND pat.attrelid=pad.adrelid AND pat.attnum=pad.adnum
Он скопирован из какого-то блога. То, что он упоминает, это хорошо. Но в таком случае источник должен быть добавлен. Люди, читающие этот блог, должны быть предупреждены.
Опечатка в pg_atttribute
- исправлена легко.
Не возвращает никаких строк, если для запрошенного столбца не задано значение по умолчанию. Лучше сделать это LEFT JOIN pg_attrdef ON ..
, чтобы вы всегда получали результирующую строку, если столбец существует. Это будет NULL, если нет значения по умолчанию, что на самом деле является правильным результатом, потому что NULL
тогда является значением по умолчанию.
Если вы удалите attname
из предложения WHERE, вы получите значения только для столбцов, которые действительно имеют значение по умолчанию. Не для других. И вам нужно добавить attname
в список SELECT, иначе вы не будете знать, для какого столбца.
Запрос также возвращает значение по умолчанию для уже удаленного столбца, что является неправильным. Подробнее о читайте в руководстве.
Но самое главное: запрос может дать совершенно неверные результаты, так как он не учитывает имя схемы. В базе данных postgres может быть любое количество table1.col1
: в разных схемах. Если у нескольких есть значения по умолчанию, вы получите несколько значений. Если у столбца, который вы имеете в виду, нет значения по умолчанию, а у другого в другой схеме - вы будете обмануты и никогда его не узнаете.
Подводя итог:
Копирование/вставка из какого-либо блога без понимания прошло опасно неправильно!
Попробуйте вместо этого:
SELECT d.adsrc AS default_value
FROM pg_catalog.pg_attribute a
LEFT JOIN pg_catalog.pg_attrdef d ON (a.attrelid, a.attnum)
= (d.adrelid, d.adnum)
WHERE NOT a.attisdropped -- no dropped (dead) columns
AND a.attnum > 0 -- no system columns
AND a.attrelid = 'myschema.mytable'::regclass
AND a.attname = 'mycolumn';
LEFT JOIN
гарантирует, что вы получите результат, пока существует столбец. Если вы хотите исключить из них, сделайте вместо этого JOIN
. Но будьте готовы к тому, чтобы время от времени не спорить.
Имейте в виду, что специальное приведение ::regclass
учитывает текущую настройку для search_path
, поэтому даже если вы не включите схему в имя (что вам следует, безусловно!), Скорее всего, вы получите ожидаемый результат ,
Подробнее об этом читайте в руководстве.
Включение pg_class
является избыточным, когда у нас есть OID таблицы. Пропустить и ускорить запрос.
Ответ 3
Мне понравился Erwin Answer, но он имел некоторые трудности:
- Иногда я получал имена столбцов "........ pg.dropped.30......." и т.д. даже с квалификацией
NOT a.attisdropped
. Это было непротиворечиво, мне пришлось перебирать результаты и проверять имена столбцов.
- Возвращаемое значение по умолчанию иногда включало приведение, например.
'I am default'::character varying
, который не работает, когда они заполняются входными значениями веб-формы. Я не мог придумать хороший способ удалить суффикс лифта, не делая что-то вроде .replace(/::.*/, '')
, которое недостаточно устойчиво. Могу поверить, что Эрвин может найти магический способ вернуть EVAL()
возвращаемое значение и использовать столбец a.atttypid
, чтобы получить правильный тип данных.
Итак, я вернулся к тому, что я делал раньше:
BEGIN;
INSERT INTO mytable DEFAULT VALUES RETURNING *;
ROLLBACK;
Следует отметить, что любые столбцы SERIAL
будут увеличиваться. Это, вероятно, не имеет значения, если оно уникально, если оно является только табличным индексом. В противном случае это прерыватель транзакций.
Другая вещь, на которую нужно обратить внимание, - это любой TRIGGER AFTER/BEFORE INSERT
, который будет срабатывать, даже если INSERT
будет откат (я думаю, что любые изменения, вызванные функцией триггера, будут откатны.)
Ответ 4
Я провожу некоторое время, пытаясь разобраться с таблицами pg_catalog. Я предполагаю, что имена и тому подобное возвращаются со времен, когда письма должны были вырезаться индивидуально и, следовательно, были очень дорогими ;-) В любом случае, это все факт жизни. Я хотел получить значение по умолчанию для столбца и столкнулся с этим потоком. Я видел упоминание о желании иметь код Эрвина Брандштеттера в качестве функции. Я завернул его и решил добавить в архив:
CREATE OR REPLACE FUNCTION data.column_default (qualified_name text, column_name text)
RETURNS text
AS $$
SELECT d.adsrc AS default_value -- A human-readable representation of the default value, already typed as text.
FROM pg_catalog.pg_attribute a
LEFT JOIN pg_catalog.pg_attrdef d ON (a.attrelid, a.attnum)
= (d.adrelid, d.adnum)
WHERE NOT a.attisdropped -- no dropped (dead) columns
AND a.attnum > 0 -- no system columns
AND a.attrelid = qualified_name::regclass
AND a.attname = column_name;
$$ LANGUAGE sql;
ALTER FUNCTION data.column_default (qualified_name text, column_name text) OWNER TO user_bender;
Я использую код (вероятно, в нечеловеческой манере) следующим образом:
select pg_class.relnamespace::regnamespace as schema_name,
attrelid::regclass as parent_name,
attname,
format_type(atttypid, atttypmod) as data_type,
column_default(attrelid::regclass::text,attname),
attnum
from pg_attribute
left join pg_class on (pg_class.oid = pg_attribute.attrelid::regclass)
Отсюда я полагаю, что напишу представление и т.д., как только узнаю лучше, что я действительно хочу. Сейчас я использую CTE как временное представление:
with attributes as
(select pg_class.relnamespace::regnamespace as schema_name,
attrelid::regclass as parent_name,
attname,
format_type(atttypid, atttypmod) as data_type,
column_default(attrelid::regclass::text,attname),
attnum
from pg_attribute
left join pg_class on (pg_class.oid = pg_attribute.attrelid::regclass)
)
select *
from attributes
where parent_name::text = 'sales'
Исправления, улучшения и предложения приветствуются... Я просто промочил ноги в pg_catalog.
Ответ 5
SELECT adsrc as default_value
FROM pg_attrdef pad, pg_atttribute pat, pg_class pc
WHERE pc.relname='your_table_name'
AND pc.oid=pat.attrelid AND pat.attname='your_column_name'
AND pat.attrelid=pad.adrelid AND pat.attnum=pad.adnum
Я нашел этот запрос для postgresql в одном из блогов. вы можете попробовать это, чтобы проверить, работает ли оно. Чтобы получить значения всех coumns, вы можете попробовать удалить AND pat.attname='your_column_name'
из предложения where.
Ответ 6
you can just type " \d table_name" command , then It will displays some information about the
например значение по умолчанию для столбца.
- создать таблицу
skytf=> CREATE TABLE mytable (
skytf(> a integer DEFAULT 2,
skytf(> b varchar(64) DEFAULT 'I am default',
skytf(> c varchar(64) DEFAULT 'I am also default'
skytf(> );
CREATE TABLE
- показать информацию о таблице
skytf=> \d mytable
Table "skytf.mytable"
Column | Type | Modifiers
--------+-----------------------+------------------------------------------------
a | integer | default 2
b | character varying(64) | default 'I am default'::character varying
c | character varying(64) | default 'I am also default'::character varying