PostgreSQL: How to use REPLACE with dynamic replacement text

Question

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.

Greg Brown · Accepted Answer · 2019-12-12 17:36:16Z

1

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;

answered Dec 12, 2019 at 17:36

Greg Brown

1,3782 gold badges18 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL: How to use REPLACE with dynamic replacement text

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related