PostgreSQL“DESCRIBE TABLE”

作者: ❀⚘粉༵红༵回༵忆༵⚘❀
发布时间: 2025-01-12 01:36:50 (13天前)
转自：

19 条回复

0#
回复此人
⑩Ⅵ嵗Ｄ夨憶 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”> <P> 使用以下SQL语句 </p> <pre> <code> SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'tbl_name' AND COLUMN_NAME = 'col_name' </code> </pre> <P> 如果替换tbl_name和col_name，它将显示您要查找的特定列的数据类型。 </p> </DIV>

编辑
1#
回复此人
v-star*위위 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”> <P> 除了PostgreSQL方式（\ d'thing'或\ dt'table'或\ ds'sequence'等等） </p> <P> SQL标准方式，如图所示 <a href="https://stackoverflow.com/questions/100504/what-is-the-sql-command-to-return-the-field-names-of-a-table#100513"> 这里 </A> ： </p> <pre> <code> select column_name, data_type, character_maximum_length from INFORMATION_SCHEMA.COLUMNS where table_name = '<name of table>'; </code> </pre> <P> 它受到许多数据库引擎的支持。 </p> </DIV>

编辑
2#
回复此人
不见你 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”> <P> 这应该是解决方案： </p> <pre> <code> SELECT * FROM information_schema.columns WHERE table_schema = 'your_schema' AND table_name = 'your_table' </code> </pre> </DIV>

编辑
3#
回复此人
一生浮华 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”> <P> 查询的这种变化（如在其他答案中所解释的）对我有用。 </p> <pre> <code> SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_NAME = 'city'; </code> </pre> <P> 这里详细描述： <a href="http://www.postgresqltutorial.com/postgresql-describe-table/" rel="noreferrer"> http://www.postgresqltutorial.com/postgresql-describe-table/ </A> </p> </DIV>

编辑
4#
回复此人
NetworkAttachedStorage | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”> <P> 如果要从查询而不是psql获取它，可以查询目录模式。这是一个执行该操作的复杂查询： </p> <pre> <code> SELECT f.attnum AS number, f.attname AS name, f.attnum, f.attnotnull AS notnull, pg_catalog.format_type(f.atttypid,f.atttypmod) AS type, CASE WHEN p.contype = 'p' THEN 't' ELSE 'f' END AS primarykey, CASE WHEN p.contype = 'u' THEN 't' ELSE 'f' END AS uniquekey, CASE WHEN p.contype = 'f' THEN g.relname END AS foreignkey, CASE WHEN p.contype = 'f' THEN p.confkey END AS foreignkey_fieldnum, CASE WHEN p.contype = 'f' THEN g.relname END AS foreignkey, CASE WHEN p.contype = 'f' THEN p.conkey END AS foreignkey_connnum, CASE WHEN f.atthasdef = 't' THEN d.adsrc END AS default FROM pg_attribute f JOIN pg_class c ON c.oid = f.attrelid JOIN pg_type t ON t.oid = f.atttypid LEFT JOIN pg_attrdef d ON d.adrelid = c.oid AND d.adnum = f.attnum LEFT JOIN pg_namespace n ON n.oid = c.relnamespace LEFT JOIN pg_constraint p ON p.conrelid = c.oid AND f.attnum = ANY (p.conkey) LEFT JOIN pg_class AS g ON p.confrelid = g.oid WHERE c.relkind = 'r'::char AND n.nspname = '%s' -- Replace with Schema name AND c.relname = '%s' -- Replace with table name AND f.attnum > 0 ORDER BY number ; </code> </pre> <P> 它非常复杂，但它确实向您展示了PostgreSQL系统目录的强大功能和灵活性，并且可以帮助您实现pg_catalog掌握;-)。请务必更改查询中的％s。第一个是Schema，第二个是表名。 </p> </DIV>

