Implementation-Specific Data Types

As mentioned above, implementations of SQL tend to have their own custom data types. These are typically provided for “ease of use” purposes, but some are altogether quite different from the standard SQL data types.

“Ease of Use” Data Types

An example of a non-standard “ease of use” data type is PostgreSQL’s text data type, which may contain a text string of any length, without having to worry too much about upper limits on string size. Unlike character(n) and character varying(n), no maximum length n may be specified. The true maximum length of a text field is limited only by the resources, like memory and disk space, available to the database server. This is a very common extension to the standard data types.

Other “ease of use” data types commonly implemented in database systems define objects such as eight-byte (64-bit) integers, network addresses, automatically-incrementing serial numbers, and currency types.

Other Non-Standard Data Types

Some of the less common non-standard data types are exemplified by PostgreSQL’s “geometric types” data types. These include point, line, circle, and others, which define geometric shapes. PostgreSQL also defines functions that are able to scale, translate, rotate, and intersect these objects. Figure 5 shows an example of one of these types.

In Figure 5, we test two line segment (lseg) intersections with the ?#, or intersection, operator.

In the first query, two temporary line segments are created: one from (0, 10) to (0, -10) and the other from (-10, 0) to (10, 0) on the plane. Intuitively, we know these two line segments run along the Cartesian x and y axes, and they intersect at (0, 0). The ?# operator returns t, or true, meaning the line segments do, in fact, intersect.

The second query attempts to intersect a line segment with endpoints (0, 10) and (0, -10) with another line segment with endpoints (-10, 11) and (10, 11). We can visualize this example and determine the lines do not intersect. At their closest proximity, they are still a distance of 1 apart. The query returns f, or false, and confirms this.

PostgreSQL and other database systems provide many non-standard data types and the operators and functions used to manipulate them. A comprehensive list of these is not practical to include in an article such as this. The best way to research these non-standard data types is to reference each database system’s manual.

Type Casting

When a value is used as something other than its normal data type, it is usually type cast to the new data type. This is commonly useful when, for example, an integer is being extracted from a string or a date is being incorporated into a string.

Type casts occur either implicitly or explicitly. An implicit cast is one that occurs when the method of converting a value is unambiguous. For example, the integer to varchar cast is usually implemented as an implicit cast because there is only one way to convert a number to a variable character string. Explicit casts, on the other hand, do not happen automatically and must be specified each time they occur.

In many programming languages, explicit casting is required when a loss of precision could result. That is, if the value 99.99 were being assigned to an integer variable, many programming languages would not allow the operation to take place without the programmer explicitly casting 99.99 to an integer type. SQL does not care about precision in casting, and this is something of which database developers must be acutely aware, especially if their programming background is in a language that requires explicit casts for conversions that would result in a loss of precision. Figure 6 shows an example of an implicit cast and the loss of precision that results from rounding a decimal number.

The SQL standard determines which data types can be implicitly cast to other data types, but the implementation of these casts varies among database systems. An example of a CAST is shown in Figure 7.

Type casting is an important part of any programming language, and learning how the SQL CAST operator interacts with each common SQL data type is a vital step to take toward becoming an expert SQL programmer.