Cheat Sheet Postgresql

Posted : admin On 1/29/2022

title: PostgreSQLicon: icon-postgresbackground: bg-blue-800tags:

  • nullcategories:
  • Databasedate: 2021-01-11 14:19:24intro: The PostgreSQL cheat sheet provides you with the common PostgreSQL commands and statements.

Postgres Cheatsheet created by your friends at Timescale.

PostgreSQL cheat sheet Author: Administrator Created Date: 5/11/2006 10:02:16 PM. PostgreSQL is an open-source, powerful and advanced version of SQL that supports different functions of SQL including, foreign keys, subqueries, functions, and user-defined types. In this quick reference cheat sheet, we will show Oracle SQL and PostgreSQL commands with examples. Python for Data Science Cheat Sheets. Python is one of the most widely used programming languages in the data science field.Python has many packages and libraries that are specifically tailored for certain functions, including pandas, NumPy, scikit-learn, Matplotlib, and SciPy.The most appealing quality of Python is that anyone who wants to learn it, even beginners, can do so quickly and easily.

Sample Postgres Db

Getting started {.cols-3}

Getting started

Switch and connect

List all databases

Connect to the database named postgres

Disconnect

Regex cheat sheet postgresql

psql commands {.col-span-2}

OptionExampleDescription
[-d] <database>psql -d mydbConnecting to database
-Upsql -U john mydbConnecting as a specific user
-h-ppsql -h localhost -p 5432 mydbConnecting to a host/port
-U-h-p-dpsql -U admin -h 192.168.1.5 -p 2506 -d mydbConnect remote PostgreSQL
-Wpsql -W mydbForce password
-cpsql -c 'c postgres' -c 'dt'Execute a SQL query or command
-Hpsql -c 'l+' -H postgres > database.htmlGenerate HTML report
-lpsql -lList all databases
-fpsql mydb -f file.sqlExecute commands from a file
-Vpsql -VPrint the psql version
{.show-header}

Getting help

--
hHelp on syntax of SQL commands
h DELETEDELETE SQL statement syntax
?List of PostgreSQL command

Run in PostgreSQL console

Regex Cheat Sheet Postgresql

Working {.cols-3}

Recon

Show version

Show system status

Show environmental variables

List users

Show current user

Show current user's permissions

Show current database

Show all tables in database

List functions

Databases

List databases

Connect to database

Show current database

Tables

List tables, in current db

List tables, globally

List table schema

Create table, with an auto-incrementing primary key

Permissions

Become the postgres user, if you have permission errors

Grant all permissions on database

Grant connection permissions on database

Grant permissions on schema

Grant permissions to functions

Grant permissions to select, update, insert, delete, on a all tables

Grant permissions, on a table

Grant permissions, to select, on a table

Columns

Update column

Delete column

Update column to be an auto-incrementing primary key

Insert into a table, with an auto-incrementing primary key

Data

