postgresql Cheat sheet

Connect: psql -U username -d database_name -h localhost -p port_number

Quit: \q

psql --version

FROM > WHERE > GROUP BY > HAVING > SELECT > DISTINCT > ORDER BY > LIMIT

TINYINT

SMALLINT

MEDIUMINT

INT

BIGINT

FLOAT

DOUBLE

DECIMAL(M,D)

CHAR(M)

VARCHAR(M)

TEXT(M)

BLOB

DATE

TIME

DATETIME

YEAR

TIMESTAMP

BOOLEAN

ENUM('value1', 'value2', ...)

SET('option1', 'option2', ...)

Array: datatype []

UUID

"M" refers to the total number of digits you want to store

"N" is the number of digits to be stored after the decimal point

CREATE DATABASE database_name [CHARACTER SET utf8];

\l

\c database_name

DROP DATABASE database_name;

ALTER DATABASE database_name CHARACTER SET utf8;

CREATE TABLE [IF NOT EXISTS] table_name (column1 datatype COMMENT 'string', ...);

CREATE TEMPORARY TABLE table;

\dt

\d table_name

DROP TABLE [IF EXISTS] table_name;

ALTER TABLE table RENAME TO new_table_name

ALTER TABLE table RENAME col1 TO col2

ALTER TABLE table ADD column_name datatype;

ALTER TABLE table ADD column_name datatype FIRST;

ALTER TABLE table ADD column_name datatype AFTER another_field;

ALTER TABLE table DROP column_name;

ALTER TABLE table CHANGE field1 new_field_name datatype FIRST

ALTER TABLE table CHANGE field1 new_field_name datatype NOT NULL ..

ALTER TABLE table ALTER field1 SET DEFAULT ...

ALTER TABLE table ALTER field1 DROP DEFAULT;

ALTER TABLE table MODIFY field1 datatype NOT NULL ...

ALTER TABLE table MODIFY field1 type1 FIRST;

ALTER TABLE table MODIFY field1 type1 AFTER another_field;

CREATE TABLE table_name ( column1 datatype constraint, ...);

CREATE TABLE table_name ( column_name datatype [ UNSIGNED ] [ NULL | NOT NULL ] [DEFAULT default_value] [SERIAL], ...);

CREATE TABLE table_name ( column_name INT SERIAL, ..., PRIMARY KEY (column_name) );

CREATE TABLE table_name ( column_name INT, FOREIGN KEY (column_name) REFERENCES other_table(other_column) );

CREATE TABLE table_name ( column1 INT, column2 INT, UNIQUE KEY `key_name` (column1, column2) );

CREATE TABLE table_name ( column_name INT, CHECK (column_name > 10) );

CREATE TABLE table_name ( City varchar(255) DEFAULT 'Danang' );

CREATE TABLE table_name ( column_name datatype UNSIGNED, );

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...), (value1, value2, ...);

UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

DELETE FROM table_name;

DELETE FROM table_name WHERE condition;

DELETE FROM table1, table2 WHERE table1.id1 = table2.id2 AND condition

TRUNCATE table_name;

SELECT * FROM table_name;

SELECT column_name AS alias_name FROM table_name;

SELECT c.OrderID FROM Customers AS c;

SELECT * FROM table1, table2;

SELECT column1, column2, ... FROM table_name;

SELECT column1, column2, ... FROM table_name WHERE condition;

SELECT ... FROM ... WHERE condition ORDER BY column1, column2;

SELECT ... FROM ... WHERE condition ORDER BY column1 [ASC|DESC], column2 [ASC|DESC];

SELECT ... FROM ... WHERE condition LIMIT 10;

SELECT ... FROM ... WHERE condition LIMIT row_count OFFSET offset

SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column;

SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column AND table1.column2 = table2.column2;

SELECT * FROM table1 LEFT JOIN table2 ON table1.column = table2.column;

SELECT * FROM table1 RIGHT JOIN table2 ON table1.column = table2.column;

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

