Data Types

The numeric type

The numeric (also known as decimal) type is a specially designed numeric data type which can represent arbitrarily large and precise values, within a fixed definition. When you create a table with a column of type numeric, you may specify in parentheses two values: the precision, and the scale.

The precision is the maximum number of sequential digits that the numeric value may hold (including digits to the right of the decimal point), while the scale describes how many of those digits of precision are to be to the right of the decimal point. If left unspecified, the precision will default to 30, and the scale will default to 6.

Unlike the floating point data types, however, the numeric type will cause an overflow error if digits are specified outside of its precision range. You may somewhat safely specify non-zero digits which are smaller than the scale of the numeric definition, but only provided that the entirety of the numeric input fits within the maximum precision defined (though the value may be rounded).

For example, in a numeric(11,6) column, you may safely insert the value 9.9999999 with two digits too many to the right (though the value is rounded up to 10.000000). However, an attempt to insert the value 99999.99999999 will fail, as shown in Example 3-24.

This problem can be explicitly avoided by using the trunc() numeric truncating function, which is illustrated in Example 3-24. Used consistently, it also can keep unwanted rounding from happening by forcing an appropriate scale on the data before attempting to insert.

booktown=# INSERT INTO numbers VALUES (9.99999999);
INSERT 3390697 1
booktown=# SELECT * FROM numbers;
    number
--------------
    10.000000
(1 row)

booktown=# INSERT INTO numbers VALUES (99999.99999999);
ERROR:  overflow on numeric ABS(value) >= 10^5 for field with precision 11 scale 6
booktown=# INSERT INTO numbers VALUES (trunc(99999.99999999, 6));
INSERT 3390698 1
booktown=# SELECT * FROM numbers;
    number
--------------
    10.000000
 99999.999999
(2 rows)
 
booktown=# INSERT INTO numbers VALUES (trunc(9.99999999, 6));
INSERT 3390699 1
booktown=# SELECT * FROM numbers;
    number
--------------
    10.000000
 99999.999999
     9.999999
(3 rows)

The monetary type

The money type is an automatically translated numeric type that formats to and from US-style currency notation, and plain numeric values. As of the writing of this book, the money type is deprecated, and is discouraged from being actively used. It is only presented here as it is still a functional data type, and may be in use on existing PostgreSQL systems.

The suggested alternative to the money type is the numeric type, with a scale of 2 to represent coin values, and a precision large enough to store the largest necessary monetary value (including two digits for the coin precision). Formatting similar to that of the money type can be achieved with the to_char function, as shown in Example 3-25. This example demonstrates the text concatentation operator covered in the section called Character and Text Operators in Chapter 4, and the ltrim() text formatting function, described in Chapter 4.

booktown=# INSERT INTO money_example VALUES ('$12.24', 12.24);
INSERT 3391095 1
booktown=# SELECT * FROM money_example;
 money_cash | numeric_cash
------------+--------------
     $12.24 |        12.24
(1 row)
 
booktown=# SELECT money_cash, 
booktown-#        '$' || ltrim(to_char(numeric_cash, '9999.99')) 
booktown-#        AS numeric_cashified 
booktown-#        FROM money_example;
 money_cash | numeric_cashified
------------+-------------------
     $12.24 | $12.24
(1 row)

The serial type

The serial type is non-standard, but useful, shortcut which allows you to easily create unique identifier columns within tables. It literally combines the functionality of a 4-byte integer data type, an index and a sequence.

In fact, as of the writing of this book, the two methods illustrated in Example 3-26 are functionally identical.

booktown=# -- First, implicitly create the sequence 
booktown=# -- and index via the serial type.
booktown=# 
booktown=# CREATE TABLE auto_identified (id serial);
NOTICE:  CREATE TABLE will create implicit sequence 'auto_identified_id_seq' for SERIAL column 'auto_identified.id'
NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'auto_identified_id_key' for table 'auto_identified'
CREATE
booktown=# -- Remove the table, and clean up the sequence behind it.
booktown=# 
booktown=# DROP TABLE auto_identified;
DROP
booktown=# DROP SEQUENCE auto_identified_id_seq;
DROP
booktown=# -- Re-create the serial type's parts manually.
booktown=# 
booktown=# CREATE SEQUENCE auto_identified_id_seq;
CREATE
booktown=# CREATE TABLE auto_identified 
booktown-# (id integer UNIQUE DEFAULT nextval('auto_identified_id_seq'));
NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'auto_identified_id_key' for table 'auto_identified'
CREATE

	Caution with Implicit Sequences
	Upon dropping a table, the implicit sequence created for the serial types are not automatically dropped. You must clean up after these types of sequences if you destroy a table which had a serial column, as shown in Example 3-26, with the DROP SEQUENCE command.

