PostgreSQL: How to use REPLACE with dynamic replacement text

I am trying to generate the string, download_url, for each row of the dataset table using REPLACE with a simple template string. The id of each row should be substituted into the template string.

Here’s what I’ve tried below. Given that id in the subquery is correlated with the outer query, I thought that this would work:

SELECT id,
       name,
       REPLACE('https://example.com/dataset/{id}/download/', '{id}', id) 
as download_url
FROM dataset;

but I am getting an error indicating no function exists:

[42883] ERROR: function replace(unknown, unknown, integer) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts.

Answer

I should have read the error message and hint properly!

The solution was to cast id as a string like so:

SELECT id,
       name,
       REPLACE('https://example.com/dataset/{id}/download/', '{id}', id::varchar) as download_url
FROM dataset;

Advertisement

Answer