PostgreSQL CRUD Commands

Here are the primary SQL commands used for interacting with PostgreSQL.

In case you aren't aware, CRUD stands for "Create", "Read", "Update", and "Delete".

Insert

INSERT INTO ingredients (
 title, image, type
) VALUES (
  'red pepper', 'red_pepper.jpg', 'vegetable'
);

NOTE: values that are strings must be in single quotes ('').

Identifiers (table and column names) can be in double quotes, though it's not necessary.

Also, the order of the fields (column names) and their respective values must match.

Your response should be:

INSERT 0 1

The 0 is no longer really in use in PostgreSQL (it's still there for backwards compatibility).

The 1 represents the insert (the number of records affected).

Whitespace isn't critical in SQL, however punctuation (as noted previously) can be.

Multiple records

You can also insert multiple records in a single command:

INSERT INTO ingredients (
  title, image, type
) VALUES
  ( 'avocado', 'avocado.jpg', 'fruit' ),
  ( 'banana', 'banana.jpg', 'fruit' ),
  ( 'beef', 'beef.jpg', 'meat' ),
  ( 'black_pepper', 'black_pepper.jpg', 'other' ),
  ( 'broccoli', 'broccoli.jpg', 'vegetable' ),
  -- etc etc
  ( 'watermelon', 'watermelon.jpg', 'fruit' )
ON CONFLICT DO NOTHING;

Note the ON CONFLICT statement. This is telling PostgreSQL that if a row exists already (such as broccoli) to just do nothing about it.

Upsert

With "upserting" the intention is to insert the record if it does not already exist (based on title in this case)...but if it does exist then update it.

INSERT INTO ingredients (
  title, image, type
) VALUES
  ( 'watermelon', 'banana.jpg', 'this won''t be updated' )
ON CONFLICT (title) DO UPDATE SET image = excluded.image;

In this example we're updating only the image value if 'watermelon' already exists.

Also note that if you need to escape a single quote (') in a value you use another single quote directly before it.

Update

UPDATE ingredients
SET image = 'strawberry.jpg'
WHERE title = 'watermelon';

The result would be:

Without the WHERE clause then all records in the table would have their image field updated.

The WHERE clause is explained further down below.

Multiple records

UPDATE ingredients
SET type = 'staple'
WHERE type = 'other'
RETURNING *;

Here we're updating records with a type equal to 'other' and changing those type values to 'staple'...and returning all results (see next section).

Returning results

This works for all commands, not just updates.

UPDATE ingredients
SET image = 'watermelon.jpg'
WHERE title = 'watermelon'
RETURNING title, image, id, type; -- can also use * to return everything

Delete

DELETE FROM ingredients
WHERE type = 'staple'
RETURNING *;

Here we have no SET clause.

Anything that matches the WHERE clause will be deleted.

RETURNING, like in updates, is optional if you want to see what was deleted.

Select

SELECT * FROM ingredients;

The * represents all available columns. Usually we do not want to select everything.

SELECT id, title FROM ingredients;

Selecting only what you actually need results in smaller payloads and also, more importantly, shows specific intent within your code.

Limit

SELECT id, title, image
FROM ingredients
LIMIT 5;

This will limit your return to only five records, the first five it finds.

Offset

What if you want the next five records?

SELECT id, title, image
FROM ingredients
LIMIT 5
OFFSET 5;

This can be inefficient at large scales and without the use of indexes.

It also has the problem of if you're paging through data and someone inserts (or deletes) a record in the meantime it could shift your results.

Add fields

In addition to selecting from the available columns on each record, you can add additional fields in your response.

SELECT *, COUNT(*) OVER ()::INTEGER AS total_count
FROM ingredients
LIMIT 5;

This will add a field labeled as total_count with a value being the total number of records in the table to each record in your response.

Filtering

Where

Sometimes you don't want all of the records but are looking for one or more specific records (based on filters).

In that case you can add a WHERE clause to filter your results down to a subset of all of your records.

SELECT *
FROM ingredients
WHERE type = 'fruit';

Not

Use <> to select records that are not equal to a certain value:

SELECT *
FROM ingredients
WHERE type <> 'fruit';

And

What if we wanted to only select ingredients that are vegetables AND have IDs that are less than 20?

SELECT *
FROM ingredients
WHERE type = 'vegetable'
  AND id < 20;

Or

You get the idea...

SELECT *
FROM ingredients
WHERE id <= 10
  OR id >= 20;

Order By

ASC

Let's say you wanted to order your results not by id or insertion order but by title:

SELECT * FROM ingredients
ORDER BY title;

This will alphabetize your returned list (in ascending ASC order by default).

DESC

The following will start by the largest id number and count backwards:

SELECT * FROM ingredients
ORDER BY id DESC;

From the Complete Intro to SQL & PostgreSQL↗ course on FEM↗ taught by Brian Holt↗.