CREATE TABLE and data types

Part of the free LogicWiz SQL & Databases course, Module 7: Creating tables. The queries and practice run in the app on real Postgres.

Aarav stored hostel fees as FLOAT. Ten students each paid ₹0.10 of late fine, and the report says the total is ₹0.9999999999999999. The maths was right. The column type was wrong.

You'll learn to

Create a table with CREATE TABLE
Pick the right type for ids, money, text, dates and yes/no values
Explain why FLOAT is wrong for money
Read the errors a type gives you when a value does not fit

Learn

A table is a promise

CREATE TABLE names a table and lists its columns. Every column gets a data type, and the type is a promise: this column only ever holds this kind of value.

INT column: only whole numbers.
DATE column: only real dates. '2025-02-30' is rejected.
VARCHAR(40): text of at most 40 characters.

So the type is your first line of defence. A wrong value is stopped when it is inserted, not found months later in a report.

Your first table

CREATE TABLE students (
  student_id    INT,
  name          VARCHAR(40),
  city          TEXT,
  joined_on     DATE,
  cgpa          NUMERIC(4,2),
  is_hosteller  BOOLEAN
);

INSERT INTO students VALUES
  (101, 'Riya Patel', 'Mumbai', '2024-08-01', 8.75, true),
  (102, 'Kabir Khan', 'Delhi', '2024-08-01', 7.4, false);

SELECT * FROM students;

The database is empty, so the script creates the table, fills it, then reads it. NUMERIC(4,2) means 4 digits in total, 2 after the decimal point, so 7.4 is stored as 7.40.

The types you will use 90% of the time

Type	Holds	Use it for
INT	whole numbers up to about 2.1 billion	ids, counts, marks, quantity
BIGINT	whole numbers up to about 9.2 × 10^18	UPI transaction ids, phone-scale counters, row ids in big apps
NUMERIC(p,s)	exact decimals: p digits, s after the point	money, CGPA, ratings
VARCHAR(n) / TEXT	text, with or without a length limit	names, emails, addresses, phone numbers
DATE	a calendar date	date of birth, journey date
TIMESTAMP	date and time	when an order was placed
BOOLEAN	true / false (or NULL)	isveg, ispremium, ispaid

A phone number is text, not a number: you never add phone numbers, and +91 or a leading 0 must survive.

FLOAT vs NUMERIC

SELECT 0.1::FLOAT + 0.2::FLOAT     AS float_sum,
       0.1::NUMERIC + 0.2::NUMERIC AS numeric_sum;

FLOAT (also REAL, DOUBLE PRECISION) stores numbers in binary, and 0.1 has no exact binary form, just like 1/3 has no exact decimal form. NUMERIC stores the decimal digits exactly.

Never store money in FLOAT or REAL. Tiny errors add up across lakhs of rows, and total = 1.00 stops being true. Use NUMERIC(10,2) for rupees, or store whole paise in a BIGINT.

When a value does not fit

CREATE TABLE coupons (
  code      VARCHAR(8),
  discount  NUMERIC(4,2)
);

INSERT INTO coupons VALUES ('DIWALI2025', 10.00);

'DIWALI2025' has 10 characters and the column allows 8, so Postgres refuses the whole row: value too long for type character varying(8). A NUMERIC(4,2) column would refuse 150.00 the same way (it allows at most 99.99).

MySQL: DECIMAL(p,s) is the same as NUMERIC. BOOLEAN is really TINYINT(1), so true comes back as 1. DATETIME is the usual date-and-time type. Postgres TEXT has no length limit; in MySQL TEXT is capped at 64 KB and cannot have a simple default.

Interviews love three questions here: CHAR vs VARCHAR (CHAR(n) pads with spaces to exactly n; VARCHAR(n) stores what you give, up to n), why not FLOAT for money (binary rounding), and INT vs BIGINT (INT overflows at about 2.1 billion, which a busy payments table passes in weeks).

In one line

Pick types on purpose: INT/BIGINT for ids, NUMERIC(p,s) for money (never FLOAT), VARCHAR/TEXT for text and phone numbers, DATE/TIMESTAMP, BOOLEAN.

Interview corner

Why should money not be stored in FLOAT?

FLOAT stores numbers in binary, and most decimal fractions (like 0.1) have no exact binary form, so sums drift: 0.1 + 0.2 gives 0.30000000000000004. Use NUMERIC(p,s)/DECIMAL, which stores decimal digits exactly, or store paise as a BIGINT.

What is the difference between CHAR, VARCHAR and TEXT?

CHAR(n) always holds exactly n characters and pads shorter values with spaces. VARCHAR(n) holds up to n characters with no padding. In Postgres TEXT is variable length with no limit and performs the same as VARCHAR; the limit in VARCHAR(n) is just a rule.

When would you choose BIGINT over INT?

When values can pass about 2.1 billion: row ids in a large or fast-growing table, transaction ids, counters in paise. Running out of INT ids in production means an urgent and slow migration, so busy tables usually start with BIGINT.