Postgres Cheatsheet

commands

psql

psql -U user -d db -h 127.0.0.1 -p 5432

SET CLIENT_ENCODING TO 'utf8';

  • \q: quit more and return back psql
  • \l：List of databases
  • \c: connect to database
  • \d: list all database objects
  • \d tbname: view table structure
  • \d+ tbname: view table structure
  • \dt: list all tables, such as \dt public.*
  • \ds: list all sequences
  • \i xxx.sql: excute sql from file

psql import sql file

psql -h 127.0.0.1 -p 5432 -d dbname -U user -f sqlfilewithfullpath

psql uxerp < sqlfilewithfullpath

pg_dump

pg_dump -d database_name -t table_name -U username > dumpfile_with_fullpath.sql

pg_ctl

start PostgreSQL servie: pg_ctl -D “D:\data\pg11” start

stop PostgreSQL service: pg_ctl -D “D:\data\pg11” stop

view tcp port of pg service

netstat -aon|findstr “5432”

tasklist|findstr “pid”

ddl

foreign key

1

1
fk_id INTEGER REFERENCES main_tb(id),

2

1
2
fk_id INTEGER,
FOREIGN KEY (fk_id) REFERENCES main_tb (id)

sequence

When creating a new table, the sequence can be created through the SERIAL pseudo-type as follows:

1
2
3
CREATE TABLE table_name(
    id SERIAL
);