Create Table with Date Column in PostgreSQL Database

PostgreSQL provides robust support for handling date and time values. The DATE data type is used to store calendar dates (year, month, day) without any time information.

In this tutorial, we will learn how to create tables with a DATE column, insert data, and query it effectively. Additionally, we will explore different use cases for date columns with examples.

Basic Syntax

The general syntax for creating a table with a DATE column in PostgreSQL is as follows:

</>
Copy
CREATE TABLE table_name (
    column_name DATE
);

The DATE data type supports values in the range 4713 BC to 5874897 AD. You can specify constraints, default values, and additional settings for the DATE column as needed.

Example 1: Create a Simple Table with DATE Column

Let’s create a table named events to store information about various events, including their names and dates:

</>
Copy
CREATE TABLE events (
    event_id SERIAL PRIMARY KEY,
    event_name VARCHAR(100) NOT NULL,
    event_date DATE NOT NULL
);
Create Table with Date Column in PostgreSQL Database

Explanation: The events table contains:

  • event_id: An auto-incrementing primary key.
  • event_name: The name of the event, which cannot be null.
  • event_date: The date of the event, which is required.

Insert sample data into the events table:

</>
Copy
INSERT INTO events (event_name, event_date)
VALUES 
('Conference', '2024-12-10'),
('Workshop', '2024-11-25'),
('Webinar', '2024-12-01');

Query the table to view the data:

</>
Copy
SELECT * FROM events;

Explanation: The event_date column stores the specific date of each event, ensuring accurate and structured date information.

Example 2: Default Value for DATE Column

You can define a default value for a DATE column using the DEFAULT keyword. Let’s modify the events table to set the default value for event_date as the current date:

Drop and recreate the table to include the default value:

</>
Copy
DROP TABLE IF EXISTS events;

CREATE TABLE events (
    event_id SERIAL PRIMARY KEY,
    event_name VARCHAR(100) NOT NULL,
    event_date DATE DEFAULT CURRENT_DATE
);
Create Table with Date Column in PostgreSQL Database, with a Default Value for DATE Column

Insert data into the table without specifying the event_date:

</>
Copy
INSERT INTO events (event_name)
VALUES 
('Team Meeting'),
('Project Kickoff');

Query the table to verify the default value:

</>
Copy
SELECT * FROM events;

Explanation: When the event_date is not provided during insertion, PostgreSQL automatically assigns the current date to the column.

Example 3: Adding Constraints to a DATE Column

You can use constraints to enforce rules on a DATE column. For example, let’s create a table named reservations where the reservation date must be in the future.

</>
Copy
CREATE TABLE reservations (
    reservation_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    reservation_date DATE NOT NULL CHECK (reservation_date > CURRENT_DATE)
);
PostgreSQL - Create Table with Date Column - Example: Adding Constraints to a DATE Column

Insert a valid reservation:

</>
Copy
INSERT INTO reservations (customer_name, reservation_date)
VALUES ('John Doe', '2024-12-01');

Attempt to insert a reservation with a past date:

</>
Copy
INSERT INTO reservations (customer_name, reservation_date)
VALUES ('Jane Smith', '2024-11-01');

Result: PostgreSQL will throw an error because the reservation date violates the CHECK constraint:

Explanation: The CHECK constraint ensures that only future dates can be inserted into the reservation_date column, preventing invalid data entry.

Conclusion

The DATE data type in PostgreSQL is versatile and allows for precise date management in tables. By defining default values, adding constraints, and combining date columns with other features, you can build robust database designs tailored to your application’s needs.