PostgreSQL where concat similar to MySQL-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

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')