How to Get a List Column Names and Datatypes of a Table in Postgresql

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

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)
        );

More info on it : http://www.postgresql.org/docs/9.3/static/catalog-pg-attribute.html

How to copy all column names and data types from table in DBeaver?

You could generate the DDL from that table and use it as you need, by clicking right mouse button and selecting from menu Generate SQL-->DDL.

PostgreSQL, get column name, column type and description

because information_schema.columns is the table with data for sure, and you reference it not the first, you need right outer join instead:

t=# SELECT c.column_name, c.data_type, pgd.description
from pg_catalog.pg_statio_all_tables as st
inner join pg_catalog.pg_description pgd on (pgd.objoid=st.relid)
right outer join information_schema.columns c on (pgd.objsubid=c.ordinal_position and  c.table_schema=st.schemaname and c.table_name=st.relname)
where table_schema = 'public' and table_name = 's150';
  column_name  | data_type | description
---------------+-----------+-------------
 customer_name | text      |
 order_id      | text      |
 order_date    | date      |
(3 rows)

PostgreSQL - pull column name, data type, and sample value from database table

You can use a variation of row count for all tables for this:

select c.table_schema, c.table_name,
       c.column_name,
       c.data_type,
       (xpath('/table/row/'||column_name||'/text()', 
          query_to_xml(format('select %I 
                               from %I.%I limit 1', c.column_name, c.table_schema, c.table_name), true, false, '')))[1]::text as sample_value
from information_schema.columns c 
where table_schema = 'foo_schema';

query_to_xml() will run the query and format the result as XML. The xpath() function then extracts that column value from the XML.

This is quite expensive as the query is run once for each column, not once for each table. Note that the sample values might not be from the same row.

You can optimize this by just running one query per table and then joining that result back to the columns:

with samples as (
  select table_schema, 
         table_name, 
         query_to_xml(format('select * from %I.%I limit 1', table_schema, table_name), true, false, '') as sample_row
  from information_schema.tables 
  where table_schema = 'foo_schema'
)
select c.table_schema, c.table_name,
       c.column_name,
       c.data_type,
       (xpath('/table/row/'||column_name||'/text()', s.sample_row))[1]::text as sample_value
from information_schema.columns c 
  join samples s on (s.table_schema, s.table_name) = (c.table_schema, c.table_name);

With the above, all sample values are from the same row.

How to list the columns of a view in Postgres?

Postgres has dedicated System Catalog Information Functions to help with that.

To get the full view definition:

SELECT pg_get_viewdef('public.view_name');

Schema-qualification is optional. If no schema is prefixed, the current search_path setting decides visibility.

A quick hack would be to just:

SELECT * FROM public.view_name LIMIT 0;

Depending on your client, column names and types should still be displayed. Or LIMIT n to get some sample values, too. The underlying query is actually executed then (unlike with LIMIT 0).

To list columns and their data type, in order, you might base the query on pg_attribute:

SELECT attname AS column_name, format_type(atttypid, atttypmod) AS data_type
FROM   pg_attribute
WHERE  attrelid = 'public.view_name'::regclass
-- AND    NOT attisdropped
-- AND    attnum > 0
ORDER  BY attnum;

Type modifiers like maximum length are included in data_type this way.

Internally, a VIEW is implemented as special table with a rewrite rule. Details in the manual here. The table is saved in the system catalogs much like any regular table.

About the cast to regclass:

• How to check if a table exists in a given schema

The same query works for tables or materialized views as well. Uncomment the additional filters above to only get visible user columns for tables.

How to get column name and data type returned by a custom query in postgres?

I don't think there's any built-in SQL function which does this for you.

If you want to do this purely at the SQL level, the simplest and cheapest way is probably to CREATE TEMP VIEW AS (<your_query>), dig the column definitions out of the catalog tables, and drop the view when you're done. However, this can have a non-trivial overhead depending on how often you do it (as it needs to write view definitions to the catalogs), can't be run in a read-only transaction, and can't be done on a standby server.

The ideal solution, if it fits your use case, is to build a prepared query on the client side, and make use of the metadata returned by the server (in the form of a RowDescription message passed as part of the query protocol). Unfortunately, this depends very much on which client library you're using, and how much of this information it chooses to expose. For example, libpq will give you access to everything, whereas the JDBC driver limits you to the public methods on its ResultSetMetadata object (though you could probably pull more information from its private fields via reflection, if you're determined enough).

If you want a read-only, low-overhead, client-independent solution, then you could also write a server-side C function to prepare and describe the query via SPI. Writing and building C functions comes with a bit of a learning curve, but you can find numerous examples on PGXN, or within Postgres' own contrib modules.

---

Related Topics