PostgreSQL – Yoshua F. Reece

Setting up PostgreSQL server, client and command line tools

May 12, 2025 yreece

I downloaded postgresql and postbird. Now I can create and manage databases locally.

I practiced creating tables, adding columns and rows with various data types, backfilling data, querying the data and adding constraints. I practiced doing this using both the Postbird GUI and psql CLI.

postgres=# select * from films;
 id |           name            | release_date | category  | stars
----+---------------------------+--------------+-----------+-------
  1 | Bugs Life                 | 1998-11-14   | animation |     3
  2 | Matilda                   | 1996-07-28   | comedy    |     4
  3 | Return of the Jedi        | 1983-05-25   | sci-fi    |     2
  4 | Shrek                     | 2001-03-23   | animation |     5
  5 | The Count of Monte-Cristo | 2024-04-03   | action    |     3
  6 | Napoleon Dynamite         | 2004-04-19   | comedy    |     5
(6 rows)

CodeCademy course: Design Databases With PostgreSQL

May 12, 2025 yreece

This was the first project in a course that I am starting. In it, I learned some foundational database concepts and how to query, create, alter database information using SQL. Here are my first ever SQL queries:

CREATE TABLE friends (
  id INTEGER, 
  name TEXT, 
  birthday DATE
);

INSERT INTO friends (id, name, birthday) 
VALUES (1, 'Ororo Munroe', '1940-05-30');

INSERT INTO friends (id, name, birthday) 
VALUES (2, 'Didi', '2024-06-16');

INSERT INTO friends (id, name, birthday) 
VALUES (3, 'Luffy', '1999-05-05');

INSERT INTO friends (id, name, birthday) 
VALUES (4, 'water god', '1225-06-16');

UPDATE friends
SET name = 'Storm'
WHERE id = 1;

ALTER TABLE friends 
ADD email TEXT;

UPDATE friends
SET email = 'storm@lessonslides.com'
WHERE id = 1;

UPDATE friends
SET email = 'didi@lessonslides.com'
WHERE id = 2;

UPDATE friends
SET email = 'luffy@lessonslides.com'
WHERE id = 3;

UPDATE friends
SET email = 'watergod@lessonslides.com'
WHERE id = 4;

DELETE FROM friends
WHERE id = 1;

SELECT * 
FROM friends;