PostgreSQL Basics 102 - Inserts Updates, Deletes and Selects

Before we start

If you don't have a database and a table already, where we talked about in the previous lesson Postgres basics

Here is a short summary, if you know what these commands do, great, keep reading this lesson, if not, make sure to read the first part.

CREATE DATABASE myroom

CREATE TABLE electronics (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    title VARCHAR (255) UNIQUE NOT NULL
);

ALTER TABLE electronics ADD COLUMN image VARCHAR (255);

ALTER TABLE electronics
ADD COLUMN type VARCHAR ( 255 ),
ADD COLUMN color VARCHAR ( 50 ) NOT NULL DEFAULT 'black';

Inserts

INSERT INTO electronics(
    title, image, type)
VALUES (
    'phone', 'phone.jpg', 'mobile'
);

You are free to organize the order of inserts. However, make sure to follow the same order for the VALUES command as well.

If you write title, image, type in this order, then, follow the same order for the VALUES.

Running this will confirm we have successfully inserted our data

select * from electronics

id | title |  type  | color |   image
----+-------+--------+-------+-----------
  1 | phone | mobile | black | phone.jpg
(1 row)

If you have noticed, I haven't declared anything for the "color" column. By default, Postgres will assign 'black' since that's how it's setup via NOT NULL DEFAULT 'black';

Adding comments

You can use --, to add comments.

INSERT INTO "electronics" (
 "title", "image", "type" -- comment here
) VALUES (
  'smart light', 'light.jpg', 'smart' -- homekit
);

If you noticed, we used double quotes (" ") for title, image, and type. Double quotes are optional, and you don't need to use them. On the other hand, using single quotes (' ') for values is required because they are strings (VARCHAR).

To view the comments, we need to open the .sql file, as they aren't stored inside the database. Running SELECT * FROM electronics will not display our comments, but opening the .sql file with a text editor will.

Inserting large data into existing table

Let's say, we found a table that we can use, and after checking it's content, it does math well with our own table. Great! Let's insert it to our table

INSERT INTO electronics (
title, image, type
) VALUES
( 'smartphone', 'smartphone.jpg', 'mobile' ),
( 'laptop', 'laptop.jpg', 'computer' ),
( 'monitor', 'monitor.jpg', 'display' ),
( 'keyboard', 'keyboard.jpg', 'input' ),
( 'mouse', 'mouse.jpg', 'input' ),
( 'speaker', 'speaker.jpg', 'audio' ),
ON CONFLICT DO NOTHING;

ON CONFLICT DO NOTHING merges our existing table with the new insert and does not throw an error for duplicates.

Let's say we had laptop in our table before inserting the new dataset. Without ON CONFLICT DO NOTHING, Postgres would throw an error, duplicate key value violates unique constraints.

With ON CONFLICT DO NOTHING in place, if there are duplicates of some keys, Postgres will simply skip them and insert the non-existing records into our table.

Updating with ON CONFLICT

If we want to update the picture of an entry,

INSERT INTO electronics (
  title, image, type
) VALUES
  ( 'speaker', 'largeSpeaker.jpg', 'this won''t change' )
ON CONFLICT (title) DO UPDATE SET image = excluded.image;

We already have a speaker entry in our table. To replace its image, we can run this code ON CONFLICT (title) DO UPDATE SET image = excluded.image;

What it basically does is, if 'title' conflicts with another entry, change the image only. It will essentially update the image and won't touch other properties.

Did you notice the double single quotes in the 'this won''t change' part? Values must be enclosed in single quotes. When we need to use a single quote within the value, we need to use two single quotes next to each other ''.

and to check the updated value:

SELECT * FROM electronics where title = 'speaker';

// will return the table below.

 id |   title    |   image    | type
----+------------+------------+-------
 7 | speaker | largeSpeaker.jpg | audio
(1 row)

