Create and Query Your First Table in PostgreSQL

Creating a Table and Inserting Data

A table is a structured grid with data inside it, consisting of rows and columns. Rows describe the kind of data we have, and columns describe that data. For example, a table of dishes might have rows with tiramisu, pasta carbonara, margarita pizza, and garlic bread. The columns describe attributes such as name, price, and category. The ID column is unique to every row and is called a primary key. A primary key can never be repeated, meaning no two rows in a table can ever have the same primary key.

Setting Up the Environment

Start by spinning up the environment. Click Start PostgreSQL and wait for it to load. Once the environment is set up, click the VS Code button to open it. In VS Code, open the integrated terminal by clicking the three lines menu, then Terminal > Create a New Terminal. Maximize the terminal for more space.

Connect to your working directory:

cd workspace

PostgreSQL is already pre-installed. Start the service:

sudo systemctl start postgresql

Verify it's running:

sudo systemctl status postgresql

Connect to PSQL:

sudo -u postgres psql

Creating a Database

Create a database called restaurant:

CREATE DATABASE restaurant;

List databases to confirm:

\l

Connect to the restaurant database:

\c restaurant

Creating a Table

Create a table called menu with columns for ID, name, price, and category:

CREATE TABLE menu (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price NUMERIC(5,2) NOT NULL,
    category VARCHAR(100) NOT NULL
);

The id column uses SERIAL PRIMARY KEY, which means PostgreSQL will automatically generate unique IDs starting from 1 and incrementing. VARCHAR(100) limits the text to 100 characters. NOT NULL means the column must have a value. NUMERIC(5,2) means up to 5 total digits with 2 digits after the decimal point.

Reading the Empty Table

Select all columns from the table to see its structure:

SELECT * FROM menu;

The table has columns for ID, name, price, and category, but it's empty.

Inserting Data

Insert rows into the menu table. Note that you don't insert a value for id because PostgreSQL handles that automatically:

INSERT INTO menu (name, price, category)
VALUES
    ('garlic bread', 3.99, 'starter'),
    ('pizza Margherita', 11.99, 'main dish'),
    ('tiramisu', 1.25, 'dessert');

The output INSERT 0 3 means three rows were inserted.

Reading the Data Back

Select all rows from the table again:

SELECT * FROM menu;

You should see the three dishes with their IDs, names, prices, and categories.

Viewing Data in pgAdmin

Open the pgAdmin console by clicking the link in the environment. Use the credentials provided (host, login, email) to create a server if you haven't already. Expand the server, then expand Databases and find the restaurant database. Expand it, go to Schemas, then Tables, and you'll see the menu table. Right-click on it, select View/Edit Data > Show All Rows. The data you inserted will be displayed.