How to add a number to a slug?

Question

PostgreSQL

INSERT INTO merch(id, slug) VALUES ($1, $2)

id - integer;
slug -string;
It slugcan only be a unique value.

How to rewrite the query above so that if the added slug field is already in the database, then postgres adds slug-n instead of slug, and if there is slug-n, then slug-n + 1, etc. is not found yet unique value?

INSERT INTO merch(id, slug) VALUES (1324, 'my-url') // my-url есть в базе
INSERT INTO mech(id, slug) VALUES (1324, 'my-url-1') // my-url-1 есть в базе
INSERT INTO merch(id, slug) VALUES (1324, 'my-url-2') // добавлено

Answer 1

To do this, you need to write a trigger that will work on the repetition of the slug and change the value accordingly.
But this is "magic" and it is not recommended to do this, because after you the developers will not understand why this is happening, it's better to do it in PHP or through what you do inserts there.