SELECT CONCAT alternative in PostgreSQL

Question

How can I write the following MySQL query in PostgreSQL syntax:

SELECT CONCAT(REPEAT("   ", (COUNT(parent.name) - 1)), cat.name) AS name

Thanks in advance!

Answer 1

The error here is that PostgreSQL doesn't allow double quotes " for literal strings (as per the SQL standard). You'll need to use single quotes '.

You also need to cast (COUNT(parent.name) - 1) to an integer, either using ::int (specific to Postgre) or CAST(... AS int).
Note that this may raise an integer out of range error if (COUNT(parent.name) - 1) is superior to 2147483647.

SELECT CONCAT(REPEAT('   ', (COUNT(parent.name) - 1)::int), cat.name) AS name

Note that you can also use || for string concatenation :

SELECT REPEAT('   ', (COUNT(parent.name) - 1)::int) || cat.name AS name

Answer 2

SELECT REPEAT("   ", (COUNT(parent.name) - 1)) || cat.name AS name

SQLFIDDLE