Tag: PostgreSql Cheat Sheet

PostgreSQL Cheat Sheet

PostgreSQL Cheat Sheet tries to provide a basic reference for beginner and advanced developers, lower the entry barrier for newcomers, and help veterans refresh the old tricks. PostgreSQL also is known as Postgres, is a free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance.

PostgreSQL: Configuration Cheat Sheet

Service management commands:

sudo service postgresql stop
sudo service postgresql start
sudo service postgresql restart

Changing verbosity & querying Postgres log:
1) First edit the config file, set a decent verbosity, save and restart postgres:

sudo vim /etc/postgresql/9.3/main/postgresql.conf

# Uncomment/Change inside:
log_min_messages = debug5
log_min_error_statement = debug5
log_min_duration_statement = -1

sudo service postgresql restart

Now you will get tons of details of every statement, error, and even background tasks like VACUUMs

tail -f /var/log/postgresql/postgresql-9.3-main.log

How to add user who executed a PG statement to log (editing postgresql.conf):

log_line_prefix = '%t %u %d %a '

Managing Triggers

Create or modify a trigger

CREATE OR MODIFY TRIGGER trigger_name
WHEN EVENT
ON table_name TRIGGER_TYPE
EXECUTE stored_procedure;

Create a trigger invoked before a new row is inserted into the person table

WHEN
  BEFORE – invoke before the event occurs
  AFTER – invoke after the event occurs
EVENT
  INSERT – invoke for INSERT
  UPDATE – invoke for UPDATE
  DELETE – invoke for DELETE
TRIGGER_TYPE
  FOR EACH ROW
  FOR EACH STATEMENT
CREATE TRIGGER before_insert_person
BEFORE INSERT
ON person FOR EACH ROW
EXECUTE stored_procedure;

Delete a specific trigger

DROP TRIGGER trigger_name

SQL Aggregate: Functuions Cheat Sheet

AVG returns the average of a list

COUNT returns the number of elements of a list

SUM returns the total of a list

MAX returns the maximum value in a list

MIN returns the minimum value in a list

PostgreSQL: Commands Cheat Sheet

Login to postgresql

psql -U postgres

Some interesting flags (to see all, use -h or --help depending on your psql version):

  • -E: will describe the underlaying queries of the \ commands (cool for learning!)
  • -l: psql will list all databases and then exit (useful if the user you connect with doesn’t has a default database, like at AWS RDS)

Most \d commands support additional param of __schema__.name__ and accept wildcards like *.*

  • \q: Quit/Exit
  • \c __database__: Connect to a database
  • \d __table__: Show table definition (columns, etc.) including triggers
  • \d+ __table__: More detailed table definition including description and physical disk size
  • \l: List databases
  • \dy: List events
  • \df: List functions
  • \di: List indexes
  • \dn: List schemas
  • \dt *.*: List tables from all schemas (if *.* is omitted will only show SEARCH_PATH ones)
  • \dT+: List all data types
  • \dv: List views
  • \dx: List all extensions installed
  • \df+ __function__ : Show function SQL code.
  • \x: Pretty-format query results instead of the not-so-useful ASCII tables
  • \copy (SELECT * FROM __table_name__) TO 'file_path_and_name.csv' WITH CSV: Export a table as CSV
  • \des+: List all foreign servers
  • \dE[S+]: List all foreign tables

User Related:

  • \du: List users
  • \du __username__: List a username if present.
  • create role __test1__: Create a role with an existing username.
  • create role __test2__ noinherit login password __passsword__;: Create a role with username and password.
  • set role __test__;: Change role for current session to __test__.
  • grant __test2__ to __test1__;: Allow __test1__ to set its role as __test2__.
  • \deu+: List all user mapping on server

Managing Indexes

Create an index on c1 and c2 of the table t

CREATE INDEX idx_name
ON t(c1,c2);

Create a unique index on c3, c4 of the table t

CREATE UNIQUE INDEX idx_name
ON t(c3,c4);

Drop an index

DROP INDEX idx_name;

Modifying Data

Insert one row into a table

INSERT INTO t(column_list)
VALUES(value_list);

Insert multiple rows into a table

INSERT INTO t(column_list)
VALUES (value_list),
(value_list), ….;

Insert rows from t2 into t1

INSERT INTO t1(column_list)
SELECT column_list
FROM t2;

Update values in the column c1, c2 that match
the condition

UPDATE t
SET c1 = new_value,
c2 = new_value
WHERE condition;

Delete all data in a table

DELETE FROM t;

Delete subset of rows in a table

DELETE FROM t
WHERE condition;

Using SQL Constraints

Set c1 and c2 as a primary key

CREATE TABLE t(
c1 INT, c2 INT, c3 VARCHAR,
PRIMARY KEY (c1,c2)
);

