Ulitization IS DISTINCT FROM - Postgres

Question

Ulitization IS DISTINCT FROM - Postgres

Navigation

#1 by (2 votes)
#2 by (1 votes)

0

What is the best use of this function in Postgres IS DISTINCT FROM , doing tests got the same result using COALESCE but in less time, follow the test:

SELECT COUNT(P.id)

FROM produto P
  INNER JOIN cliente CL ON P.id_cliente = CL.id_cliente

WHERE 
  COALESCE(CL.tp_pessoa,'') <> 'JURIDICA' -- teste com COALESCE, média de 610 ms

  (CL.tp_pessoa <> 'JURIDICA' OR CL.tp_pessoa IS NULL) -- teste com OR, média de 668 ms

  CL.tp_pessoa IS DISTINCT FROM 'JURIDICA' -- teste com IS DISTINCT FROM, média de 667 ms

  OUTRO TESTE:

  COALESCE(CL.tp_pessoa,'') <> COALESCE(P.observacao,'') -- teste com IS DISTINCT FROM, média de 940 ms

  CL.tp_pessoa IS DISTINCT FROM P.observacao -- teste com '''IS DISTINCT FROM''', média de 930 ms, aqui teve uma leve vantagem da função

In addition to the lower performance, this is a function that is not found in other banks like SQL Server , another reason not to use it.

By doing another test, where both criteria can be NULL , IS DISTINCT FROM had a slight advantage, would this be its use, where else does it apply?

sql postgresql

asked by anonymous 20.11.2015 / 14:11

2 answers

2

The IS DISTINCT FROM comparison returns true when the data is different, the difference is that it treats null as a known value.

It can be used whenever you want to compare values and if you want to ignore null values.

For more details, see Postgres documentation .

23.11.2015 / 14:52

How to create an array with object from an SQL query PHP code is not recognized on the page

score 1 · Accepted Answer

The main reason for using IS DISTINCT FROM is the use of column indexes, this function can use them to gain performance while COALESCE can not.

This is not a valid example:

COALESCE(CL.tp_pessoa,'') <> COALESCE(P.observacao,'') -- teste com IS DISTINCT FROM, média de 940 ms

CL.tp_pessoa IS DISTINCT FROM P.observacao

The COALESCE turns null into '', so null = '', already for IS DISTINCT FROM null < > '', then the result is not always the same, it's worth more what you want as a return on verification.