Question 194729 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql Error Generation Expression Is Not Immutable When Referencing Timestamp

I am receiving an error when I try to create a generated tsvector column that has a timestamp included. The error is

`ERROR: generation expression is not immutable

SQL state: 42P17`

Here is the generated column I am trying to get to work. I have tried removing the format, but that didn’t seem to help.

CREATE TABLE lms.test_person ( first_name text, last_name text, date_of_birth timestamp without time zone, search tsvector GENERATED ALWAYS AS (to_tsvector('english', first_name || ' ' || last_name || ' ' || TO_CHAR(date_of_birth, 'YYYY-MM-DD') )) STORED );

I have also tried

CREATE TABLE lms.test_person ( first_name text, last_name text, date_of_birth timestamp without time zone, search tsvector GENERATED ALWAYS AS (to_tsvector('english', first_name || ' ' || last_name || ' ' || date_of_birth::text )) STORED );

because I read that the concatenation of a non-text item is not immutable, but that did not seem to work either.
If I drop the date_of_birth from the to_tsvector everything works fine. I am using version 15.1

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I finally did get a working solution. It was a matter of cobbling together the right immutable functions to get it to work. I used date_part to extract the parts I needed and formatted it manually. Note: I went with a different format than I originally posted above, but the solution can be adjusted to use the above format. I had also used the suggestion of examining the functions for immutability from a psql prompt using df+

    df+ to_tsvector

    Here is what I came up with in the end.

    CREATE TABLE lms.test_person (
    first_name text,
    last_name text,
    date_of_birth timestamp without time zone,
    search tsvector GENERATED ALWAYS AS 
    (to_tsvector('english', first_name || ' ' || last_name || ' ' || lpad(date_part('month', date_of_birth)::text, 2, '0') || lpad(date_part('day', date_of_birth)::text, 2, '0') || lpad(date_part('year', date_of_birth)::text, 4, '0') )) STORED
);

  2. 0

    we can go around this by creating your own immutable wrapper function and use it ,

    CREATE FUNCTION immutable_to_char(timestamp without time zone) RETURNS text AS
$$ select TO_CHAR($1, 'YYYY-MM-DD'); $$
LANGUAGE sql immutable;

    and then do your query as ,

    CREATE TABLE lms.test_person ( 
    first_name text, 
    last_name text, 
    date_of_birth timestamp without time zone, 
    search tsvector GENERATED ALWAYS AS  (
        to_tsvector('english', 
                    first_name || ' ' || 
                    last_name  || ' ' || 
                    immutable_to_char(date_of_birth) 
                   )
    ) STORED 
);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search