mgmanalili/postgresql-cheatsheet.md

## postgresql-cheatsheet.md

      
    Raw
  

              postgresql-cheatsheet.md
            
          
    PostgreSQL Cheat Sheet

Quick Commands

Creating a DB and Setting its Ownership

To create a database, you need to first ensure that the database's role exists first. Role and User are synonymous
in PostgreSQL. Once you create the ROLE, you can create the Database and set the OWNER as the ROLE.
Create Role
CREATE ROLE <USERNAME> ENCRYPTED PASSWORD '<PASSWORD>' LOGIN;

Create Database
CREATE DATABASE <DATABASE-NAME> OWNER "<USERNAME";

Dropping a Database
DROP DATABASE "<DATABASE-NAME>";

Deleting a ROLE
DROP ROLE "<USERNAME>";

PSQL Commands

The following commands are for PSQL. You can access these and more by writing \?.
  \da[S]   [PATTERN]      list aggregates
  \db[+]   [PATTERN]      list tablespaces
  \dc[S+]  [PATTERN]      list conversions
  \dC[+]   [PATTERN]      list casts
  \dd[S]   [PATTERN]      show object descriptions not displayed elsewhere
  \ddp     [PATTERN]      list default privileges
  \dD[S+]  [PATTERN]      list domains
  \det[+]  [PATTERN]      list foreign tables
  \des[+]  [PATTERN]      list foreign servers
  \deu[+]  [PATTERN]      list user mappings
  \dew[+]  [PATTERN]      list foreign-data wrappers
  \df[antw][S+] [PATRN]  list [only agg/normal/trigger/window] functions
  \dF[+]   [PATTERN]      list text search configurations
  \dFd[+]  [PATTERN]      list text search dictionaries
  \dFp[+]  [PATTERN]      list text search parsers
  \dFt[+]  [PATTERN]      list text search templates
  \dg[+]   [PATTERN]      list roles
  \di[S+]  [PATTERN]      list indexes
  \dl                    list large objects, same as \lo_list
  \dL[S+]  [PATTERN]      list procedural languages
  \dm[S+]  [PATTERN]      list materialized views
  \dn[S+]  [PATTERN]      list schemas
  \do[S]   [PATTERN]      list operators
  \dO[S+]  [PATTERN]      list collations
  \dp      [PATTERN]      list table, view, and sequence access privileges
  \drds    [PATRN1 [PATRN2]] list per-database role settings
  \ds[S+]  [PATTERN]      list sequences
  \dt[S+]  [PATTERN]      list tables
  \dT[S+]  [PATTERN]      list data types
  \du[+]   [PATTERN]      list roles
  \dv[S+]  [PATTERN]      list views
  \dE[S+]  [PATTERN]      list foreign tables
  \dx[+]   [PATTERN]      list extensions
  \dy      [PATTERN]      list event triggers
  \l[+]    [PATTERN]      list databases
  \sf[+]   FUNCNAME        show a function's definition
  \z       [PATTERN]      same as \dp
Here are a couple of quick commands for common tasks that we do.
\l  - list all the databases in the server
\dt - shows all the tables in the selected database
\d+ <table-name> - describe the table, show it's columns, their types and their storage.
User Access Control

Create a User

Before creating a database, you need to create a user.
NOTE: you will need to be the postgres user to run this command.
createuser <new-user> --pwprompt

--pwprompt will require you to set the password for the user you're creating.
You can access the database created for the user (db name is the same name as the username), or you can access the postgres database as such:
psql postgres -U <new-user>

Connection to a Database

psql -h <host> -p <port> -u <database>
psql -h <host> -p <port> -U <username> -W <password> <database>

Databases

Create Database

CREATE DATABASE name
    [ [ WITH ] [ OWNER [=] user_name ]
               [ TEMPLATE [=] template ]
               [ ENCODING [=] encoding ]
               [ LC_COLLATE [=] lc_collate ]
               [ LC_CTYPE [=] lc_ctype ]
               [ TABLESPACE [=] tablespace_name ]
               [ ALLOW_CONNECTIONS [=] allowconn ]
               [ CONNECTION LIMIT [=] connlimit ]
               [ IS_TEMPLATE [=] istemplate ] ]
More Information: https://www.postgresql.org/docs/current/static/sql-createdatabase.html
You can create the database as the postgres user using the createdb <db-name> command.
Drop Database

DROP DATABASE [ IF EXISTS ] name
More Information: https://www.postgresql.org/docs/current/static/sql-dropdatabase.html
Alter Database

ALTER DATABASE name [ [ WITH ] option [ ... ] ]

where option can be:

    CONNECTION LIMIT connlimit

ALTER DATABASE name RENAME TO new_name

ALTER DATABASE name OWNER TO new_owner

