The monetary data type is used to store currency data in the PostgreSQL database table. The money data type of PostgreSQL is used to store currency data with fixed fractional precision. The ic_monetary setting is used to determine the fractional precision. The size of the money data type is 8 bytes and the range of this data type is -92233720368547758.08 to +92233720368547758.07. The integer and floating data types can be used also to store currency values in the table of the PostgreSQL database. The output of this data type depends on the locale value. Different ways of defining and using monetary data types in the PostgreSQL database have been shown in this tutorial.
Pre-requisites:
You have to install the latest version of PostgreSQL packages on the Linux operating system before executing the SQL statements shown in this tutorial. Run the following commands to install and start the PostgreSQL:
$ sudo systemctl start postgresql.service
Run the following command to login to PostgreSQL with root permission:
Use of MONEY Data Type:
Before creating any table with the Boolean data type you have to create a PostgreSQL database. So, run the following command to create a database named ‘testdb’:
The following output will appear after creating the database:
Create a table named ‘products_1’ in the current database with three fields. The first field name is Id which is the primary key of the table. The value of this field will be incremented automatically when a new record will insert. The second field name is Name and the data type is VARCHAR (30). The third field name is Price and the data type is MONEY.
Id SERIAL PRIMARY KEY,
Name VARCHAR (30),
Price MONEY);
The following output will appear if the table is created successfully:
Run the following three INSERT queries that will insert three different values into the field of MONEY data type:
# INSERT INTO products_1(Name, Price) VALUES ('Pen', 2.50);
# INSERT INTO products_1(Name, Price) VALUES ('Water Color', 15.895634);
The following output will appear after executing the above queries:
Run the following SELECT query to read all records of the products_1 table:
The following output will appear after executing the above query. The output shows the default currency symbol in the field of MONEY data type.
Use of NUMERIC Data Type:
The NUMERIC data type is another data type to store currency value in the PostgreSQL table. Create a table named ‘products_2’ in the current database with three fields. The first field name is Id which is the primary key of the table. The value of this field will be incremented automatically when a new record will insert. The second field name is Name and the data type is VARCHAR (30). The third field name is Price and the data type is NUMERIC (5, 3).
Id SERIAL PRIMARY KEY,
Name VARCHAR (30),
Price NUMERIC (5, 3));
The following output will appear if the table is created successfully:
Run the following INSERT queries that will insert two records into the products_2 table:
# INSERT INTO products_2(Name, Price) VALUES ('Color Pencil ', 10.999999);
The following output will appear after executing the above queries:
The NUMERIC data type generates an error if the value exceeds the precision value of the data type. The following INSERT query will generate an error:
The following output shows the error generated by the NUMERIC field:
Any currency symbol can be added before the value of the NUMERIC field in the SELECT query. So, it is better to use the NUMERIC data type than the MONEY data type for storing currency data. Run the following SELECT query that will read all values of the products_2 table by adding ‘$’ before the Price field:
The following output will appear after executing the above query:
Use of FLOAT Data Type:
The float data type is another option to store currency data. But the float data type is not recommended for this purpose because the values are stored as an approximation in this data type. The use of this data type has been shown here for storing currency value.
Create a table named ‘products_3’ in the current database with three fields. The first field name is Id which is the primary key of the table. The value of this field will be incremented automatically when a new record will insert. The second field name is Name and the data type is VARCHAR (30). The third field name is Price and the data type is FLOAT.
Id SERIAL PRIMARY KEY,
Name VARCHAR (30),
Price FLOAT);
The following output will appear if the table is created successfully:
Run the following INSERT queries that will insert two values into the field of FLOAT data type:
# INSERT INTO products_3(Name, Price) VALUES ('Samsung Printer', 180.4578);
# INSERT INTO products_3(Name, Price) VALUES ('Dell Printer', 320.56);
The following output will appear after executing the above queries.
Run the following SELECT query to read all records of the products_3 table:
The following output will appear after executing the above query:
Conclusion:
The uses of different data types to store currency data in the PostgreSQL table have been shown in this tutorial by using multiple tables. MONEY, NUMERIC, and FLOAT data types have been used to store the currency data and the advantages and disadvantages of each data type have been explained here.
