CREATE TABLE
CREATE TABLE defines a table's name, its columns, their data types and any rules.
In this page:
Syntax
CREATE TABLE table_name (
id SERIAL PRIMARY KEY,
column1 data_type NOT NULL,
column2 data_type DEFAULT value
);CREATE TABLE
List each column with a name and type, and add constraints such as PRIMARY KEY, NOT NULL, UNIQUE, DEFAULT and CHECK. In PostgreSQL an auto-numbered key is usually written as GENERATED ALWAYS AS IDENTITY (or the older SERIAL).
The example below uses portable syntax that also runs on SQLite.
Note:
Every table should have a primary key.
Example: CREATE TABLE
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
salary INTEGER DEFAULT 3000 CHECK (salary >= 0)
);
INSERT INTO employees (id, name, email) VALUES (1, 'Ada', '[email protected]');
INSERT INTO employees (id, name, email, salary) VALUES (2, 'Bob', '[email protected]', 4200);
SELECT * FROM employees;
-- Output:
-- id | name | email | salary
-- 1 | Ada | [email protected] | 3000
-- 2 | Bob | [email protected] | 4200Login to try C/C++/Java/PHP code in the editor
Related Topics
Common Mistakes
- Forgetting a primary key
- Choosing TEXT for everything
- Skipping NOT NULL on required columns
Chapter Summary
- CREATE TABLE lists columns and types
- Constraints: PRIMARY KEY, NOT NULL, UNIQUE, DEFAULT, CHECK
- Use IDENTITY for auto ids
- Every table needs a primary key
🔒
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: