Thread: To get the column names, data types, and nullables of tables in the schema owned by MASTER_USER
To get the column names, data types, and nullables of tables in the schema owned by MASTER_USER
From
Eugene Yin
Date:
<div style="color:#000; background-color:#fff; font-family:HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande,sans-serif;font-size:13px"><div class="" id="yui_3_16_0_1_1452135740077_68667" itemprop="text" style="margin: 0px0px 5px; padding: 0px; border: 0px; font-size: 15px; width: 660px; word-wrap: break-word; line-height: 1.3; color: rgb(34,36, 38); font-family: 'Helvetica Neue', Helvetica, Arial, sans-serif;"><div class="" id="yui_3_16_0_1_1452135740077_68669"style="margin-bottom: 1em; border: 0px; clear: both;">PostgreSQL ver: 9.4.5 OS: Linux</div><div class="" id="yui_3_16_0_1_1452135740077_68669" style="margin-bottom: 1em; border: 0px; clear: both;">GOAL:To get the column names, data types, and nullables of tables in the schema owned by MASTER_USER</div><div class=""id="yui_3_16_0_1_1452135740077_68671" style="margin-bottom: 1em; border: 0px; clear: both;">In Oracle, I can usethe following statement:</div><pre class="" id="yui_3_16_0_1_1452135740077_68673" style="margin-top: 0px; padding: 5px;border: 0px; font-size: 13px; overflow: auto; width: auto; max-height: 600px; font-family: Consolas, Menlo, Monaco, 'LucidaConsole', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;color: rgb(57, 51, 24); word-wrap: normal; background-color: rgb(238, 238, 238);"><code class="" id="yui_3_16_0_1_1452135740077_68675"style="margin-top: 0px; margin-bottom: 0px; padding: 0px; border: 0px; font-family:Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono','Courier New', monospace, sans-serif; white-space: inherit;"><span class="" id="yui_3_16_0_1_1452135740077_68677" style="margin:0px; padding: 0px; border: 0px; color: rgb(0, 0, 139);">select</span><span class="" id="yui_3_16_0_1_1452135740077_68679"style="margin: 0px; padding: 0px; border: 0px; color: rgb(0, 0, 0);"> t</span><spanclass="" id="yui_3_16_0_1_1452135740077_68681" style="margin: 0px; padding: 0px; border: 0px; color: rgb(0,0, 0);">.</span><span class="" id="yui_3_16_0_1_1452135740077_68683" style="margin: 0px; padding: 0px; border: 0px;color: rgb(0, 0, 0);">table_name</span><span class="" id="yui_3_16_0_1_1452135740077_68685" style="margin: 0px; padding:0px; border: 0px; color: rgb(0, 0, 0);">,</span><span class="" id="yui_3_16_0_1_1452135740077_68687" style="margin:0px; padding: 0px; border: 0px; color: rgb(0, 0, 0);"> t</span><span class="" id="yui_3_16_0_1_1452135740077_68689"style="margin: 0px; padding: 0px; border: 0px; color: rgb(0, 0, 0);">.</span><span class=""id="yui_3_16_0_1_1452135740077_68691" style="margin: 0px; padding: 0px; border: 0px; color: rgb(0, 0, 0);">column_name</span><spanclass="" id="yui_3_16_0_1_1452135740077_68693" style="margin: 0px; padding: 0px; border: 0px;color: rgb(0, 0, 0);">,</span><span class="" id="yui_3_16_0_1_1452135740077_68695" style="margin: 0px; padding: 0px;border: 0px; color: rgb(0, 0, 0);"> t</span><span class="" id="yui_3_16_0_1_1452135740077_68697" style="margin: 0px;padding: 0px; border: 0px; color: rgb(0, 0, 0);">.</span><span class="" id="yui_3_16_0_1_1452135740077_68699" style="margin:0px; padding: 0px; border: 0px; color: rgb(0, 0, 0);">data_type</span><span class="" id="yui_3_16_0_1_1452135740077_68701"style="margin: 0px; padding: 0px; border: 0px; color: rgb(0, 0, 0);">,</span><span class=""id="yui_3_16_0_1_1452135740077_68703" style="margin: 0px; padding: 0px; border: 0px; color: rgb(0, 0, 0);"> t</span><spanclass="" id="yui_3_16_0_1_1452135740077_68705" style="margin: 0px; padding: 0px; border: 0px; color: rgb(0,0, 0);">.</span><span class="" id="yui_3_16_0_1_1452135740077_68707" style="margin: 0px; padding: 0px; border: 0px;color: rgb(0, 0, 0);">NULLABLE</span><span class="" id="yui_3_16_0_1_1452135740077_68709" style="margin: 0px; padding:0px; border: 0px; color: rgb(0, 0, 0);">,</span><span class="" id="yui_3_16_0_1_1452135740077_68711" style="margin:0px; padding: 0px; border: 0px; color: rgb(0, 0, 0);"> </span><span class="" id="yui_3_16_0_1_1452135740077_68713"style="margin: 0px; padding: 0px; border: 0px; color: rgb(0, 0, 0);">(</span><span class=""id="yui_3_16_0_1_1452135740077_68715" style="margin: 0px; padding: 0px; border: 0px; color: rgb(0, 0, 139);">SELECT</span><spanclass="" id="yui_3_16_0_1_1452135740077_68717" style="margin: 0px; padding: 0px; border: 0px; color:rgb(0, 0, 0);"> col</span><span class="" id="yui_3_16_0_1_1452135740077_68719" style="margin: 0px; padding: 0px; border:0px; color: rgb(0, 0, 0);">.</span><span class="" id="yui_3_16_0_1_1452135740077_68721" style="margin: 0px; padding:0px; border: 0px; color: rgb(0, 0, 0);">column_name </span><span class="" id="yui_3_16_0_1_1452135740077_68723"style="margin: 0px; padding: 0px; border: 0px; color: rgb(0, 0, 139);">FROM</span><spanclass="" id="yui_3_16_0_1_1452135740077_68725" style="margin: 0px; padding: 0px; border: 0px; color:rgb(0, 0, 0);"> all_constraints cons</span><span class="" id="yui_3_16_0_1_1452135740077_68727" style="margin: 0px;padding: 0px; border: 0px; color: rgb(0, 0, 0);">,</span><span class="" id="yui_3_16_0_1_1452135740077_68729" style="margin:0px; padding: 0px; border: 0px; color: rgb(0, 0, 0);"> all_cons_columns col </span><span class="" id="yui_3_16_0_1_1452135740077_68731"style="margin: 0px; padding: 0px; border: 0px; color: rgb(0, 0, 139);">WHERE</span><spanclass="" id="yui_3_16_0_1_1452135740077_68733" style="margin: 0px; padding: 0px; border: 0px; color:rgb(0, 0, 0);"> col</span><span class="" id="yui_3_16_0_1_1452135740077_68735" style="margin: 0px; padding: 0px; border:0px; color: rgb(0, 0, 0);">.</span><span class="" id="yui_3_16_0_1_1452135740077_68737" style="margin: 0px; padding:0px; border: 0px; color: rgb(0, 0, 0);">table_name </span><span class="" id="yui_3_16_0_1_1452135740077_68739" style="margin:0px; padding: 0px; border: 0px; color: rgb(0, 0, 0);">=</span><span class="" id="yui_3_16_0_1_1452135740077_68741"style="margin: 0px; padding: 0px; border: 0px; color: rgb(0, 0, 0);"> t</span><spanclass="" id="yui_3_16_0_1_1452135740077_68743" style="margin: 0px; padding: 0px; border: 0px; color: rgb(0,0, 0);">.</span><span class="" id="yui_3_16_0_1_1452135740077_68745" style="margin: 0px; padding: 0px; border: 0px;color: rgb(0, 0, 0);">table_name </span><span class="" id="yui_3_16_0_1_1452135740077_68747" style="margin:0px; padding: 0px; border: 0px; color: rgb(0, 0, 139);">AND</span><span class="" id="yui_3_16_0_1_1452135740077_68749"style="margin: 0px; padding: 0px; border: 0px; color: rgb(0, 0, 0);"> cons</span><spanclass="" id="yui_3_16_0_1_1452135740077_68751" style="margin: 0px; padding: 0px; border: 0px; color: rgb(0,0, 0);">.</span><span class="" id="yui_3_16_0_1_1452135740077_68753" style="margin: 0px; padding: 0px; border: 0px;color: rgb(0, 0, 0);">constraint_type </span><span class="" id="yui_3_16_0_1_1452135740077_68755" style="margin: 0px;padding: 0px; border: 0px; color: rgb(0, 0, 0);">=</span><span class="" id="yui_3_16_0_1_1452135740077_68757" style="margin:0px; padding: 0px; border: 0px; color: rgb(0, 0, 0);"> </span><span class="" id="yui_3_16_0_1_1452135740077_68759"style="margin: 0px; padding: 0px; border: 0px; color: rgb(128, 0, 0);">'P'</span><spanclass="" id="yui_3_16_0_1_1452135740077_68761" style="margin: 0px; padding: 0px; border: 0px; color:rgb(0, 0, 0);"> </span><span class="" id="yui_3_16_0_1_1452135740077_68763" style="margin: 0px;padding: 0px; border: 0px; color: rgb(0, 0, 139);">AND</span><span class="" id="yui_3_16_0_1_1452135740077_68765" style="margin:0px; padding: 0px; border: 0px; color: rgb(0, 0, 0);"> cons</span><span class="" id="yui_3_16_0_1_1452135740077_68767"style="margin: 0px; padding: 0px; border: 0px; color: rgb(0, 0, 0);">.</span><span class=""id="yui_3_16_0_1_1452135740077_68769" style="margin: 0px; padding: 0px; border: 0px; color: rgb(0, 0, 0);">constraint_name</span><span class="" id="yui_3_16_0_1_1452135740077_68771" style="margin: 0px; padding: 0px; border:0px; color: rgb(0, 0, 0);">=</span><span class="" id="yui_3_16_0_1_1452135740077_68773" style="margin: 0px; padding:0px; border: 0px; color: rgb(0, 0, 0);"> col</span><span class="" id="yui_3_16_0_1_1452135740077_68775" style="margin:0px; padding: 0px; border: 0px; color: rgb(0, 0, 0);">.</span><span class="" id="yui_3_16_0_1_1452135740077_68777"style="margin: 0px; padding: 0px; border: 0px; color: rgb(0, 0, 0);">constraint_name </span><span class="" id="yui_3_16_0_1_1452135740077_68779" style="margin: 0px;padding: 0px; border: 0px; color: rgb(0, 0, 139);">AND</span><span class="" id="yui_3_16_0_1_1452135740077_68781" style="margin:0px; padding: 0px; border: 0px; color: rgb(0, 0, 0);"> cons</span><span class="" id="yui_3_16_0_1_1452135740077_68783"style="margin: 0px; padding: 0px; border: 0px; color: rgb(0, 0, 0);">.</span><span class=""id="yui_3_16_0_1_1452135740077_68785" style="margin: 0px; padding: 0px; border: 0px; color: rgb(0, 0, 0);">owner</span><span class="" id="yui_3_16_0_1_1452135740077_68787" style="margin: 0px; padding: 0px; border: 0px; color:rgb(0, 0, 0);">=</span><span class="" id="yui_3_16_0_1_1452135740077_68789" style="margin: 0px; padding: 0px; border:0px; color: rgb(0, 0, 0);"> col</span><span class="" id="yui_3_16_0_1_1452135740077_68791" style="margin: 0px; padding:0px; border: 0px; color: rgb(0, 0, 0);">.</span><span class="" id="yui_3_16_0_1_1452135740077_68793" style="margin:0px; padding: 0px; border: 0px; color: rgb(0, 0, 0);">owner </span><span class="" id="yui_3_16_0_1_1452135740077_68795"style="margin: 0px; padding: 0px; border: 0px; color: rgb(0, 0, 139);">and</span><spanclass="" id="yui_3_16_0_1_1452135740077_68797" style="margin: 0px; padding: 0px; border: 0px; color:rgb(0, 0, 0);"> cons</span><span class="" id="yui_3_16_0_1_1452135740077_68799" style="margin: 0px; padding: 0px; border:0px; color: rgb(0, 0, 0);">.</span><span class="" id="yui_3_16_0_1_1452135740077_68801" style="margin: 0px; padding:0px; border: 0px; color: rgb(0, 0, 0);">owner </span><span class="" id="yui_3_16_0_1_1452135740077_68803" style="margin:0px; padding: 0px; border: 0px; color: rgb(0, 0, 0);">=</span><span class="" id="yui_3_16_0_1_1452135740077_68805"style="margin: 0px; padding: 0px; border: 0px; color: rgb(0, 0, 0);"> </span><span class=""id="yui_3_16_0_1_1452135740077_68807" style="margin: 0px; padding: 0px; border: 0px; color: rgb(128, 0, 0);">'MASTER_USER'</span><spanclass="" id="yui_3_16_0_1_1452135740077_68809" style="margin: 0px; padding: 0px; border: 0px;color: rgb(0, 0, 0);"> </span><span class="" id="yui_3_16_0_1_1452135740077_68811" style="margin: 0px; padding: 0px;border: 0px; color: rgb(0, 0, 0);">)</span><span class="" id="yui_3_16_0_1_1452135740077_68813" style="margin: 0px; padding:0px; border: 0px; color: rgb(0, 0, 0);"> Primary_Key_Column</span></code></pre><div class="" id="yui_3_16_0_1_1452135740077_68815"style="margin-bottom: 1em; border: 0px; clear: both;">from user_tab_columns t;</div><divclass="" id="yui_3_16_0_1_1452135740077_68817" style="margin-bottom: 1em; border: 0px; clear: both;">Now, I amon Postgres (9.4.5). How can I convert the above statement into the equivalent SQL on Postgres?</div><div class="" id="yui_3_16_0_1_1452135740077_68817"style="margin-bottom: 1em; border: 0px; clear: both;"><br /></div><div class="" id="yui_3_16_0_1_1452135740077_68819"style="margin-bottom: 1em; border: 0px; clear: both;">Thanks</div><div class="" id="yui_3_16_0_1_1452135740077_68821"style="margin-bottom: 1em; border: 0px; clear: both;">Eugene</div><div class="" dir="ltr"id="yui_3_16_0_1_1452135740077_68823"><br class="" id="yui_3_16_0_1_1452135740077_68825" /></div></div></div>
Re: To get the column names, data types, and nullables of tables in the schema owned by MASTER_USER
From
Joe Conway
Date:
On 01/07/2016 12:22 PM, Eugene Yin wrote:
> PostgreSQL ver: 9.4.5 OS: Linux
> GOAL: To get the column names, data types, and nullables of tables in
> the schema owned by MASTER_USER
> In Oracle, I can use the following statement:
>
> |selectt.table_name,t.column_name,t.data_type,t.NULLABLE,(SELECTcol.column_name
> FROMall_constraints cons,all_cons_columns col WHEREcol.table_name
> =t.table_name ANDcons.constraint_type ='P'ANDcons.constraint_name
> =col.constraint_name ANDcons.owner =col.owner andcons.owner
> ='MASTER_USER')Primary_Key_Column|
>
> from user_tab_columns t;
> Now, I am on Postgres (9.4.5). How can I convert the above statement
> into the equivalent SQL on Postgres?
Rather than trying to rewrite that specific query, I'll leave that as an
exercise for you. But to help you get there, start psql with -E option.
Then you will see the queries behind all the meta-commands. E.g. to
describe table tenk1 in database regression:
# psql -E regression
psql (9.5rc1)
Type "help" for help.
regression=# \d tenk1
[...lots of SQL queries for describing the table...]
Table "public.tenk1" Column | Type | Modifiers
-------------+---------+-----------unique1 | integer |unique2 | integer |two | integer |four |
integer|ten | integer |twenty | integer |hundred | integer |thousand | integer |twothousand |
integer|fivethous | integer |tenthous | integer |odd | integer |even | integer |stringu1 | name
|stringu2 | name |string4 | name |
Indexes: "tenk1_hundred" btree (hundred) "tenk1_thous_tenthous" btree (thousand, tenthous) "tenk1_unique1" btree
(unique1) "tenk1_unique2" btree (unique2)
HTH,
Joe
--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development