Postgresql generated column fails when concating not null columns

Question

I have this table definition in pgAdmin4:

CREATE TABLE IF NOT EXISTS cdr_event
(
    id bigint primary key generated always as identity,
    start_time timestamptz NOT NULL DEFAULT now(),
    end_time timestamptz NULL,
    group_id VARCHAR(10) NOT NULL,
    environment VARCHAR(10) NOT NULL,     
    level VARCHAR(10) NOT NULL,           
    schema VARCHAR(30) NOT NULL,      
    instance INTEGER NOT NULL,            
    hive_instance_db_name VARCHAR(100) GENERATED ALWAYS AS (group_id||'_'||environment||'_'||level||'_'||schema||'_'||instance) STORED,
    hive_static_db_name VARCHAR(100) GENERATED ALWAYS AS (group_id||'_'||environment||'_'||level||'_'||schema) STORED,
);

this fails with

ERROR:  generation expression is not immutable
SQL state: 42P17

Why does postgres consider the concat mutable when the dependent columns are all NOT NULL? This thread suggests it should work

Is there anyway to create a concat-ed generated column without creating a custom concat function?

Thanks

Jim Jones · Accepted Answer · 2021-08-11T11:28:27.133

4

Try keeping the involved columns of the same type, e.g. casting instance to text should do the trick:

CREATE TABLE IF NOT EXISTS cdr_event
(
    id bigint primary key generated always as identity,
    start_time timestamptz NOT NULL DEFAULT now(),
    end_time timestamptz NULL,
    group_id VARCHAR(10) NOT NULL,
    environment VARCHAR(10) NOT NULL,     
    level VARCHAR(10) NOT NULL,           
    schema VARCHAR(30) NOT NULL,      
    instance INTEGER NOT NULL,            
    hive_instance_db_name VARCHAR(100) GENERATED ALWAYS AS (group_id||'_'||environment||'_'||level||'_'||schema||'_'||instance::text) STORED,
    hive_static_db_name VARCHAR(100) GENERATED ALWAYS AS (group_id||'_'||environment||'_'||level||'_'||schema) STORED
);

Consider using text instead of varchar.

Demo: db<>fiddle

edited Aug 11 '21 at 11:28

answered Aug 11 '21 at 11:09

Jim Jones

18,404
3
35
44

1

Only `instance` is the problem. And casting it to `text` will do. No need to cut off `schema`. I'm still wondering why the implicit cast would be considered not immutable though? – sticky bit Aug 11 '21 at 11:19
@stickybit that's right! I oversaw it was an integer .. thanks for pointing out. +1 just edited my answer – Jim Jones Aug 11 '21 at 11:23
@stickybit I'm still wondering the same thing and still searching for an explanation :D – Jim Jones Aug 11 '21 at 11:26
@JimJones thanks, that works. yeah i tested the concat worked as a select, using the implicit cast. Seems weird you need to cast it here. hey ho – crispo Aug 11 '21 at 16:03

Postgresql generated column fails when concating not null columns

1 Answers1

Linked