Skip to main content

PostgreSQL

brew info postgresql

https://github.com/dhamaniasad/awesome-postgres

Learn Postgres at the Playground - https://www.crunchydata.com/developers/tutorials - https://news.ycombinator.com/item?id=32498435

Postgres WASM - https://news.ycombinator.com/item?id=33067962

Reserved words

https://www.postgresql.org/docs/current/sql-keywords-appendix.html

Error codes

https://www.postgresql.org/docs/current/errcodes-appendix.html

Start/stop

With Brew:

brew services start postgresql
brew services stop postgresql
brew services restart postgresql
brew services list

With pg_ctl:

pg_ctl -D /usr/local/var/postgres start
pg_ctl -D /usr/local/var/postgres stop

Commands

postgres --help

Version: postgres --version or postgres -V

Check if Postgres is running: pgrep -l postgres. If there's no output it's not running.

Check the logs:

Create database: createdb dbname

Delete database: dropdb dbname

Recreate/reset database: dropdb dbname && createdb dbname

psql

Command reference: https://www.postgresql.org/docs/current/app-psql.html

Start and connect to a db: psql dbname

Connect with a specific user instead of the default: psql -d dbname -U username

Print current user: \conninfo - see https://stackoverflow.com/questions/39735141/how-to-check-connected-user-on-psql

List users: \du

Quit: \q

Connect to a db: \c dbname or \connect dbname. We can specify username, host, port etc.

Load .sql file: \i src/database-setup.sql

List databases: \l

List the relations (tables) of your currently connected database: \d

List tables (relations): \dt

List tables and indexes: \dti

Show table schema: \d tablename

pg_ctl

Docs: https://www.postgresql.org/docs/14/app-pg-ctl.html

pg_ctl -D /usr/local/var/postgres start
pg_ctl -D /usr/local/var/postgres stop

Upgrade to new version

Eg from 13 to 14.

As explained in brew info postgresql you can do brew postgresql-upgrade-database.

See https://medium.com/backticks-tildes/resetting-your-postgres-database-f43ba9f1c601

pgAdmin

At the home, click 'Add New Server', then fill the dialog with this data:

  • General tab
    • Name: Servidor Local
  • Connection tab
    • Host name/address: localhost
    • Port: 5432
    • Username: albertvilacalvo