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

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;

PostgreSQL: Configuration Cheat Sheet
Managing Triggers
SQL Aggregate: Functuions Cheat Sheet
PostgreSQL: Commands Cheat Sheet
Managing Indexes
Modifying Data
Using SQL Constraints
Managing Tables
Using SQL Operators
Managing Views
Querying data from multiple tables
Querying Data From Table

Table of contents