PostgreSQL Table Operations - 1

PostgreSQL provides robust and flexible commands for table management. This guide covers DDL operations such as create, select into, create as, create sequence

CREATE TABLE

The CREATE TABLE statement is fundamental for structuring data within a database. It allows you to define a table’s name, columns, data types, and any constraints. By mastering this statement, you can ensure efficient and reliable database design. Basic syntax is :

CREATE TABLE table_name (
    column_name TYPE column_constraint,
    table_constraint table_constraint
) INHERITS existing_table_name;        

Column-Level Constraints

These constraints are applied to individual columns:

1. UNIQUE: Ensures all values in the column are distinct.
2. NOT NULL: Ensures the column cannot have NULL values.

Table-Level Constraints

These constraints define rules for the entire table:

1. PRIMARY KEY: Uniquely identifies a row in the table.
2. FOREIGN KEY: Establishes a relationship between table

Key points

• INHERITS: Allows a table to inherit columns and constraints from an existing table.
• TEMPORARY: Creates tables that exist only for the duration of the session.
• TABLESPACE: Tablespaces allow you to specify the physical storage location of a table. It is useful for optimizing disk usage or isolating critical data.
• PARTITION BY: Partitioning splits a table into smaller, manageable pieces based on a column's value. This improves query performance and simplifies data management for large datasets.

SELECT INTO

The SELECT INTO statement is a SQL feature that creates a new table directly from the result set of a query. SELECT INTO does not return data to the client but saves it in a new table, allowing for streamlined data handling and improved query performance. Basic syntax is:

SELECT
    column_list 
INTO [ TEMPORARY | TEMP | UNLOGGED ] [ TABLE ] new_table_name
FROM
    table_name
WHERE
    condition;        

TEMP (Temporary)

• Exists only during the session.
• Changes are logged to WAL (Write Ahead Logging)
• Slightly slower due to WAL logging.
• Ideal for temporary calculations or transformations.
• Not accessible outside the session.

UNLOGGED

• Exists beyond the session but is non-durable.
• Changes are not logged to WAL.
• Faster due to bypassing WAL.
• Suitable for performance-critical but non-durable data.
• Data loss risk during server crashes.

We can use additional clauses GROUP BY, JOINS, HAVING along with WHERE to filter the data

Key points

• Ideal for temporary or one time use cases
• SELECT INTO statement does not copy constraints like primary keys, foreign keys, unique from source table to the new table.
• We need to add them manually if required. The CREATE TABLE AS statement provides enhanced functionality and flexibility.

CREATE TABLE AS

CREATE TABLE AS statement is used to create a new table and populate it with data returned by a query. The new table does not inherit indexes or constraints from the source table. These need to be added manually if required. Basic syntax is:

CREATE TABLE new_table_name AS query;        

Temporary Tables

Temporary tables are session-specific and automatically dropped at the end of the session or transaction. Ideal for transient data that does not need to persist. Basic syntax is:

CREATE TEMP TABLE new_table_name AS query;        

Unlogged Tables

Unlogged tables improve performance like faster data insertion and querying due to no WAL (Write ahead logging) writes. However, they are not crash-safe, and data is lost during crashes. Basic syntax is:

CREATE UNLOGGED TABLE new_table_name AS query;        

Specifying Column Names

You can define custom column names for the new table by specifying them explicitly. Basic syntax is:

CREATE TABLE new_table_name ( column_name_list) AS query;        

IF NOT EXISTS

Use the IF NOT EXISTS clause to avoid an error if the table already exists. Basic syntax is:

CREATE TABLE IF NOT EXISTS new_table_name AS query;        

Difference between CREATE TABLE & SELECT INTO :

CREATE TABLE AS is a more appropriate and feature-rich alternative to SELECT INTO. It allows copying data along with additional options for creating indexes, specifying tablespaces, and other configurations.

CREATE SEQUENCE

• A sequence is a database object designed to generate a series of unique numeric values, often used for generating primary keys or unique identifiers.
• It ensures uniqueness and integrity even in concurrent environments, making it ideal for multi-user or multi-process applications.

Syntax

CREATE SEQUENCE sequence_name
    [INCREMENT BY increment]
    [START WITH start]
    [MINVALUE min]
    [MAXVALUE max]
    [CYCLE | NO CYCLE]
    [CACHE cache_size]
    [OWNED BY { table_name.column_name | NONE }];        

1. sequence_name: The name of the sequence to be created.
2. INCREMENT BY: Specifies the interval between sequence values, default value is 1 (incremental) or -1 (decremental for descending sequences).
3. START WITH: Defines the initial value of the sequence, default value is 1.
4. MINVALUE: The minimum value the sequence can generate, default value is 1 for ascending sequences or the maximum negative value for descending sequences.
5. MAXVALUE: The maximum value the sequence can generate. By default the largest positive value for ascending sequences.
6. CYCLE: Enables the sequence to restart at MINVALUE (for ascending) or MAXVALUE (for descending) once it exceeds its bounds.
7. NO CYCLE: Disables cycling and raises an error if the sequence exceeds its bounds (default).
8. CACHE: Specifies how many sequence numbers to cache in memory for performance improvement, default value is 1. Using the CACHE parameter improves performance by reducing disk I/O operations.
9. OWNED BY: Associates the sequence with a specific table column. If the table or column is dropped, the sequence is also dropped, default value is NONE.

NOTE : I used DVD rental database to perform SQL queries. Set up DVD rental database

Great! Now lets try to practice the above clauses, Sample queries.

GFG tutorials :

CREATE TABLE

SELECT INTO

CREATE TABLE AS

CREATE SEQUENCE