Prev Next

PostgreSQL CREATE TABLE

Creating a table in PostgreSQL is done using the CREATE TABLE command. You can define column names, data types, and constraints during table creation.

Key Topics

1. Basic Syntax

Below is the generic syntax for creating a table:

CREATE TABLE table_name (
    column_name1 data_type constraint,
    column_name2 data_type constraint,
    ...
);

2. Common Constraints

  • PRIMARY KEY: Ensures a unique identifier for each record.
  • NOT NULL: Disallows NULL values in a column.
  • UNIQUE: Ensures values in a column are unique across rows.
  • CHECK: Validates the data against a given condition.

3. Examples

CREATE TABLE employees (
    emp_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    salary NUMERIC(10,2) CHECK (salary > 0)
);

Code Explanation: The SERIAL type auto-increments the emp_id. The CHECK constraint ensures the salary is always greater than 0.

Best Practices

  • Choose appropriate data types to store data efficiently.
  • Use constraints to maintain data integrity at the database level.
  • Consider adding indexes on frequently queried columns for better performance.

Key Takeaways

  • Use CREATE TABLE to define structure, data types, and constraints.
  • Constraints help maintain data integrity and consistency.
  • Plan your schema carefully to optimize performance and scalability.
Prev Next