If we had a large inventory for a shop and not every product had an image, we could point all of them to a placeholder.jpg, and update it with help of the ON CONFLICT. There are some useful ways to use ON CONFLICT.

Another way to update:

UPDATE electronics SET image = 'default.jpg' WHERE title = 'speaker';

So, in another database, we could run this to replace images:

UPDATE inventory SET image = 'outOfStock.jpg' where stock = 0;

If you want to display changes right after updating them:

UPDATE electronics SET image = 'largeSpeaker.jpg' WHERE title = 'speaker' RETURNING id, title, image;

// will print this right after.

 id |   title    |     image
----+------------+----------------
 7 | speaker | largeSpeaker.jpg

RETURNING keyword with properties you want to view followed by will do that for you.

If you think that's too much typing, you could use * for everything. Instead of typing RETURNING id, title, image, you could use RETURNING *

Updating inserts

Let's add new values,

INSERT INTO electronics
  (title, image)
VALUES
  ('place holder 1', 'palceH.jpg'),
  ('place holder 2', 'palceH.jpg');

and let's update them,

UPDATE electronics
SET image = 'noImage.jpg'
WHERE image = 'placeH.jpg'
RETURNING *;

// will return
 id |   title    |     image
----+------------+---------------
 10 | place holder 1 | noImage.jpg
 11 | place holder 2 | noImage.jpg
(2 rows)

This query updates the image column in the electronics table, setting it to 'noImage.jpg' for all rows where the image column has a value of 'placeH.jpg'. It then returns all of the updated rows, because of *.

If you know a coding language, this is about the same thing using Javascript.

for (let i = 0; i < imageArray.length; i++) {
  if (imageArray[i] === 'placeH.jpg') {
    imageArray[i] = 'noImage.jpg'
  }
}

Deleting

This query will check electronics table and delete entries with an image with placeholder.jpg as a value and return (show) you the changes.

DELETE FROM electronics
WHERE image = 'placeholder.jpg'
RETURNING *;

Selects

Will return and display everything.

SELECT * FROM  electronics

Will return id and title from electronics

SELECT id, title FROM electronics

When writing an application, it's crucial to select only the data you need. This not only helps you understand the purpose of a component when reviewing your code later but also improves performance by reducing payload size.

For instance, in a React component displaying first and last names, there's no need to retrieve unrelated information like images or addresses.

Think of it like packing for a trip: you wouldn't bring snow boots to Hawaii in July. Similarly, only select necessary data from your database. This approach keeps your code efficient and clear.

To limit the number of records you want to view, you can use LIMIT.

SELECT id, title, image
FROM electronics
LIMIT 5;

Using LIMIT 5 returns the first 5 records. Implementing a LIMIT is essential because some databases have millions or even billions of entries. Selecting all records without a limit may crash the servers.

To view the next 5 records, use OFFSET 5.

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

Keep in mind that this approach might not work if the database is actively receiving or deleting entries. We'll discuss optimizing queries in more detail later on.

Better way to select and find entries is to use WHERE

SELECT id, title, type
FROM electronics
WHERE id > 13 limit 10;

Using WHERE is better and more efficient. When you specify an id and ask for 10 more items which id greater than 13, Postgres can easily find that id and show you the next 10 times. WHERE id > allows to perform the same function with less calculations and resources.

We can also define a range with OR

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

You could also use AND instead of OR for similar reasons.

We can use <> as the not keyword:

SELECT *
FROM electronics
WHERE type <> 'audio';

Will return and display everything with a title different than audio.

You shouldn't rely on the way Postgres returns the entries. Although it often orders values by their insertion order, the documentation states that this is not the required behavior. Depending on the situation, Postgres might order entries by other standards. Therefore, it's a good idea to ORDER data the way you want it.

To order the results, you can use ORDER BY like this:

SELECT * FROM electronics ORDER BY title;

If we need the reverse order, keyword DESC can help us.

SELECT * FROM electronics ORDER BY id DESC;