0
CREATE TABLE tl (
joining_date date,
experience int GENERATED ALWAYS AS (case when joining_date is not null then age (current_date, joining_date) ) STORED
);

I'm getting an error here, anyone knows what is the problem here.

Note: I'll close this question as soon i got an answer, pls don't close it without giving an answer.

Bharani Daran
  • 9
  • 2
  • 1
    What exactly is the error you get? And what does `select version();` show you? –  Dec 02 '20 at 12:45
  • I closed your [previous question](https://stackoverflow.com/questions/65102283/add-a-new-derived-data-in-the-table-postgresql) regarding this as a duplicate, because this **has** been answered before. –  Dec 02 '20 at 12:46
  • AGE() is not an immutable function, it changes every day. – Frank Heikens Dec 02 '20 at 12:52
  • @a_horse_with_no_name my error is ERROR: syntax error at or near ")" LINE 5: ...e is not null then age (current_date, joining_date) ) STORED ^ SQL state: 42601 Character: 175 – Bharani Daran Dec 02 '20 at 12:55
  • @FrankHeikens can you please give me an correct syntax to add experience as a derived data from the joining date to add in the table? – Bharani Daran Dec 02 '20 at 12:56
  • @a_horse_with_no_name my version is PostgreSQL 12.3 on x86_64-apple-darwin, compiled by Apple LLVM version 6.0 (clang-600.0.54) (based on LLVM 3.5svn), 64-bit – Bharani Daran Dec 02 '20 at 12:57
  • As long as AGE() is not an immutable function, you can't use this function for a generated column. See the manual https://www.postgresql.org/docs/13/ddl-generated-columns.html What you want, is just not possible. You could use AGE() in your SELECT statements or put it in a VIEW – Frank Heikens Dec 02 '20 at 13:35
  • Even if `AGE()` was immutable it returns an interval which will not work with the column type of `integer`. `select age (current_date, joining_date); ERROR: cannot cast type interval to integer` – Adrian Klaver Dec 02 '20 at 16:11
  • The syntax error you got was because `case when joining_date is not null then age (current_date, joining_date)` did not finish with `end`, e.g. `...(current_date, joining_date) end`. If you add that then you get: `ERROR: generation expression is not immutable` – Adrian Klaver Dec 02 '20 at 16:15
  • Oh thanks guys now i understand thank u – Bharani Daran Dec 02 '20 at 16:39

0 Answers0