tag:blogger.com,1999:blog-212188702024-02-08T01:56:16.920-08:00Golden's placePostgreSQL, SQL, Java and other.Egor Spivachttp://www.blogger.com/profile/10301192285096408561noreply@blogger.comBlogger4125tag:blogger.com,1999:blog-21218870.post-91268954485659339182012-08-13T06:51:00.000-07:002012-08-13T06:51:33.287-07:00How to get some information about PostgreSQL structure (Part 3)<div dir="ltr" style="text-align: left;" trbidi="on">
<h2>
Constraints</h2>
<b>Get constraints list for current database: </b><br />
<pre style="background: #ffffff; color: black;"><span style="color: maroon; font-weight: bold;">SELECT</span> <span style="color: maroon; font-weight: bold;">DISTINCT</span> <span style="color: #808030;">(</span>pc2<span style="color: #808030;">.</span>relname <span style="color: #808030;">|</span><span style="color: #808030;">|</span> <span style="color: #0000e6;">'.'</span> <span style="color: #808030;">|</span><span style="color: #808030;">|</span> <span style="color: maroon; font-weight: bold;">r</span><span style="color: #808030;">.</span>conname<span style="color: #808030;">)</span> <span style="color: maroon; font-weight: bold;">AS</span> fullname<span style="color: #808030;">,</span>
<span style="color: maroon; font-weight: bold;">r</span><span style="color: #808030;">.</span>conname <span style="color: maroon; font-weight: bold;">AS</span> constraint_name<span style="color: #808030;">,</span>
<span style="color: maroon; font-weight: bold;">r</span><span style="color: #808030;">.</span>contype <span style="color: maroon; font-weight: bold;">AS</span> constraint_type<span style="color: #808030;">,</span>
<span style="color: maroon; font-weight: bold;">r</span><span style="color: #808030;">.</span>condeferrable <span style="color: maroon; font-weight: bold;">AS</span> is_deferrable<span style="color: #808030;">,</span>
<span style="color: maroon; font-weight: bold;">r</span><span style="color: #808030;">.</span>condeferred <span style="color: maroon; font-weight: bold;">AS</span> is_deferred<span style="color: #808030;">,</span>
<span style="color: maroon; font-weight: bold;">r</span><span style="color: #808030;">.</span>confupdtype <span style="color: maroon; font-weight: bold;">AS</span> update_action<span style="color: #808030;">,</span>
<span style="color: maroon; font-weight: bold;">r</span><span style="color: #808030;">.</span>confdeltype <span style="color: maroon; font-weight: bold;">AS</span> delete_action<span style="color: #808030;">,</span>
pc1<span style="color: #808030;">.</span>relname <span style="color: maroon; font-weight: bold;">AS</span> foreign_table<span style="color: #808030;">,</span>
pc2<span style="color: #808030;">.</span>relname <span style="color: maroon; font-weight: bold;">AS</span> this_table<span style="color: #808030;">,</span>
kcu1<span style="color: #808030;">.</span>constraint_schema <span style="color: maroon; font-weight: bold;">AS</span> this_schema<span style="color: #808030;">,</span>
pg_catalog<span style="color: #808030;">.</span>pg_get_constraintdef<span style="color: #808030;">(</span><span style="color: maroon; font-weight: bold;">r</span><span style="color: #808030;">.</span><span style="color: maroon; font-weight: bold;">oid</span><span style="color: #808030;">,</span> <span style="color: maroon; font-weight: bold;">true</span><span style="color: #808030;">)</span> <span style="color: maroon; font-weight: bold;">as</span> sqlstr
<span style="color: maroon; font-weight: bold;">FROM</span> pg_constraint <span style="color: maroon; font-weight: bold;">AS</span> <span style="color: maroon; font-weight: bold;">r</span>
<span style="color: maroon; font-weight: bold;">LEFT</span> <span style="color: maroon; font-weight: bold;">JOIN</span> pg_class <span style="color: maroon; font-weight: bold;">AS</span> pc1 <span style="color: maroon; font-weight: bold;">ON</span> pc1<span style="color: #808030;">.</span><span style="color: maroon; font-weight: bold;">oid</span> <span style="color: #808030;">=</span> <span style="color: maroon; font-weight: bold;">r</span><span style="color: #808030;">.</span>confrelid
<span style="color: maroon; font-weight: bold;">LEFT</span> <span style="color: maroon; font-weight: bold;">JOIN</span> pg_class <span style="color: maroon; font-weight: bold;">AS</span> pc2 <span style="color: maroon; font-weight: bold;">ON</span> pc2<span style="color: #808030;">.</span><span style="color: maroon; font-weight: bold;">oid</span> <span style="color: #808030;">=</span> <span style="color: maroon; font-weight: bold;">r</span><span style="color: #808030;">.</span>conrelid
<span style="color: maroon; font-weight: bold;">LEFT</span> <span style="color: maroon; font-weight: bold;">JOIN</span> information_schema<span style="color: #808030;">.</span>key_column_usage <span style="color: maroon; font-weight: bold;">AS</span> kcu1 <span style="color: maroon; font-weight: bold;">ON</span> </pre>
<pre style="background: #ffffff; color: black;">(kcu1<span style="color: #808030;">.</span>table_name<span style="color: #808030;">=</span>pc2<span style="color: #808030;">.</span>relname <span style="color: maroon; font-weight: bold;">AND</span> kcu1<span style="color: #808030;">.</span>constraint_name<span style="color: #808030;">=</span><span style="color: maroon; font-weight: bold;">r</span><span style="color: #808030;">.</span>conname)
<span style="color: maroon; font-weight: bold;">ORDER</span> <span style="color: maroon; font-weight: bold;">BY</span> <span style="color: #008c00;">1</span><span style="color: #808030;">;</span>
</pre>
<h2 style="text-align: left;">
Tablespaces</h2>
<b>Get tablespaces list:</b><br />
<pre style="background: #ffffff; color: black;"><span style="color: maroon; font-weight: bold;">SELECT</span> spcname <span style="color: maroon; font-weight: bold;">AS</span> name <span style="color: maroon; font-weight: bold;">FROM</span> pg_tablespace <span style="color: maroon; font-weight: bold;">ORDER</span> <span style="color: maroon; font-weight: bold;">BY</span> spcname <span style="color: maroon; font-weight: bold;">ASC</span>
</pre>
<h2 style="text-align: left;">
Views</h2>
<b>Get all views for schema <i>public</i> with sql code:</b><br />
<pre style="background: #ffffff; color: black;"><span style="color: maroon; font-weight: bold;">SELECT</span> <span style="color: maroon; font-weight: bold;">c</span><span style="color: #808030;">.</span><span style="color: maroon; font-weight: bold;">oid</span><span style="color: #808030;">,</span> <span style="color: maroon; font-weight: bold;">c</span><span style="color: #808030;">.</span>xmin<span style="color: #808030;">,</span> <span style="color: maroon; font-weight: bold;">c</span><span style="color: #808030;">.</span>relname<span style="color: #808030;">,</span> pg_get_userbyid<span style="color: #808030;">(</span><span style="color: maroon; font-weight: bold;">c</span><span style="color: #808030;">.</span>relowner<span style="color: #808030;">)</span> <span style="color: maroon; font-weight: bold;">AS</span> viewowner<span style="color: #808030;">,</span> <span style="color: maroon; font-weight: bold;"> </span></pre>
<pre style="background: #ffffff; color: black;"><span style="color: maroon; font-weight: bold;">c</span><span style="color: #808030;">.</span>relacl<span style="color: #808030;">,</span> description<span style="color: #808030;">,</span> pg_get_viewdef<span style="color: #808030;">(</span><span style="color: maroon; font-weight: bold;">c</span><span style="color: #808030;">.</span><span style="color: maroon; font-weight: bold;">oid</span><span style="color: #808030;">,</span> <span style="color: maroon; font-weight: bold;">true</span><span style="color: #808030;">)</span> <span style="color: maroon; font-weight: bold;">AS</span> code
<span style="color: maroon; font-weight: bold;">FROM</span> pg_class <span style="color: maroon; font-weight: bold;">c</span>
<span style="color: maroon; font-weight: bold;">LEFT</span> <span style="color: maroon; font-weight: bold;">OUTER</span> <span style="color: maroon; font-weight: bold;">JOIN</span> pg_description des <span style="color: maroon; font-weight: bold;">ON</span> <span style="color: #808030;">(</span>des<span style="color: #808030;">.</span>objoid<span style="color: #808030;">=</span><span style="color: maroon; font-weight: bold;">c</span><span style="color: #808030;">.</span><span style="color: maroon; font-weight: bold;">oid</span> <span style="color: maroon; font-weight: bold;">and</span> des<span style="color: #808030;">.</span>objsubid<span style="color: #808030;">=</span><span style="color: #008c00;">0</span><span style="color: #808030;">)</span>
<span style="color: maroon; font-weight: bold;">LEFT</span> <span style="color: maroon; font-weight: bold;">JOIN</span> pg_catalog<span style="color: #808030;">.</span>pg_namespace n <span style="color: maroon; font-weight: bold;">ON</span> n<span style="color: #808030;">.</span><span style="color: maroon; font-weight: bold;">oid</span> <span style="color: #808030;">=</span> <span style="color: maroon; font-weight: bold;">c</span><span style="color: #808030;">.</span>relnamespace
<span style="color: maroon; font-weight: bold;">WHERE</span> <span style="color: #808030;">(</span>
<span style="color: #808030;">(</span><span style="color: maroon; font-weight: bold;">c</span><span style="color: #808030;">.</span>relhasrules <span style="color: maroon; font-weight: bold;">AND</span> <span style="color: #808030;">(</span><span style="color: maroon; font-weight: bold;">EXISTS</span> <span style="color: #808030;">(</span>
<span style="color: maroon; font-weight: bold;">SELECT</span> <span style="color: maroon; font-weight: bold;">r</span><span style="color: #808030;">.</span>rulename <span style="color: maroon; font-weight: bold;">FROM</span> pg_rewrite <span style="color: maroon; font-weight: bold;">r</span>
<span style="color: maroon; font-weight: bold;">WHERE</span> <span style="color: #808030;">(</span> <span style="color: #808030;">(</span><span style="color: maroon; font-weight: bold;">r</span><span style="color: #808030;">.</span>ev_class <span style="color: #808030;">=</span> <span style="color: maroon; font-weight: bold;">c</span><span style="color: #808030;">.</span><span style="color: maroon; font-weight: bold;">oid</span><span style="color: #808030;">)</span> <span style="color: maroon; font-weight: bold;">AND</span> <span style="color: #808030;">(</span>bpchar<span style="color: #808030;">(</span><span style="color: maroon; font-weight: bold;">r</span><span style="color: #808030;">.</span>ev_type<span style="color: #808030;">)</span> <span style="color: #808030;">=</span> <span style="color: #0000e6;">'1'</span><span style="color: #797997;">:</span><span style="color: #797997;">:bpchar</span><span style="color: #808030;">)</span><span style="color: #808030;">)</span>
<span style="color: #808030;">)</span><span style="color: #808030;">)</span>
<span style="color: #808030;">)</span>
<span style="color: maroon; font-weight: bold;">OR</span> <span style="color: #808030;">(</span><span style="color: maroon; font-weight: bold;">c</span><span style="color: #808030;">.</span>relkind <span style="color: #808030;">=</span> <span style="color: #0000e6;">'v'</span><span style="color: #797997;">:</span><span style="color: #797997;">:char</span><span style="color: #808030;">)</span><span style="color: #808030;">)</span>
<span style="color: maroon; font-weight: bold;">AND</span> n<span style="color: #808030;">.</span>nspname<span style="color: #808030;">=</span><span style="color: #0000e6;">'<b><i>public</i></b>'</span>
<span style="color: maroon; font-weight: bold;">ORDER</span> <span style="color: maroon; font-weight: bold;">BY</span> relname <span style="color: maroon; font-weight: bold;">ASC</span>
</pre>
<br />
<b>See other parts:</b><br />
<a href="http://golden13.blogspot.com/2012/08/how-to-get-some-information-about_7.html">Part 2</a><br />
<a href="http://golden13.blogspot.com/2012/08/how-to-get-some-information-about.html">Part 1</a>
</div>
Egor Spivachttp://www.blogger.com/profile/10301192285096408561noreply@blogger.com0tag:blogger.com,1999:blog-21218870.post-60154073115793127042012-08-07T22:32:00.002-07:002012-08-13T07:09:32.971-07:00How to get some information about PostgreSQL structure (Part 2)<div dir="ltr" style="text-align: left;" trbidi="on">
<h2 style="text-align: left;">
Schemas</h2>
<b><span class="short_text" id="result_box" lang="en"><span class="hps">How to get a</span> <span class="hps">list of</span> <span class="hps">schemes</span></span>:</b><br />
<pre style="background: #ffffff; color: black;"><span style="color: maroon; font-weight: bold;">SELECT</span>
<span style="color: maroon; font-weight: bold;">CASE</span>
<span style="color: maroon; font-weight: bold;">WHEN</span> nspname <span style="color: maroon; font-weight: bold;">LIKE</span> E<span style="color: #0000e6;">'pg\_temp\_%'</span> <span style="color: maroon; font-weight: bold;">THEN</span> <span style="color: #008c00;">1</span>
<span style="color: maroon; font-weight: bold;">WHEN</span> <span style="color: #808030;">(</span>nspname <span style="color: maroon; font-weight: bold;">LIKE</span> E<span style="color: #0000e6;">'pg\_%'</span><span style="color: #808030;">)</span> <span style="color: maroon; font-weight: bold;">THEN</span> <span style="color: #008c00;">0</span>
<span style="color: maroon; font-weight: bold;">ELSE</span> <span style="color: #008c00;">3</span>
<span style="color: maroon; font-weight: bold;">END</span> <span style="color: maroon; font-weight: bold;">AS</span> nsptyp<span style="color: #808030;">,</span> nsp<span style="color: #808030;">.</span>nspname<span style="color: #808030;">,</span> nsp<span style="color: #808030;">.</span><span style="color: maroon; font-weight: bold;">oid</span><span style="color: #808030;">,</span> pg_get_userbyid<span style="color: #808030;">(</span>nspowner<span style="color: #808030;">)</span>
<span style="color: maroon; font-weight: bold;">AS</span> namespaceowner<span style="color: #808030;">,</span>
nspacl<span style="color: #808030;">,</span> description<span style="color: #808030;">,</span>
has_schema_privilege<span style="color: #808030;">(</span>nsp<span style="color: #808030;">.</span><span style="color: maroon; font-weight: bold;">oid</span><span style="color: #808030;">,</span> <span style="color: #0000e6;">'CREATE'</span><span style="color: #808030;">)</span> <span style="color: maroon; font-weight: bold;">as</span> cancreate
<span style="color: maroon; font-weight: bold;">FROM</span> pg_namespace nsp
<span style="color: maroon; font-weight: bold;">LEFT</span> <span style="color: maroon; font-weight: bold;">OUTER</span> <span style="color: maroon; font-weight: bold;">JOIN</span> pg_description des <span style="color: maroon; font-weight: bold;">ON</span> des<span style="color: #808030;">.</span>objoid<span style="color: #808030;">=</span>nsp<span style="color: #808030;">.</span><span style="color: maroon; font-weight: bold;">oid</span>
<span style="color: maroon; font-weight: bold;">WHERE</span> <span style="color: maroon; font-weight: bold;">NOT</span> <span style="color: #808030;">(</span><span style="color: #808030;">(</span>nspname <span style="color: #808030;">=</span> <span style="color: #0000e6;">'pg_catalog'</span> <span style="color: maroon; font-weight: bold;">AND</span> <span style="color: maroon; font-weight: bold;">EXISTS</span>
<span style="color: #808030;">(</span><span style="color: maroon; font-weight: bold;">SELECT</span> <span style="color: #008c00;">1</span> <span style="color: maroon; font-weight: bold;">FROM</span> pg_class
<span style="color: maroon; font-weight: bold;">WHERE</span> relname <span style="color: #808030;">=</span> <span style="color: #0000e6;">'pg_class'</span>
<span style="color: maroon; font-weight: bold;">AND</span> relnamespace <span style="color: #808030;">=</span> nsp<span style="color: #808030;">.</span><span style="color: maroon; font-weight: bold;">oid</span> <span style="color: maroon; font-weight: bold;">LIMIT</span> <span style="color: #008c00;">1</span><span style="color: #808030;">)</span><span style="color: #808030;">)</span> <span style="color: maroon; font-weight: bold;">OR</span>
<span style="color: #808030;">(</span>nspname <span style="color: #808030;">=</span> <span style="color: #0000e6;">'information_schema'</span> <span style="color: maroon; font-weight: bold;">AND</span>
<span style="color: maroon; font-weight: bold;">EXISTS</span> <span style="color: #808030;">(</span><span style="color: maroon; font-weight: bold;">SELECT</span> <span style="color: #008c00;">1</span> <span style="color: maroon; font-weight: bold;">FROM</span> pg_class
<span style="color: maroon; font-weight: bold;">WHERE</span> relname <span style="color: #808030;">=</span> <span style="color: #0000e6;">'tables'</span>
<span style="color: maroon; font-weight: bold;">AND</span> relnamespace <span style="color: #808030;">=</span> nsp<span style="color: #808030;">.</span><span style="color: maroon; font-weight: bold;">oid</span> <span style="color: maroon; font-weight: bold;">LIMIT</span> <span style="color: #008c00;">1</span><span style="color: #808030;">)</span><span style="color: #808030;">)</span> <span style="color: maroon; font-weight: bold;">OR</span>
<span style="color: #808030;">(</span>nspname <span style="color: maroon; font-weight: bold;">LIKE</span> <span style="color: #0000e6;">'_%'</span> <span style="color: maroon; font-weight: bold;">AND</span>
<span style="color: maroon; font-weight: bold;">EXISTS</span> <span style="color: #808030;">(</span><span style="color: maroon; font-weight: bold;">SELECT</span> <span style="color: #008c00;">1</span> <span style="color: maroon; font-weight: bold;">FROM</span> pg_proc
<span style="color: maroon; font-weight: bold;">WHERE</span> proname<span style="color: #808030;">=</span><span style="color: #0000e6;">'slonyversion'</span>
<span style="color: maroon; font-weight: bold;">AND</span> pronamespace <span style="color: #808030;">=</span> nsp<span style="color: #808030;">.</span><span style="color: maroon; font-weight: bold;">oid</span> <span style="color: maroon; font-weight: bold;">LIMIT</span> <span style="color: #008c00;">1</span><span style="color: #808030;">)</span><span style="color: #808030;">)</span> <span style="color: maroon; font-weight: bold;">OR</span>
<span style="color: #808030;">(</span>nspname <span style="color: #808030;">=</span> <span style="color: #0000e6;">'dbo'</span> <span style="color: maroon; font-weight: bold;">AND</span>
<span style="color: maroon; font-weight: bold;">EXISTS</span> <span style="color: #808030;">(</span><span style="color: maroon; font-weight: bold;">SELECT</span> <span style="color: #008c00;">1</span> <span style="color: maroon; font-weight: bold;">FROM</span> pg_class
<span style="color: maroon; font-weight: bold;">WHERE</span> relname <span style="color: #808030;">=</span> <span style="color: #0000e6;">'systables'</span>
<span style="color: maroon; font-weight: bold;">AND</span> relnamespace <span style="color: #808030;">=</span> nsp<span style="color: #808030;">.</span><span style="color: maroon; font-weight: bold;">oid</span> <span style="color: maroon; font-weight: bold;">LIMIT</span> <span style="color: #008c00;">1</span><span style="color: #808030;">)</span><span style="color: #808030;">)</span> <span style="color: maroon; font-weight: bold;">OR</span>
<span style="color: #808030;">(</span>nspname <span style="color: #808030;">=</span> <span style="color: #0000e6;">'sys'</span> <span style="color: maroon; font-weight: bold;">AND</span>
<span style="color: maroon; font-weight: bold;">EXISTS</span> <span style="color: #808030;">(</span><span style="color: maroon; font-weight: bold;">SELECT</span> <span style="color: #008c00;">1</span> <span style="color: maroon; font-weight: bold;">FROM</span> pg_class
<span style="color: maroon; font-weight: bold;">WHERE</span> relname <span style="color: #808030;">=</span> <span style="color: #0000e6;">'all_tables'</span>
<span style="color: maroon; font-weight: bold;">AND</span> relnamespace <span style="color: #808030;">=</span> nsp<span style="color: #808030;">.</span><span style="color: maroon; font-weight: bold;">oid</span> <span style="color: maroon; font-weight: bold;">LIMIT</span> <span style="color: #008c00;">1</span><span style="color: #808030;">)</span><span style="color: #808030;">)</span>
<span style="color: #808030;">)</span>
<span style="color: maroon; font-weight: bold;">AND</span> nspname <span style="color: maroon; font-weight: bold;">NOT</span> <span style="color: maroon; font-weight: bold;">LIKE</span> E<span style="color: #0000e6;">'pg\_temp\_%'</span>
<span style="color: maroon; font-weight: bold;">AND</span> nspname <span style="color: maroon; font-weight: bold;">NOT</span> <span style="color: maroon; font-weight: bold;">LIKE</span> E<span style="color: #0000e6;">'pg\_toast_temp\_%'</span>
<span style="color: maroon; font-weight: bold;">ORDER</span> <span style="color: maroon; font-weight: bold;">BY</span> <span style="color: #008c00;">1</span><span style="color: #808030;">,</span> nspname
</pre>
<h2 style="text-align: left;">
Tables</h2>
<b>Get all tables for schema "public"</b><br />
<pre style="background: #ffffff; color: black;"><span style="color: maroon; font-weight: bold;">SELECT</span> n<span style="color: #808030;">.</span>nspname <span style="color: maroon; font-weight: bold;">as</span> <span style="color: maroon;">"Schema"</span><span style="color: #808030;">,</span> <span style="color: maroon; font-weight: bold;">c</span><span style="color: #808030;">.</span>relname <span style="color: maroon; font-weight: bold;">AS</span> datname<span style="color: #808030;">,</span>
<span style="color: maroon; font-weight: bold;">CASE</span> <span style="color: maroon; font-weight: bold;">c</span><span style="color: #808030;">.</span>relkind
<span style="color: maroon; font-weight: bold;">WHEN</span> <span style="color: #0000e6;">'r'</span> <span style="color: maroon; font-weight: bold;">THEN</span> <span style="color: #0000e6;">'table'</span>
<span style="color: maroon; font-weight: bold;">WHEN</span> <span style="color: #0000e6;">'v'</span> <span style="color: maroon; font-weight: bold;">THEN</span> <span style="color: #0000e6;">'view'</span>
<span style="color: maroon; font-weight: bold;">WHEN</span> <span style="color: #0000e6;">'i'</span> <span style="color: maroon; font-weight: bold;">THEN</span> <span style="color: #0000e6;">'index'</span>
<span style="color: maroon; font-weight: bold;">WHEN</span> <span style="color: #0000e6;">'S'</span> <span style="color: maroon; font-weight: bold;">THEN</span> <span style="color: #0000e6;">'sequence'</span>
<span style="color: maroon; font-weight: bold;">WHEN</span> <span style="color: #0000e6;">'s'</span> <span style="color: maroon; font-weight: bold;">THEN</span> <span style="color: #0000e6;">'special'</span>
<span style="color: maroon; font-weight: bold;">END</span> <span style="color: maroon; font-weight: bold;">as</span> <span style="color: maroon;">"Type"</span><span style="color: #808030;">,</span> u<span style="color: #808030;">.</span>usename <span style="color: maroon; font-weight: bold;">as</span> <span style="color: maroon;">"Owner"</span><span style="color: #808030;">,</span>
<span style="color: #808030;">(</span><span style="color: maroon; font-weight: bold;">SELECT</span> obj_description<span style="color: #808030;">(</span><span style="color: maroon; font-weight: bold;">c</span><span style="color: #808030;">.</span><span style="color: maroon; font-weight: bold;">oid</span><span style="color: #808030;">,</span> <span style="color: #0000e6;">'pg_class'</span><span style="color: #808030;">)</span><span style="color: #808030;">)</span> <span style="color: maroon; font-weight: bold;">AS</span> <span style="color: maroon; font-weight: bold;">comment</span>
<span style="color: maroon; font-weight: bold;">FROM</span> pg_catalog<span style="color: #808030;">.</span>pg_class <span style="color: maroon; font-weight: bold;">c</span>
<span style="color: maroon; font-weight: bold;">LEFT</span> <span style="color: maroon; font-weight: bold;">JOIN</span> pg_catalog<span style="color: #808030;">.</span>pg_user u <span style="color: maroon; font-weight: bold;">ON</span> u<span style="color: #808030;">.</span>usesysid <span style="color: #808030;">=</span> <span style="color: maroon; font-weight: bold;">c</span><span style="color: #808030;">.</span>relowner
<span style="color: maroon; font-weight: bold;">LEFT</span> <span style="color: maroon; font-weight: bold;">JOIN</span> pg_catalog<span style="color: #808030;">.</span>pg_namespace n <span style="color: maroon; font-weight: bold;">ON</span> n<span style="color: #808030;">.</span><span style="color: maroon; font-weight: bold;">oid</span> <span style="color: #808030;">=</span> <span style="color: maroon; font-weight: bold;">c</span><span style="color: #808030;">.</span>relnamespace
<span style="color: maroon; font-weight: bold;">WHERE</span> n<span style="color: #808030;">.</span>nspname<span style="color: #808030;">=</span><span style="color: #0000e6;">'<i><b>public</b></i>'</span> <span style="color: maroon; font-weight: bold;">AND</span> <span style="color: maroon; font-weight: bold;">c</span><span style="color: #808030;">.</span>relkind <span style="color: maroon; font-weight: bold;">IN</span> <span style="color: #808030;">(</span><span style="color: #0000e6;">'r'</span><span style="color: #808030;">,</span><span style="color: #0000e6;">''</span><span style="color: #808030;">)</span>
<span style="color: maroon; font-weight: bold;">AND</span> n<span style="color: #808030;">.</span>nspname <span style="color: maroon; font-weight: bold;">NOT</span> <span style="color: maroon; font-weight: bold;">IN</span> <span style="color: #808030;">(</span><span style="color: #0000e6;">'pg_catalog'</span><span style="color: #808030;">,</span> <span style="color: #0000e6;">'pg_toast'</span><span style="color: #808030;">,</span> <span style="color: #0000e6;">'information_schema'</span><span style="color: #808030;">)</span>
<span style="color: maroon; font-weight: bold;">ORDER</span> <span style="color: maroon; font-weight: bold;">BY</span> datname <span style="color: maroon; font-weight: bold;">ASC</span> </pre>
<br />
<h2 style="text-align: left;">
Fields</h2>
<b>Get all fields for table "table1", with additional information </b>(type, default value, not null flag, length, comment, foreign key name, primary key name)<br />
<pre style="background: #ffffff; color: black;"><span style="color: maroon; font-weight: bold;">SELECT</span> pg_tables<span style="color: #808030;">.</span>tablename<span style="color: #808030;">,</span> pg_attribute<span style="color: #808030;">.</span>attname <span style="color: maroon; font-weight: bold;">AS</span> field<span style="color: #808030;">,</span>
format_type<span style="color: #808030;">(</span>pg_attribute<span style="color: #808030;">.</span>atttypid<span style="color: #808030;">,</span> <span style="color: #008c00;">NULL</span><span style="color: #808030;">)</span> <span style="color: maroon; font-weight: bold;">AS</span> <span style="color: maroon;">"type"</span><span style="color: #808030;">,</span>
pg_attribute<span style="color: #808030;">.</span>atttypmod <span style="color: maroon; font-weight: bold;">AS</span> <span style="color: #bb7977; font-weight: bold;">len</span><span style="color: #808030;">,</span>
<span style="color: #808030;">(</span><span style="color: maroon; font-weight: bold;">SELECT</span> col_description<span style="color: #808030;">(</span>pg_attribute<span style="color: #808030;">.</span>attrelid<span style="color: #808030;">,</span>
pg_attribute<span style="color: #808030;">.</span>attnum<span style="color: #808030;">)</span><span style="color: #808030;">)</span> <span style="color: maroon; font-weight: bold;">AS</span> <span style="color: maroon; font-weight: bold;">comment</span><span style="color: #808030;">,</span>
<span style="color: maroon; font-weight: bold;">CASE</span> pg_attribute<span style="color: #808030;">.</span>attnotnull
<span style="color: maroon; font-weight: bold;">WHEN</span> <span style="color: maroon; font-weight: bold;">false</span> <span style="color: maroon; font-weight: bold;">THEN</span> <span style="color: #008c00;">1</span> <span style="color: maroon; font-weight: bold;">ELSE</span> <span style="color: #008c00;">0</span>
<span style="color: maroon; font-weight: bold;">END</span> <span style="color: maroon; font-weight: bold;">AS</span> <span style="color: maroon;">"notnull"</span><span style="color: #808030;">,</span>
pg_constraint<span style="color: #808030;">.</span>conname <span style="color: maroon; font-weight: bold;">AS</span> <span style="color: maroon;">"key"</span><span style="color: #808030;">,</span> pc2<span style="color: #808030;">.</span>conname <span style="color: maroon; font-weight: bold;">AS</span> ckey<span style="color: #808030;">,</span>
<span style="color: #808030;">(</span><span style="color: maroon; font-weight: bold;">SELECT</span> pg_attrdef<span style="color: #808030;">.</span>adsrc <span style="color: maroon; font-weight: bold;">FROM</span> pg_attrdef
<span style="color: maroon; font-weight: bold;">WHERE</span> pg_attrdef<span style="color: #808030;">.</span>adrelid <span style="color: #808030;">=</span> pg_class<span style="color: #808030;">.</span><span style="color: maroon; font-weight: bold;">oid</span>
<span style="color: maroon; font-weight: bold;">AND</span> pg_attrdef<span style="color: #808030;">.</span>adnum <span style="color: #808030;">=</span> pg_attribute<span style="color: #808030;">.</span>attnum<span style="color: #808030;">)</span> <span style="color: maroon; font-weight: bold;">AS</span> <span style="color: maroon; font-weight: bold;">def</span>
<span style="color: maroon; font-weight: bold;">FROM</span> pg_tables<span style="color: #808030;">,</span> pg_class
<span style="color: maroon; font-weight: bold;">JOIN</span> pg_attribute <span style="color: maroon; font-weight: bold;">ON</span> pg_class<span style="color: #808030;">.</span><span style="color: maroon; font-weight: bold;">oid</span> <span style="color: #808030;">=</span> pg_attribute<span style="color: #808030;">.</span>attrelid
<span style="color: maroon; font-weight: bold;">AND</span> pg_attribute<span style="color: #808030;">.</span>attnum <span style="color: #808030;">></span> <span style="color: #008c00;">0</span>
<span style="color: maroon; font-weight: bold;">LEFT</span> <span style="color: maroon; font-weight: bold;">JOIN</span> pg_constraint <span style="color: maroon; font-weight: bold;">ON</span> pg_constraint<span style="color: #808030;">.</span>contype <span style="color: #808030;">=</span> <span style="color: #0000e6;">'p'</span><span style="color: #797997;">:</span><span style="color: #797997;">:</span><span style="color: maroon;">"char"</span>
<span style="color: maroon; font-weight: bold;">AND</span> pg_constraint<span style="color: #808030;">.</span>conrelid <span style="color: #808030;">=</span> pg_class<span style="color: #808030;">.</span><span style="color: maroon; font-weight: bold;">oid</span> <span style="color: maroon; font-weight: bold;">AND</span>
<span style="color: #808030;">(</span>pg_attribute<span style="color: #808030;">.</span>attnum <span style="color: #808030;">=</span> <span style="color: maroon; font-weight: bold;">ANY</span> <span style="color: #808030;">(</span>pg_constraint<span style="color: #808030;">.</span>conkey<span style="color: #808030;">)</span><span style="color: #808030;">)</span>
<span style="color: maroon; font-weight: bold;">LEFT</span> <span style="color: maroon; font-weight: bold;">JOIN</span> pg_constraint <span style="color: maroon; font-weight: bold;">AS</span> pc2 <span style="color: maroon; font-weight: bold;">ON</span> pc2<span style="color: #808030;">.</span>contype <span style="color: #808030;">=</span> <span style="color: #0000e6;">'f'</span><span style="color: #797997;">:</span><span style="color: #797997;">:</span><span style="color: maroon;">"char"</span>
<span style="color: maroon; font-weight: bold;">AND</span> pc2<span style="color: #808030;">.</span>conrelid <span style="color: #808030;">=</span> pg_class<span style="color: #808030;">.</span><span style="color: maroon; font-weight: bold;">oid</span>
<span style="color: maroon; font-weight: bold;">AND</span> <span style="color: #808030;">(</span>pg_attribute<span style="color: #808030;">.</span>attnum <span style="color: #808030;">=</span> <span style="color: maroon; font-weight: bold;">ANY</span> <span style="color: #808030;">(</span>pc2<span style="color: #808030;">.</span>conkey<span style="color: #808030;">)</span><span style="color: #808030;">)</span>
<span style="color: maroon; font-weight: bold;">WHERE</span> pg_class<span style="color: #808030;">.</span>relname <span style="color: #808030;">=</span> pg_tables<span style="color: #808030;">.</span>tablename
<span style="color: maroon; font-weight: bold;">AND</span> pg_tables<span style="color: #808030;">.</span>tableowner <span style="color: #808030;">=</span> <span style="color: maroon;">"current_user"</span><span style="color: #808030;">(</span><span style="color: #808030;">)</span>
<span style="color: maroon; font-weight: bold;">AND</span> pg_attribute<span style="color: #808030;">.</span>atttypid <span style="color: #808030;"><</span><span style="color: #808030;">></span> <span style="color: #008c00;">0</span><span style="color: #797997;">:</span><span style="color: #797997;">:oid</span>
<span style="color: maroon; font-weight: bold;">AND</span> tablename<span style="color: #808030;">=</span><span style="color: #0000e6;">'<i><b>table1</b></i>'</span>
<span style="color: maroon; font-weight: bold;">ORDER</span> <span style="color: maroon; font-weight: bold;">BY</span> field <span style="color: maroon; font-weight: bold;">ASC</span> </pre>
<br />
<b>See other parts:</b><br />
<a href="http://golden13.blogspot.com/2012/08/how-to-get-some-information-about_13.html">Part 3</a><br />
<a href="http://golden13.blogspot.com/2012/08/how-to-get-some-information-about.html">Part 1</a></div>
Egor Spivachttp://www.blogger.com/profile/10301192285096408561noreply@blogger.com2tag:blogger.com,1999:blog-21218870.post-68631795609676995632012-08-06T05:48:00.001-07:002012-08-09T06:12:35.660-07:00Unique Index vs Unique Constraint<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
Do you know the difference between unique constraints and unique indexes?<br />
In general, there are no differences, but, you <b>can't use unique index in a foreign key</b>.<br />
<br />
How to use the unique constraint:<br />
<b></b><br />
<br /></div>
<pre style="background: #ffffff; color: black;"><span style="color: dimgrey;">-- Table 2</span>
<span style="color: maroon; font-weight: bold;">CREATE</span> <span style="color: maroon; font-weight: bold;">TABLE</span> test2
<span style="color: #808030;">(</span>
test2_unique <span style="color: maroon; font-weight: bold;">integer</span><span style="color: #808030;">,</span>
<span style="color: maroon; font-weight: bold;">CONSTRAINT</span> test2_unique <span style="color: maroon; font-weight: bold;">UNIQUE</span> <span style="color: #808030;">(</span>test2_unique<span style="color: #808030;">)</span>
<span style="color: #808030;">)</span>
<span style="color: maroon; font-weight: bold;">WITH</span> <span style="color: #808030;">(</span>OIDS<span style="color: #808030;">=</span><span style="color: maroon; font-weight: bold;">FALSE</span><span style="color: #808030;">)</span><span style="color: #808030;">;</span>
<span style="color: maroon; font-weight: bold;">ALTER</span> <span style="color: maroon; font-weight: bold;">TABLE</span> test2 OWNER <span style="color: maroon; font-weight: bold;">TO</span> postgres<span style="color: #808030;">;</span>
<span style="color: dimgrey;">-- Table 1</span>
<span style="color: maroon; font-weight: bold;">CREATE</span> <span style="color: maroon; font-weight: bold;">TABLE</span> test1
<span style="color: #808030;">(</span>
test1_unique <span style="color: maroon; font-weight: bold;">integer</span> <span style="color: maroon; font-weight: bold;">NOT</span> <span style="color: maroon; font-weight: bold;">NULL</span> <span style="color: maroon; font-weight: bold;">DEFAULT</span> <span style="color: #008c00;">1</span><span style="color: #808030;">,</span>
pk serial <span style="color: maroon; font-weight: bold;">NOT</span> <span style="color: maroon; font-weight: bold;">NULL</span><span style="color: #808030;">,</span>
<span style="color: maroon; font-weight: bold;">CONSTRAINT</span> pk_key <span style="color: maroon; font-weight: bold;">PRIMARY</span> <span style="color: maroon; font-weight: bold;">KEY</span> <span style="color: #808030;">(</span>pk<span style="color: #808030;">)</span><span style="color: #808030;">,</span>
<span style="color: maroon; font-weight: bold;">CONSTRAINT</span> test1_test2_unique <span style="color: maroon; font-weight: bold;">FOREIGN</span> <span style="color: maroon; font-weight: bold;">KEY</span> <span style="color: #808030;">(</span>test1_unique<span style="color: #808030;">)</span>
<span style="color: maroon; font-weight: bold;">REFERENCES</span> test2 <span style="color: #808030;">(</span>test2_unique<span style="color: #808030;">)</span> <span style="color: maroon; font-weight: bold;">MATCH</span> SIMPLE
<span style="color: maroon; font-weight: bold;">ON</span> <span style="color: maroon; font-weight: bold;">UPDATE</span> <span style="color: maroon; font-weight: bold;">CASCADE</span> <span style="color: maroon; font-weight: bold;">ON</span> <span style="color: maroon; font-weight: bold;">DELETE</span> <span style="color: maroon; font-weight: bold;">CASCADE</span>
<span style="color: #808030;">)</span>
<span style="color: maroon; font-weight: bold;">WITH</span> <span style="color: #808030;">(</span>OIDS<span style="color: #808030;">=</span><span style="color: maroon; font-weight: bold;">FALSE</span><span style="color: #808030;">)</span><span style="color: #808030;">;</span>
<span style="color: maroon; font-weight: bold;">ALTER</span> <span style="color: maroon; font-weight: bold;">TABLE</span> test1 OWNER <span style="color: maroon; font-weight: bold;">TO</span> postgres<span style="color: #808030;">;</span> </pre>
<pre style="background: #ffffff; color: black;"><span style="color: dimgrey;"> </span></pre>
<br />
Also, you need create an unique index for test1_unique field,
for better performance:<br />
<br />
<pre style="background: #ffffff; color: black;"><span style="color: maroon; font-weight: bold;">CREATE</span> <span style="color: maroon; font-weight: bold;">INDEX</span> fki_test1_test2_unique
<span style="color: maroon; font-weight: bold;">ON</span> test1 <span style="color: maroon; font-weight: bold;">USING</span> btree <span style="color: #808030;">(</span>test1_unique<span style="color: #808030;">)</span><span style="color: #808030;">;</span>
</pre>
</div>Egor Spivachttp://www.blogger.com/profile/10301192285096408561noreply@blogger.com1tag:blogger.com,1999:blog-21218870.post-41479528476126890452012-08-06T01:31:00.000-07:002012-08-13T06:56:00.150-07:00How to get some information about PostgreSQL structure (Part 1)<div dir="ltr" style="text-align: left;" trbidi="on">
<h2 style="text-align: left;">
Types</h2>
<div style="text-align: left;">
<b>Get Types list:</b></div>
<pre style="background: #ffffff; color: black;"><span style="color: maroon; font-weight: bold;">SELECT</span> <span style="color: maroon; font-weight: bold;">oid</span><span style="color: #808030;">,</span> format_type<span style="color: #808030;">(</span><span style="color: maroon; font-weight: bold;">oid</span><span style="color: #808030;">,</span> <span style="color: maroon; font-weight: bold;">NULL</span><span style="color: #808030;">)</span> <span style="color: maroon; font-weight: bold;">AS</span> typname <span style="color: maroon; font-weight: bold;">FROM</span> pg_type <span style="color: maroon; font-weight: bold;">WHERE</span> typtype<span style="color: #808030;">=</span><span style="color: #0000e6;">'b'</span>
</pre>
<h2 style="text-align: left;">
Users</h2>
<div style="text-align: left;">
<b>Get users list:</b></div>
<pre style="background: #ffffff; color: black;"><span style="color: maroon; font-weight: bold;">SELECT</span> rolname <span style="color: maroon; font-weight: bold;">FROM</span> pg_roles <span style="color: maroon; font-weight: bold;">WHERE</span> rolcanlogin <span style="color: maroon; font-weight: bold;">ORDER</span> <span style="color: maroon; font-weight: bold;">BY</span> <span style="color: #008c00;">1</span>
</pre>
<h2 style="text-align: left;">
Databases</h2>
<b>Get databases list by name of schema:</b><br />
<pre style="background: #ffffff; color: black;"><span style="color: maroon; font-weight: bold;">SELECT</span> n<span style="color: #808030;">.</span>nspname <span style="color: maroon; font-weight: bold;">as</span> <span style="color: maroon;">"Schema"</span><span style="color: #808030;">,</span> <span style="color: maroon; font-weight: bold;">c</span><span style="color: #808030;">.</span>relname <span style="color: maroon; font-weight: bold;">as</span> datname<span style="color: #808030;">,</span> <span style="color: maroon; font-weight: bold;">CASE</span> <span style="color: maroon; font-weight: bold;">c</span><span style="color: #808030;">.</span>relkind </pre>
<pre style="background: #ffffff; color: black;"><span style="color: maroon; font-weight: bold;">WHEN</span> <span style="color: #0000e6;">'r'</span> <span style="color: maroon; font-weight: bold;">THEN</span> <span style="color: #0000e6;">'table'</span> <span style="color: maroon; font-weight: bold;"> </span></pre>
<pre style="background: #ffffff; color: black;"><span style="color: maroon; font-weight: bold;">WHEN</span> <span style="color: #0000e6;">'v'</span> <span style="color: maroon; font-weight: bold;">THEN</span> <span style="color: #0000e6;">'view'</span> </pre>
<pre style="background: #ffffff; color: black;"><span style="color: maroon; font-weight: bold;">WHEN</span> <span style="color: #0000e6;">'i'</span> <span style="color: maroon; font-weight: bold;">THEN</span> <span style="color: #0000e6;">'index'</span> </pre>
<pre style="background: #ffffff; color: black;"><span style="color: maroon; font-weight: bold;">WHEN</span> <span style="color: #0000e6;">'S'</span> <span style="color: maroon; font-weight: bold;">THEN</span> <span style="color: #0000e6;">'sequence'</span> <span style="color: maroon; font-weight: bold;"> </span></pre>
<pre style="background: #ffffff; color: black;"><span style="color: maroon; font-weight: bold;">WHEN</span> <span style="color: #0000e6;">'s'</span> <span style="color: maroon; font-weight: bold;">THEN</span> <span style="color: #0000e6;">'special'</span> </pre>
<pre style="background: #ffffff; color: black;"><span style="color: maroon; font-weight: bold;">END</span> <span style="color: maroon; font-weight: bold;">as</span> <span style="color: maroon;">"Type"</span><span style="color: #808030;">,</span> u<span style="color: #808030;">.</span>usename <span style="color: maroon; font-weight: bold;">as</span> <span style="color: maroon;">"Owner"</span>
<span style="color: maroon; font-weight: bold;">FROM</span> pg_catalog<span style="color: #808030;">.</span>pg_class <span style="color: maroon; font-weight: bold;">c</span>
<span style="color: maroon; font-weight: bold;">LEFT</span> <span style="color: maroon; font-weight: bold;">JOIN</span> pg_catalog<span style="color: #808030;">.</span>pg_user u <span style="color: maroon; font-weight: bold;">ON</span> u<span style="color: #808030;">.</span>usesysid <span style="color: #808030;">=</span> <span style="color: maroon; font-weight: bold;">c</span><span style="color: #808030;">.</span>relowner
<span style="color: maroon; font-weight: bold;">LEFT</span> <span style="color: maroon; font-weight: bold;">JOIN</span> pg_catalog<span style="color: #808030;">.</span>pg_namespace n <span style="color: maroon; font-weight: bold;">ON</span> n<span style="color: #808030;">.</span><span style="color: maroon; font-weight: bold;">oid</span> <span style="color: #808030;">=</span> <span style="color: maroon; font-weight: bold;">c</span><span style="color: #808030;">.</span>relnamespace
<span style="color: maroon; font-weight: bold;">WHERE</span> n<span style="color: #808030;">.</span>nspname<span style="color: #808030;">=</span><span style="color: #0000e6;">'YourSchemaName'</span> <span style="color: maroon; font-weight: bold;">AND</span> <span style="color: maroon; font-weight: bold;">c</span><span style="color: #808030;">.</span>relkind <span style="color: maroon; font-weight: bold;">IN</span> <span style="color: #808030;">(</span><span style="color: #0000e6;">'r'</span><span style="color: #808030;">,</span><span style="color: #0000e6;">''</span><span style="color: #808030;">)</span> <span style="color: maroon; font-weight: bold;"> </span></pre>
<pre style="background: #ffffff; color: black;"><span style="color: maroon; font-weight: bold;">AND</span> n<span style="color: #808030;">.</span>nspname <span style="color: maroon; font-weight: bold;">NOT</span> <span style="color: maroon; font-weight: bold;">IN</span> <span style="color: #808030;">(</span><span style="color: #0000e6;">'pg_catalog'</span><span style="color: #808030;">,</span> <span style="color: #0000e6;">'pg_toast'</span><span style="color: #808030;">,</span> <span style="color: #0000e6;">'information_schema'</span><span style="color: #808030;">)</span> </pre>
<br />
<b>See other parts:</b><br />
<a href="http://golden13.blogspot.com/2012/08/how-to-get-some-information-about_13.html">Part 3</a><br />
<a href="http://golden13.blogspot.com/2012/08/how-to-get-some-information-about_7.html">Part 2</a>
</div>
Egor Spivachttp://www.blogger.com/profile/10301192285096408561noreply@blogger.com0