Обсуждение: To get the column names, data types, and nullables of tables in the schema owned by MASTER_USER
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:
select t.table_name, t.column_name, t.data_type, t.NULLABLE, (SELECT col.column_name FROM all_constraints cons, all_cons_columns col WHERE col.table_name = t.table_name AND cons.constraint_type = 'P' AND cons.constraint_name = col.constraint_name AND cons.owner = col.owner and cons.owner = 'MASTER_USER' ) Primary_Key_Columnfrom 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?
Thanks
Eugene
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