Basics

Connect as user postgres:

1

psql -U postgres

Connect to a specific database:

\c database_name;

Quit the psql:

\q

List all databases:

\l

Lists all tables in the current database:

\dt

List all users:

\du

Create a new role username with a password:

1

CREATE ROLE username NOINHERIT LOGIN PASSWORD password;

Managing tables

Create a new table or a temporary table

1
2
3
4
5
6
7

	
CREATE [TEMP] TABLE [IF NOT EXISTS] table_name(
   pk SERIAL PRIMARY KEY,
   c1 type(size) NOT NULL,
   c2 type(size) NULL,
   ...
);

Add a new column to a table:

1

ALTER TABLE table_name ADD COLUMN new_column_name TYPE;

Drop a column in a table:

1

ALTER TABLE table_name DROP COLUMN column_name;

Rename a column:

1

ALTER TABLE table_name RENAME column_name TO new_column_name;

Set or remove a default value for a column:

1

ALTER TABLE table_name ALTER COLUMN [SET DEFAULT value | DROP DEFAULT]

Add a primary key to a table.

1

ALTER TABLE table_name ADD PRIMARY KEY (column,...);

Remove the primary key from a table.

1
2

ALTER TABLE table_name 
DROP CONSTRAINT primary_key_constraint_name;

Rename a table.

1

ALTER TABLE table_name RENAME TO new_table_name;

Credentials via environment variables

1
2
3

PGHOST=10.1.1.1 \
  PGUSER=user PGPASSWORD=password \
  psql my_db  -c "select * from my_table"

See also