Subject: Re: To get the column names, data types, and nullables of
 tables in the schema owned by MASTER_USER
To: Eugene Yin <eugeneymail@ymail.com>,
 "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
References: 
 <751099510.1801394.1452198146367.JavaMail.yahoo.ref@mail.yahoo.com>
 <751099510.1801394.1452198146367.JavaMail.yahoo@mail.yahoo.com>
From: Joe Conway <mail@joeconway.com>
Message-ID: <568ECEAF.2030204@joeconway.com>
Date: Thu, 7 Jan 2016 12:46:39 -0800
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:38.0) Gecko/20100101
 Thunderbird/38.4.0
MIME-Version: 1.0
In-Reply-To: <751099510.1801394.1452198146367.JavaMail.yahoo@mail.yahoo.com>
Content-Type: multipart/signed; micalg=pgp-sha1;
 protocol="application/pgp-signature";
 boundary="bTBjpavPGgXJ6FKIt397phIqRjBe7TCqh"
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

This is an OpenPGP/MIME signed message (RFC 4880 and 3156)
--bTBjpavPGgXJ6FKIt397phIqRjBe7TCqh
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable

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:
>=20
> |selectt.table_name,t.column_name,t.data_type,t.NULLABLE,(SELECTcol.col=
umn_name
> FROMall_constraints cons,all_cons_columns col WHEREcol.table_name
> =3Dt.table_name ANDcons.constraint_type =3D'P'ANDcons.constraint_name
> =3Dcol.constraint_name ANDcons.owner =3Dcol.owner andcons.owner
> =3D'MASTER_USER')Primary_Key_Column|
>=20
> 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=3D# \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


--=20
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development


--bTBjpavPGgXJ6FKIt397phIqRjBe7TCqh
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.22 (GNU/Linux)

iQIcBAEBAgAGBQJWjs6wAAoJEDfy90M199hlcwIP/AgUsC4wBpI376FiLzRupRON
UI6gkuatZIK9WE2KQTQ1BJAcHJVstl/63sBZ1keSQ368qhinLu03ajWG4m1TJNIf
Sals8aNZKBJ3Hn9aYpH0xL6ub04NkPowENlLsoZlwG6v8OoADVpjMpkZZRgIejU7
qPWC34bUuAG2nE7o+fOrOVh6FSZDYSKNX0agbpgnRbEmLQ6kaxcwonTGefkWevYk
41SVJMCriTnl4k9AdWvOyy+Q5+Y4xCx+qwfruP3Bv9t/mP7pGQAPGNXKBwHtaKGR
uYY7LNxiTKMJyygi4mufDnTvBSgp0BvHY89kSMS604p7jsMCH4kU/9llnD2q11mi
JljjqlVvkP/MjtaWCe7m3hrhibGDsfdiE5zU89+1cZhYV+y6nfS6hKuh6MlbDWi/
2Az42bjg9HFLLBlMdLW/GlwdYSQxmgpXOFEpnkFx0gyu8itkIiZ5AVh4TohJ7Xxy
yiyRmLypacgyjtwDUiYcR02DM1T5LapjnCsTcZzSdkJWz9R7umTUwL5l+0BMvStT
jGhlYDXwlLPmLwhoK4uE5XgeCokL7C27KSIkXcH4g/dq1FwrO7aCt1BTBMLGZ2yX
tFufXCaoz67WNa7ypYMiZMqhw5k9NUQZCuAm5qJTKYwsqVlggzKJzzscC68QIZ7Z
SjdKqDQ99ANKDQlsprEo
=KM7u
-----END PGP SIGNATURE-----

--bTBjpavPGgXJ6FKIt397phIqRjBe7TCqh--