See Chapter 5 for more information on using sequences.

Backward Compatibility

To ensure compatibility with earlier versions of PostgreSQL, the developers have continued to provide the older datetime and timespan data types. The datetime type is now equivalent to timestamp, while the timespan is now equivalent to the interval types.

Other date/time data types include abstime and reltime, which are lower precision types. However, these types are internal to PostgreSQL, and any or all of these types may disappear in a future release. It is advised therefore to design new applications with the SQL-compliant data types in mind, and to convert older applications from any of these data types as soon as is possible.

Date Conventions

Date input can be accepted by PostgreSQL in many common formats, including the ISO-8601 format, the traditional SQL format, the original PostgreSQL format, and more. Table 3-15 lists several of these date formats.

These formats are relevant to the date and the timestamp data types.

When specifying a named month in a date value to PostgreSQL, you may either type the complete month name, or choose from a set of defined abbreviations for each month. These abbreviations are listed in Table 3-16.

Similarly, Table 3-17 lists PostgreSQL-recognized abbreviations for weekday names.

Despite the wide variety of ways in which PostgreSQL can interpret date values, the values are always stored uniformally, and will be returned in a consistent format. As such, you have a variety of methods available to you to customize the default behavior with which date and time values are returned to you.

	On Formatting
	While date values can always be formatted after being selected via several formatting functions (e.g., to_char()), it is more efficient to configure your defaults as close to the most commonly used conventions as you can before having to resort to manual type conversion and text formatting.

To set the general date/time output format, the SET command can be applied to the run-time variable DATESTYLE. This variable may be set to one of four available general styles shown in Table 3-18.

As an example, you can use the following SQL statement to set the date style to SQL:

booktown=# SET DATESTYLE TO SQL;
SET VARIABLE

If you perform a SELECT current_timestamp query after setting this variable, PostgreSQL should return the current time using the ISO format as instructed:

booktown=# SELECT current_timestamp;
         timestamp
----------------------------
 08/10/2001 13:25:55.00 PDT
(1 row)

	Displaying Run-time DATESTYLE
	The SHOW command may be used to display this run-time variable at any time. booktown=# SHOW DATESTYLE; NOTICE: DateStyle is SQL with US (NonEuropean) conventions SHOW VARIABLE

In addition to these general formats, PostgreSQL's date output format has two other variants which further describe how to display the date, shown in Table 3-19: European and non-European (US). These determine whether the format is month followed by day, or vice versa. This variation can be applied on top of the previous four general formats with the same syntax to SET DATESTYLE, and will not modify your chosen format except for the arrangement of the month and day.

Furthermore, you may set both the general format and day/month convention by supplying both variables to the SET command, comma delimited. The order of these variables is not important to the SET command as long as the variables are not mutually exclusive (e.g., SQL and ISO), as shown in Example 3-27.

booktown=# SET DATESTYLE TO ISO,US;
SET VARIABLE
booktown=# SHOW DATESTYLE;
NOTICE:  DateStyle is ISO with US (NonEuropean) conventions
SHOW VARIABLE
booktown=# SET DATESTYLE TO NONEUROPEAN, GERMAN;
SET VARIABLE
booktown=# SHOW DATESTYLE;
NOTICE:  DateStyle is German with European conventions
SHOW VARIABLE

If you do not specify a month/day format, a reasonable default will usually be chosen (e.g., European is the default for the German regional format).

While this is a convenient way to set the output format, it is important to notice that this is a run-time variable, which means that it exists only for the lifespan of your connected session to PostgreSQL when configured with the SET DATESTYLE command. There are two other ways to configure this format.

