Useful Postgres / PSQL Commands

From the Command Line:

Connecting to a Postgres server

1

psql -h < host > -U < username >

Connect to Database (Once connected to server)

1

\c < database name >

Read File into Database

1

psql -h < host > -U < username > -f < filename >

Listing Commands

List Databases

1

\l

List Tables

1

\dt

Query all tables in DB

1

SELECT * FROM INFORMATION_SCHEMA.TABLES

Get row for for a table

1

SELECT COUNT(*) FROM <table name>;

Get a random row from a table

1

SELECT * FROM <table name> OFFSET floor(random() * (SELECT COUNT(*) FROM <table name>)) LIMIT 1;

Delete all data from table

1

TRUNCATE TABLE <table name(s)>;

Delete all data from table AND reset the auto-increment id

1

TRUNCATE TABLE <table name(s)> RESTART IDENTITY;