SQL CREATE TABLE and Data Types

CREATE TABLE defines a new table: its name, its columns and each column's data type. Choosing good data types keeps data correct (no text in a price column) and queries fast. This lesson starts the part of SQL known as DDL, the Data Definition Language.

Syntax

CREATE TABLE table_name (
  column1 DATA_TYPE [constraints],
  column2 DATA_TYPE [constraints],
  ...
);

Common PostgreSQL data types

NUMERIC(10, 2) means up to 10 digits in total, 2 of them after the decimal point, so up to 99,999,999.99.

Examples

1. Create a table

A coupons table for ShopKart's promotions:

CREATE TABLE coupons (
  coupon_code   VARCHAR(20),
  description   TEXT,
  discount_pct  NUMERIC(5, 2),
  min_order_amt NUMERIC(10, 2),
  valid_from    DATE,
  valid_to      DATE,
  is_active     BOOLEAN
);

Result:

Query OK, 0 row(s) affected.

2. Check the new table

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'coupons'
ORDER BY ordinal_position;

Result:

(7 rows)

information_schema is a set of built-in views that describe every table and column. It works in PostgreSQL, MySQL and SQL Server.

3. Auto-incrementing IDs

Let the database generate IDs:

CREATE TABLE wishlist (
  wishlist_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id INT,
  product_id  INT,
  added_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO wishlist (customer_id, product_id) VALUES (1, 103), (4, 111);

SELECT wishlist_id, customer_id, product_id FROM wishlist;

Result:

(2 rows)

You didn't supply wishlist_id or added_at; the database filled them in. MySQL uses AUTO_INCREMENT and SQL Server uses IDENTITY(1,1) for the same idea. (PRIMARY KEY and DEFAULT are explained in the constraints lesson.)

4. CREATE TABLE AS: save a query result

Create a table straight from a SELECT, handy for snapshots and analysis tables:

CREATE TABLE delivered_orders_2025 AS
SELECT order_id, customer_id, order_date
FROM orders
WHERE status = 'Delivered'
  AND order_date BETWEEN '2025-01-01' AND '2025-12-31';

SELECT COUNT(*) AS rows_copied FROM delivered_orders_2025;

Result:

(1 row)

The new table copies column types but not keys, constraints or indexes.

5. Data types enforce correctness

Text that isn't a valid date is rejected:

INSERT INTO coupons (coupon_code, valid_from) VALUES ('DIWALI25', 'next Friday');

Error: invalid input syntax for type date: "next Friday"

A value longer than VARCHAR(20) is rejected too:

INSERT INTO coupons (coupon_code) VALUES ('THIS_CODE_IS_FAR_TOO_LONG');

Error: value too long for type character varying(20)

Common mistakes

• Storing numbers or dates as text. '2499' sorts after '10000' as text, and date maths doesn't work on strings.
• FLOAT for money. Use NUMERIC.
• Phone numbers as INT. They aren't quantities: leading zeros and +91 get lost. Use VARCHAR.
• Spaces or capitals in names. "Order Date" then needs double quotes forever. Use snake_case.

Practice question

Create a product_reviews table with an auto-generated ID, product ID, customer ID, a rating from 1 to 5 (whole number), review text and the review date. Insert one review and select it.

CREATE TABLE product_reviews (
  review_id   INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  product_id  INT,
  customer_id INT,
  rating      SMALLINT,
  review_text TEXT,
  review_date DATE
);

INSERT INTO product_reviews (product_id, customer_id, rating, review_text, review_date)
VALUES (102, 7, 4, 'Good bass for the price', '2025-05-10');

SELECT * FROM product_reviews;

Key takeaways

• CREATE TABLE defines columns and their data types.
• Use INT for counts, NUMERIC for money, VARCHAR/TEXT for text, DATE/TIMESTAMP for time.
• Identity columns generate IDs automatically.
• CREATE TABLE ... AS SELECT saves a query result as a new table (without constraints).