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!
-
use concatenation signs ||Jeremy C.– Jeremy C.2015-06-10 11:59:09 +00:00Commented Jun 10, 2015 at 11:59
Add a comment
|
2 Answers
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
5 Comments
Георги Банков
Thanks for the useful answer, but I have another issue: it throws me an error: function repeat(unknown, bigint) does not exist.
Георги Банков
I am using PostgreSQL. The error says: No function matches the given name and argument types. You might need to add explicit type casts. I tried to add CAST to the function arguments, but the error remains.
Георги Банков
Ahh, I tried casting to numeric, instead of int and that was causing the error. Still pretty new new to PostgreSQL. Thanks and +1
Joe B.
plus 1 for casting to int
