Bootstrap FreeKB - Postgres (SQL) - List columns in a table using the psql command
Postgres (SQL) - List columns in a table using the psql command

Updated:   |  Postgres (SQL) articles

The psql --list command can be used to display the Postgres databases. Something like this should be returned.

~]# psql --username johndoe --dbname db001 --list
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
-----------+----------+----------+------------+------------+-----------------------
 db001     | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 db002     | john.doe | UTF8     | en_US.utf8 | en_US.utf8 |

 

The psql command with the \dt (describe table) command can be used to list the tables in a database. Something like this should be returned.

~]# psql --username johndoe --dbname db001 --command "\dt"
                   List of relations
 Schema |             Name              | Type  | Owner 
--------+-------------------------------+-------+-------
 public | table001                      | table | johndoe
 public | table002                      | table | johndoe
 public | table003                      | table | johndo

 

The psql command with the \d <table name> command can be used to list the columns in a table. If using the default posgres user account, I typically go with the following command.

sudo -u postgres psql --command "\d mytable"

 

If you have created a user account, the -u or --username option and --password flag can be used.

psql --username johndoe --password --command "\d mytable"

 

Better yet, you can setup passwordless authentication using the hidden .pgpass file and then connect without the --password flag.

psql --username johndoe --dbname mydb --command "\d mytable"

 

Something like this should be returned.

                                            Table "public.mytable"
        Column         |           Type           | Collation | Nullable |                       Default                       
-----------------------+--------------------------+-----------+----------+-----------------------------------------------------
 id                    | integer                  |           | not null | 
 name                  | varchar(30)              |           |          | 
 date_updated          | date                     |           |          | 

 

Or, the -x or --expanded flag can be used for a different format output.

~]$ psql --username johndoe --dbname mydb --expanded --command "\d mytable"
                                     Table "public.mydb"
     Column      |          Type          |                      Modifiers
-----------------+------------------------+------------------------------------------------------
 id              | integer                | not null default nextval('mytable_id_seq'::regclass)
 date_updated    | date                   |
 name            | character varying(100) | not null
Indexes:
    "mytable_pkey" PRIMARY KEY, btree (id)

 




Did you find this article helpful?

If so, consider buying me a coffee over at Buy Me A Coffee



Comments


Add a Comment


Please enter e420b2 in the box below so that we can be sure you are a human.