What Data Type Is Year in PostgreSQL?

In PostgreSQL, the year data type is used to store a specific year value. It is a relatively simple data type that represents a four-digit year, such as 2021 or 1999.

Creating a Year Column

To create a column with the year data type in PostgreSQL, you can use the CREATE TABLE statement. Here’s an example:

CREATE TABLE my_table (
    id SERIAL PRIMARY KEY,
    event_name VARCHAR(100),
    event_year YEAR
);

In this example, we have created a table called my_table. It has three columns: id, event_name, and event_year. The event_year column is of type year.

Inserting Values into a Year Column

To insert values into a column of type year, you can use the regular INSERT statement:

INSERT INTO my_table (event_name, event_year)
VALUES ('New Year Celebration', 2021),
       ('Summer Festival', 2022),
       ('Annual Conference', 2023);

In this example, we have inserted three rows into the table. Each row contains an event name and its corresponding year.

Selecting Values from a Year Column

You can select values from a column of type year using the SELECT statement:

SELECT event_name
FROM my_table
WHERE event_year = 2021;

This query will retrieve all event names from the table where the event_year is 2021.

Working with Year Data

The year data type in PostgreSQL allows you to perform various operations on year values, such as:

• Extracting the Year: You can extract the year from a date value using the EXTRACT function. For example:

SELECT EXTRACT(YEAR FROM event_date) AS event_year
FROM my_table;

• Date Arithmetic: You can perform arithmetic operations using years. For example, to add 5 years to a date, you can use the INTERVAL keyword:

SELECT event_name
FROM my_table
WHERE event_year + INTERVAL '5 years' > CURRENT_DATE;

Dropping a Year Column

If you want to remove a column of type year, you can use the ALTER TABLE statement:

ALTER TABLE my_table
DROP COLUMN event_year;

This will remove the event_year column from the table.

In Conclusion

The year data type in PostgreSQL is a simple yet useful way to store and manipulate specific year values. It allows for easy insertion, selection, and arithmetic operations on year data. By understanding how to work with this data type, you can effectively manage and analyze temporal information in your PostgreSQL databases.