Why Am I Getting a an Error When Creating a Generated Column in Postgresql

Why am I getting a an error when creating a generated column in PostgreSQL?

The concat() function is not IMMUTABLE (only STABLE) because it can invoke datatype output functions (like timestamptz_out) that depend on locale settings. Tom Lane (core developer) explains it here.

And first_name || ' ' || last_name is not equivalent to concat(first_name, ' ', last_name) while at least one column can be NULL.

Detailed explanation:

• Combine two columns and add into one new column

Solution

To make it work, exactly the way you demonstrated:

CREATE TABLE person (
  person_id smallserial PRIMARY KEY
, first_name varchar(50)
, last_name  varchar(50)
, full_name  varchar(101) GENERATED ALWAYS AS
                         (CASE WHEN first_name IS NULL THEN last_name
                               WHEN last_name  IS NULL THEN first_name
                               ELSE first_name || ' ' || last_name END) STORED
, ...
);

db<>fiddle here

The CASE expression is as fast as it gets - substantially faster than multiple concatenation and function calls. And exactly correct.

Or, if you know what you are doing and have the necessary privileges, create an IMMUTABLE concat function as demonstrated here (to replace the CASE expression):

• Create an immutable clone of concat_ws

Aside: full_name needs to be varchar(101) (50+50+1) to make sense. Or just use text columns instead. See:

• Any downsides of using data type "text" for storing strings?

General Advice

The best solution depends on how you plan to deal with NULL values (and empty strings) exactly. I would probably not add a generated column. That's typically more expensive and error prone overall than to concatenate the full name on the fly. Consider a view, or a function encapsulating the concatenation logic.

Related:

• Computed / calculated / virtual / derived columns in PostgreSQL
• Store common query as column?

Error when creating a generated column in Postgresql

As you don't expect the column to be updated, when the row is changed, you can define your own function that generates the number:

create function generate_acc_num(id int)
returns text
as
$$
  select to_char(current_date, 'YY')||id::text;
$$
language sql
immutable; --<< this is lying to Postgres!

Note that you should never use this function for any other purpose. Especially not as an index expression.

Then you can use that in a generated column:

CREATE TABLE Person 
(
  id  integer generated always as identity primary key,
  acc_num  text UNIQUE GENERATED ALWAYS AS (generate_acc_num(id)) STORED
);

Unable to make generated column in postgresql for Json data

The righthand side of the ->> operator should be a value. In this case, since it's a string, you need to surround it with single quotes ('):

create table json_tab2 (
    data jsonb,
    pname text generated always as (data ->> 'name') stored
    -- Here ---------------------------------^----^
);

Generate value from columns in Postgres

You need to provide the data type for the column as @Belayer commented.

And then you need to explicitly cast domain_name as text (or some varchar). Otherwise you'll get an error that the expression isn't immutable as @nbk commented. serial is translated to be basically an integer and for whatever reason implicit casts of an integer in concatenations are considered not immutable by the engine. We had that just recently here.

So overall, using the given types for the columns, you want something like:

CREATE TABLE public.some_data
             (user_name varchar NULL,
              domain_name serial NOT NULL,
              email text GENERATED ALWAYS AS (user_name || '@' || domain_name::text) STORED);

But it's a little weird that a domain name is a serial? Shouldn't that be a text or similar? Then you wouldn't need the cast of course.

Postgresql: Generated column as output from a function

It worked after making the function(ttt.getfunction) IMMUTABLE.

---

Related Topics