1. Working with data types
Working with data types is straightforward in a database management system like PostgreSQL.
2. Working with data types
As said before, data types are attribute constraints and are therefore implemented for single columns of a table. They define the so-called "domain" of values in a column, that means, what form these values can take – and what not. Therefore, they also define what operations are possible with the values in the column, as you saw in the previous exercises. Of course, through this, consistent storage is enforced, so a street number will always be an actual number, and a postal code will always have no more than 6 digits, according to your conventions. This greatly helps with data quality.
3. The most common types
Here are the most common types in PostgreSQL. Note that these types are specific to PostgreSQL but appear in many other database management systems as well, and they mostly conform to the SQL standard. The "text" type allows characters strings of any length, while the "varchar" and "char" types specify a maximum number of characters, or a character string of fixed length, respectively. You'll use these two for your database. The "boolean" type allows for two boolean values, for example, "true" and "false" or "1" and "0", and for a third unknown value, expressed through "NULL".
4. The most common types (cont'd.)
Then there are various formats for date and time calculations, also with timezone support. "numeric" is a general type for any sort of numbers with arbitrary precision, while "integer" allows only whole numbers in a certain range. If that range is not enough for your numbers, there's also "bigint" for larger numbers.
5. Specifying types upon table creation
Here's an example of how types are specified upon table creation. Let's say the social security number, "ssn", should be stored as an integer as it only contains whole numbers. The name may be a string with a maximum of 64 characters, which might or might not be enough. The date of birth, "dob", is naturally stored as a date, while the average grade is a numeric value with a precision of 3 and a scale of 2, meaning that numbers with a total of three digits and two digits after the fractional point are allowed. Lastly, the information whether the tuition of the student was paid is, of course, a boolean one, as it can be either true or false.
6. Alter types after table creation
Altering types after table creation is also straightforward, just use the shown "ALTER TABLE ALTER COLUMN" statement. In this case, the maximum name length is extended to 128 characters.
Sometimes it may be necessary to truncate column values or transform them in any other way, so they fit with the new data type. Then you can use the "USING" keyword, and specify a transformation that should happen before the type is altered. Let's say you'd want to turn the "average_grade" column into an integer type. Normally, PostgreSQL would just keep the part of the number before the fractional point. With "USING", you can tell it to round the number to the nearest integer, for example.
7. Let's apply this!
Let's apply this to your database and add the proper types to your table columns.