DEV Community


Posted on

PostgreSQL 101: From what’s database to basic SQL queries

In this article, I will share what I’ve learned about basic knowledge of database and queries with PostgreSQL examples including one of installation processes.


What is database

A database is an organized collection of structured data stored electronically, typically managed by a DBMS, forming a database system (What is database software?).

There are two main DBMS: Relational databases and NoSQL/Non relational database. I will focus more on relational databases in this article as PostgreSQL is a relational database.

Relational database is consist of tables with columns and rows. Columns hold specific data types and fields store attribute values. Rows represent a collection of related values. Rows can be so called a primary key, an unique identifier and relationships between rows in different tables are established using foreign keys (What is a Relational Database?).

SQL allows us to communicate with relational databases.

**NoSQL/Non relational databases **are databases which store data in a different way from relational databases. There are variety of ways to store data such as document, key-value, wide-column, and graph (What is NoSQL?.

Work with PostgreSQL

1. Install PostgreSQL

Here is a way to download PostgreSQL on MacOS with homebrew.


  1. (Optional) Download GUI for PostgreSQL: I chose pgAdmin 4 simply because it’s free (What are the best Mac OS X GUIs for PostgreSQL?)

    1. make sure homebrew is updated: brew update
    2. confirm that everything is working fine: brew doctor


  1. brew install postgresql Check the command to start postgresql service. It was brew services start postgresql@14 in my case.

2. Create Database

  1. Start the service: brew services start postgresql@14
    You will see a success message like Successfully started ‘postgresql@14’ (label: homebrew.mxcl.postgresql@14)

  2. Create a database: createdb ‘test’

  3. (Optional for GUI) create an user: createuser testuser

  4. (Optional for GUI) open pgAdmin4

  5. (Optional for GUI) Click “Add New Server”

  1. (Optional for GUI) Input the server name (e.g. localhost)

  1. (Optional for GUI) click connection tab

  2. (Optional for GUI) Input database name, user name, and address

9 (Optional for GUI) You will see Explorer

  1. Connect to the database: psql 'test' (and \q to exit)

3. Create tables

Make sure you’ve connected the target database before creating tables.


CREATE TABLE table_name (column_1 datatype, ..., column_n datatype);

# Example
CREATE TABLE users (name text, age smallint, birthday date);
Enter fullscreen mode Exit fullscreen mode

You can find various data types provided by PostgreSQL here.
Chapter 8. Data Types

Then you will find the table with GUI, or you can check with \d command with Terminal.

# Command result

List of relations
 Schema | Name  | Type  |    Owner     
 public | users | table | ownername
(1 row)
Enter fullscreen mode Exit fullscreen mode

tables in GUI

4. Insert into: How to insert values


INSERT INTO table_name (column_1, ..., column_n) VALUES (value_1, ... value_n);

# Example
INSERT INTO users (name, age, birthday) VALUES ('Hermione', 10, '1979-09-19');
Enter fullscreen mode Exit fullscreen mode

5. Select: How to get table values

Basic syntax:

SELECT column_1, ..., column_n FROM table_name;

# Example1
SELECT name, age, birthday FROM users;

# Result1
 name     | age |  birthday  
 Hermione |  10 | 1979-09-19

# Example2
SELECT name FROM users;

# Result2
Enter fullscreen mode Exit fullscreen mode

When you want to select all columns:

SELECT * FROM table_name;

# Example
SELECT * FROM users;

# Result
 name     | age |  birthday  
 Hermione |  10 | 1979-09-19
Enter fullscreen mode Exit fullscreen mode

You can find some conditional selections:

Also we have some SQL functions:
SQL COUNT(), AVG() and SUM() Functions

6. Alter: How to add another column to an existing table


ALTER TABLE table_name ADD column_name datatype;

# Example
ALTER TABLE users ADD house text;

# Result
   name   | age |  birthday  | house 
 Hermione |  10 | 1997-09-19 | 
 Ron      |  10 | 1980-03-01 | 
Enter fullscreen mode Exit fullscreen mode

7. Update: How to update existing row values


UPDATE table_name SET column_name1 = column_value1 WHERE column_name2 = column_value2;

UPDATE users SET house = 'Gryffindor' WHERE name = 'Hermione';

# Result
   name   | age |  birthday  |   house    
 Ron      |  10 | 1980-03-01 | 
 Hermione |  10 | 1997-09-19 | Gryffindor
Enter fullscreen mode Exit fullscreen mode

8. Join: How to connect tables


SELECT * FROM table1_name JOIN table2_name ON table1_name.primary_key = table1_name.foreign_key
Enter fullscreen mode Exit fullscreen mode

Suppose we have tier table below and want to get users information with rank data:

CREATE TABLE tier (id serial NOT NULL PRIMARY KEY, rank text, name text UNIQUE NOT NULL);

 id | rank |   name   
  1 | s    | Hermione
  2 | s    | Ron
  3 | s    | Harry
  4 | a    | Draco
Enter fullscreen mode Exit fullscreen mode

Then the SQL query will be:

SELECT name, age, birthday, house, rank FROM users JOIN tier ON =;

# Result
   name   | age |  birthday  |   house    | id | rank |   name   
 Hermione |  10 | 1997-09-19 | Gryffindor |  1 | s    | Hermione
 Ron      |  10 | 1980-03-01 | Gryffindor |  2 | s    | Ron
 Harry    |  10 | 1980-07-31 | Gryffindor |  3 | s    | Harry
 Draco    |  10 | 1980-06-05 | Slytherin  |  4 | a    | Draco
Enter fullscreen mode Exit fullscreen mode

9. Delete : How to delete rows


DELETE FROM table_name WHERE your conditoin;

# Example
DELETE FROM users WHERE name='Voldemort';
Enter fullscreen mode Exit fullscreen mode

10. Drop table: How to delete tables


DROP TABLE table_name;

# Example
Enter fullscreen mode Exit fullscreen mode

That’s it! Thank you for reading :)
The original article is here

Top comments (0)