ALTER DATABASE name SET TABLESPACE new_tablespace

ALTER DATABASE name SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER DATABASE name SET configuration_parameter FROM CURRENT
ALTER DATABASE name RESET configuration_parameter
ALTER DATABASE name RESET ALL
More Information: https://www.postgresql.org/docs/current/static/sql-alterdatabase.html
Tables

Create Table

There are a lot of details, so you're probably better off just going to the official docs to figure out how to do this.
https://www.postgresql.org/docs/current/static/sql-createtable.html
Drop Table

DROP TABLE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]
More Information: https://www.postgresql.org/docs/current/static/sql-droptable.html
Alter Table

There are a lot of details, so you're probably better off just going to the official docs to figure out how to do this.
https://www.postgresql.org/docs/9.6/static/sql-altertable.html
Basic CRUD Operations

Insert

[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
    { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
    [ ON CONFLICT [ conflict_target ] conflict_action ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

where conflict_target can be one of:

    ( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]
    ON CONSTRAINT constraint_name

and conflict_action is one of:

    DO NOTHING
    DO UPDATE SET { column_name = { expression | DEFAULT } |
                    ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) |
                    ( column_name [, ...] ) = ( sub-SELECT )
                  } [, ...]
              [ WHERE condition ]
More Information: https://www.postgresql.org/docs/current/static/sql-insert.html
Delete

[ WITH [ RECURSIVE ] with_query [, ...] ]
DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    [ USING using_list ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
More Information: https://www.postgresql.org/docs/current/static/sql-delete.html
Update

[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    SET { column_name = { expression | DEFAULT } |
          ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) |
          ( column_name [, ...] ) = ( sub-SELECT )
        } [, ...]
    [ FROM from_list ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
More Information: https://www.postgresql.org/docs/current/static/sql-update.html
Querying

[WITH with_queries] SELECT select_list FROM table_expression [sort_specification]
More Information: https://www.postgresql.org/docs/current/static/queries.html
Full Text Search

FUll Text Search allows put a set of text and search from it.
Let's do an initial setup of the database.
CREATE TABLE posts (
  id serial primary key,
  title VARCHAR(100),
  body TEXT
);


INSERT INTO posts VALUES
  (1, '13 Things that have changed since BJP Took Over Three Years Ago', 'Caste violence, unemployment and crimes against women have worsened. Poverty and GDP have improved.'),
  (2, 'The Hindu Mahasabha Rejected Statues Of Gandhi’s Assassin For Looking Too Chubby', 'You think people would not fat shame you just because you’re an inanimate object? Think again, guy.'),
  (3, 'U.S. Court Issues Summons To Indian PM Over Alleged Role In Deadly Gujarat Riots', 'Narendra Modi arrives in New York Friday for a five-day visit to the U.S.');
Basic Introduction

Querying for Records

SELECT id
  FROM posts
  WHERE to_tsvector('english', title) @@ to_tsquery('english', 'BJP');

#  id 
# ----
#   1
# (1 row)

Note: For to_tsvector(...) and to_tsquery(...), the first record is the language, and the second value is the field and value respectively.
You can also avoid this if default_text_search_config(string) is set (More information: https://www.postgresql.org/docs/current/static/runtime-config-client.html#GUC-DEFAULT-TEXT-SEARCH-CONFIG)
The last query can be done as:
SELECT id FROM posts WHERE to_tsvector(title) @@ to_tsquery('BJP');
Creating Indexes

To speed up the search, we need to create a GIN index on the column we will be querying on. To do this, we must do the following:
CREATE INDEX posts_title_idx ON posts USING GIN (to_tsvector('english', title));
To do this on multiple columns, we can do this:
CREATE INDEX posts_fulltxt_idx ON posts USING GIN(to_tsvector('english', title || ' ' || body));
Now, we can query based  like such:
SELECT * FROM posts WHERE title @@ to_tsquery('BJP');
Dealing with Unstructured Data

PostgreSQL can index JSON data, and query data from them. The following describes how to do this.
There are 3 types of data types that support Unstructured Data in PostgreSQL:

JSONB - In most cases
JSON - If you’re just processing logs, don’t often need to query, and use as more of an audit trail
hstore - Can work fine for text based key-value looks, but in general JSONB can still work great here

Here are some great resources: https://www.citusdata.com/blog/2016/07/14/choosing-nosql-hstore-json-jsonb/
JSONB

The B in JSONB is supposed to stand for 'better', but it really means that the data is Binary. Similar to BSON, JSONB removes spaces, and stores the data in a binary format into the database.
Creating Table with JSONB Column

CREATE TABLE product (id UUID, name VARCHAR(200), price NUMERIC(3, 2), attributes JSONB);
Inserting Data

Following from Previous example, this is how we insert data into JSONB column
INSERT INTO product VALUES 
( uuid_generate_v4(), 'Carrots', 1.45, '{ "color": "orange", "size": "medium", "is_cleaned": false, "type": "vegetable" }'),
( uuid_generate_v4(), 'Apples', 2.95, '{ "color": "red", "size": "large", "is_cleaned": false, "type": "fruit" }'),
( uuid_generate_v4(), 'Orange', 1.95, '{ "color": "orange", "size": "small", "is_cleaned": true, "type": "fruit" }'),
( uuid_generate_v4(), 'Cucumber', 3.55, '{ "color": "green", "size": "medium", "is_cleaned": false, "type": "vegetable" }'),
( uuid_generate_v4(), 'Lettuce', 1.35, '{ "color": "green", "size": "large", "type": "vegetable" }'),
( uuid_generate_v4(), 'Telephone', 99.99, '{ "speed": "1GHz", "memory": "4Gb", "storage": "128Gb" }')
Creating Index

Querying Data

Exact Match (Equality)
Find records with exact JSON match
test-db=# SELECT * FROM product WHERE product.attributes = '{ "color": "green", "size": "large", "type": "vegetable" }';
                  id                  |  name   | price |                        attributes                        
--------------------------------------+---------+-------+----------------------------------------------------------
 b1487e38-e384-411e-b51a-591898642116 | Lettuce |  1.35 | {"size": "large", "type": "vegetable", "color": "green"}
(1 row)
Containment
Finds rows with JSON objects that contain the queried JSON object.
Essentially, this equates to "find records where this query is a subset of the record."
test-db=# SELECT * FROM product WHERE product.attributes @> '{ "color": "green" }';
                  id                  |   name   | price |                                   attributes               
                    
--------------------------------------+----------+-------+------------------------------------------------------------
--------------------
 b9d83d67-325b-4bc9-bd05-f0867c36dc58 | Cucumber |  3.55 | {"size": "medium", "type": "vegetable", "color": "green", "
is_cleaned": false}
 b1487e38-e384-411e-b51a-591898642116 | Lettuce  |  1.35 | {"size": "large", "type": "vegetable", "color": "green"}
(2 rows)
Check if a Key Exists
Find rows where the JSON object has a given key:
test-db=# SELECT * FROM product WHERE attributes ? 'speed';
                  id                  |   name    | price |                       attributes                       
--------------------------------------+-----------+-------+--------------------------------------------------------
 a733b586-6ff4-49d4-9f3e-0bff9a42a4bc | Telephone |  3.99 | {"speed": "1GHz", "memory": "4Gb", "storage": "128Gb"}
(1 row)
Check if ANY key in a list of Keys exists
Find rows where the JSON object matches any key in the given set of keys:
test-db=# SELECT * FROM product WHERE attributes ?| array['speed', 'size'];
                  id                  |   name    | price |                                   attributes                                    
--------------------------------------+-----------+-------+---------------------------------------------------------------------------------
 6d4aa0e9-6ec0-4f73-9427-ecf3d3b164ec | Carrots   |  1.45 | {"size": "medium", "type": "vegetable", "color": "orange", "is_cleaned": false}
 de83c998-98fb-4bbb-bb4f-3fe2b00040a9 | Apples    |  2.95 | {"size": "large", "type": "fruit", "color": "red", "is_cleaned": false}
 b22d242f-1a0e-4c61-b219-aed9289df7bc | Orange    |  1.95 | {"size": "small", "type": "fruit", "color": "orange", "is_cleaned": true}
 b9d83d67-325b-4bc9-bd05-f0867c36dc58 | Cucumber  |  3.55 | {"size": "medium", "type": "vegetable", "color": "green", "is_cleaned": false}
 b1487e38-e384-411e-b51a-591898642116 | Lettuce   |  1.35 | {"size": "large", "type": "vegetable", "color": "green"}
 a733b586-6ff4-49d4-9f3e-0bff9a42a4bc | Telephone |  3.99 | {"speed": "1GHz", "memory": "4Gb", "storage": "128Gb"}
(6 rows)

Check if ALL key in a list of Keys exists
Find rows where the JSON object matches all key in the given set of keys:
test-db=# SELECT * FROM product WHERE attributes ?& array['speed', 'size'];
 id | name | price | attributes 
----+------+-------+------------
(0 rows)

test-db=# SELECT * FROM product WHERE attributes ?& array['speed', 'storage'];
                  id                  |   name    | price |                       attributes                       
--------------------------------------+-----------+-------+--------------------------------------------------------
 a733b586-6ff4-49d4-9f3e-0bff9a42a4bc | Telephone |  3.99 | {"speed": "1GHz", "memory": "4Gb", "storage": "128Gb"}
(1 row)
You can get more information here: http://schinckel.net/2014/05/25/querying-json-in-postgres/