current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

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.

share|improve this question
1  
according to this - stackoverflow.com/questions/8250389/…, PostgreSQL doesn't support computed columns –  Roman Pekar Nov 8 '13 at 8: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 8 '13 at 9:06

1 Answer 1

up vote 1 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.