My Docs
BlogGithubLinkedin
Cheat Sheets
Cheat Sheets
  • πŸ“‹Cheat Sheets
    • Files
    • Terminal Cheat Sheet
    • Flexbox Cheat Sheet
    • Common HTTP status codes Cheat Sheet
    • networking_cheatsheet
    • Regular Expressions Cheat Sheet
    • REGEX units Cheat Sheet
  • bash_cheatsheet
  • Google dork cheatsheet
  • Cheatsheet-v2
  • πŸ•ΈοΈπŸ’» πŸ’» Javascript
    • JavaScript
      • Javascript Python cheatsheet
      • General
        • JavaScript Promise API Cheat Sheet
        • Chai.js
        • Canvas
        • ES6 EXPORTS
        • Asynchronous JavaScript Cheat Sheet
      • React
        • React Cheat Sheet
          • React Component Guide
        • React Patterns:
        • react-examples
        • React.js
          • Bootstrap
        • React.js cheatsheet 2
        • React-router
        • React.js (v0.14)
        • React.js
        • React Patterns:
      • βš–οΈLibraries & Frameworks
        • LOADASH Cheat Sheet
        • sequelize_cheatsheet
        • Sequelize Cheatsheet
      • Node & Express
        • ExpressJS Cheat Sheet
      • CHEATSHEET
      • NPM Cheat Sheet
        • NPM Command Line Cheat Sheet
      • Function Context Cheatsheet
      • js-model
  • πŸ’»CS-Concepts
    • Computer Science Concepts
      • Data Structures
        • The Queue data structure
        • Cheat Sheet for Beginners: JavaScript Data Structures Methods
        • MDN Web Docs Glossary: Definitions of Web-related terms \| MDN
        • Data Structures Cheat Sheet
        • The Tree data structure
        • An Executable Data Structures Cheat Sheet for Interviews
      • networking_cheatsheet
  • Tools
    • πŸ› οΈTools
      • VSCODE Cheat Sheet
      • Emmet
  • πŸ“ΌGuides-Tutorials
    • Tutorials
      • React.js
  • JavaScript Arrays
  • editorconfig
  • AWS CLI
  • ES6 EXPORTS
  • Flynn
  • Github
    • Github
      • Github Cheat Sheet
    • git log
    • GITHUB Cheat Sheet
      • An Executable Data Structures Cheat Sheet for Interviews
      • graphs_cheatsheet
  • General
    • General
  • πŸ‘¨β€πŸ’»πŸ‘¨πŸ’» πŸ‘¨πŸ’» πŸ’» Programming Languages
    • 🐍Python:
      • Python
        • What is Python
      • Regex In Python
    • HTML
  • EC2 API tools
    • MARKDOWN
    • πŸ§˜β™‚ PSQL
      • POSTGRES
      • postgreSQL_cheatsheet
  • ES6 IMPORTS
    • bash_cheatsheet
    • cleancode
    • πŸ”¨Bash
      • Bash Cheat Sheet
      • Learn Bash Scripting: Bash Scripting Cheatsheet
      • Curl
      • Bash Shortcuts Cheat Sheet
      • SSH Cheatsheet
      • Linux
    • CSS
      • CSS
        • CSS Grid
        • cssnext
        • CSS Cheat Sheet
        • CSS Flex Box
        • CSS tricks
        • CSS selectors
        • cssnext
        • CSS background
        • CSS animations
    • Typescript
  • Computer Science Concepts
    • An Executable Data Structures Cheat Sheet for Interviews
    • graphs_cheatsheet
    • networking_cheatsheet
    • Firebase
    • networking_cheatsheet
    • πŸ›Heroku Cheat Sheet
    • Binary Tree
  • πŸ“šDocs
    • Docs
      • editorconfig
      • EC2 API tools
      • Asynchronous JavaScript Cheat Sheet
      • CHEATSHEET (3)
      • js-model
      • Emmet
      • Binary Tree
      • Python
      • Contributor Covenant Code of Conduct
      • networking_cheatsheet
      • Common HTTP status codes Cheat Sheet
      • AWS CLI
      • Linux
      • networking_cheatsheet
      • React Patterns:
      • MDN Web Docs Glossary: Definitions of Web-related terms \| MDN
      • JavaScript Arrays
      • Linux
      • Javascript Python cheatsheet
      • Cheatsheet-v2
      • Binary Tree
      • Heroku Cheat Sheet
      • Asynchronous JavaScript Cheat Sheet
      • Cheatsheet Compilation
      • AWS CLI
      • EC2 API tools
      • Common HTTP status codes Cheat Sheet
      • Firebase
      • The Queue data structure
      • Cheat Sheet for Beginners: JavaScript Data Structures Methods
Powered by GitBook
On this page
  • \PostgreSQL commands\\\
  • Managing databases
  • Managing tables
  • Managing views
  • Managing indexes
  • Querying data from tables
  • Set operations
  • Modifying data
  • Performance

Was this helpful?

Edit on GitHub
  1. EC2 API tools
  2. πŸ§˜β™‚ PSQL

postgreSQL_cheatsheet

PreviousPOSTGRESNextES6 IMPORTS

Last updated 3 years ago

Was this helpful?

\PostgreSQL commands\\\

Create a new :

CREATE ROLE role_name;

Create a new role with a username and password:

CREATE ROLE username NOINHERIT LOGIN PASSWORD password;