Set c2 column as a foreign key

CREATE TABLE t1(
c1 INT PRIMARY KEY,
c2 INT,
FOREIGN KEY (c2) REFERENCES t2(c2)
);

Make the values in c1 and c2 unique

CREATE TABLE t(
c1 INT, c1 INT,
UNIQUE(c2,c3)
);

Ensure c1 > 0 and values in c1 >= c2

CREATE TABLE t(
c1 INT, c2 INT,
CHECK(c1> 0 AND c1 >= c2)
);

Set values in c2 column not NULL

CREATE TABLE t(
c1 INT PRIMARY KEY,
c2 VARCHAR NOT NULL
);

Managing Tables

Create a new table with three columns

CREATE TABLE t (
id INT PRIMARY KEY,
name VARCHAR NOT NULL,
price INT DEFAULT 0
);

Delete the table from the database

DROP TABLE t ;

Add a new column to the table

ALTER TABLE t ADD column;

Drop column c from the table

ALTER TABLE t DROP COLUMN c ;

Add a constraint

ALTER TABLE t ADD constraint;

Drop a constraint

ALTER TABLE t DROP constraint;

Rename a table from t1 to t2

ALTER TABLE t1 RENAME TO t2

Rename column c1 to c2

ALTER TABLE t1 RENAME c1 TO c2 ;

Remove all data in a table

TRUNCATE TABLE t;

Using SQL Operators

Combine rows from two queries

SELECT c1, c2 FROM t1
UNION [ALL]
SELECT c1, c2 FROM t2;

Return the intersection of two queries

SELECT c1, c2 FROM t1
INTERSECT
SELECT c1, c2 FROM t2;

Subtract a result set from another result set

SELECT c1, c2 FROM t1
MINUS
SELECT c1, c2 FROM t2;

Query rows using pattern matching %, _

SELECT c1, c2 FROM t1
WHERE c1 [NOT] LIKE pattern;

Query rows in a list

SELECT c1, c2 FROM t
WHERE c1 [NOT] IN value_list;

Query rows between two values

SELECT c1, c2 FROM t
WHERE c1 BETWEEN low AND high;

Check if values in a table is NULL or not

SELECT c1, c2 FROM t
WHERE c1 IS [NOT] NULL;

Managing Views

Create a new view that consists of c1 and c2

CREATE VIEW v(c1,c2)
AS
SELECT c1, c2
FROM t;

Create a new view with check option

CREATE VIEW v(c1,c2)
AS
SELECT c1, c2
FROM t;
WITH [CASCADED | LOCAL] CHECK OPTION;

Create a recursive view

CREATE RECURSIVE VIEW v
AS
select-statement -- anchor part
UNION [ALL]
select-statement; -- recursive part

Create a temporary view

CREATE TEMPORARY VIEW v
AS
SELECT c1, c2
FROM t;

Delete a view

DROP VIEW view_name

Querying data from multiple tables

Inner join t1 and t2

SELECT c1, c2
FROM t1
INNER JOIN t2 ON condition;

Left join t1 and t1

SELECT c1, c2
FROM t1
LEFT JOIN t2 ON condition;

Perform full outer join

SELECT c1, c2
FROM t1
FULL OUTER JOIN t2 ON condition;

Produce a Cartesian product of rows in tables

SELECT c1, c2
FROM t1
CROSS JOIN t2;

Join t1 to itself using INNER JOIN clause

SELECT c1, c2
FROM t1 A
INNER JOIN t2 B ON condition;

Querying Data From Table

Query data in columns c1, c2 from a table

SELECT c1, c2 FROM t;

Query all rows and columns from a table

SELECT * FROM t;

Query data and filter rows with a condition

SELECT c1, c2 FROM t
WHERE condition;

Query distinct rows from a table

SELECT DISTINCT c1 FROM t
WHERE condition;

Group rows using an aggregate function

SELECT c1, aggregate(c2)
FROM t
GROUP BY c1;

Filter groups using HAVING clause

SELECT c1, aggregate(c2)
FROM t
GROUP BY c1
HAVING condition;

Table of contents

Regex Cheat SheetCrontab Cheat SheetDB2 Cheat SheetHTML Cheat SheetDocker Cheat SheetMariaDB Cheat SheetSvelte Cheat SheetDatabase Cheat SheetjQuery Mobile Cheat SheetEmberJS Cheat SheetBabylonJS Cheat SheetLinux Command Line Cheat SheetPython Cheat SheetMongoDB Cheat SheetSQLite Cheat SheetNPM Cheat SheetAmethyst Cheat SheetLaravel 5 Cheat SheetVS Code Cheat Sheet - WindowConda Cheat Sheet