StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied! <p>You can query the <a href="http://www.postgresql.org/docs/current/interactive/catalogs.html" rel="nofollow">system catalogs</a> for <strong>unique constraints</strong>, in particular <code>pg_constraint</code> and <code>pg_attribute</code>:</p> <pre class="lang-sql prettyprint-override"><code>SELECT c.conname, pg_get_constraintdef(c.oid) FROM pg_constraint c JOIN ( SELECT array_agg(attnum::int) AS attkey FROM pg_attribute WHERE attrelid = 'tb'::regclass -- table name optionally schema-qualified AND attname = ANY('{c1,c2}') ) a ON c.conkey::int[] <@ a.attkey AND c.conkey::int[] @> a.attkey WHERE c.contype = 'u' AND c.conrelid = 'tb'::regclass; </code></pre> <ul> <li><p>The <a href="http://www.postgresql.org/docs/current/interactive/datatype-oid.html" rel="nofollow">object identifer type</a> <code>regclass</code> helps to unambiguously identify your table.</p></li> <li><p>The system catalog information function <a href="http://www.postgresql.org/docs/current/interactive/functions-info.html" rel="nofollow"><code>pg_get_constraintdef()</code></a> gets you nicely formatted information, which is not strictly necessary for your request.</p></li> <li><p>Also using <a href="http://www.postgresql.org/docs/current/interactive/functions-array.html" rel="nofollow">array operators</a> <code><@</code> and <code>@></code> to make sure the arrays match completely. (The order of columns is unknown.) The system columns are <code>smallint</code> and <code>smallint[]</code> respectively. Cast to <code>integer</code> to make it work with those operators.</p></li> <li><p>Column names are case sensitive when looking them up in the system catalog directly. If you didn't double-quote <code>C1</code> and <code>C2</code> at creation time, you have to use <code>c1</code> and <code>c2</code> in this context.</p></li> <li><p>There could also be a multicolumn <strong>primary key constraint</strong> enforcing uniqueness. To cover that in the query use instead:</p> <pre class="lang-sql prettyprint-override"><code>WHERE c.contype IN ('u', 'p') </code></pre></li> </ul> <p>Building on @Roman's fiddle, this one also demonstrates the pk case:</p> <p><strong><a href="http://sqlfiddle.com/#!12/76134/1" rel="nofollow">->SQLfiddle</a></strong></p> <h3>Unique index</h3> <p>Both of the above (unique & pk constraints) are implemented by way of a unique index. In addition there can also be <strong>unique indices</strong> doing effectively the same as formally declared unique constraint. To catch <strong>all of them</strong> query the system catalog <a href="http://www.postgresql.org/docs/current/interactive/catalog-pg-index.html" rel="nofollow"><code>pg_index</code></a> instead, in a similar fashion:</p> <pre class="lang-sql prettyprint-override"><code>SELECT c.relname AS idx_name FROM ( SELECT indexrelid, string_to_array(indkey::text, ' ')::int[] AS indkey FROM pg_index WHERE indrelid = 'tb'::regclass AND indisunique -- contains "indisprimary" ) i JOIN ( SELECT array_agg(attnum::int) AS attkey FROM pg_attribute WHERE attrelid = 'tb'::regclass AND attname = ANY('{c1,c2}') ) a ON i.indkey <@ a.attkey AND i.indkey @> a.attkey JOIN pg_class c ON c.oid = i.indexrelid; </code></pre> <p>Special difficulty here is the internal type <code>int2vector</code>. I deal with it by casting text and converting to <code>int[]</code>.</p> <p>Be aware that implementation of catalog tables might change across major. Unlikely that these queries break, but possible.</p>

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