CREATE TABLE in PostgreSQL 🚀

The CREATE TABLE statement in PostgreSQL is used to define a new table with specific columns and data types.

1. Basic Syntax

CREATE TABLE table_name (    column_name1 DATA_TYPE CONSTRAINTS,    column_name2 DATA_TYPE CONSTRAINTS,    ...);

2. Example: Create a Users Table

CREATE TABLE users (    id SERIAL PRIMARY KEY,    name VARCHAR(100) NOT NULL,    email VARCHAR(150) UNIQUE NOT NULL,    age INT CHECK (age >= 18),    created_at TIMESTAMP DEFAULT NOW());

Explanation

• id SERIAL PRIMARY KEY: Auto-incrementing unique ID
• name VARCHAR(100) NOT NULL: Name cannot be empty
• email VARCHAR(150) UNIQUE NOT NULL: Ensures unique emails
• age INT CHECK (age >= 18): Age must be 18 or more
• created_at TIMESTAMP DEFAULT NOW(): Auto-fills with current timestamp

3. Create a Table with Foreign Key

CREATE TABLE orders (    id SERIAL PRIMARY KEY,    user_id INT REFERENCES users(id) ON DELETE CASCADE,    product_name VARCHAR(200),    quantity INT CHECK (quantity > 0),    order_date TIMESTAMP DEFAULT NOW());

Foreign Key Behavior

• REFERENCES users(id): user_id must match an existing id in the users table
• ON DELETE CASCADE: If a user is deleted, their orders will also be deleted

4. Create a Table with JSONB Column

CREATE TABLE products (    id SERIAL PRIMARY KEY,    name VARCHAR(100) NOT NULL,    details JSONB);

(JSONB stores structured JSON data efficiently)

5. Create a Table with an Array Column

CREATE TABLE students (    id SERIAL PRIMARY KEY,    name VARCHAR(100) NOT NULL,    subjects TEXT[] DEFAULT ARRAY['Math', 'Science']);

(Arrays allow storing multiple values in a single column)

6. Create a Table with Auto-Incrementing Primary Key

CREATE TABLE employees (    id BIGSERIAL PRIMARY KEY,    name VARCHAR(100),    salary NUMERIC(10, 2));

(BIGSERIAL is used for large auto-incrementing numbers)

7. Check Existing Tables

SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

8. Drop a Table

DROP TABLE users;

(Delete the table and all its data)

Next Steps

✅ Insert data with INSERT INTO
✅ Query data with SELECT
✅ Update data with UPDATE