Friday, March 6, 2020

How to describe a table in a different schema in PostgreSQL



First, list the databases on the server:
[postgres@oric-pg01.oric.no ~]$ psql
psql (11.6)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
postgres=# \list
                                    List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |      Access privileges
-----------+----------+----------+------------+------------+------------------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =Tc/postgres                +
           |          |          |            |            | postgres=CTc/postgres       +
           |          |          |            |            | postgres_exporter=c/postgres
 kldb      | postgres | UTF8     | en_US.utf8 | en_US.utf8 | postgres=CTc/postgres       +
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres                 +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres                 +
           |          |          |            |            | postgres=CTc/postgres
(4 rows)
Then, connect to the correct database
postgres=# \connect kldb
You are now connected to database "kldb" as user "postgres".

You can now describe any table in the database:
kldb=# \d kld.actions

                                          Table "kld.actions"
     Column     |           Type           | Collation | Nullable |                             Default
----------------+--------------------------+-----------+----------+-----------------------------------------------------------------
 id             | integer                  |           | not null | nextval('kld.action_id_seq'::regclass)
 qid            | character varying(11)    |           | not null |
 systemid       | character varying(20)    |           | not null |
 actiontype     | character varying(20)    |           | not null |
 relation       | character varying(50)    |           |          |
 messageheader  | character varying(50)    |           |          |
 rnumber        | character varying(13)    |           |          |
 versionID      | numeric                  |           |          |
 errormsg       | character varying(4000)  |           |          |
 occured_time   | timestamp with time zone |           | not null | now()
Indexes:
    "pk__actions" PRIMARY KEY, btree (id)
    "idx__actions_sk1" btree (partsid, systemid, hendelsestype)

You can also use the metacommand "dt" or "dt+", like this:
prod=# \dt+ sales.*
                                       List of relations
  Schema   |                Name                | Type  |   Owner   |    Size    | Description
-----------+------------------------------------+-------+-----------+------------+-------------
 sales     | mytable1                           | table | jim       | 8192 bytes |
 sales     | mytable2                           | table | jim       | 8192 bytes |
 sales     | mytable3                           | table | jim       | 16 kB      |
 sales     | mytable4                           | table | jim       | 9757 MB    |
 sales     | mytable5                           | table | jim       | 312 GB     |
 (5 rows)


No comments:

Post a Comment