PostgreSQL provides a DATE data type that allows us to store the date values in YYYY-MM-DD format. The DATE data type takes 4 bytes to store a date value in the storage. The DATE data type stores a date between 4713 BC to 5874897 AD.

This write will help you to understand how to use the DATE data type to insert or store a date value in PostgreSQL. So, let’s begin!

How to Use the DATE Data Type in PostgreSQL?

Let’s understand the usage of the DATE data type with suitable examples.

Example #1: How to Create a Column With DATE Data Type?

Let’s create a table named book_info and add three columns: book_id, book_name, and published_date. The data types of these columns will be INT, VARCHAR, and DATE, respectively. To do that, execute the below query:

CREATE TABLE   book_info( 
  book_id INT   PRIMARY KEY, 
  book_name VARCHAR NOT NULL, 
  published_date DATE
  );

- Firstly, we utilized the “CREATE TABLE” command followed by the table name to create a table.

- Next, we specified the column names along with their data types.

- We specified “book_id INT PRIMARY KEY” to create a column named book_id having integer data type, which would be a PRIMARY KEY.

- Next, we specified “book_name VARCHAR NOT NULL” to create a column named book_name having data type VARCHAR, which wouldn’t accept a null value.

- Finally, we created a column published_date that will accept the date type values.

On successfully executing the above-given query, you will get the following output:

The “book_info” table has been created successfully. Now, let’s populate the table’s structure using the SELECT command:

SELECT * FROM book_info;

The book_info table has been created successfully. Let’s consider another example to learn how to insert the date type values into a table.

Example #2: How to Insert a Date Into a Table in Postgres?

In this example, we will insert the data into the book_info table. To do that, let’s execute the INSERT INTO statement:

INSERT INTO book_info (book_id, book_name, published_date)
VALUES (1, 'The Little Prince', '1954-07-29'),
 (2, 'The Lord of the Rings', '1943-06-04'),
 (3, 'The Kite Runner', '2009-05-29'),
 (4, 'The Great Gatsby', '1925-04-10'),
 (5, 'East of Eden', '1952-09-19');

In the book_info table, we have three columns: book_id, book_name, published_date that will accept integer, string, and date type values, respectively.

The output shows that five records have been inserted into the book_info table. Let’s verify the record insertion using the SELECT statement:

SELECT * FROM book_info;

The output verified that all the records, including the date values, have been successfully added to the book_info table.

How to DEFAULT Keyword With DATE Data Type in PostgreSQL?

Postgres provides a DEFAULT keyword that assists us in setting a default date value. Use the following syntax while table creation to specify the current date as a default value:

col_name DATE NOT NULL DEFAULT CURRENT_DATE;

- col_name represents a column to be created.

- DATE is a data type.

- NOT NULL is a keyword that enforces a column not to accept the null values.

- DEFAULT is a keyword used with the DATE data type to set a default date value.

- CURRENT_DATE is a Postgres function that returns the current date.

Let’s understand it practically!

Example #1: How to Create a Column With Current Date as a Default Value in Postgres?

Let’s execute the query below to set the current date as default for the published_date column:

CREATE TABLE submit_article( 
   article_id INT PRIMARY KEY, 
   article_name VARCHAR NOT NULL, 
   submission_date DATE NOT NULL DEFAULT CURRENT_DATE
   );

The above snippet served the following functionalities:

- Created a table named submit_article.

- Created three columns: article_id, article_name, and submission_date having data types INT, VARCHAR, and DATE, respectively.

- The DEFAULT keyword is used while creating the submission_date column to set the current date as a default value.

The output verified that the submit_article had been created successfully. Let’s run the SELECT statement to see the table’s structure:

SELECT * FROM submit_article;

There are three columns in the submit_article table: aritcle_id, article_name, and submission_date.

Example #2: How to Insert a Date Into a Table in Postgres?

Let’s insert some rows into the submit_article table using the INSERT INTO command:

INSERT INTO submit_article (article_id, article_name)
 VALUES (1, 'Postgres FETCH Clause'),
   (2, 'Postgres WHERE Clause'),
   (3,'Postgres BETWEEN Operator');

In this example, we inserted article ids and article names using the insert command. Here is what we will get on successful execution:

We didn’t insert any value in the submission date column. However, from the output, you can observe that PostgreSQL inserted the current date in the submission_date column. This is how the DEFAULT keyword works with the DATE data type.

Conclusion

PostgreSQL supports the DATE data type that stores the date values in YYYY-MM-DD format. Postgres utilizes 4 bytes of storage to store a date value. To store the current date as a default value, use the DEFAULT keyword and CURRENT_DATE function along with the DATE data type. This write-up explained the various use cases of the DATE data type with the help of suitable examples.