![]() VALUES( 'Phone', 123456.21) Code language: SQL (Structured Query Language) ( sql ) In case you store a value whose precision exceeds the declared precision, PostgreSQL will raise an error as shown in the following example: INSERT INTO products ( name, price) The following query returns all rows of the products table: SELECT * FROM products Code language: SQL (Structured Query Language) ( sql ) ( 'Tablet', 500.214) Code language: SQL (Structured Query Language) ( sql )īecause the scale of the price column is 2, PostgreSQL rounds the value 500.215 up to 500.22 and rounds the value 500.214 down to 500.21 : Second, insert some products with prices whose scales exceed the scale declared in the price column: INSERT INTO products ( name, price) ) Code language: SQL (Structured Query Language) ( sql ) If you store a value with a scale greater than the declared scale of the NUMERIC column, PostgreSQL will round the value to a specified number of fractional digits.įirst, create a new table named products for the demonstration: DROP TABLE IF EXISTS products Let’s take some example of using the PostgreSQL NUMERIC type. If precision is not required, you should not use the NUMERIC type because calculations on NUMERIC values are typically slower than integers, floats, and double precisions. In PostgreSQL, the NUMERIC and DECIMAL types are equivalent and both of them are also a part of SQL standard. NUMERIC Code language: SQL (Structured Query Language) ( sql ) If you omit both precision and scale, you can store any precision and scale up to the limit of the precision and scale mentioned above. The following shows the syntax of NUMERIC type with scale zero: NUMERIC(precision) Code language: SQL (Structured Query Language) ( sql ) ![]() The scale of the NUMERIC type can be zero or positive. The NUMERIC type can hold a value up to 131,072 digits before the decimal point 16,383 digits after the decimal point. ![]() For example, the number 1234.567 has the precision 7 and scale 3. In this syntax, the precision is the total number of digits and the scale is the number of digits in the fraction part. The following illustrate the syntax of the NUMERIC type: NUMERIC(precision, scale) Code language: SQL (Structured Query Language) ( sql ) Typically, you use the NUMERIC type for numbers that require exactness such as monetary amounts or quantities. The NUMERIC type can store numbers with a lot of digits. Introduction to PostgreSQL NUMERIC data type Summary: in this tutorial, you will learn about the PostgreSQL NUMERIC type for storing numeric data.
0 Comments
Leave a Reply. |