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-11 11:28:27Z

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, 2021 at 11:28

answered Aug 11, 2021 at 11:09

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

sticky bit Over a year ago

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?

Jim Jones Over a year ago

@stickybit that's right! I oversaw it was an integer .. thanks for pointing out. +1 just edited my answer

Jim Jones Over a year ago

@stickybit I'm still wondering the same thing and still searching for an explanation :D

crispo Over a year ago

@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

Collectives™ on Stack Overflow

Postgresql generated column fails when concating not null columns

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related