• You may change the PGDATESTYLE environment variable used by the backend directly on postmaster's start-up (e.g., adding a Bash export PGDATESTYLE="FORMAT" variable setting to the postgres user's .bash_profile file). This has the effect of being global to all date and time formatting performed by PostgreSQL.

• You may change the PGDATESTYLE environment variable used by a client application (assuming it was written with the libpq library) on its session start-up, if you wish the client rather than the server to configure the output. For example, setting the PGDATESTYLE variable at a Bash prompt with the export command before starting psql sets the format such that psql will recognize it, and automatically configure itself for that style of output.

Time Conventions

Time values, like date values, may be entered in to a table in a number of ways. Commonly used formats are listed in Table 3-20. These apply to values of type time, and time with time zone.

In addition to these formats, PostgreSQL allows for further description of a time value which is defined as time with time zone by supporting extra time zone parameters following the time value. The supported formats are illustrated in Table 3-21.

The time with time zone data type is mainly supported by PostgreSQL to adhere to existing SQL standards, and for portability with other database management systems. If you need to work with time zones, it is recommended that you use the timestamp data type discussed in the section called Timestamps. This is primarily because of the fact that, due to daylight savings, time zones cannot always be meaningfully interpretted without an associated date.

Internally, PostgreSQL keeps track of all time zone information as a numeric offset of GMT (Greenwich Mean Time), which is also known as UTC (Universal Coordinated Time). By default, PostgreSQL's time display will use the time zone that your server's operating system is configured for. If you wish the time value to operate under a different time zone, there are four ways in which you can modify the output.

	Time Zone Caveats
	If an invalid time zone is specified, then most systems will default to the GMT time zone. Additionally, if the compiler option USE_AUSTRALIAN_RULES was set when PostgreSQL was built, the EST time zone will refer to Australian Eastern Standard Time( with an offset of +10:00 hours from GMT) rather than US Eastern Standard Time.

Timestamps

The PostgreSQL timestamp combines the functionality of the PostgreSQL date and time types into a single data type. The syntax of a timestamp value consists of a valid date format, followed by at least one whitespace character, and a valid time format. It can be followed optionally by a time zone value, if specified.

Combinations of all date and time formats listed in Table 3-15 and Table 3-20 are each supported in this fashion. Table 3-22 illustrates some examples of valid timestamp input.

	Time Zones and Timestamps
	While PostgreSQL supports the syntax of creating a column or value with the type timestamp without time zone, as of PostgreSQL 7.1.2 the resultant data type still contains a time zone.

Intervals

The SQL92 standard specifies a data typed called an interval, which represents a fixed span of time. By itself, an interval represents only a quantity of time, and does not begin or end at any set date or time. These intervals can be useful when applied to date and time values to calculate a new date or time, either by subtracting or adding the quantity. They can also be handy for quickly determining the precise interval between two date or time values. This can be achieved by subtracting date values, time values or timestamps from one another.

The two syntax variations in Table 3-23 can specify an interval within PostgreSQL:

booktown=# SELECT date('1980-06-25');
    date
------------
 1980-06-25
(1 row)

booktown=# SELECT interval('21 years 8 days');
    interval
-----------------
 21 years 8 days
(1 row)

booktown=# SELECT date('1980-06-25') + interval('21 years 8 days') 
booktown-# AS spanned_date;
      spanned_date
------------------------
 2001-07-03 00:00:00-07
(1 row)
 
booktown=# SELECT date('1980-06-25') - interval('21 years 8 days ago') 
booktown-# AS twice_inverted_interval_date;
 twice_inverted_interval_date
------------------------------
 2001-07-03 00:00:00-07
(1 row)

Example 3-28 shows functional syntax for date and interval values being meaningfully combined. You can see that subtracting an inverted time interval (e.g., one with the term ago) is functionally identical to adding a normal interval. This can be thought of as similar to the effect of adding negative numbers to integer values.

Built-in date and time constants

PostgreSQL provides three built-in functions for retreiving the current time, date, and timestamp. These are aptly named current_date, current_time, and current_timestamp.

In addition to these functions, there are many special constants which are supported by PostgreSQL. These constants represent common date/time values, such as now, tomorrow, and yesterday. The predefined date and time constants supported by PostgreSQL are listed in Table 3-24.

