PostgreSQL Create Table

PostgreSQL Create Table

In PostgreSQL Create Table is used to store data in rows and columns. You create a table using the CREATE TABLE statement.

Basic Syntax

Simple Create Table Example

Explanation:

SERIAL → auto-increment integer
PRIMARY KEY → unique identifier
VARCHAR(n) → variable-length string
INT → integer value

Create Table with Constraints

Common constraints:

NOT NULL
UNIQUE
PRIMARY KEY
CHECK
DEFAULT

Create Table with Foreign Key

CREATE TABLE departments (
dept_id SERIAL PRIMARY KEY,
dept_name VARCHAR(100) NOT NULL
);

CREATE TABLE staff (
staff_id SERIAL PRIMARY KEY,
staff_name VARCHAR(100),
dept_id INT,
CONSTRAINT fk_dept
FOREIGN KEY (dept_id)
REFERENCES departments(dept_id)
);

CREATE TABLE departments (

dept_id SERIAL PRIMARY KEY,

dept_name VARCHAR(100) NOT NULL

);

CREATE TABLE staff (

staff_id SERIAL PRIMARY KEY,

staff_name VARCHAR(100),

dept_id INT,

CONSTRAINT fk_dept

FOREIGN KEY (dept_id)

REFERENCES departments(dept_id)

);

This creates a relationship between tables.

Create Table Using IF NOT EXISTS

Prevents error if table already exists.

Create Table from Another Table

Creates a copy of table structure and data.

View Table Structure

Common Data Types Used

Data Type	Description
`SERIAL`	Auto-increment integer
`INT`	Integer
`VARCHAR(n)`	String
`TEXT`	Large text
`BOOLEAN`	true/false
`DATE`	Date
`TIMESTAMP`	Date & time
`NUMERIC(p,s)`	Decimal numbers

Common Errors & Fixes

Error: relation already exists
Fix: Use IF NOT EXISTS
Error: invalid input syntax
Fix: Check data type mismatch
Error: foreign key constraint fails
Fix: Insert parent table data first

Best Practices

Use meaningful table and column names
Always define primary keys
Use constraints to maintain data integrity
Choose correct data types

PostgreSQL Create Table