How to Get Column Attributes Query from Table Name Using Postgresql

How to get column attributes query from table name using PostgreSQL?

Here's query against the system catalog that should fetch everything you need (with a bonus primary-key field thrown in for free).

SELECT DISTINCT
    a.attnum as num,
    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)
WHERE a.attnum > 0 AND pgc.oid = a.attrelid
AND pg_table_is_visible(pgc.oid)
AND NOT a.attisdropped
AND pgc.relname = 'TABLE_NAME'  -- Your table name here
ORDER BY a.attnum;

Which would return results like:

 num |    name     |             typ             | notnull |       comment       | primary_key 
-----+-------------+-----------------------------+---------+---------------------+-------------
   1 | id          | integer                     | t       | a primary key thing | t
   2 | ref         | text                        | f       |                     | f
   3 | created     | timestamp without time zone | t       |                     | f
   4 | modified    | timestamp without time zone | t       |                     | f
   5 | name        | text                        | t       |                     | f

• num: The column number
• name: The column name
• typ: the data type
• notnull: Is the column defined as NOT NULL
• comment: Any COMMENT defined for the column
• primary_key: Is the column defined as PRIMARY KEY
• default: The command used for the default value

How to check for column attributes in postgreSQL?

You can use information_schema for this

select * 
from information_schema.columns 
where table_name = 'users'

How to get column attributes from a query using PostgreSQL?

Assuming you're using psycopg2 as your database driver, then the cursor.description field is what you want:

import pprint
import psycopg2
conn = psycopg2.connect('');
curs = conn.cursor()
curs.execute("SELECT 1 as col1, 2 as col2, 'text' as colblah");
pprint.pprint(curs.description)

produces:

(Column(name='col1', type_code=23, display_size=None, internal_size=4, precision=None, scale=None, null_ok=None),
 Column(name='col2', type_code=23, display_size=None, internal_size=4, precision=None, scale=None, null_ok=None),
 Column(name='colblah', type_code=705, display_size=None, internal_size=-2, precision=None, scale=None, null_ok=None))

The type codes are PostgreSQL's internal object IDs.

For more detail see the psycopg2 manual, which explains how to turn the type oids into type names, among other things.

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

PostgreSQL - query all tables' all table columns

You can do this in a single query by using array_agg() and a join on the information_schema.tables and information_schema.columns tables.

This would return something similar to your expected output:

select
    t.table_name,
    array_agg(c.column_name::text) as columns
from
    information_schema.tables t
inner join information_schema.columns c on
    t.table_name = c.table_name
where
    t.table_schema = 'public'
    and t.table_type= 'BASE TABLE'
    and c.table_schema = 'public'
group by t.table_name;

Here I'm taking all the tables first, then I join it with the columns tables, and finally use array_agg() to aggregate them all to an array, grouped by the table name.

Hope it helps :) Feel free to ask if you have any doubts.

Is it possible to name SQL result columns from rows in another table? (Postgres)

Use INNER JOIN for that:

SELECT u.id, a_city.value AS city, a_region.value AS region
 FROM users u
 INNER JOIN user_attribute_values a_city ON a_city.client_id = u.id AND a_city.attribute_id = 8
 INNER JOIN user_attribute_values a_region ON a_region.client_id = u.id AND a_region.attribute_id = 9
 WHERE LENGTH(a_city.value) > 0
   AND LENGTH(a_region.value) > 0

---

Related Topics