Working with Data in PostgreSQL

When you create a table in PostgreSQL, you specify the type of data that you will store in each column. For example, if you are storing a customer name, you will want to store alphabetic characters. If you are storing a customer's birth date, you will want to store values that can be interpreted as dates. An account balance would be stored in a numeric column.

Every value in a PostgreSQL database is defined within a data type. Each data type has a name (NUMERIC, TIMESTAMP, CHARACTER, and so on) and a range of valid values. When you enter a value in PostgreSQL, the data that you supply must conform to the syntax required by the type. PostgreSQL defines a set of functions that can operate on each data type; you can also define your own functions. Every data type has a set of operators that can be used with values of that type. An operator is a symbol used to build up complex expressions from simple expressions. You're already familiar with arithmetic operators such as + (addition) and - (subtraction). An operator represents some sort of computation applied to one or more operands. For example, in the expression 5 + 3, + is the operator and 5 and 3 are the operands. Most operators require two operands, some require a single operand, and others can function in either context. An operator that works with two operands is called a binary operator. An operator that works with one operand is called a unary operator.

You can convert most values from one data type to another. I'll describe type conversion at the end of this chapter.

This chapter explores each of the data types built into a standard PostgreSQL distribution (yes, you can also define your own custom data types). For each type, I'll show you the range of valid values, the syntax required to enter a value of that type, and a list of operators that you can use with that type.

Each section includes a table showing which operators you can use with a specific data type. For example, in the discussion of character data types, you will see that the string concatenation operator (||) can be used to append one string value to the end of another string value. The operator table in that section shows that you use the string concatenation operator to join two CHARACTER values, two VARCHAR values, or two TEXT values. What the table does not show is that you can use the string concatenation operator to append an INTEGER value to the end of a VARCHAR. PostgreSQL automatically converts the INTEGER value into a string value and then applies the || operator. It's important to keep this point in mind as you read through this chapterthe operator tables don't show all possible combinations, only the combinations that don't require type conversion.

Later in this chapter, I'll give a brief description of the process that PostgreSQL uses to decide whether an operator (or function) is applicable, and if so, which values require automatic type conversion. For a detailed explanation of the process, see Chapter 5 of the PostgreSQL User's Guide that came with your copy of PostgreSQL.

Besides the operators listed in this section, PostgreSQL offers a huge selection of functions that you can call from within expressions. For a complete, up-to-date list of functions, see the PostgreSQL User's Guide.