StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Create some test data...</p> <pre><code>create table test (a int, b int, c int, constraint pk_test primary key(a, b)); create table test2 (a int, b int, c int, constraint uk_test2 unique (b, c)); create table test3 (a int, b int, c int, constraint uk_test3b unique (b), constraint uk_test3c unique (c),constraint uk_test3ab unique (a, b)); </code></pre> <p>List indexes and columns indexed:</p> <pre><code>select t.relname as table_name, i.relname as index_name, a.attname as column_name from pg_class t, pg_class i, pg_index ix, pg_attribute a where t.oid = ix.indrelid and i.oid = ix.indexrelid and a.attrelid = t.oid and a.attnum = ANY(ix.indkey) and t.relkind = 'r' and t.relname like 'test%' order by t.relname, i.relname; table_name | index_name | column_name ------------+------------+------------- test | pk_test | a test | pk_test | b test2 | uk_test2 | b test2 | uk_test2 | c test3 | uk_test3ab | a test3 | uk_test3ab | b test3 | uk_test3b | b test3 | uk_test3c | c </code></pre> <p>Roll up the column names:</p> <pre><code>select t.relname as table_name, i.relname as index_name, array_to_string(array_agg(a.attname), ', ') as column_names from pg_class t, pg_class i, pg_index ix, pg_attribute a where t.oid = ix.indrelid and i.oid = ix.indexrelid and a.attrelid = t.oid and a.attnum = ANY(ix.indkey) and t.relkind = 'r' and t.relname like 'test%' group by t.relname, i.relname order by t.relname, i.relname; table_name | index_name | column_names ------------+------------+-------------- test | pk_test | a, b test2 | uk_test2 | b, c test3 | uk_test3ab | a, b test3 | uk_test3b | b test3 | uk_test3c | c </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload