I am currently working on a project involving historical financial data in the UK, specifically using the pounds-shillings-pence system. I am uncertain about the best PostgreSQL data type to represent this type of currency. Despite searching on StackOverflow, I have not found a post containing all three terms "Pounds," "Shillings," and "Pence."
In my application, the input comprises character strings like £57 19s 7d, £8 1s, and £30. My goal is to perform summations of money values and multiplications of money values by dimensionless numbers. Ideally, I would prefer the result back in £sd notation, but I am also open to a floating-point representation.
My initial attempt using the MONEY data type resulted in an error:
CREATE TABLE test_table (price MONEY);
INSERT INTO test_table VALUES ('£30');
The error received was invalid input syntax for type money: "£30". Is importing the data as a character string and then parsing it into a numeric type the most sensible option? If so, what is the best way to accomplish this? I am currently using PostgreSQL 16.1 on MacOS 14.2.1.
Any guidance or recommendations on handling historical currencies in PostgreSQL would be greatly appreciated. Thank you!