编辑
5#
回复此人
蜡笔小辛 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”> <P> 您也可以使用以下查询进行检查 </p> <pre> <code> Select * from schema_name.table_name limit 0; </code> </pre> <P> 示例：我的表有2列名称和密码。给出截图如下。 </p> <P> <a href="https://i.stack.imgur.com/QokcT.png" rel="nofollow noreferrer"> <img src =“https://i.stack.imgur.com/QokcT.png”alt =“添加图片”/> </A> </p> <P> *使用PG admin3 </p> </DIV>

编辑
6#
回复此人
林老爷的日常 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”> <P> 你可以做一个 <code> \d *search pattern * </code> 的<strong> 用星号 </强> 查找与您感兴趣的搜索模式匹配的表格。 </p> </DIV>

编辑
7#
回复此人
布偶的表弟派大星丶 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”> <P> psql相当于 <code> DESCRIBE TABLE </code> 是 <code> \d table </code> 。 </p> <P> 有关更多详细信息，请参阅PostgreSQL手册的psql部分。 </p> </DIV>

编辑
8#
回复此人
甲基蓝 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”> <P> 试试这个（在 <code> psql </code> 命令行工具）： </p> <pre> <code> \d+ tablename </code> </pre> <P> 看到 <a href="http://www.postgresql.org/docs/current/interactive/app-psql.html#APP-PSQL-META-COMMANDS" rel="noreferrer"> 手册 </A> 了解更多信息。 </p> </DIV>

编辑
9#
回复此人
早岁那知世事艰 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”> <P> 在的<strong> MySQL的 </强> ，DESCRIBE table_name </p> <HR /> <P> 在的<strong> PostgreSQL的 </强> ，\ d table_name </p> <HR /> <P> 或者，您可以使用此长命令： </p> <pre> <code> SELECT a.attname AS Field, t.typname || '(' || a.atttypmod || ')' AS Type, CASE WHEN a.attnotnull = 't' THEN 'YES' ELSE 'NO' END AS Null, CASE WHEN r.contype = 'p' THEN 'PRI' ELSE '' END AS Key, (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid), '\'(.*)\'') FROM pg_catalog.pg_attrdef d WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) AS Default, '' as Extras FROM pg_class c JOIN pg_attribute a ON a.attrelid = c.oid JOIN pg_type t ON a.atttypid = t.oid LEFT JOIN pg_catalog.pg_constraint r ON c.oid = r.conrelid AND r.conname = a.attname WHERE c.relname = 'tablename' AND a.attnum > 0 ORDER BY a.attnum </code> </pre> </DIV>

编辑
10#
回复此人
薄情 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”> <P> 描述表的最佳方式，例如列，类型，列的修饰符等。 </p> <pre> <code> \d+ tablename or \d tablename </code> </pre> </DIV>

编辑
11#
回复此人
大黑骡子王 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”> <P> 我为get表架构制定了以下脚本。 </p> <pre> <code> 'CREATE TABLE ' || 'yourschema.yourtable' || E'\n(\n' || array_to_string( array_agg( ' ' || column_expr ) , E',\n' ) || E'\n);\n' from ( SELECT ' ' || column_name || ' ' || data_type || coalesce('(' || character_maximum_length || ')', '') || case when is_nullable = 'YES' then ' NULL' else ' NOT NULL' end as column_expr FROM information_schema.columns WHERE table_schema || '.' || table_name = 'yourschema.yourtable' ORDER BY ordinal_position ) column_list; </code> </pre> </DIV>

