Skip to content

Instantly share code, notes, and snippets.

@sadfuzzy

sadfuzzy/psql_cheatsheet.md

Last active September 26, 2015 14:20
Show Gist options
  • Save sadfuzzy/11168496 to your computer and use it in GitHub Desktop.
Save sadfuzzy/11168496 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. sadfuzzy revised this gist Apr 22, 2014. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion psql_cheatsheet.md
    View file
    Original file line number Diff line number Diff line change
    @@ -3,7 +3,7 @@
    ### Opening PostgreSQL console:

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

    ### Working with pgsql console
  2. sadfuzzy revised this gist Apr 22, 2014. 1 changed file with 5 additions and 4 deletions.
    9 changes: 5 additions & 4 deletions psql_cheatsheet.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,11 +1,12 @@
    # PostgreSQL Cheatsheet
    1. Opening PostgreSQL console:
    ## PSQL main commands
    ### Opening PostgreSQL console:

    ```sh
    psql -h <hostname> -U <user> <dbname> # dbname is optional
    ```

    2. Working with pgsql console
    ### Working with pgsql console

    ```
    \? - Help
    @@ -19,7 +20,7 @@ psql -h <hostname> -U <user> <dbname> # dbname is optional
    \q - Exiting from console
    ```

    3. Dumping postgres database
    ### Dumping postgres database

    ```sh
    pg_dump db_name > db_name_dump.sql # Dump database_name
    @@ -31,7 +32,7 @@ pg_dump -Ftar -fdb_name.tar db_name
    pg_restore -d newdb db_name_dump.sql
    ```

    4. Running a SQL script (command line)
    ### Running a SQL script (command line)

    ```sh
    psql -f script.sql db_name
  3. sadfuzzy revised this gist Apr 22, 2014. 1 changed file with 60 additions and 40 deletions.
    100 changes: 60 additions & 40 deletions psql_cheatsheet.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,10 +1,12 @@
    # PostgreSQL Cheatsheet
    1. Opening PostgreSQL console:

    ```sh
    psql -h <hostname> -U <user> <dbname>
    psql -h <hostname> -U <user> <dbname> # dbname is optional
    ```
    dbname is optional

    2. Working with pgsql console

    ```
    \? - Help
    \? <keyword> - Help for SQL keyword
    @@ -18,34 +20,50 @@ dbname is optional
    ```

    3. Dumping postgres database
    pg_dump and pg_restore pair

    ```sh
    pg_dump therrr-dev > dump.sql
    pg_dumpall > dump.sql
    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 -fcp_dev.tar cp_dev
    pg_restore -d newdb db.dump
    pg_dump -Ftar -fdb_name.tar db_name
    pg_restore -d newdb db_name_dump.sql
    ```

    Running a SQL script (command line)
    4. Running a SQL script (command line)

    ```sh
    psql -f script.sql databaseName
    psql -f script.sql db_name
    ```

    ## Basic SQL
    ```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
    ## Some usefull sql commands syntax
    ```SQL
    -- comment to end of line
    -- 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 ANALYZE
    ```
    Except ordinary ANALYZE - for showing exact query benchmarking, i.e
    ```SQL
    explain analyze SELECT "products".* FROM "products"

    -- 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
    @@ -61,57 +79,59 @@ ORDER BY name ASC LIMIT 100 OFFSET 100;
    (8 rows)

    -- Create database
    CREATE DATABASE dbName;
    CREATE TABLE (with auto numbering integer id)
    CREATE TABLE tableName (
    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 tableName ADD PRIMARY KEY (id);
    Create an INDEX
    CREATE UNIQUE INDEX indexName ON tableName (columnNames);
    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 WHERE column ~ 'foo.*';
    SELECT column FROM table_name WHERE column ~ 'foo.*';

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

    -- Pagination
    SELECT cols FROM table LIMIT 10 OFFSET 30;
    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;
    VACUUM ANALYZE table_name;

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

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

    -- Show all runtime parameters
    SHOW ALL;

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

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

    -- Basic SQL
    -- Get all columns and rows from a table
    SELECT * FROM table;
    -- Add a new row
    INSERT INTO table (column1,column2)
    VALUES (1, 'one');
    -- Update a row
    UPDATE table SET foo = 'bar' WHERE id = 1;
    -- Delete a row
    DELETE FROM table WHERE id = 1;
    ```
  4. sadfuzzy revised this gist Apr 22, 2014. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion psql_cheatsheet.md
    View file
    Original file line number Diff line number Diff line change
    @@ -5,7 +5,7 @@ psql -h <hostname> -U <user> <dbname>
    dbname is optional

    2. Working with pgsql console
    ```SQL
    ```
    \? - Help
    \? <keyword> - Help for SQL keyword
    \du - List roles
  5. sadfuzzy revised this gist Apr 22, 2014. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions psql_cheatsheet.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,11 +1,11 @@
    1. Opening PostgreSQL console:
    ```
    ```sh
    psql -h <hostname> -U <user> <dbname>
    ```
    dbname is optional

    2. Working with pgsql console
    ```
    ```SQL
    \? - Help
    \? <keyword> - Help for SQL keyword
    \du - List roles
  6. sadfuzzy revised this gist Apr 22, 2014. 1 changed file with 4 additions and 4 deletions.
    8 changes: 4 additions & 4 deletions psql_cheatsheet.md
    View file
    Original file line number Diff line number Diff line change
    @@ -19,7 +19,7 @@ dbname is optional

    3. Dumping postgres database
    pg_dump and pg_restore pair
    ```
    ```sh
    pg_dump therrr-dev > dump.sql
    pg_dumpall > dump.sql

    @@ -30,12 +30,12 @@ pg_restore -d newdb db.dump
    ```

    Running a SQL script (command line)
    ```
    ```sh
    psql -f script.sql databaseName
    ```

    Some usefull sql commands syntax
    ```
    ```SQL
    -- comment to end of line
    /* C-like comment, possibly
    multiple lines */
    @@ -44,7 +44,7 @@ ALTER USER username WITH PASSWORD 'username';
    EXPLAIN ANALYZE
    ```
    Except ordinary ANALYZE - for showing exact query benchmarking, i.e
    ```
    ```SQL
    explain analyze SELECT "products".* FROM "products"
    WHERE (lower(name) like '%pre%' or lower(description) like '%pre%')
    ORDER BY name ASC LIMIT 100 OFFSET 100;
  7. sadfuzzy revised this gist Apr 22, 2014. 1 changed file with 32 additions and 76 deletions.
    108 changes: 32 additions & 76 deletions psql_cheatsheet.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,8 +1,11 @@
    1. Opening PostgreSQL console:
    ```
    psql -h <hostname> -U <user> <dbname>
    ```
    dbname is optional

    2. Working with pgsql console
    ```
    \? - Help
    \? <keyword> - Help for SQL keyword
    \du - List roles
    @@ -12,34 +15,39 @@ dbname is optional
    \d <table_name> - List columns in table
    \d+ <table_name> - Describe table
    \q - Exiting from console
    ```

    3. Dumping postgres database
    pg_dump and pg_restore pair
    ```
    pg_dump therrr-dev > dump.sql
    pg_dumpall > dump.sql
    # Example on dumping & restoring postgresql database
    # Don't forget to 'CREATE DATABASE newdb;'
    pg_dump -Ftar -fcp_dev.tar cp_dev
    pg_restore -d newdb db.dump
    ```

    Running a SQL script (command line)
    ```
    psql -f script.sql databaseName
    ```

    Some usefull sql commands syntax
    ```
    -- comment to end of line

    /* C-like comment, possibly
    multiple lines */

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

    ```
    Except ordinary ANALYZE - for showing exact query benchmarking, i.e
    cp_dev=# explain analyze SELECT "products".* FROM "products"
    cp_dev-# WHERE (lower(name) like '%pre%' or lower(description) like '%pre%')
    cp_dev-# ORDER BY name ASC LIMIT 100 OFFSET 100;
    ```
    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)
    @@ -52,110 +60,58 @@ cp_dev-# ORDER BY name ASC LIMIT 100 OFFSET 100;
    Total runtime: 1018.692 ms
    (8 rows)
    PostgreSQL Cheat Sheet
    CREATE DATABASE

    -- Create database
    CREATE DATABASE dbName;

    CREATE TABLE (with auto numbering integer id)

    CREATE TABLE tableName (
    id serial PRIMARY KEY,
    name varchar(50) UNIQUE NOT NULL,
    dateCreated timestamp DEFAULT current_timestamp
    );
    Add a primary key

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

    Create an INDEX

    CREATE UNIQUE INDEX indexName ON tableName (columnNames);
    Backup a database (command line)

    pg_dump dbName > dbName.sql

    Backup all databases (command line)

    pg_dumpall > pgbackup.sql

    Run a SQL script (command line)

    psql -f script.sql databaseName

    Search using a regular expression

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

    The first N records

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

    Pagination

    -- Pagination
    SELECT cols FROM table LIMIT 10 OFFSET 30;

    Prepared Statements

    -- 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 a Function
    CREATE OR REPLACE FUNCTION month (timestamp) RETURNS integer
    AS 'SELECT date_part(''month'', $1)::integer;'
    LANGUAGE 'sql';

    Table Maintenance

    -- Table Maintenance
    VACUUM ANALYZE table;

    Reindex a database, table or index

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

    Show query plan

    EXPLAIN SELECT * FROM table;

    Import from a file

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

    Show all runtime parameters

    -- Show all runtime parameters
    SHOW ALL;

    Grant all permissions to a user

    -- Grant all permissions to a user
    GRANT ALL PRIVILEGES ON table TO username;

    Perform a transaction

    -- Perform a transaction
    BEGIN TRANSACTION
    UPDATE accounts SET balance += 50 WHERE id = 1;
    COMMIT;
    Basic SQL
    Get all columns and rows from a table

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

    Add a new row

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

    Update a row

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

    Delete a row

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

  8. sadfuzzy created this gist Apr 22, 2014.
    161 changes: 161 additions & 0 deletions psql_cheatsheet.md
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,161 @@
    1. Opening PostgreSQL console:
    psql -h <hostname> -U <user> <dbname>
    dbname is optional

    2. 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

    3. Dumping postgres database
    pg_dump and pg_restore pair
    pg_dump therrr-dev > dump.sql
    pg_dumpall > dump.sql

    # Example on dumping & restoring postgresql database
    # Don't forget to 'CREATE DATABASE newdb;'
    pg_dump -Ftar -fcp_dev.tar cp_dev
    pg_restore -d newdb db.dump

    Running a SQL script (command line)
    psql -f script.sql databaseName

    Some usefull sql commands syntax
    -- comment to end of line

    /* C-like comment, possibly
    multiple lines */

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

    Except ordinary ANALYZE - for showing exact query benchmarking, i.e
    cp_dev=# explain analyze SELECT "products".* FROM "products"
    cp_dev-# WHERE (lower(name) like '%pre%' or lower(description) like '%pre%')
    cp_dev-# 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)

    PostgreSQL Cheat Sheet
    CREATE DATABASE

    CREATE DATABASE dbName;

    CREATE TABLE (with auto numbering integer id)

    CREATE TABLE tableName (
    id serial PRIMARY KEY,
    name varchar(50) UNIQUE NOT NULL,
    dateCreated timestamp DEFAULT current_timestamp
    );

    Add a primary key

    ALTER TABLE tableName ADD PRIMARY KEY (id);

    Create an INDEX

    CREATE UNIQUE INDEX indexName ON tableName (columnNames);

    Backup a database (command line)

    pg_dump dbName > dbName.sql

    Backup all databases (command line)

    pg_dumpall > pgbackup.sql

    Run a SQL script (command line)

    psql -f script.sql databaseName

    Search using a regular expression

    SELECT column FROM table WHERE column ~ 'foo.*';

    The first N records

    SELECT columns FROM table LIMIT 10;

    Pagination

    SELECT cols FROM table 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;

    Reindex a database, table or index

    REINDEX DATABASE dbName;

    Show query plan

    EXPLAIN SELECT * FROM table;

    Import from a file

    COPY destTable FROM '/tmp/somefile';

    Show all runtime parameters

    SHOW ALL;

    Grant all permissions to a user

    GRANT ALL PRIVILEGES ON table TO username;

    Perform a transaction

    BEGIN TRANSACTION
    UPDATE accounts SET balance += 50 WHERE id = 1;
    COMMIT;

    Basic SQL
    Get all columns and rows from a table

    SELECT * FROM table;

    Add a new row

    INSERT INTO table (column1,column2)
    VALUES (1, 'one');

    Update a row

    UPDATE table SET foo = 'bar' WHERE id = 1;

    Delete a row

    DELETE FROM table WHERE id = 1;