sql - tipo - generador de consultas arcgis

¿Cómo obtener una lista de nombres de columnas y tipos de datos de una tabla en PostgreSQL?

Question

Con la siguiente consulta, podemos obtener una lista de nombres de columnas y tipos de datos de una tabla en PostgreSQL.

Answer 1

Abra la línea de comando psql y escriba:

/d+ table_name

Answer 2

No olvide agregar el nombre del esquema en caso de que tenga varios esquemas con los mismos nombres de tabla.

SELECT column_name, data_type FROM information_schema.columns WHERE table_name = ''your_table_name'' and table_schema = ''your_schema_name'';

o utilizando psql:

/d+ your_schema_name.your_table_name

Answer 3

Para obtener información sobre la columna de la tabla, puede utilizar:

/dt+ [tablename]

Para obtener información sobre el tipo de datos en la tabla, puede utilizar:

/dT+ [datatype]

Answer 4

Respuesta actualizada de Pratik para admitir más esquemas y nulasbles:

SELECT
"pg_attribute".attname as "Column",
pg_catalog.format_type("pg_attribute".atttypid, "pg_attribute".atttypmod) as "Datatype",
not("pg_attribute".attnotnull) AS "Nullable"
FROM
pg_catalog.pg_attribute "pg_attribute"
WHERE
"pg_attribute".attnum > 0
AND NOT "pg_attribute".attisdropped
AND "pg_attribute".attrelid = (
SELECT "pg_class".oid
FROM pg_catalog.pg_class "pg_class"
LEFT JOIN pg_catalog.pg_namespace "pg_namespace" ON "pg_namespace".oid = "pg_class".relnamespace
WHERE
"pg_namespace".nspname = ''schema''
AND "pg_class".relname = ''table''
);

Answer 5

seleccione column_name, data_type de information_schema.columns donde table_name = ''your_table_name'' y table_catalog = ''your_database_name'' y table_schema = ''your_schema_name'';

Answer 6

SELECT
a.attname as "Column",
pg_catalog.format_type(a.atttypid, a.atttypmod) as "Datatype"
FROM
pg_catalog.pg_attribute a
WHERE
a.attnum > 0
AND NOT a.attisdropped
AND a.attrelid = (
SELECT c.oid
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ ''^(hello world)$''
AND pg_catalog.pg_table_is_visible(c.oid)
);

Más información al respecto: http://www.postgresql.org/docs/9.3/static/catalog-pg-attribute.html

Answer 7

select column_name,data_type
from information_schema.columns
where table_name = ''table_name'';

Con la consulta anterior se pueden columnas y su tipo de datos.

Answer 8

 SELECT DISTINCT
ROW_NUMBER () OVER (ORDER BY pgc.relname , a.attnum) as rowid ,
pgc.relname as table_name ,
a.attnum as attr,
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)
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = pgc.relnamespace
WHERE 1=1
AND pgc.relkind IN (''r'','''')
AND n.nspname <> ''pg_catalog''
AND n.nspname <> ''information_schema''
AND n.nspname !~ ''^pg_toast''
AND a.attnum > 0 AND pgc.oid = a.attrelid
AND pg_table_is_visible(pgc.oid)
AND NOT a.attisdropped
ORDER BY rowid
;