PostgreSQL where concat similar to MySQL

Question

I'm using the following query on my MySQL database:

SELECT * FROM users WHERE CONCAT(first_name, ' ', last_name) = 'John Doe'

I also have a PostgreSQL 8.4 database and i tried running the same query on that but get error that it does not recognise the CONCAT function.

Any ideas how I can achieve the same query on my postgresql ?

Thanks

Answer 1

You can use postgres' concatenation operator ||:

 SELECT * FROM users WHERE first_name || ' ' || last_name = 'John Doe'

Although you may find the following to be better suited for your condition to avoid doing expensive concatenations:

 SELECT * FROM users WHERE (first_name, last_name) = ('John', 'Doe')

Answer 2

8.4 is ridiculously obsolete and should not be used.

concat was not implemented until 9.1 (which is also very obsolete and also should not be used)

You can use the || operator, but keep in mind that they handle NULL values differently.