Generated columns in Postgres using a given pattern

Question

I need to generate a SQL query which can generate a column that follows a certain pattern

The pattern is stored like this Hello [Name] [lastname]

where name and lastname are both columns on the table.

How do i add a constant string at end, start and indbetween attributes?

and how do distinguish between something being a table column and something just being a string i have added that the column should contain?

My query currently looks like this - and pattern then take

ALTER TABLE IF public.nameRegistration
DROP COLUMN IF EXISTS generated_colum 
ADD COLUMN generated_colum TEXT generated ALWAYS as (|Pattern|) stored;

and pattern is just Hello Name lastname

How do you "generate the query" - can you show the code? Is the pattern stored in the database itself? — Bergi, Apr 24 '20 at 12:36

GMB · Accepted Answer · 2020-04-24T14:04:18.667

4

You would just do string concatenation:

ALTER TABLE ADD COLUMN 
    generated_colum TEXT generated ALWAYS 
    as ('Hello ' || name || ' ' || lastname) stored;

As commented by a_horse_with_no_name, in case any of the two column may be null you can use concat_ws():

ALTER TABLE ADD COLUMN 
    generated_colum TEXT generated ALWAYS 
    as (concat_ws(' ', 'Hello', name, lastname)) stored;

edited Apr 24 '20 at 14:04

answered Apr 24 '20 at 12:33

GMB

1

If you want to properly deal with NULL values, `concat_ws(' ', 'Hello', name, lastname)` might be better – Apr 24 '20 at 12:38

1 Answers1