DESCRIBE OUTPUT

Synopsis

DESCRIBE OUTPUT statement_name

Description

List the output columns of a prepared statement, including the column name (or alias), catalog, schema, table, type, type size in bytes, and a boolean indicating if the column is aliased.

Examples

Prepare and describe a query with four output columns:

PREPARE my_select1 FROM
SELECT * FROM nation;

DESCRIBE OUTPUT my_select1;

 Column Name | Catalog | Schema | Table  |  Type   | Type Size | Aliased
-------------+---------+--------+--------+---------+-----------+---------
 nationkey   | tpch    | sf1    | nation | bigint  |         8 | false
 name        | tpch    | sf1    | nation | varchar |         0 | false
 regionkey   | tpch    | sf1    | nation | bigint  |         8 | false
 comment     | tpch    | sf1    | nation | varchar |         0 | false
(4 rows)

Prepare and describe a query whose output columns are expressions:

PREPARE my_select2 FROM
SELECT count(*) as my_count, 1+2 FROM nation;

DESCRIBE OUTPUT my_select2;

 Column Name | Catalog | Schema | Table |  Type  | Type Size | Aliased
-------------+---------+--------+-------+--------+-----------+---------
 my_count    |         |        |       | bigint |         8 | true
 _col1       |         |        |       | bigint |         8 | false
(2 rows)

Prepare and describe a row count query:

PREPARE my_create FROM
CREATE TABLE foo AS SELECT * FROM nation;

DESCRIBE OUTPUT my_create;

 Column Name | Catalog | Schema | Table |  Type  | Type Size | Aliased
-------------+---------+--------+-------+--------+-----------+---------
 rows        |         |        |       | bigint |         8 | false
(1 row)

Describe for given subquery

DESCRIBE OUTPUT (SELECT * FROM nation)

 Column Name | Catalog | Schema | Table  |     Type     | Type Size | Aliased
-------------+---------+--------+--------+--------------+-----------+---------
 n_nationkey | tpch    | sf1    | nation | bigint       |         8 | false
 n_name      | tpch    | sf1    | nation | varchar(25)  |         0 | false
 n_regionkey | tpch    | sf1    | nation | bigint       |         8 | false
 n_comment   | tpch    | sf1    | nation | varchar(152) |         0 | false
(4 rows)

more complex query

DESCRIBE OUTPUT (
WITH 
asia_customers AS (
    SELECT 
        c_custkey,
        c_name,
        c_nationkey
    FROM tpch.sf1.customer
    WHERE c_nationkey IN (
        SELECT n_nationkey 
        FROM tpch.sf1.nation 
        WHERE n_regionkey = (SELECT r_regionkey FROM tpch.sf1.region WHERE r_name = 'ASIA')
    )
),
asia_orders AS (
    SELECT 
        o_orderkey,
        o_custkey,
        o_orderdate
    FROM tpch.sf1.orders
    INNER JOIN asia_customers ON o_custkey = asia_customers.c_custkey
    WHERE o_orderstatus = 'F'
),
order_lineitem_total AS (
    SELECT 
        l_orderkey,
        SUM(l_extendedprice * (1 - l_discount)) AS order_total
    FROM tpch.sf1.lineitem
    GROUP BY l_orderkey
)
SELECT 
    ac.c_name AS customer_name,
    ao.o_orderdate AS order_date,
    ot.order_total AS order_amount
FROM asia_customers ac
INNER JOIN asia_orders ao ON ac.c_custkey = ao.o_custkey
INNER JOIN order_lineitem_total ot ON ao.o_orderkey = ot.l_orderkey
ORDER BY order_amount DESC
LIMIT 10);

  Column Name  | Catalog | Schema |  Table   |     Type      | Type Size | Aliased
---------------+---------+--------+----------+---------------+-----------+---------
 customer_name | tpch    | sf1    | customer | varchar(25)   |         0 | true
 order_date    | tpch    | sf1    | orders   | date          |         4 | true
 order_amount  |         |        |          | decimal(38,4) |        16 | true
(3 rows)

See also

PREPARE