PostgreSQL – Does the || Operator Result in Blank if One Column is Blank

operatorpostgresql

I am running a query in Postgres using the || operator to concatenate some columns and it seems where one of the columns is blank the whole result is blank. Is that normal || behaviour?

With "blank" I mean NULL.

Best Answer

Depends what "blank" means, NULL or the empty string.

If it means NULL, then yes, that's normal. SELECT 'ab' || NULL ; will return null.
If it means an empty string, then no. SELECT 'ab' || '' ; will return 'ab'.

To avoid the issue, you can convert the nulls to empty string with COALESCE():

SELECT 
    COALESCE(str1, '') || COALESCE(str2, '') || ... || COALESCE(strN, '')

or use CONCAT() or CONCAT_WS() functions - which ignore nulls:

SELECT 
    CONCAT(str1, str2, ..., strN)

SELECT 
    CONCAT_WS('', str1, str2, ..., strN)

Related Solutions

Postgresql – Postgres full text search with multiple columns, why concat in index and not at runtime

No you don't need separate indexes. Use the weights feature. They are just a label your can query against. You can have up to four labels to query against (A-D).

--search any "field" for quick:
select 'quick:1A brown:2B quick:3C'::tsvector @@ 'quick'::tsquery; --true

--search B "field" for quick:
select 'quick:1A brown:2B quick:3C'::tsvector @@ 'quick:B'::tsquery; --false

--search B or C "fields" for quick:
select 'quick:1A brown:2B quick:3C'::tsvector @@ 'quick:BC'::tsquery; --true

You might want to concatenate tsvectors, so that you can separately apply weights to them and then put them together:

select
  setweight( name_column::tsvector, 'A') || setweight( phone_column::tsvector, 'B');

PostgreSQL – What Does the <-> Operator Do?

It's supposed to be the "distance" operator from the additional module pg_trgm.

The manual:

text <-> text real Returns the "distance" between the arguments, that is one minus the similarity() value.

The module has to be installed (once per database) with:

CREATE EXTENSION pg_trgm;

More details:

How is LIKE implemented?

Theoretically, any user with the necessary privileges could create an operator with that operator name using CREATE OPERATOR - but not in the example you link to.

Best Answer

Related Solutions

Postgresql – Postgres full text search with multiple columns, why concat in index and not at runtime

PostgreSQL – What Does the <-> Operator Do?

Related Question