Show query result column types (PostgreSQL)

postgresql get column names from query
postgres show tables in schema
postgres jsonb
information_schema.columns postgres
psql show table contents
pg_typeof
sql return data type
hive get column type

Is there a way to easily get the column types of a query result? I read the psql documentation, but I don't think it supports that. Ideally, I'd be able to get something like:

 columna : text | columnb : integer
----------------+-------------------
 oh hai         |                42

Is there a way I can get this information without coding something up?

I don't think you can print exactly what you have in the sample, unless you write a stored procedure for it.

One way to do it (two "selects"):

  1. create table my_table as select ...
  2. \d my_table
  3. select * from my_table

Query to return output column names and data types of a query , To get the same list of column names and types from a query, you could use a You can use the psql command line client. It is a PgAdmin3 macro that can be accessed with a shortcut to display the definition of a This may be overly simple​, but pgAdmin4 shows the field types in the output results. Query below returns a list of all columns in a specific table in PostgreSQL. Query select ordinal_position as position, column_name, data_type, case when character_maximum_length is not null then character_maximum_length else numeric_precision end as max_length, is_nullable, column_default as default_value from information_schema.columns where table_name = 'Table name' -- enter table name here

It is possible to get any SELECT query result column type.

Example

Given the following query and result, let's answer the question *"What is the column type of all_ids?"*

SELECT array_agg(distinct "id") "all_ids" FROM "auth_user";

                 all_ids
--------------------------------------------
 {30,461577687337538580,471090357619135524}
(1 row)

We need a mechanism to unveil the type of "all_ids".

On the postgres mailing list archives I found reference to a native pg function called pg_typeof.

Example usage:

SELECT pg_typeof(array_agg(distinct "id")) "all_ids" FROM "auth_user";

Output:

 all_ids
----------
 bigint[]
(1 row)

Cheers!

Documentation: 10: 8.16. Composite Types, then the same inventory_item composite type shown above would come into For instance, to select just one field from the result of a function that returns a  They will both be some integer type. PQftablecol. Returns the column number (within its table) of the column making up the specified query result column. Query-result column numbers start at 0, but table columns have nonzero numbers. int PQftablecol(const PGresult *res, int column_number);

It is definitely possible with \gdesc command(PostgreSQL 11):

\gdesc

Shows the description (that is, the column names and data types) of the result of the current query buffer. The query is not actually executed; however, if it contains some type of syntax error, that error will be reported in the normal way.

If the current query buffer is empty, the most recently sent query is described instead.

For example:

$ SELECT * FROM pg_database \gdesc

    COLUMN     |   TYPE    
---------------+-----------
 datname       | name
 datdba        | oid
 encoding      | INTEGER
 datcollate    | name
 datctype      | name
 datistemplate | BOOLEAN
 datallowconn  | BOOLEAN
 datconnlimit  | INTEGER
 datlastsysoid | oid
 datfrozenxid  | xid
 datminmxid    | xid
 dattablespace | oid
 datacl        | aclitem[]

PostgreSQL DESCRIBE TABLE, This tutorial shows you how to query information on columns of a table using psql [postgres]: psql (9.4.2) dvdrental=# \d city Table "public.city" Column | Type  \gdesc Shows the description (that is, the column names and data types) of the result of the current query buffer. The query is not actually executed; however, if it contains some type of syntax error, that error will be reported in the normal way. If the current query buffer is empty, the most recently sent query is described instead.

PostgreSQL CREATE TABLE AS Statement By Examples, Second, provide a query whose result set is added to the new table after the AS As clearly shown in the output, the names and data types of the action_film  PostgreSQL does not provide this statement directly but offers you something similar. PostgreSQL provides you with two ways to show databases. PostgreSQL listing databases using psql tool. If you are using psql tool to connect to PostgreSQL database server, you can issue the \l command to shows all databases in the current server as follows: \l

Find all numeric columns in PostgreSQL database, Useful SQL queries for PostgreSQL to explore database schema. Query; Columns; Rows; Sample results Numeric in PostgreSQL are columns with the following data types: smallint, integer, bigint, decimal, numeric, real, double precision,  I'm trying to create a dynamic system that allows the users to import lists of data from Excel so I need to have dynamic columns, ex: custom_columns_table id list_id data_type column_name

Retrieving Rows with SELECT, A query on a table may return a result set with the same column structure as in the table, and columns may be listed more than once, or not at all, as shown in Conveniently, different types of targets may be arbitrarily mixed in the target list. When a record variable is the target, it automatically configures itself to the row type of the query result columns. The INTO clause can appear almost anywhere in the SQL command. Customarily it is written either just before or just after the list of select_expressions in a SELECT command, or at the end of the command for other command types.

Comments
  • Ha, I just asked this question yesterday in #postgresql (no answer then)
  • I don't think the psql can show it to you directly. But it should be fairly easy to modify it to do so. Perhaps you could write a stored procedure to emulate this though.
  • PostgreSQL 11 introduced gdesc Better late than never :)
  • Yeah, I just needed the types, not necessarily that format. Thanks.
  • This seems rather kludgy and inconvenient compared to using pg_typeof().
  • @JayTaylor I agree. Life is a journey. :-)
  • Does anything as convenient exist in postgres/psql 10?
  • @JustinBailey Unfortunately, I didn't find nice equivalent for psql 10.
  • you can use later version psql to access older PostgreSQL server, e.g. apt install postgresql-client-11