编辑
12#
回复此人
满目山河 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”> <P> 为了改进其他答案的SQL查询（这很棒！），这里是一个修改过的查询。它还包括约束名称，继承信息以及分解为组成部分（类型，长度，精度，比例）的数据类型。它还会过滤掉已删除的列（数据库中仍存在这些列）。 </p> <pre> <code> SELECT n.nspname as schema, c.relname as table, f.attname as column, f.attnum as column_id, f.attnotnull as not_null, f.attislocal not_inherited, f.attinhcount inheritance_count, pg_catalog.format_type(f.atttypid,f.atttypmod) AS data_type_full, t.typname AS data_type_name, CASE WHEN f.atttypmod >= 0 AND t.typname <> 'numeric'THEN (f.atttypmod - 4) --first 4 bytes are for storing actual length of data END AS data_type_length, CASE WHEN t.typname = 'numeric' THEN (((f.atttypmod - 4) >> 16) & 65535) END AS numeric_precision, CASE WHEN t.typname = 'numeric' THEN ((f.atttypmod - 4)& 65535 ) END AS numeric_scale, CASE WHEN p.contype = 'p' THEN 't' ELSE 'f' END AS is_primary_key, CASE WHEN p.contype = 'p' THEN p.conname END AS primary_key_name, CASE WHEN p.contype = 'u' THEN 't' ELSE 'f' END AS is_unique_key, CASE WHEN p.contype = 'u' THEN p.conname END AS unique_key_name, CASE WHEN p.contype = 'f' THEN 't' ELSE 'f' END AS is_foreign_key, CASE WHEN p.contype = 'f' THEN p.conname END AS foreignkey_name, CASE WHEN p.contype = 'f' THEN p.confkey END AS foreign_key_columnid, CASE WHEN p.contype = 'f' THEN g.relname END AS foreign_key_table, CASE WHEN p.contype = 'f' THEN p.conkey END AS foreign_key_local_column_id, CASE WHEN f.atthasdef = 't' THEN d.adsrc END AS default_value FROM pg_attribute f JOIN pg_class c ON c.oid = f.attrelid JOIN pg_type t ON t.oid = f.atttypid LEFT JOIN pg_attrdef d ON d.adrelid = c.oid AND d.adnum = f.attnum LEFT JOIN pg_namespace n ON n.oid = c.relnamespace LEFT JOIN pg_constraint p ON p.conrelid = c.oid AND f.attnum = ANY (p.conkey) LEFT JOIN pg_class AS g ON p.confrelid = g.oid WHERE c.relkind = 'r'::char AND f.attisdropped = false AND n.nspname = '%s' -- Replace with Schema name AND c.relname = '%s' -- Replace with table name AND f.attnum > 0 ORDER BY f.attnum ; </code> </pre> </DIV>

编辑
13#
回复此人
一只特立独行的猪 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”> <pre> <code> Use this command \d table name like \d queuerecords Table "public.queuerecords" Column | Type | Modifiers -----------+-----------------------------+----------- id | uuid | not null endtime | timestamp without time zone | payload | text | queueid | text | starttime | timestamp without time zone | status | text | </code> </pre> </DIV>

编辑
14#
回复此人
哈哈哈哈 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”> <pre> <code> In postgres \d is used to describe the table structure. e.g. \d schema_name.table_name; this command will provide you the basic info of table such as, columns, type and modifiers. If you want more info about table use \d+ schema_name.table_name; this will give you extra info such as, storage, stats target and description </code> </pre> </DIV>

编辑
15#
回复此人
Moso31 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”> <P> 您可以使用psql斜杠命令执行此操作： </p> <pre> <code> \d myTable describe table </code> </pre> <P> 它也适用于其他对象： </p> <pre> <code> \d myView describe view \d myIndex describe index \d mySequence describe sequence </code> </pre> <P> 资源： <a href="http://www.faqs.org/docs/ppbook/c4890.htm" rel="noreferrer"> faqs.org </A> </p> </DIV>

编辑
16#
回复此人
我头上有犄角 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”> <P> / dt是commad，它列出了数据库中存在的所有表。运用 <BR/> / d命令和/ d +我们可以获取表的详细信息。 sysntax就像 <BR/> * / d table_name（或）\ d + table_name </p> </DIV>

编辑
17#
回复此人
筱梨 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”> <P> 除了命令行 <code> \d+ <table_name> </code> 你已经找到了，你也可以使用 <a href="http://www.postgresql.org/docs/current/static/information-schema.html"> 信息架构 </A> 使用查找列数据 <a href="http://www.postgresql.org/docs/current/static/infoschema-columns.html"> info_schema.columns </A> </p> <pre> <code> SELECT * FROM info_schema.columns WHERE table_schema = 'your_schema' AND table_name = 'your_table' </code> </pre> </DIV>

编辑

登录后才能参与评论