WHERE column_name = value;

WHERE column_name <> value;

WHERE column_name != value;

WHERE column_name > value;

WHERE column_name < value;

WHERE column_name >= value;

WHERE column_name <= value;

WHERE column_name BETWEEN value1 AND value2;

WHERE column_name IN (value1, value2, ...);

WHERE column_name NOT IN (value1, value2, ...);

WHERE column_name IS NULL;

WHERE column_name IS NOT NULL;

WHERE column_name LIKE 'd%';

WHERE column_name LIKE '%d%';

WHERE column_name NOT LIKE 'd%';

WHERE column_name1 = value AND column_name2 = value;

WHERE column_name1 = value OR column_name2 = value;

SELECT COUNT(id) FROM table_name;

SELECT MAX(age) FROM table_name;

SELECT MIN(age) FROM table_name;

SELECT SUM(age) FROM table_name;

SELECT AVG(age) FROM table_name;

SELECT UCASE(first_name), LCASE(first_name) FROM table_name;

SELECT DISTINCT field1 FROM ...;

SELECT DISTINCT field1, field2 FROM;

SELECT CONCAT(first_name, ' ', last_name) AS 'Name', dept FROM users;

SELECT DISTINCT location FROM users;

SELECT NOW();

SELECT CURDATE();

SELECT CURTIME();

SELECT YEAR(NOW());

SELECT TIMESTAMPDIFF(MINUTE, '2023-01-01 12:00:00', NOW());

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');

\df

SELECT age, COUNT(age) FROM users GROUP BY age;

SELECT age, COUNT(age) FROM users WHERE age > 20 GROUP BY age;

SELECT age, COUNT(age) FROM users GROUP BY age HAVING count(age) >=2;

SELECT * FROM table1 WHERE column_name IN (SELECT column_name FROM table2 WHERE condition);

SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;

CREATE INDEX index_name ON table_name (column_name);

CREATE TABLE t1 (col1 VARCHAR(10), col2 VARCHAR(20), INDEX index_name (col1, col2)

CREATE TABLE t1 (col1 VARCHAR(10), col2 VARCHAR(20), INDEX index_name (col1, col2(10))

CREATE INDEX index_name ON table_name (column1, column2, ...);

CREATE UNIQUE INDEX index_name ON table_name (column_name);

CREATE UNIQUE INDEX index_name ON table_name (column1, column2, ...);

DROP INDEX index_name ON table_name;

\di

select * from Email where id > 10 and id < 15 FOR UPDATE;

select * from Email where id > 10 and id < 15 FOR SHARE;

LOCK TABLES table_name IN SHARE MODE;

LOCK TABLES table_name IN SHARE UPDATE EXCLUSIVE MODE;

LOCK TABLES table_name IN SHARE ROW EXCLUSIVEMODE;

LOCK TABLES table_name IN EXCLUSIVE MODE;

LOCK TABLES table_name IN ACCESS EXCLUSIVE MODE;

LOCK TABLES table_name IN SHARE ROW EXCLUSIVEMODE;

LOCK TABLES table_name IN ACCESS SHARE MODE;

LOCK TABLES table_name IN ROW SHARE MODE;

LOCK TABLES table_name IN ROW EXCLUSIVE MODE;

Start Transaction: BEGIN

Start Transaction: BEGIN TRANSACTION

Commit Transaction: COMMIT;

Commit Transaction: END TRANSACTION;

ROLLBACK;

SET AUTOCOMMIT = ON|OFF;

pg_dump -U username -h localhost -p port_number -d database_name -F c -f backup_file.dump

pg_restore -U username -h localhost -p port_number -d database_name -F c -c backup_file.dump

CREATE USER new_user WITH PASSWORD 'your_password';

List Users: \du

ALTER USER your_user WITH PASSWORD 'new_password';

DROP USER user_name;

GRANT permission_type ON DATABASE db_name to new_user;