PostgeSQL Cheatsheet

psql_cheatsheet.md

PostgreSQL Cheatsheet

PSQL main commands

Opening PostgreSQL console:

psql -h <hostname> -U <user> <db> # db is optional

Working with pgsql console

\?                - Help
\? <keyword>      - Help for SQL keyword
\du               - List roles
\l                - List databases
\c <db_name>      - Connect to database
\dt               - List tables (with selected DB)
\d <table_name>   - List columns in table
\d+ <table_name>  - Describe table
\q                - Exiting from console

Dumping postgres database

pg_dump db_name > db_name_dump.sql # Dump database_name
pg_dumpall > full_dump.sql         # Dump all databases 

# Example on dumping & restoring postgresql database
# Don't forget to 'CREATE DATABASE newdb;'
pg_dump -Ftar -fdb_name.tar db_name
pg_restore -d newdb db_name_dump.sql

Running a SQL script (command line)

psql -f script.sql db_name

Basic SQL

-- Get all columns and rows from a table
SELECT * FROM table_name;

-- Add a new row
INSERT INTO table_name (column1,column2) VALUES (1, 'one');

-- Update a row
UPDATE table_name SET foo = 'bar' WHERE id = 1;

-- Delete a row
DELETE FROM table_name WHERE id = 1;

Some usefull sql commands syntax

-- Comments
-- This is a comment to end of line
/* C-like comment, possibly
multiple lines */

-- Changing password for postgresql user *username* 
ALTER USER username WITH PASSWORD 'username';

-- Explain query
EXPLAIN ANALYZE SELECT "products".* FROM "products"
WHERE (lower(name) like '%pre%' or lower(description) like '%pre%')
ORDER BY name ASC LIMIT 100 OFFSET 100;
                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=29995.24..29995.49 rows=100 width=2190) (actual time=1017.821..1017.832 rows=100 loops=1)
   ->  Sort  (cost=29994.99..30126.81 rows=52728 width=2190) (actual time=1017.809..1017.819 rows=200 loops=1)
         Sort Key: name
         Sort Method: top-N heapsort  Memory: 78kB
         ->  Seq Scan on products  (cost=0.00..27716.12 rows=52728 width=2190) (actual time=0.955..973.433 rows=20569 loops=1)
               Filter: ((lower((name)::text) ~~ '%pre%'::text) OR (lower(description) ~~ '%pre%'::text))
               Rows Removed by Filter: 651987
 Total runtime: 1018.692 ms
(8 rows)

-- Create database
CREATE DATABASE db_name;

-- Create table with auto numbering integer id
CREATE TABLE table_name (
 id serial PRIMARY KEY,
 name varchar(50) UNIQUE NOT NULL,
 dateCreated timestamp DEFAULT current_timestamp
);

-- Add a primary key
ALTER TABLE table_name ADD PRIMARY KEY (id);

-- Create an INDEX
CREATE UNIQUE INDEX index_name ON table_name (column_name1, column_name2, ...);

-- Search using a regular expression
SELECT column FROM table_name WHERE column ~ 'foo.*';

-- The first N records
SELECT columns FROM table_name LIMIT 10;

-- Pagination
SELECT columns FROM table_name LIMIT 10 OFFSET 30;

-- Prepared Statements
PREPARE preparedInsert (int, varchar) AS
  INSERT INTO tableName (intColumn, charColumn) VALUES ($1, $2);
EXECUTE preparedInsert (1,'a');
EXECUTE preparedInsert (2,'b');
DEALLOCATE preparedInsert;

-- Create a Function
CREATE OR REPLACE FUNCTION month (timestamp) RETURNS integer
 AS 'SELECT date_part(''month'', $1)::integer;'
LANGUAGE 'sql';

-- Table Maintenance
VACUUM ANALYZE table_name;

-- Reindex a database, table or index
REINDEX DATABASE db_name;

-- Import from a file
COPY dest_table FROM '/tmp/somefile';

-- Show all runtime parameters
SHOW ALL;

-- Grant all permissions to a user
GRANT ALL PRIVILEGES ON table_name TO user_name;

-- Perform a transaction
BEGIN TRANSACTION
 UPDATE accounts SET balance += 50 WHERE id = 1;
COMMIT;