gpt-3.5-turbo-0613· OpenAI
WildChat-4.8M (AI2)
give me a postgres query that will return the columns, data types, indexes and…
- User
give me a postgres query that will return the columns, data types, indexes and constraints of a table without using information_schema
- 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 columndata_type: The data type of the columnis_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, andpg_index) to gather the required information about the specified table.