Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0613· OpenAIAi2 WildChat-4.8M (AI2)

give me a postgres query that will return the columns, data types, indexes and…

  1. User

    give me a postgres query that will return the columns, data types, indexes and constraints of a table without using information_schema

  2. Assistant

    To retrieve the columns, data types, indexes, and constraints of a table in PostgreSQL without using the information_schema, you can use the following query:

    SELECT a.attname AS column_name,
      format_type(a.atttypid, a.atttypmod) AS data_type,
      ix.indisprimary AS is_primary_key,
      CASE WHEN a.attnotnull = true THEN 'NOT NULL' ELSE '' END AS is_not_null,
      CASE WHEN fk.constraint_type = 'FOREIGN KEY' THEN fk.constraint_name ELSE '' END AS foreign_key
    FROM pg_class c
    JOIN pg_attribute a ON a.attrelid = c.oid
    LEFT JOIN (SELECT conname, confrelid, confkey[1] AS confkey, confrelname, conrelname, confmatchtype,
                      (SELECT string_agg(attname, ', ' ORDER BY x.n)
                       FROM generate_subscripts(confkey, 1) x(n)
                       JOIN pg_attribute k ON k.attnum = confkey[x.n]
                       WHERE k.attrelid = confrelid) AS foreign_keys
                FROM pg_constraint
                WHERE confrelid != 0) fk ON fk.conrelname = c.relname AND fk.conname = any(c.conname)
    LEFT JOIN pg_index ix ON ix.indrelid = c.oid AND a.attnum = ANY(ix.indkey)
    WHERE c.relname = 'your_table_name'
      AND a.attnum > 0;
    

    Replace 'your_table_name' in the query with the name of the table for which you want to retrieve the information.

    This query will return the following columns:

    • column_name: The name of the column
    • data_type: The data type of the column
    • is_primary_key: Whether the column is part of the primary key (true/false)
    • is_not_null: Whether the column is defined as NOT NULL (true/false)
    • foreign_key: The name of the foreign key constraint if the column is a foreign key, otherwise an empty string

    This query uses the underlying system tables (pg_class, pg_attribute, pg_constraint, and pg_index) to gather the required information about the specified table.