Create Table with Default Timestamp in PostgreSQL Database

In PostgreSQL, the TIMESTAMP data type is used to store both date and time values. It’s common to set a default timestamp value for a column, such as the current time when a record is created. This feature is particularly useful for tracking when records are added or updated in a table.

In this tutorial, we will learn how to create tables with a default timestamp column and demonstrates various use cases with examples.

Basic Syntax

The general syntax for creating a table with a TIMESTAMP column and a default value is:

</>
Copy
CREATE TABLE table_name (
    column_name TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

The CURRENT_TIMESTAMP function provides the current date and time when a row is inserted into the table. You can also use NOW() as an alternative to CURRENT_TIMESTAMP.

Example 1: Create a Table with Default Timestamp

Let’s create a table named orders to track customer orders. The table includes a created_at column that defaults to the current timestamp.

</>
Copy
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    order_total DECIMAL(10, 2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
PostgreSQL - Create Table with Default Timestamp

Insert sample data into the table, without specifying the created_at column:

</>
Copy
INSERT INTO orders (customer_name, order_total)
VALUES 
('Alice', 250.00),
('Bob', 125.50),
('Charlie', 300.75);

Query the table to view the data:

</>
Copy
SELECT * FROM orders;

Explanation: The created_at column automatically populates with the current timestamp when a row is inserted, making it easy to track when the order was created.

Example 2: Adding a Default Timestamp for Updates

You can also use a TIMESTAMP column to track the last update time for a row. In this case, you can pair CURRENT_TIMESTAMP with the ON UPDATE clause to automatically update the column whenever the row is modified.

Let’s modify the orders table to include an updated_at column:

</>
Copy
DROP TABLE IF EXISTS orders;

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    order_total DECIMAL(10, 2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
PostgreSQL - Adding a Default Timestamp for Updates

Create a trigger function to update the updated_at column automatically:

</>
Copy
CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Add a trigger to the orders table:

</>
Copy
CREATE TRIGGER set_updated_at
BEFORE UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION update_timestamp();

Insert sample data:

</>
Copy
INSERT INTO orders (customer_name, order_total)
VALUES 
('Alice', 250.00),
('Bob', 125.50);

Update an order and observe the behavior of the updated_at column:

</>
Copy
UPDATE orders
SET order_total = 275.00
WHERE customer_name = 'Alice';

Query the table to view the changes:

</>
Copy
SELECT * FROM orders;

Explanation: The updated_at column is automatically updated with the current timestamp whenever a row is modified, while the created_at column remains unchanged.

Conclusion

The TIMESTAMP data type in PostgreSQL is an excellent tool for tracking when records are created or updated. By using default values such as CURRENT_TIMESTAMP, you can automate these processes and maintain accurate metadata. The additional functionality of triggers enables you to handle updates dynamically.