How to store mm/yyyy date on postgresql?

@hal.littel 

In PostgreSQL, you can store a mm/yyyy date as a text field or as a date field with a specific day (e.g., mm/yyyy/01) attached.

Option 1: Store as a text field You can store a mm/yyyy date as a text field in the format "mm/yyyy". This allows you to input and store dates without having a specific day attached to it.

Example:

1
2
3
4
5
6
7

CREATE TABLE example (
    id SERIAL PRIMARY KEY,
    month_year TEXT
);

INSERT INTO example (month_year) VALUES ('01/2022');
INSERT INTO example (month_year) VALUES ('02/2022');

Option 2: Store as a date field with a specific day If you need to perform date calculations or comparisons, it may be beneficial to store the mm/yyyy date as a date field with a specific day attached.

Example:

1
2
3
4
5
6
7

CREATE TABLE example (
    id SERIAL PRIMARY KEY,
    month_year DATE
);

INSERT INTO example (month_year) VALUES ('2022-01-01');
INSERT INTO example (month_year) VALUES ('2022-02-01');

This way you can use date functions and calculations in your SQL queries.

Choose the option that best suits your needs depending on how you plan to use and manipulate the mm/yyyy dates in your database.