Re: To get the column names, data types, and nullables of tables in the schema owned by MASTER_USER

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