Postgres Concatenation

Question

I'm trying to a simple concatenation in PostgreSQL and it keeps up throwing up an error message. I don't understand what I am doing wrong here.

select concat('abcde', 'fgh');
No function matches the given name and argument types. You might need to add explicit type casts.

select concat(cast('abcde' as text), cast('fgh' as text));
No function matches the given name and argument types. You might need to add explicit type casts.

I am using Postgres version 8.4.11. Please let me know what is going on.

Answer 1

The concat operator is ||, so select 'abcde' || 'fgh' should work. Also, as @jonathan.cone suggested, check out the docs.

Answer 2

concat was added in 9.1, it doesn't exist in 8.4. As others have noted, use the || operator.

Compare the 8.4 docs to the 9.1 docs and you'll notice that the concat function isn't present in the 8.4 docs.

See that bar at the top of the docs that says "This page in other versions" ? It's really handy when you're working with an old version, or if you find a link to an old version of a page via Google and you're on a newer version. Always make sure you're looking at the docs for the right version.

It'd be nice if the tables for functions etc included a "First appeared in version " - but unfortunately they don't.

If you're ever confused about the availablilty of a function, you can use \df in psql to list and search functions.

For example, to list all functions named concat use \df concat

For all functions in the pg_catalog schema (built-in functions) use \df pg_catalog. - note the trailing period, telling psql you mean "any function within the schema pg_catalog" not "the function named pg_catalog".

For all functions with names starting with concat use \df concat*

It's a very powerful tool. The same language works when searching for schema (\dn), tables (\dt), etc.