postgreSQL_cheatsheet
\PostgreSQL commands\\\
Create a new role:
Create a new role with a username
and password
:
Change role for the current session to the new_role
:
Allow role_1
to set its role as role_2:
===========================
Managing databases
=========================== Create a new database:
Delete a database permanently:
===========================
Managing tables
=========================== Create a new table or a temporary table
Add a new column to a table:
Drop a column in a table:
Set or remove a default value for a column:
Add a primary key to a table.
Remove the primary key from a table.
Drop a table and its dependent objects:
===========================
Managing views
=========================== Create a view:
Refresh a materialized view:
Drop a view:
Drop a materialized view:
Rename a view:
===========================
Managing indexes
=========================== Creating an index with the specified name on a table
Removing a specified index from a table
===========================
Querying data from tables
=========================== Query all data from a table:
Query data from specified columns of all rows in a table:
Query data and select only unique rows:
Query data from a table with a filter:
Assign an alias to a column in the result set:
Query data using the LIKE
operator:
Query data using the BETWEEN
`` operator:
Query data using the IN
`` operator:
Constrain the returned rows with the LIMIT
clause:
Query data from multiple using the inner join, left join, full outer join, cross join and natural join:
Return the number of rows of a table.
Sort rows in ascending or descending order:
Group rows using GROUP BY
clause.
Filter groups using the HAVING
clause.
===========================
Set operations
=========================== Combine the result set of two or more queries with UNION
operator:
Minus a result set using EXCEPT
operator:
Get intersection of the result sets of two queries:
===========================
Modifying data
=========================== Insert a new row into a table:
Insert multiple rows into a table:
Update data for all rows:
Update data for a set of rows specified by a condition in the WHERE
clause.
Delete all rows of a table:
Delete specific rows based on a condition:
Performance
Show the query plan for a query:
Show and execute the query plan for a query:
Collect statistics:
Last updated