Postgresql - How to round number down to nearest multiple of significance?

Im looking similar thing in PostgreSQL I can do in Excel using FLOOR function.

For example:

=FLOOR(199999;100000)

Will give me 100 000

I tried in pgsql:

SELECT round(199999 ,-5)

But this rounds number UP -> 200000.

2 Answers

demos:db<>fiddle

round() always rounds 0-4 down and 5-9 up, so you cannot achieve a floor round with this.

This would be be the way to do it.

SELECT floor(199999 / 100000) * 100000

which equals

SELECT floor(199999 / pow(10,5)) * pow(10,5)

With that you could write your own function:

CREATE OR REPLACE FUNCTION floor_precision(_value int, _precision int)
RETURNS integer AS $$
DECLARE rounded integer;
BEGIN SELECT floor(_value / pow(10, _precision)) * pow(10, _precision) INTO rounded; RETURN rounded;
END;
$$ LANGUAGE plpgsql;
SELECT floor_precision(199999, 5)

Alternatively you could to this iof you like to use the round() function with its second parameter:

SELECT round(199999 - 50000, -5)

which equals

SELECT round(199999 - (pow(10,5) / 2)::int, -5)

Naturally, you can create an own function here as well:

CREATE OR REPLACE FUNCTION floor_precision(_value int, _precision int)
RETURNS integer AS $$
DECLARE rounded integer;
BEGIN SELECT round(_value - (pow(10, _precision) / 2)::int, _precision * -1) INTO rounded; RETURN rounded;
END;
$$ LANGUAGE plpgsql;
SELECT floor_precision(199999, 5)

Second variant seems to be much faster according to the fiddle's execution plan.

Good function, but it can be taken a step further and create a SQL function.

create or replace
function floor_precision(_value int, _precision int) returns integer language sql immutable strict
as $$ select round(_value - (pow(10, _precision) / 2)::int, _precision * -1)::int;
$$;