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 postgres 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)
The alter table <table name> add column <column name> <column type> command can be used to add a column to a table.
psql --username johndoe --dbname mydb --expanded --command "alter table mytable add column department varchar(50)"
Did you find this article helpful?
If so, consider buying me a coffee over at