How to get a list column names and datatypes of a table in PostgreSQL

Question

How can I get a list of column names and datatypes of a table in PostgreSQL using a query

User · Answer

Don t forget to add the schema name in case you have multiple schemas with the same table names   SELECT column name  data type  FROM information schema columns WHERE table name    your table name  AND table schema    your schema name     or using psql    d  your schema name your table name

User · Answer

To make this topic  more complete    I required the column names and data types on a SELECT statement  not a table    If you want to do this on a SELECT statement instead of an actual existing table  you can do the following   DROP TABLE IF EXISTS abc  CREATE TEMPORARY TABLE abc AS -- your select statement here  SELECT        FROM foo -- end your select statement    select column name  data type  from information schema columns  where table name    abc   DROP IF EXISTS abc    Short explanation  it makes a  temp  table of your select statement  which you can  call  upon via the query provided by  among others   a horse with no name and  selva   Hope this helps

User · Answer

without mentioning schema also you can get the required details  Try this query-   select column name data type  from information schema columns  where table name    table name

User · Answer

To get information about the table s column  you can use    dt   tablename    To get information about the datatype in the table  you can use    dT   datatype

User · Answer

SELECT column name data type  FROM information schema columns  WHERE table name    your table name   AND table catalog    your database name   AND table schema    your schema name

User · Answer

Updated Pratik answer to support more schemas and nullables   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  gt  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

User · Answer

A version that supports finding the column names and types of a table in a specific schema  and uses JOINs without any subqueries  SELECT     pg attribute attname AS column name      pg catalog format type pg attribute atttypid  pg attribute atttypmod  AS data type FROM     pg catalog pg attribute INNER JOIN     pg catalog pg class ON pg class oid   pg attribute attrelid INNER JOIN     pg catalog pg namespace ON pg namespace oid   pg class relnamespace WHERE     pg attribute attnum  gt  0     AND NOT pg attribute attisdropped     AND pg namespace nspname    my schema      AND pg class relname    my table  ORDER BY     attnum ASC

User · Answer

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  lt  gt   pg catalog          AND n nspname  lt  gt   information schema          AND n nspname      pg toast       AND a attnum  gt  0 AND pgc oid   a attrelid     AND pg table is visible pgc oid      AND NOT a attisdropped     ORDER BY rowid

User · Answer

select column name data type  from information schema columns  where table name    table name     with the above query you can columns and its datatype

User · Answer

Below will list all the distinct data types of all the table in the provided schema name   copy  select distinct data type  column name from information schema columns where table name in  SELECT tablename FROM pg catalog pg tables WHERE schemaname     pg catalog  AND schemaname     information schema  and schemaname     lt Your schema name gt     to  datatypes csv   delimiter as     CSV header

User · Answer

SELECT         a attname as  Column           pg catalog format type a atttypid  a atttypmod  as  Datatype      FROM         pg catalog pg attribute a     WHERE         a attnum  gt  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                 More info on it   http   www postgresql org docs 9 3 static catalog-pg-attribute html

User · Answer

Open psql command line and type    d  table name

[sql] How to get a list column names and datatypes of a table in PostgreSQL?

Examples related to sql

Examples related to postgresql

Examples related to sqldatatypes