Defining numeric data columns
1. Defining numeric data columns
Now that we have seen how to represent text data in our database, let's move on to the representation of numeric data.2. Numeric data with discrete values
Imagine we are creating a database for a local retail clothing store. We will likely be interested in having a representation for the store's employees. We can store details about the store's employees in a table named "employee" as part of a "people" schema. A definition of the table is included here with some basic columns for the employees. What if we wanted to also track the number of sales generated by an employee?3. Numeric data with discrete values
This can be done by including a "num underscore sales" column when creating the employee table. We do not anticipate awarding half of a sale to an employee so a discrete value is appropriate here.4. Integer types
PostgreSQL provides other options for representing discrete numerical values which differ in the size of the range of values which each type can represent. The SMALLINT type would be useful in the case that we wanted to store a person's age in whole numbers due to the small range of possible values.5. Integer types
We used the INTEGER type previously to represent the number of sales in our employee table. The INTEGER type would also be appropriate for a table column representing the population of large cities.6. Integer types
The BIGINT type could be used to represent the number of credit card transactions processed in a year for VISA or MasterCard.7. Integer types
SERIAL is a positive integer type that increments by one each time a new record is added to a table making it a good choice for representing PRIMARY KEYs.8. Integer types
The BIGSERIAL type would be a good choice for a table tracking NYC subway rides in a given year assigning a unique id to each trip taken.9. Numeric data with continuous values
Floating-point (aka decimal valued) columns are also available in PostgreSQL. Let's revisit the employee table from earlier in this lesson.10. Numeric data with continuous values
If we wanted to track the salary of each employee, we can use the decimal type to represent salary data in our table. The decimal type can take two arguments: precision and scale. Precision is the total number of digits in the number before and after the decimal point. Scale is the number of digits to the right of the decimal point. In this table, we limit the column entries to 8 digits of precision and 2 digits of scale. This assumes we won't have anyone making 1 million dollars or more at our small shop.11. Floating-point types
The decimal and numeric types are interchangeable. These types allow for a wide-range of floating-point values to be stored in a table as can be seen based on the range description.12. Floating-point types
The real type guarantees 6 decimals of precision.13. Floating-point types
The double precision type is similar but guarantees 15 digits of precision. Real and double precision are best-suited for columns where it is desirable to reduce storage space and it is ok that the number that is inserted into the database is not exactly equal to the number that is returned by a query. Use numeric or decimal in most cases.14. Let's practice!
Now it's your turn to think through scenarios and determine the best numeric type to use based on the data which you would like to represent in your database!Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.