cheat.sheets:psql
# psql
# PostgreSQL command-line client.
# Connect to database.
# It connects to localhost using default port 5432
# with default user as currently logged in user
psql database
# Connect to database on given server host running on given port
# with given username, without a password prompt
psql -h host -p port -U username database
# Connect to database; user will be prompted for password
psql -h host -p port -U username -W database
# Execute a single SQL query or PostgreSQL command
# on the given database (useful in shell scripts)
psql -c 'query' database
# Execute commands from a file on the given database
psql database -f file.sql
# See also:
# pg_dump, pg_restore, pg_top
# PostgreSQL cheat sheets at /psql/
# list of pages: /psql/:list
# search in pages: /psql/~keyword
cheat:psql
---
tags: [ database ]
---
# psql is the PostgreSQL terminal interface. The following commands were tested on version 9.5.
# Connection options:
# -U username (if not specified current OS user is used).
# -p port.
# -h server hostname/address.
# Connect to a specific database:
psql -U <username> -h <host> -d <database>
# Get databases on a server:
psql -U <username> -h <host> --list
# Execute sql query and save output to file:
psql -U <username> -d <database> -c 'select * from tableName;' -o <outfile>
# Execute query and get tabular html output:
psql -U <username> -d <database> -H -c 'select * from tableName;'
# Execute query and save resulting rows to csv file:
# (if column names in the first row are not needed, remove the word 'header')
psql -U <username> -d <database> -c 'copy (select * from tableName) to stdout with csv header;' -o <outfile>
# Read commands from file:
psql -f <outfile>
# Restore databases from file:
psql -f <outfile> <username>
tldr:psql
# psql
# PostgreSQL command-line client.
# More information: <https://www.postgresql.org/docs/current/app-psql.html>.
# Connect to the database. By default, it connects to the local socket using port 5432 with the currently logged in user:
psql database
# Connect to the database on given server host running on given port with given username, without a password prompt:
psql -h host -p port -U username database
# Connect to the database; user will be prompted for password:
psql -h host -p port -U username -W database
# Execute a single SQL query or PostgreSQL command on the given database (useful in shell scripts):
psql -c 'query' database
# Execute commands from a file on the given database:
psql database -f file.sql
$
cheat.sh