Change role for the current session to the new_role:

SET ROLE new_role;

Allow role_1 to set its role as role_2:

GRANT role_2 TO role_1;

===========================

Managing databases

=========================== :

CREATE DATABASE [IF NOT EXISTS] db_name;

:

DROP DATABASE [IF EXISTS] db_name;

===========================

Managing tables

CREATE [TEMP] TABLE [IF NOT EXISTS] table_name(
       pk SERIAL PRIMARY KEY,
   c1 type(size) NOT NULL,
   c2 type(size) NULL,
   ...
);
ALTER TABLE table_name ADD COLUMN new_column_name TYPE;
ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE table_name RENAME column_name TO new_column_name;

Set or remove a default value for a column:

ALTER TABLE table_name ALTER COLUMN [SET DEFAULT value | DROP DEFAULT]
ALTER TABLE table_name ADD PRIMARY KEY (column,...);

Remove the primary key from a table.

ALTER TABLE table_name
DROP CONSTRAINT primary_key_constraint_name;
ALTER TABLE table_name RENAME TO new_table_name;
DROP TABLE [IF EXISTS] table_name CASCADE;

===========================

Managing views

CREATE OR REPLACE view_name AS
query;
CREATE RECURSIVE VIEW view_name(column_list) AS
SELECT column_list;
CREATE MATERIALIZED VIEW view_name
AS
query
WITH [NO] DATA;

Refresh a materialized view:

REFRESH MATERIALIZED VIEW CONCURRENTLY view_name;

Drop a view:

DROP VIEW [ IF EXISTS ] view_name;

Drop a materialized view:

DROP MATERIALIZED VIEW view_name;

Rename a view:

ALTER VIEW view_name RENAME TO new_name;

===========================

Managing indexes

=========================== Creating an index with the specified name on a table

CREATE [UNIQUE] INDEX index_name
ON table (column,...)

Removing a specified index from a table

DROP INDEX index_name;

===========================

Querying data from tables

=========================== Query all data from a table:

SELECT * FROM table_name;

Query data from specified columns of all rows in a table:

SELECT column_list
FROM table;

Query data and select only unique rows:

SELECT DISTINCT (column)
FROM table;

Query data from a table with a filter:

SELECT *
FROM table
WHERE condition;
SELECT column_1 AS new_column_1, ...
FROM table;
SELECT * FROM table_name
WHERE column LIKE '%value%'
SELECT * FROM table_name
WHERE column BETWEEN low AND high;
SELECT * FROM table_name
WHERE column IN (value1, value2,...);
SELECT * FROM table_name
LIMIT limit OFFSET offset
ORDER BY column_name;
SELECT *
FROM table1
INNER JOIN table2 ON conditions
SELECT *
FROM table1
LEFT JOIN table2 ON conditions
SELECT *
FROM table1
FULL OUTER JOIN table2 ON conditions
SELECT *
FROM table1
CROSS JOIN table2;
SELECT *
FROM table1
NATURAL JOIN table2;

Return the number of rows of a table.

SELECT COUNT (*)
FROM table_name;

Sort rows in ascending or descending order:

SELECT select_list
FROM table
ORDER BY column ASC [DESC], column2 ASC [DESC],...;
SELECT *
FROM table
GROUP BY column_1, column_2, ...;
SELECT *
FROM table
GROUP BY column_1
HAVING condition;

===========================

Set operations

SELECT * FROM table1
UNION
SELECT * FROM table2;
SELECT * FROM table1
EXCEPT
SELECT * FROM table2;

Get intersection of the result sets of two queries:

SELECT * FROM table1
INTERSECT
SELECT * FROM table2;

===========================

Modifying data

INSERT INTO table(column1,column2,...)
VALUES(value_1,value_2,...);

Insert multiple rows into a table:

INSERT INTO table_name(column1,column2,...)
VALUES(value_1,value_2,...),
      (value_1,value_2,...),
      (value_1,value_2,...)...
UPDATE table_name
SET column_1 = value_1,
    ...;

Update data for a set of rows specified by a condition in the WHERE clause.

UPDATE table
SET column_1 = value_1,
    ...
WHERE condition;
DELETE FROM table_name;

Delete specific rows based on a condition:

DELETE FROM table_name
WHERE condition;

Performance

Show the query plan for a query:

EXPLAIN query;

Show and execute the query plan for a query:

EXPLAIN ANALYZE query;

Collect statistics:

ANALYZE table_name;

=========================== or a

to a table:

in a table:

:

Add a to a table.

.

and its dependent objects:

=========================== :

:

:

Assign an to a column in the result set:

Query data using the operator:

Query data using the `` operator:

Query data using the `` operator:

Constrain the returned rows with the clause:

Query data from multiple using the , , , and :

Group rows using clause.

Filter groups using the clause.

=========================== Combine the result set of two or more queries with operator:

Minus a result set using operator:

=========================== :

data for all rows:

of a table:

role
Create a new database
Delete a database permanently
Create a new table
temporary table
Add a new column
Drop a column
Rename a column
primary key
Rename a table
Drop a table
Create a view
Create a recursive view
Create a materialized view
alias
LIKE
BETWEEN
IN
LIMIT
inner join
left join
full outer join
cross join
natural join
GROUP BY
HAVING
UNION
EXCEPT
Insert a new row into a table
Update
Delete all rows