CREATE & INSERT - Interview Prep Notes

The CREATE and INSERT commands are DDL (Data Definition Language) and DML (Data Manipulation Language) respectively. They form the foundation of working with PostgreSQL — you define structure first, then populate it with data.

CREATE DATABASE

CREATE DATABASE company;

Connect to it:

\c company

Drop a database (cannot drop a database you’re connected to):

DROP DATABASE company;

CREATE TABLE

Basic syntax

CREATE TABLE table_name (
  column_name  data_type  constraints,
  ...
);

Example — employees table

CREATE TABLE employees (
  id         SERIAL PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  last_name  VARCHAR(50) NOT NULL,
  email      VARCHAR(100) UNIQUE NOT NULL,
  age        INT CHECK (age >= 18),
  salary     NUMERIC(10, 2) DEFAULT 0.00,
  department VARCHAR(50),
  hire_date  DATE DEFAULT CURRENT_DATE
);

Example — with foreign key

CREATE TABLE departments (
  id    SERIAL PRIMARY KEY,
  name  VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE employees (
  id            SERIAL PRIMARY KEY,
  first_name    VARCHAR(50) NOT NULL,
  last_name     VARCHAR(50) NOT NULL,
  department_id INT REFERENCES departments(id) ON DELETE SET NULL,
  salary        NUMERIC(10, 2) NOT NULL
);

Common data types

Type	Description	Example
`SERIAL`	Auto-incrementing integer	`id SERIAL PRIMARY KEY`
`INT` / `INTEGER`	Whole numbers	`age INT`
`NUMERIC(p, s)`	Exact decimal with precision `p` and scale `s`	`salary NUMERIC(10, 2)`
`VARCHAR(n)`	Variable-length string up to `n` chars	`name VARCHAR(100)`
`TEXT`	Unlimited-length string	`bio TEXT`
`BOOLEAN`	`TRUE` / `FALSE`	`is_active BOOLEAN`
`DATE`	Calendar date (no time)	`hire_date DATE`
`TIMESTAMP`	Date + time	`created_at TIMESTAMP`
`UUID`	Universally unique identifier	`id UUID DEFAULT gen_random_uuid()`
`JSONB`	Binary JSON (indexable)	`metadata JSONB`

Column constraints

Constraint	Meaning
`PRIMARY KEY`	Unique + NOT NULL, identifies each row
`NOT NULL`	Column cannot be empty
`UNIQUE`	No duplicate values allowed
`DEFAULT value`	Fallback value when none is provided
`CHECK (expr)`	Custom validation rule
`REFERENCES table(col)`	Foreign key — must match a value in another table

Table-level constraints

CREATE TABLE enrollments (
  student_id INT REFERENCES students(id),
  course_id  INT REFERENCES courses(id),
  grade      CHAR(1) CHECK (grade IN ('A', 'B', 'C', 'D', 'F')),
  PRIMARY KEY (student_id, course_id)  -- composite primary key
);

◆ PRIMARY KEY vs UNIQUE

A table can have only one PRIMARY KEY (which is UNIQUE + NOT NULL), but multiple UNIQUE constraints. UNIQUE columns can contain NULL values (and multiple NULLs are considered distinct).

ALTER TABLE

Modify an existing table without recreating it.

-- Add a column
ALTER TABLE employees ADD COLUMN phone VARCHAR(15);

-- Drop a column
ALTER TABLE employees DROP COLUMN phone;

-- Rename a column
ALTER TABLE employees RENAME COLUMN department TO dept;

-- Change data type
ALTER TABLE employees ALTER COLUMN salary TYPE NUMERIC(12, 2);

-- Add a constraint
ALTER TABLE employees ADD CONSTRAINT salary_positive CHECK (salary >= 0);

-- Remove a constraint
ALTER TABLE employees DROP CONSTRAINT salary_positive;

-- Rename the table
ALTER TABLE employees RENAME TO staff;

DROP TABLE & TRUNCATE

-- Delete the table and all its data permanently
DROP TABLE employees;

-- Delete only if it exists (avoids error)
DROP TABLE IF EXISTS employees;

-- Cascade drop — also drops dependent objects (foreign keys, views)
DROP TABLE departments CASCADE;

-- Remove all rows but keep the table structure
TRUNCATE TABLE employees;

-- Truncate and reset the SERIAL counter
TRUNCATE TABLE employees RESTART IDENTITY;

◆ DROP vs TRUNCATE vs DELETE

DROP removes the table entirely (structure + data). TRUNCATE removes all rows instantly but keeps the table structure — it’s faster than DELETE because it doesn’t scan rows. DELETE removes rows one by one and can be filtered with WHERE.

INSERT INTO

Single row

INSERT INTO employees (first_name, last_name, email, age, salary, department)
VALUES ('John', 'Doe', 'john@example.com', 28, 75000.00, 'Engineering');

Multiple rows

INSERT INTO employees (first_name, last_name, email, age, salary, department)
VALUES
  ('Jane', 'Smith', 'jane@example.com', 32, 85000.00, 'Engineering'),
  ('Bob', 'Wilson', 'bob@example.com', 45, 92000.00, 'Marketing'),
  ('Alice', 'Johnson', 'alice@example.com', 29, 78000.00, 'Design');

Insert with DEFAULT values

-- salary gets its DEFAULT (0.00), hire_date gets CURRENT_DATE
INSERT INTO employees (first_name, last_name, email, age, department)
VALUES ('Eve', 'Brown', 'eve@example.com', 35, 'Sales');

Insert with RETURNING

PostgreSQL-specific — returns the inserted data without a separate SELECT.

INSERT INTO employees (first_name, last_name, email, age, salary, department)
VALUES ('Tom', 'Lee', 'tom@example.com', 40, 95000.00, 'Engineering')
RETURNING id, first_name, last_name;

 id | first_name | last_name
----+------------+-----------
  5 | Tom        | Lee

Insert from a SELECT (copy data between tables)

INSERT INTO archived_employees (first_name, last_name, email, salary)
SELECT first_name, last_name, email, salary
FROM employees
WHERE hire_date < '2020-01-01';

ON CONFLICT (Upsert)

Handle duplicate key violations gracefully — insert or update.

-- Do nothing if email already exists
INSERT INTO employees (first_name, last_name, email, salary)
VALUES ('John', 'Doe', 'john@example.com', 80000.00)
ON CONFLICT (email) DO NOTHING;

-- Update salary if email already exists
INSERT INTO employees (first_name, last_name, email, salary)
VALUES ('John', 'Doe', 'john@example.com', 80000.00)
ON CONFLICT (email)
DO UPDATE SET salary = EXCLUDED.salary;

◆ EXCLUDED keyword

In an ON CONFLICT ... DO UPDATE clause, EXCLUDED refers to the row that was proposed for insertion. Use EXCLUDED.column_name to reference the values that would have been inserted.

💡 Interview Tip

ON CONFLICT (upsert) is a PostgreSQL-specific feature. In MySQL the equivalent is INSERT ... ON DUPLICATE KEY UPDATE. Knowing this distinction shows cross-database awareness.

Common interview questions

What is the difference between SERIAL and IDENTITY?

Both auto-generate integer values. SERIAL creates a sequence behind the scenes and is PostgreSQL-specific. GENERATED ALWAYS AS IDENTITY is the SQL-standard way (PostgreSQL 10+) and is preferred in modern code:

CREATE TABLE users (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(100)
);

What happens if you INSERT without specifying all columns?

Columns not listed in the INSERT statement receive their DEFAULT value. If a column has no default and is NOT NULL, the insert will fail with an error.

What is the difference between VARCHAR and TEXT?

VARCHAR(n) enforces a maximum length of n characters. TEXT has no length limit. In PostgreSQL, performance is identical — TEXT is generally preferred unless you need a length constraint for data validation.

Can you INSERT into a view?

Yes, if the view is simple (single table, no aggregates, no DISTINCT, no GROUP BY). For complex views, you need INSTEAD OF triggers. Insertable views must map directly to the underlying table columns.

PostgreSQL

​CREATE DATABASE

​CREATE TABLE

​Basic syntax

​Example — employees table

​Example — with foreign key

​Common data types

​Column constraints

​Table-level constraints

​ALTER TABLE

​DROP TABLE & TRUNCATE

​INSERT INTO

​Single row

​Multiple rows

​Insert with DEFAULT values

​Insert with RETURNING

​Insert from a SELECT (copy data between tables)

​ON CONFLICT (Upsert)

​Common interview questions