Values, Types, and Expressions

Choosing a numeric type

Picking a column type is a promise about what may be stored there, so the first decision for a number is how large it can grow. Whole numbers come in three sizes.

The type integer is the common choice, as it offers the best balance between range, storage size, and performance, while smallint is generally only used if disk space is at a premium and bigint is designed for when the range of integer is insufficient.

The declared range is enforced, not advisory: attempts to store values outside the allowed range result in an error.

Fractional quantities raise a different question: does the value have to be exact?

The numeric type can store numbers with a very large number of digits, and it is especially recommended for storing monetary amounts and other quantities where exactness is required. The price is speed: calculations on numeric values are very slow compared to the integer types or the floating-point types.

The alternative trades exactness for that speed. The types real and double precision are inexact, variable-precision numeric types, with real offering a precision of at least six decimal digits and double precision at least fifteen.

Inexactness has a visible symptom: comparing two floating-point values for equality might not always work as expected.

A numeric column is declared with two numbers, and mixing them up is a classic error. The precision of a numeric value is the total count of significant digits on both sides of the decimal point, while the scale is the count of decimal digits in the fractional part.

So a value written with two digits before the point and four after it has a precision of six and a scale of four: precision counts everything, scale counts only the fractional half.

The pattern in that table is the point: the same precision can be split in different ways, and the scale is what says where the decimal point sits.

Put the two properties together on the case that comes up most often: money.

A ledger column that must be summed without accumulated rounding drift, and whose stored balances have to compare predictably, needs exactness rather than speed. If you require exact storage and calculations, such as for monetary amounts, use the numeric type instead, with a declared precision and scale.

• double precision and real are documented as inexact, so equality tests on them can behave unexpectedly.
• bigint is exact but holds no fractional part, so it can only carry an amount converted into whole units.
• numeric accepts slower calculations in exchange for the exactness the ledger needs.

Choosing character, Boolean, and date-time types

Text has two length-limited types, and they differ in what they do with a value shorter than the limit.

Both character varying with a length limit and character with a length limit can store strings up to that many characters rather than bytes, and storing a longer string raises an error unless the excess characters are all spaces, in which case the string is truncated to the maximum length.

The difference appears with short values. Values of type character are physically padded with spaces to the declared width and are stored and displayed that way, while a character varying value simply stores the shorter string.

That padding changes how values compare. Trailing spaces in a character value are treated as semantically insignificant and are disregarded when comparing two values of type character, whereas trailing spaces are semantically significant in character varying and text values.

The text type stores strings of any length, and the documentation advises that in most situations text or character varying should be used instead of the blank-padded type, which is usually the slowest of the three.

Truth values have their own type, and it is a short list. The boolean type, whose alias is bool, is the logical Boolean type that holds true or false, and it is one of the types named by the SQL standard. Bit strings and binary data have their own types and express no truth value.

Points in time are split by intent rather than by precision.

A date value is a calendar date of year, month, and day, a timestamp without time zone holds a date and a time with no zone information, and a timestamp with time zone, also spelled timestamptz, holds a date and a time including the time zone.

A time of day alone follows the same split, with a with-time-zone spelling and a without-time-zone spelling, and an interval expresses a span of time rather than a point in it.

Arithmetic and operator precedence

Value expressions calculate values from primitive parts using arithmetic, logical, set, and other operations, and the result of such an expression is sometimes called a scalar to distinguish it from the result of a table expression.

How an expression with several operators is grouped is not negotiable. The precedence and associativity of the operators is hard-wired into the parser, and parentheses have to be added when an expression with multiple operators should be parsed in some other way than the precedence rules imply.

In the documented ordering, exponentiation binds more tightly than multiplication, division, and modulo, which in turn bind more tightly than addition and subtraction; unary sign operators bind more tightly still. Most operators have the same precedence and are left-associative.

Precedence is not the same thing as evaluation order, and confusing the two produces bugs that appear only under load.

