Introduction to PostgreSQL Database

Hello my friend! 🐘 Today we learn about PostgreSQL. Many developer call it "Postgres". It is one of most powerful database in the world. Let's go!

1. What Is PostgreSQL?

PostgreSQL is a RDBMS (Relational Database Management System). It keep data in table with row and column, and we use SQL to manage data.

PostgreSQL is open-source and free. It start long time ago (1986 at University of California, Berkeley), so it is very stable and trusted.

Big company like Apple, Instagram, Spotify, and Reddit use PostgreSQL.

2. Why Learn PostgreSQL?

• ✅ Free and open-source: No need pay money, use for any project.
• ✅ Follow SQL standard very close: What you learn here, can use in other database too.
• ✅ Very reliable: Strong transaction support (ACID), data always safe and correct.
• ✅ Many data type: Not only number and text, also JSON, ARRAY, UUID, date and time, and more.
• ✅ Can extend: Add extension like PostGIS (for map and location).
• ✅ Work with many language: Python, Java, Node.js, PHP, Go, and more.

3. PostgreSQL vs MySQL — Quick Look

	PostgreSQL	MySQL
Type	Object-relational database	Relational database
SQL standard	Follow very close	Follow, but some difference
Data type	Very many (JSON, ARRAY, UUID...)	Common type
Best for	Complex query, big and serious app	Simple and fast website
Auto number	SERIAL or GENERATED ... AS IDENTITY	AUTO_INCREMENT

Both is very good. Choose by your project.

4. Connect with psql

psql is command-line tool come with PostgreSQL. Open terminal and type:

psql -U postgres

Some useful psql command (start with \):

Command	Meaning
\l	Show all database
\c school	Connect to school database
\dt	Show all table
\d students	Show structure of students table
\q	Exit psql

If you don't like command line, you can use pgAdmin — it is free tool with screen and button.

5. Create a Database

CREATE DATABASE school;

Then connect to it:

\c school

Note: PostgreSQL don't have USE school; like MySQL. Use \c instead.

6. Create a Student Table

CREATE TABLE students (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  age INT,
  email VARCHAR(100) UNIQUE,
  skills TEXT[],
  created_at TIMESTAMP DEFAULT NOW()
);

• SERIAL mean PostgreSQL give auto number for id (1, 2, 3...).
• UNIQUE mean two student cannot have same email.
• TEXT[] is array — one column can keep many value. MySQL don't have this!
• DEFAULT NOW() put current date and time auto.

7. Insert Data

INSERT INTO students (name, age, email, skills)
VALUES ('Sok', 20, 'sok@example.com', ARRAY['SQL', 'Python']),
       ('Dara', 19, 'dara@example.com', ARRAY['HTML', 'CSS']),
       ('Vuth', 21, 'vuth@example.com', ARRAY['SQL', 'Java']);

Cool trick: use RETURNING to see the row you just insert:

INSERT INTO students (name, age, email)
VALUES ('Sreyna', 18, 'sreyna@example.com')
RETURNING id, name;

8. Query Data

See all student:

SELECT * FROM students;

Result:

id	name	age	email	skills
1	Sok	20	sok@example.com	{SQL,Python}
2	Dara	19	dara@example.com	{HTML,CSS}
3	Vuth	21	vuth@example.com	{SQL,Java}
4	Sreyna	18	sreyna@example.com	NULL

Find student older than 19:

SELECT name, age FROM students WHERE age > 19;

Find student who know SQL (search inside array):

SELECT name FROM students WHERE 'SQL' = ANY(skills);

Search name without care upper or lower case, use ILIKE:

SELECT * FROM students WHERE name ILIKE 's%';

This find Sok and Sreyna.

9. Update and Delete

UPDATE students SET age = 22 WHERE name = 'Vuth';

DELETE FROM students WHERE name = 'Dara';

10. Bonus: JSON in PostgreSQL

PostgreSQL can keep JSON data inside table, so it is SQL and a little bit like NoSQL too!

CREATE TABLE profiles (
  id SERIAL PRIMARY KEY,
  info JSONB
);

INSERT INTO profiles (info)
VALUES ('{"name": "Sok", "city": "Phnom Penh"}');

SELECT info->>'name' AS name FROM profiles
WHERE info->>'city' = 'Phnom Penh';

JSONB is JSON that store in binary, faster for search. ->> get value as text.

Tips

• Always use single quote 'text' for text value. Double quote "name" is for column or table name in PostgreSQL.
• PostgreSQL change table and column name to small letter, if you not use double quote. So just use small letter with underscore, like created_at.
• Use \d table_name in psql when you forget structure of table. Very helpful.
• Use RETURNING after INSERT, UPDATE, or DELETE to see what change.
• Before UPDATE or DELETE, run SELECT with same WHERE first. Make sure you change the right row!
• Use JSONB not JSON, it is faster for search.
• Try free online PostgreSQL if you don't want install yet, then install later when ready.

Conclusion

PostgreSQL is free, powerful, and very reliable database. It follow SQL standard, have many data type like array and JSON, and big company trust it. If you know MySQL, learn PostgreSQL is easy — most SQL is same. Keep learning my friend! 💡

Related Posts

• Practice with employee database in PostgreSQL (video in Khmer)
• Learn introduction to MySQL database
• Learn introduction to NoSQL database
• Learn common SQL data types