CODEX

PostgreSQL Cheat Sheet

Each table is made up of rows and columns. If you think of a table as a grid, the column go from left to right across the grid and each entry of data is listed down as a row.

Bryan Guner
Mar 5 · 8 min read

Each row in a relational is uniquely identified by a primary key. This can be by one or more sets of column values. In most scenarios it is a single column, such as employeeID.

Every relational table has one primary key. Its purpose is to uniquely identify each row in the database. No two rows can have the same primary key value. The practical result of this is that you can select every single row by just knowing its primary key.

SQL Server UNIQUE constraints allow you to ensure that the data stored in a column, or a group of columns, is unique among the rows in a table.

Although both UNIQUE and PRIMARY KEY constraints enforce the uniqueness of data, you should use the UNIQUE constraint instead of PRIMARY KEY constraint when you want to enforce the uniqueness of a column, or a group of columns, that are not the primary key columns.

Different from PRIMARY KEY constraints, UNIQUE constraints allow NULL. Moreover, UNIQUE constraints treat the NULL as a regular value, therefore, it only allows one NULL per column.

Create a new role:

Create a new role with a and :

Change role for the current session to the :

Allow to set its role as

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:

Rename a column:

Set or remove a default value for a column:

Add a primary key to a table.

Remove the primary key from a table.

Rename a table.

Drop a table and its dependent objects:

Managing views

Create a view:

Create a recursive view:

Create a materialized 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 operator:

Query data using the operator:

Query data using the operator:

Constrain the returned rows with the 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 clause.

Filter groups using the clause.

Set operations

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

Minus a result set using 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 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:

Postgres & JSON:

Creating the DB and the Table

Populating the DB

Lets see everything inside the table books:

Output:

operator returns values out of JSON columns

Selecting 1 column:

Output:

Selecting 2 columns:

Output:

vs

The operator returns the original JSON type (which might be an object), whereas returns text.

Return NESTED objects

You can use the to return a nested object and thus chain the operators:

Output:

Filtering

Select rows based on a value inside your JSON:

Notice WHERE uses so we must compare to JSON

Or we could use and compare to

Output:

Nested filtering

Find rows based on the value of a nested JSON object:

Output:

A real world example

We’re going to store events in this table, like pageviews. Each event has properties, which could be anything (e.g. current page) and also sends information about the browser (like OS, screen resolution, etc). Both of these are completely free form and could change over time (as we think of extra stuff to track).

Now lets select everything:

Output:

JSON operators + PostgreSQL aggregate functions

Using the JSON operators, combined with traditional PostgreSQL aggregate functions, we can pull out whatever we want. You have the full might of an RDBMS at your disposal.

  • Lets see browser usage:

Output:

  • Total revenue per visitor:

Output:

  • Average screen resolution

Output:

If you found this guide helpful feel free to checkout my github/gists where I host similar content:

bgoonz’s gists · GitHub

Or Checkout my personal Resource Site:

If you found this guide helpful feel free to checkout my GitHub/gists where I host similar content:

Or Checkout my personal Resource Site:

CodeX

Everything connected with Code & Tech!

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store