The order of evaluation of subexpressions is not defined, and the inputs of an operator or function are not necessarily evaluated from left to right, so a calculation must never depend on a side effect happening first.

Read that as a rule for writing code: parentheses are how you control meaning, and nothing you write in one operand is guaranteed to run before the other.

Comparing values and combining predicates

A comparison is an expression like any other, and its result has a type. All comparison operators are binary operators that return values of type boolean.

That single fact explains a common error. Chaining a comparison so that the result of one feeds straight into another is not valid, because there is no comparison operator between a Boolean result and a number.

The range test has its own predicate. The BETWEEN predicate performs the range test instead, and it treats the endpoint values as included in the range, so a value equal to either endpoint passes. An exclusive range has to be written out with strict comparisons.

Comparison operators are available for all built-in data types that have a natural ordering, and values of related types can usually be compared because the parser coerces the less-general type to the more-general one.

Predicates are then combined with the logical operators, and they too have a fixed order of binding. In the documented precedence ordering, NOT for logical negation binds more tightly than AND for logical conjunction, which binds more tightly than OR for logical disjunction, so a condition that mixes them without parentheses is grouped in that order.

Two further properties matter when a condition gets complicated:

• Boolean combinations of AND, OR, and NOT in a qualification can be reorganized in any manner allowed by the laws of Boolean algebra, so the written arrangement is not necessarily the one that is executed.
• If the result of an expression can be determined by evaluating only some parts of it, the remaining subexpressions might not be evaluated at all, which is not the same as the guaranteed left-to-right behaviour some programming languages provide.

Together those two rules say the same thing as the section above: write conditions whose meaning survives regrouping, and never rely on one side being tested first.

Concatenating text without relying on implicit conversion

Joining text is where types quietly matter. The string concatenation operator, written as two vertical bars, joins two strings into one.

It is more forgiving than it looks, and that is the trap. It will accept non-string input so long as at least one input is of string type, and for other cases inserting an explicit coercion to text is what makes the non-string input acceptable. So a label built from a text prefix and a number happens to work, but only because of that accommodation — write the conversion yourself and the expression no longer depends on it.

Two limits are worth carrying:

• The non-string input cannot be of an array type, so an array has to be cast to text explicitly before it is joined.
• Strings here include values of the character, character varying, and text types, and a character value is converted to text before the operator is applied, which strips any trailing spaces it carried.

Calling functions with positional, named, and default arguments

The same function can be called in more than one way, and the notation decides which arguments may be left out.

In positional notation a function call is written with its argument values in the same order as they are defined in the function declaration, while in named notation the arguments are matched to the parameters by name and can be written in any order. In named notation the parameter name is written before the value and separated from it by an arrow token.

Parameters that have default values given in the declaration need not be written in the call at all, but the freedom this gives differs by notation: in positional notation parameters can only be omitted from right to left, whereas named notation can omit any combination of defaulted parameters.

Mixed notation combines the two, with positional arguments written first, because named arguments cannot precede positional ones. One current limit: named and mixed notations cannot currently be used when calling an aggregate function, although they do work when an aggregate function is used as a window function.

Key takeaways

• integer is the balanced default for whole numbers, and a value outside a type's range raises an error.
• numeric is the type recommended when exactness is required, such as for monetary amounts, at the cost of slower calculations; real and double precision are inexact.
• Precision counts every significant digit of a numeric value and scale counts only the fractional ones.
• character pads values to its declared width and disregards trailing spaces when comparing, while character varying and text keep them.
• boolean holds true and false, and only the with-time-zone spelling of a timestamp records zone information.
• Comparison operators are binary and return a Boolean, and BETWEEN includes both endpoints.
• Multiplication binds more tightly than addition, and NOT more tightly than AND, then OR — parentheses impose any other grouping, and evaluation order is never promised.
• Concatenation accepts a non-string operand only when one operand is already a string, so write the conversion to text yourself.
• A defaulted parameter can be dropped from the right in positional notation, while named notation can drop any combination of them.