Postgresql. Create table with column that concatenate values from other columns

Question

I have create table script in MSSQL:

CREATE TABLE [dbo].[Address](
    [Id] [int] IDENTITY(1,1) NOT NULL,
        [Street] [nvarchar](100) NOT NULL,
    [BuildingNumber] [nvarchar](15) NULL,
    [ApartmentNumber] [nvarchar](15) NULL,
        [City] [nvarchar](100) NOT NULL,
    [Country] [nvarchar](250) NULL,
        [Postcode] [nvarchar](15) NULL,
    **[Search]  AS (((((((([Street]+' ')+[BuildingNumber])+' ')+isnull([ApartmentNumber],''))+' ')+[Postcode])+' ')+[City]) PERSISTED,**
 CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

How can I create "Search" column with values from other columns in PostgreSQL? I tried to set DEFAULT value, but Postgresql didn't let mi create Default value with column names.

according to this - http://stackoverflow.com/questions/8250389/computed-calculated-columns-in-postgresql, PostgreSQL doesn't support computed columns — Roman Pekar, Nov 08 '13 at 08:24
You can use: 1) trigger on Insert or Update to compute `Search` yourself; or 2) use a view to get the `Search` column every time you need it in a query. See also the accepted answer in the @Roman Pekar link. — neutrino, Nov 08 '13 at 09:06

score 2 · Answer 1 · answered Dec 07 '13 at 09:09

One often overlooked way to do this is to use a table method. See the caveats below.

Note, I am converting the id to a serial type since that is the closest equivalent. I will leave it up to you which, if any, fields to change to TEXT type in postgreSQL.

CREATE TABLE dbo.address(
    id serial PRIMARY KEY,
    street varchar(100) NOT NULL,
    building_number varchar(15) NULL,
    apartment_number varchar(15) NULL,
    city varchar(100) NOT NULL,
    country varchar(250) NULL,
    postcode varchar(15) NULL
);

CREATE OR REPLACE FUNCTION search(dbo.address) LANGUAGE SQL RETURNS text AS
$$
select $1.street || ' ' || coalesece($1.building_number, '') || ' ' || 
       coalesce($1.apartment_number,'') || ' ' || coalesce($1.postcode, '') 
       || ' ' || $1.city
$$ IMMUTABLE;

Note this function on a row provided as an argument, not on the underlying table. It can then be marked immutable since it only depends on its arguments.

If we want to persist it we then use a functional index:

CREATE INDEX address_search_idx ON dbo.address(search(address));

We can then query it using a syntax similar to (but not quite identical to) the column syntax:

SELECT * FROM dbo.address a WHERE a.search like '313 Baker St%';

Note that you cannot omit the a. from a.search since this is changed to search(a) by the parser and the parser is not going to guess which relations you might have meant.

So it's not quite a calculated column but it is very close, to the point of being usable as if it was one.

Postgresql. Create table with column that concatenate values from other columns

1 Answers1