PostgreSQL - CREATE TABLE

In PostgreSQL, the CREATE TABLE command is used to create a new table in the database. Below is the basic syntax for creating a table:

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

Where:

table_name is the name you want to give to the table.
column specifies the name of the column.
data_type specifies the type of data the column can hold (e.g., INTEGER, TEXT, DATE, etc.).
constraints are the rules you want to apply to the data values (e.g., NOT NULL, PRIMARY KEY, etc.).

Examples:

Creating a simple table:

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    birth_date DATE,
    hire_date DATE
);

In the above table:

employee_id is of type SERIAL, which means PostgreSQL will automatically generate and increment this number for new rows. It's also the primary key for the table, ensuring uniqueness.
first_name and last_name are text columns that cannot be left empty (NOT NULL constraint).
birth_date and hire_date are date columns.

Creating a table with foreign keys and other constraints:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER,
    order_date DATE NOT NULL,
    amount DECIMAL(10, 2) NOT NULL CHECK (amount >= 0),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

In this table:

order_id is the primary key.
customer_id is a foreign key that references the customer_id from the customers table. This ensures that for every order, there is a corresponding customer in the customers table.
There's a CHECK constraint on the amount column to ensure that the values are non-negative.

Remember, the CREATE TABLE command defines the table schema. You can then use INSERT to add data to the table, UPDATE to modify data, and SELECT to retrieve data.

Defining columns and data types in CREATE TABLE in PostgreSQL:
- Description: Creates a new table and defines its columns along with their data types.
- Code:
```
CREATE TABLE your_table (
    column1 datatype,
    column2 datatype,
    ...
);
```
Creating primary keys and unique constraints in PostgreSQL tables:
- Description: Enforces uniqueness and defines primary keys for tables.
- Code:
```
CREATE TABLE your_table (
    id SERIAL PRIMARY KEY,
    unique_column datatype UNIQUE,
    ...
);
```
Setting default values for columns in CREATE TABLE in PostgreSQL:
- Description: Specifies default values for columns.
- Code:
```
CREATE TABLE your_table (
    column1 datatype DEFAULT default_value,
    ...
);
```

Creating foreign keys with REFERENCES in PostgreSQL:

Description: Establishes relationships between tables using foreign keys.

Code:

CREATE TABLE table1 (
    id SERIAL PRIMARY KEY
);

CREATE TABLE table2 (
    id SERIAL PRIMARY KEY,
    table1_id INTEGER REFERENCES table1(id)
);

Defining CHECK constraints in PostgreSQL tables:
- Description: Adds constraints to limit the values that can be inserted into a column.
- Code:
```
CREATE TABLE your_table (
    column1 INTEGER CHECK (column1 > 0),
    ...
);
```
Creating indexes with CREATE TABLE in PostgreSQL:
- Description: Creates indexes on columns to improve query performance.
- Code:
```
CREATE TABLE your_table (
    column1 INTEGER,
    ...
    INDEX (column1)
);
```
Setting column constraints like NOT NULL in PostgreSQL tables:
- Description: Specifies that a column cannot contain NULL values.
- Code:
```
CREATE TABLE your_table (
    column1 INTEGER NOT NULL,
    ...
);
```

Inheriting from existing tables in PostgreSQL:

Description: Creates a new table that inherits columns and constraints from an existing table.

Code:

CREATE TABLE parent_table (
    id SERIAL PRIMARY KEY,
    common_column INTEGER
);

CREATE TABLE child_table (
    child_column VARCHAR(255)
) INHERITS (parent_table);