In PostgreSQL I would like to achieve something similar to mysql query:
select price, format(price,2,'pl_PL') as pricePL from table
*************************** 1. row ***************************
price: 0.010000
pricePL: 0,01
In PostgreSQL I'm storing price as numeric(10,6)
In order to display numbers in two different formats, you're going to have to treat one of them as a string. I presume that if you could set your locale to Poland, the strings would automatically be formatted in the Polish fashion.
I'm going to assume that you can't do this for whatever reason, so here are a couple of ideas - all of the code below is available on the fiddle here.
First create a sample table:
CREATE TABLE price
(
pr NUMERIC NOT NULL
);
Populate it:
INSERT INTO price VALUES (0.01000000), (0.1), (2.4);
And then we run the following SQL:
SELECT
ROUND(pr, 2) AS price,
REPLACE(ROUND(pr, 2)::TEXT, '.', ',') AS "price_PL"
FROM
price;
Result:
price price_PL
0.01 0,01
0.1 0,1
2.4 2,4
We make use of the ROUND() (manual) function and also the REPLACE() (manual) function to "coerce" the pr field to appear as we want it - to 2 places. We use PostgreSQL's CAST operator - double colon (::) to turn pr into TEXT and then we REPLACE() the full stop (aka period, dot) with a comma - i.e. Polish format for currency.
Now, you could also do the following if you don't want to perform this calculation every time you want to calculate the Polish price.
CREATE TABLE price
(
pr NUMERIC NOT NULL,
pr_PL TEXT
GENERATED ALWAYS AS (REPLACE(ROUND(pr, 2)::TEXT, '.', ',')) STORED
);
It's populated as before.
SELECT * FROM price;
Result:
pr pr_pl
0.01000000 0,01
0.1 0,10
2.4 2,40
We'll now run the same formatting SQL as before, but with a bit extra. To see a little bit better what's going on, we can use PostgreSQL's very handy pg_typeof() function (manual) by running this:
SELECT
ROUND(pr, 2),
pr_pl,
pg_typeof(ROUND(pr, 2)) AS pr_type,
pg_typeof(pr_pl) AS "pr_PL_type"
FROM
price;
Result:
pr pr_pl pr_type pr_PL_type
0.01 0,01 numeric text
0.1 0,1 numeric text
2.4 2,4 numeric text
I'd say this is as close as you'll get to the MySQL functionality you outlined in your question. p.s. welcome to dba.se!
