0

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

Improve this question
4
  • check this: stackoverflow.com/questions/5973030/… Commented Aug 21, 2023 at 12:16
  • Do not use current_date in your queries. Follow the example of the other columns and use the date column. Commented Aug 21, 2023 at 13:19
  • i used date still same error Commented Aug 21, 2023 at 14:39
  • Why not create a view over CREATE TABLE fy_calendar1(date date not null) that does the DATE_PART and to_char manipulations? Commented Aug 21, 2023 at 14:53
Related questions
1111
PostgreSQL error: Fatal: role "username" does not exist
431
How to generate the "CREATE TABLE" SQL statement for an existing table in PostgreSQL?
2
Adding generated column existing table in postgresql
Load 6 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.