The now and current timestamp constants may seem to be identical, looking solely at their names. They are, however, very different in terms of storing them in a table. The now constant is translated into the timestamp of the relevant transaction time (e.g., the time of insertion of an INSERT statement). In contrast, the current constant, as it is a deferred identifier, will actually appear as the phrase 'current' in the database if inserted. From there, it can be translated (e.g., via the to_char() function) to the timestamp associated with the transaction time of any query which requests that value. In other words, current will always tell you the "current" time when queried, regardless of when it was stored to the table.

Therefore, you generally always want to use now when storing a transaction timestamp in a table, or even the current_timestamp function, which is equivalent to the output of now. Example 3-29 shows how this could be a potentially disastrous SQL design issue if not properly understood. It shows a pair of INSERT statements; one which uses now, another which uses current.

booktown=# INSERT INTO shipments (customer_id, isbn, ship_date) 
booktown-#        VALUES (1, '039480001X', 'current');
INSERT 3391221 1
booktown=# INSERT INTO shipments (customer_id, isbn, ship_date) 
booktown-#        VALUES (2, '0394800753', 'now');
INSERT 3391222 1
booktown=# SELECT isbn, ship_date FROM shipments;
    isbn    |       ship_date
------------+------------------------
 039480001X | current
 0394800753 | 2001-08-10 18:17:49-07
(2 rows)

booktown=# SELECT isbn, 
booktown-#        to_char(ship_date, 'YYYY-MM-DD HH24:MI:SS') 
booktown-#        AS value
booktown-#   FROM shipments;
    isbn    |        value
------------+---------------------
 039480001X | 2001-08-10 18:21:22
 0394800753 | 2001-08-10 18:17:49
(2 rows)

booktown=# SELECT isbn, to_char(ship_date, 'YYYY-MM-DD HH24:MI:SS') AS value
booktown-#   FROM shipments;
    isbn    |        value
------------+---------------------
 039480001X | 2001-08-10 18:22:35
 0394800753 | 2001-08-10 18:17:49
(2 rows)

Arrays in tables

When defining an array, the syntax allows for the array to be defined either as fixed-length or variable-length, however as of PostgreSQL 7.1.2, the fixed-length size restriction is not enforced. This means that you may treat the array as having a fixed number of elements at all times, but it can still be dynamically sized depending on the context in which it is treated. For example, it is perfectly acceptable for a single column defined as an array to contain three values in one record, four values in another, and no values in a third.

Additionally, arrays may be defined as being multi-dimensional, meaning that each element of the array may actually represent another array, rather than an atomic value. Values which are selected from a multi-dimensional array will consist of nested curly braces in order to show an array within an array.

booktown=# SELECT editions FROM my_notes WHERE title='The Cat in the Hat';
                           editions
---------------------------------------------------------------
 {{"039480001X","1st Ed, Hard Cover"},{"0394900014","1st Ed"}}
(1 row)

Array constants

In order to actually insert array values into a table column, you of course need a way to refer to several values as an array in a SQL statement. The formal syntax of an array constant is a grouping of values, separated by delimiters (commas, for built-in data types), constrained by curly braces ({}), which are in turn constrained by single quotes.

 '{value1 , value2 , [...] }' 

The values in this syntax can be any valid PostgreSQL data type. As the entire array is constrained by single quotes, the use of single quotes within an array value must be escaped, just as they must be within a string constant. The use of commas to delimit the values, however, poses an interesting problem pertaining to the use of character strings which contain commas themselves, as the commas will be interpreted as delimiters if not within single-quotes. However, as just mentioned, the singles quotes constrain the array, not the array's values.

PostgreSQL's method of handling this is to use double-quotes to quote string constants where single-quotes would ordinarily be used outside of an array context.

 '{"value1" , "value 2, which contains a comma" }'

It's vital to remember that arrays require the single quotes surrounding the curly braces in order to be interpretted correctly by PostgreSQL. You can think of array constants as being akin to a special type of string constant, which is interpretted as an array based on where it is used (e.g., when used to add records to a target column which is of an array data type). This is because, unless used in an array context, a constant of the this format will be interpretted by PostgreSQL as a normal string constant (as it is bound by single quotes) which just happens to include curly braces.