使用给定模式在Postgres中生成的列

我需要生成一个SQL查询,该查询可以生成遵循某种模式的列

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

where name and lastname are both columns on the table.

如何在结尾,开始和中间属性之间添加常量字符串?

以及如何区分是表列的东西和仅仅是列中应该包含的字符串的东西?

我的查询当前看起来像这样-然后使用模式

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

评论
  • aumujun
    aumujun 回复

    您只需执行字符串连接:

    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))