Получить значения столбцов таблицы по умолчанию в Postgres?

Ответ 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