ALTER TABLE campaign
ADD COLUMN year INT GENERATED ALWAYS AS (
CASE
WHEN name ~ '^C\d+ \d{4}$'
THEN SUBSTRING(name, 4, 4)::INT
ELSE NULL
END
) STORED;
But the Create script only shows the the column as integer
CREATE TABLE IF NOT EXISTS public.campaign
(
id integer NOT NULL DEFAULT nextval('campaign_id_seq'::regclass),
... etc ...
year integer,
... etc ...
)
pg_dump shows the column with it generation function( amongst other stuff)
pg_dump -st table_name
CREATE TABLE public.campaign (
id integer NOT NULL,
name character varying(255) NOT NULL,
...
etc
...
year integer GENERATED ALWAYS AS (
CASE
WHEN ((name)::text ~ '^C\d+ \d{4}$'::text) THEN ("substring"((name)::text, 4, 4))::integer
ELSE NULL::integer
END) STORED
);
But the Create script only shows the the column as integer
pg_dump shows the column with it generation function( amongst other stuff)
pg_dump -st table_name