agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedTo get the column names, data types, and nullables of tables in the schema owned by MASTER_USER
2+ messages / 2 participants
[nested] [flat]
* To get the column names, data types, and nullables of tables in the schema owned by MASTER_USER
@ 2016-01-07 20:22 Eugene Yin <eugeneymail@ymail.com>
2016-01-07 20:46 ` Re: To get the column names, data types, and nullables of tables in the schema owned by MASTER_USER Joe Conway <mail@joeconway.com>
0 siblings, 1 reply; 2+ messages in thread
From: Eugene Yin @ 2016-01-07 20:22 UTC (permalink / raw)
To: pgsql-sql
PostgreSQL ver: 9.4.5 OS: LinuxGOAL: To get the column names, data types, and nullables of tables in the schema owned by MASTER_USERIn 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?
ThanksEugene
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: To get the column names, data types, and nullables of tables in the schema owned by MASTER_USER
2016-01-07 20:22 To get the column names, data types, and nullables of tables in the schema owned by MASTER_USER Eugene Yin <eugeneymail@ymail.com>
@ 2016-01-07 20:46 ` Joe Conway <mail@joeconway.com>
0 siblings, 0 replies; 2+ messages in thread
From: Joe Conway @ 2016-01-07 20:46 UTC (permalink / raw)
To: Eugene Yin <eugeneymail@ymail.com>; pgsql-sql
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
Attachments:
[application/pgp-signature] signature.asc (835B, ../../568ECEAF.2030204@joeconway.com/2-signature.asc)
download
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2016-01-07 20:46 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-01-07 20:22 To get the column names, data types, and nullables of tables in the schema owned by MASTER_USER Eugene Yin <eugeneymail@ymail.com>
2016-01-07 20:46 ` Joe Conway <mail@joeconway.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox