agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Joe Conway <mail@joeconway.com>
To: Eugene Yin <eugeneymail@ymail.com>
To: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: To get the column names, data types, and nullables of tables in the schema owned by MASTER_USER
Date: Thu, 7 Jan 2016 12:46:39 -0800
Message-ID: <568ECEAF.2030204@joeconway.com> (raw)
In-Reply-To: <751099510.1801394.1452198146367.JavaMail.yahoo@mail.yahoo.com>
References: <751099510.1801394.1452198146367.JavaMail.yahoo.ref@mail.yahoo.com>
<751099510.1801394.1452198146367.JavaMail.yahoo@mail.yahoo.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
view thread (2+ messages)
Message-ID: <568ECEAF.2030204@joeconway.com>
Permalink: ../568ECEAF.2030204@joeconway.com/
Also on: postgresql.org/message-id/568ECEAF.2030204@joeconway.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mail@joeconway.com, eugeneymail@ymail.com
Subject: Re: To get the column names, data types, and nullables of tables in the schema owned by MASTER_USER
In-Reply-To: <568ECEAF.2030204@joeconway.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox