generate column while creating the table in PostgreSQL - generation expression is not immutable error

Question

I am planning to create a table with generate columns

CREATE TABLE fy_calendar1(
    date date not null,    
    day varchar generated always AS (DATE_PART('day', date)) stored,
    dow_in_month varchar  generated always AS (DATE_PART('day', date)) stored,
    day_of_year varchar generated always AS (DATE_PART('DOY', date)) stored,
    week_of_year varchar generated always as (DATE_PART('week', date)) stored,
    month_of_year varchar generated always as (DATE_PART('MONTH', date)) stored,
   quarter_of_year varchar generated always as (DATE_PART('QUARTER', date)) stored,
   year varchar generated always as (DATE_PART('year', date)) stored,   
   day_name_week varchar generated always as (to_char(date, 'day')) stored
)

while creating

question 1:

           day_name_week varchar generated always as (to_char(date, 'day')) stored

I am getting below error

SQL Error [42P17]: ERROR: generation expression is not immutable

question 2: for the last day of the month

last_day_of_month varchar generated always as (DATE_TRUNC('MONTH', date) + INTERVAL '1 MONTH - 1 day') stored

I am getting the same error. SQL Error [42P17]: ERROR: generation expression is not immutable

could you please advice me on above questions.

Thank you, Raja

check this: https://stackoverflow.com/questions/5973030/error-functions-in-index-expression-must-be-marked-immutable-in-postgres — TmTron, Aug 21 '23 at 12:16
Do not use `current_date` in your queries. Follow the example of the other columns and use the `date` column. — Mike Organek, Aug 21 '23 at 13:19
Why not create a view over `CREATE TABLE fy_calendar1(date date not null)` that does the `DATE_PART` and `to_char` manipulations? — Adrian Klaver, Aug 21 '23 at 14:53

generate column while creating the table in PostgreSQL - generation expression is not immutable error

0 Answers0