[Select](http://www.postgresql.org/docs/current/static/sql-select.html] all data

Read one row of data

Search for data

Insert data

Update data

Delete all data

Sql injection cheat sheet postgresql

Delete specific data

Users

List roles

Alter user password

Schema

List schemas

Commands {.cols-3}

Tables

--
d <table>Describe table
d+ <table>Describe table with details
dtList tables from current schema
dt *.*List tables from all schemas
dt <schema>.*List tables for a schema
dpList table access privileges
det[+]List foreign tables

Query buffer

--
e [FILE]Edit the query buffer (or file)
ef [FUNC]Edit function definition
pShow the contents
rReset (clear) the query buffer
s [FILE]Display history or save it to file
w FILEWrite query buffer to file

Informational {.row-span-4}

--
l[+]List all databases
dn[S+]List schemas
di[S+]List indexes
du[+]List roles
ds[S+]List sequences
df[antw][S+]List functions
deu[+]List user mappings
dv[S+]List views
dlList large objects
dT[S+]List data types
da[S]List aggregates
db[+]List tablespaces
dc[S+]List conversions
dC[+]List casts
ddpList default privileges
dd[S]Show object descriptions
dD[S+]List domains
des[+]List foreign servers
dew[+]List foreign-data wrappers
dF[+]List text search configurations
dFd[+]List text search dictionaries
dFp[+]List text search parsers
dFt[+]List text search templates
dL[S+]List procedural languages
do[S]List operators
dO[S+]List collations
drdsList per-database role settings
dx[+]List extensions

S: show system objects, +: additional detail

Connection

--
c [DBNAME]Connect to new database
encoding [ENCODING]Show or set client encoding
password [USER]Change the password
conninfoDisplay information

Formatting

--
aToggle between unaligned and aligned
C [STRING]Set table title, or unset if none
f [STRING]Show or set field separator for unaligned
HToggle HTML output mode
`t [onoff]`
T [STRING]Set or unset HTML <table> tag attributes
`x [onoff]`

Input/Output

--
copy ...Import/export table
See also:copy
echo [STRING]Print string
i FILEExecute file
o [FILE]Export all results to file
qecho [STRING]String to output stream

Variables

--
prompt [TEXT] NAMESet variable
set [NAME [VALUE]]Set variable (or list all if no parameters)
unset NAMEDelete variable

Misc

--
cd [DIR]Change the directory
`timing [onoff]`
! [COMMAND]Execute in shell
! ls -lList all in shell

Large Objects

  • lo_export LOBOID FILE
  • lo_import FILE [COMMENT]
  • lo_list
  • lo_unlink LOBOID

Miscellaneous {.cols-3}

Backup

Use pg_dumpall to backup all databases

Use pg_dump to backup a database

  • -a Dump only the data, not the schema
  • -s Dump only the schema, no data
  • -c Drop database before recreating
  • -C Create database before restoring
  • -t Dump the named table(s) only
  • -F Format (c: custom, d: directory, t: tar){.style-none}

Use pg_dump -? to get the full list of options

Restore

Restore a database with psql

Restore a database with pg_restore

  • -U Specify a database user
  • -c Drop database before recreating
  • -C Create database before restoring
  • -e Exit if an error has encountered
  • -F Format (c: custom, d: directory, t: tar, p: plain text sql(default)){.style-none}

Use pg_restore -? to get the full list of options

Remote access

Get location of postgresql.conf

Append to postgresql.conf

Append to pg_hba.conf (Same location as postgresql.conf)

Restart PostgreSQL server

Import/Export CSV

Export table into CSV file

Import CSV file into table

See also: Copy

See Also

  • Posgres-cheatsheet(gist.github.com)

A quick reference to PostgreSQL.

Created on: 2019-01-22

Tag: cheat_sheet

To check postgresql version:

source: https://stackoverflow.com/a/13733856

To create a super suer with passowrd:

To create a database on Postgresql:

To remove an extension:

To connect to remote psql host 1:

To backup a psql database without typing password 2:

To create database from sql file:

to backup a single table:

to export a sql statement output to a csv file with header:

to export a sql statement output to a geojson file:

Postgresql
to export a table to a csv file::

COPY current_relation_members TO '/var/lib/postgresql/csv/current_relation_members.CSV' DELIMITER ',' CSV HEADER;

The following are the Meta-Commands for psql command. This can be used with either with -c flag of the psql command like:

Or within the interactive prompt that comes after sudo -u postgres psql command. There are many Meta-Commands and there are available in the psql document. Bellow are a few useful most useful for me:

  • connect to a database: c $DATEBASE_NAME

  • list all users: du

  • list all database: l or list

  • list all table: dt

To allow remote connections to PostgreSQL database server, first check listen_addresses in postgresql.conf:

Postgresql Cheat Sheet 9.5

The output would show something like this:

Now let's edit the postgresql.conf file in our editor of choice:

Search for listen_addresses, and set it to '*' for all addresses or comma separated IP address, save the file and exit. Now we need to change the pg_hba.conf file so let's open it with our editor:

More details here PostgreSQL: Documentation: Connections and Authentication.

Now add the following to the end of file:

Now save the file and exit. Now restart PostgreSQL:

OR:

source: https://bosnadev.com/2015/12/15/allow-remote-connections-postgresql-database-server/

Now connect to the remote server:

Cheat Sheet Postgresql Download

source: https://askubuntu.com/a/423181

1
2
3