Postgresql – Search on concatenate columns

postgresqlstring-searching

I have this :

CREATE TABLE users
(
    first_name varchar(60) NOT NULL, 
    last_name varchar(60) NOT NULL
);

INSERT INTO users
    (first_name, last_name)
VALUES
    ('Sylvia ', 'Hoeks'),
    ('Harrison', 'Ford');

I get the list like this 

SELECT first_name || ' ' || last_name AS user_full_name FROM users;

But I would like to be able to search on the column user_full_name. Is it possible without a subquerie or CTE (like below)? 

WITH users_list AS (
    SELECT first_name || ' ' || last_name AS user_full_name
    FROM users
)

SELECT * FROM users_list WHERE user_full_name ILIKE '%lvi%';

I tried with the first column !?

SELECT first_name || ' ' || last_name AS user_full_name FROM users WHERE 1 ILIKE '%lvi%';
/*
ERROR:  operator does not exist: integer ~~* unknown
LINE 1: ...|| last_name AS user_full_name FROM users WHERE 1 ILIKE '%lv...
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
*/

I would like to be able to do something like this.

SELECT first_name || ' ' || last_name AS user_full_name FROM users WHERE user_full_name ILIKE '%lvi%';

For the moment I do:

SELECT first_name || ' ' || last_name AS user_full_name FROM users WHERE first_name ILIKE '%lvi%' OR last_name ILIKE '%lvi%';

Fiddle if needed : https://www.db-fiddle.com/f/c3HANw8M9W7kSVeEwidpne/0

Best Answer

The usual way to do this, is to use a derived table:

select * 
from (
 select first_name, 
        last_name, 
        first_name || ' ' || last_name AS user_full_name
 from users
) t
where user_full_name ilike '%lvi%';

That is extremely basic and generic SQL and any obfuscation layer (aka "ORM") should be able to deal with that.

Other options you have:

1. Use a view

Create a view with the calculated function and tell your obfuscation layer to use that view.

2. Create a "virtual" column

Postgres doesn't really have calculated columns (like e.g. Oracle or SQL Server) but you can simulate them with a function:

create function user_full_name(p_row users)
  returns text
as 
$$
  select concat_ws(' ', p_row.first_name, p_row.last_name);
$$
language sql;

This can be used like this:

select u.user_full_name
from users u
where u.user_full_name ilike %lvi%'

Note that the prefix for the column name is required otherwise it does not work. I don't know if you can teach your obfuscation layer to create SQL like that.

3. Populate an additional column with a trigger

CREATE TABLE users
(
    first_name varchar(60) NOT NULL, 
    last_name varchar(60) NOT NULL,
    user_full_name text
);

create or replace function update_full_name() 
  returns trigger
as
$$
begin
  new.user_full_name := concat_ws(' ', new.first_name, new.last_name);
  return new;
end;
$$
language plpgsql;

create trigger update_full_name_trigger
  before update or insert on users
  for each row
  execute procedure update_full_name();

Then you can simply write:

select user_full_name
from users u
where user_full_name ilike %lvi%';

That is probably the solution that is the easiest to be integrated into an obfuscation layer.

Related Question