PostgreSQL supports a TIMESTAMP data type that is used to store the DateTime values in the database. In PostgreSQL, “NULL” is used as the column’s default value, if no default value is explicitly declared. However, if a particular value is assigned as the column’s default value, then the null values will be replaced with the respective default values.

This post presented an in-depth overview of how to set a timestamp as the column’s default value.

How to Set a TIMESTAMP as a Column’s Default Value in Postgres?

Postgres allows us to set a TIMESTAMP as the column’s default value. For this purpose, the DEFAULT keyword is used with the targeted column at the time of table creation, as shown below:

CREATE TABLE tbl_name(
col_name DATA TYPE DEFAULT default_val
);

Postgres allows us to set the TIMESTAMP as the default value of an already existing table’s column:

ALTER TABLE tbl_name
ALTER COLUMN col_name SET DEFAULT default_val;

Example 1: Setting a Column’s Default Value While Table Creation

Let’s create a new sample table named “std_details” with three columns: std_id, std_name, std_age:

CREATE TABLE emp_details( 
emp_id SMALLINT, 
emp_name TEXT, 
emp_joining_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Execute the “\d” command followed by the table’s name to see the table’s structure:

\d emp_details;

The CURRENT_TIMESTAMP is set as the selected column’s default value. Now, insert a row in the “emp_details” table to get a profound understanding of the column’s default value:

INSERT INTO emp_details(emp_id, emp_name)
VALUES (1, 'Stephen');

Execute the “SELECT” query to see the table’s data:

SELECT * FROM emp_details;

The output shows that the current DateTime has been inserted into the “emp_joining_date” column, by default.

Example 2: Setting a Column’s Default Value While Table Alteration

A sample table named “std_details” has already been created in the database, whose details are depicted in the following snippet:

In the following snippet, the ALTER TABLE statement is executed to set the current DateTime as the default value of the “s_joining_date” column:

ALTER TABLE std_details
ALTER COLUMN s_joining_date SET DEFAULT NOW();

The “std_details” table has been altered successfully. Run the “\d” command followed by the table’s name to see the table’s structure:

\d std_details;

The NOW() function is set as the default value for the “s_joining_date” column.

Conclusion

Postgres allows us to set a TIMESTAMP as the column’s default value. For this purpose, the DEFAULT keyword is used with the column name at the time of table creation. Postgres allows us to set the TIMESTAMP as the default value of an already existing table’s column. To do that, the ALTER TABLE and ALTER COLUMN commands are used with the SET DEFAULT keyword. This post presented a comprehensive guide on how to set a TIMESTAMP as the column’s default value in Postgres using suitable examples.