1. Column Types and Constraints
Now it's time to turn to the contents of individual columns: the data types and the constraints on what values can exist in each column.
2. Column constraints
Foreign keys and primary keys are two types of constraints that limit the values in a column, but columns can also be constrained in other ways.
Unique means that each value except NULL must be different from the values in all other rows. Not NULL means what it says - the column cannot contain null values.
Check constraints are a way of implementing additional conditions on the values of a column, such as requiring the column only contain positive values, or ensuring that the value of one column is greater than the value of another column.
3. Data types
Constraints can limit the values in a column, but the main thing that determines what values are allowed is the column's type. Each column in the database can only store one type of data.
In this course, we're talking about three of the most common types of data: numeric, character, and date/time. These three, along with boolean - which holds true or false values - are the most common types you'll encounter, but they're not the only ones.
There are also special data types to hold monetary values, geometric data like points or lines, and structured data types like XML and JSON. These special types differ more across database implementations than the four common ones.
4. Numeric types: PostgreSQL documentation
Within the broad categories of numeric, character, or date/time data,
there are multiple column types with different details. For example, different numeric types require different amounts of memory per row and can store different ranges of values.
In the upcoming chapters, we'll talk more about these specific types, so no need to worry about the details at this point.
5. Types in entity relationship diagrams
You can find the type of each column in the entity relationship diagram. Here is the fortune500 table.
There are three different numeric data types used in the table: integer, real, and numeric. Even if you don't have an entity relationship diagram, the column type is a core piece of information you can expect to find in other kinds of documentation.
6. Casting with CAST()
Values can be converted temporarily from one type to another through a process called casting. When you cast a column as a different type, the data is converted to the new type only for the current query.
To change a value's type, use the cast function, first, specify the value you want to cast. This can be a single value or the name of a column. Then use the keyword AS. Finally, specify the name of the type you want to convert the data to.
Here's an example of casting the single numeric value 3-point-7 as an integer. Casting from numeric to integer rounds the value to the nearest integer, which is 4.
To convert the type of an entire column, enter the name of the column as the value. Here, a column called total is converted to type integer. We need a from clause to specify which table the column comes from.
7. Casting with ::
There's an alternate notation for casting values: a double colon. It does the same thing as the cast function, but it's more compact.
Put the value to convert before the double colon and the type to cast it as after the double colon. The examples here are the same as those on the previous slide, except with the double colon notation instead of the cast function.
8. Time to practice!
OK, you know what time it is. Let's practice.