9.4.1 DECODE

Features: Compares values and converts to other values.

Specification format

General rules

DECODE compares values of the value expression to be converted and the search values one by one, and if the values of the value expression to be converted and the search values match, a corresponding result value is returned. If none of the value expressions to be converted and search values match, the default value is returned if a default value has been specified. A NULL value is returned if a default value has not been specified.
If the same search value is specified more than once, then the result value returned is the one listed for the first occurrence of the search value.
The following data types can be used in result values and in the default value:
- CHAR
- VARCHAR
- NCHAR
- NCHAR VARYING
- TEXT
- INTEGER
- BIGINT
- NUMERIC
- DATE
- TIME WITHOUT TIME ZONE
- TIMESTAMP WITHOUT TIME ZONE
- TIMESTAMP WITH TIME ZONE
The same data type must be specified for all value expressions to be converted and the search values. However, note that different data types may also be specified if a literal is specified in the search value, and the value expressions that will be converted contain data types that can be converted. Refer to "Table A.1 Data type combinations that contain literals and can be converted implicitly" in "A.3 Implicit Data Type Conversions" for information on data types that can be specified when a literal is specified in the search value.
If the result values and default value are all literals, the data types for these values will be as shown below:
- If all values are character string literals, all will become character string types.
- If there is one or more numeric literal, all will become numeric types.
- If there is one or more literal cast to the datetime/time types, all will become datetime/time types.
If the result values and default value contain a mixture of literals and non-literals, the literals will be converted to the data types of the non-literals. Refer to "Table A.1 Data type combinations that contain literals and can be converted implicitly" in "A.3 Implicit Data Type Conversions" for information on data types that can be converted.

The same data type must be specified for all result values and for the default value. However, different data types can be specified if the data type of any of the result values or default value can be converted - these data types are listed below:

Table 9.3 Data type combinations that can be converted by DECODE (summary)
		Other result values or default value
		Numeric type	Character string type	Date/time type
Result value (any)	Numeric type	Y	N	N
	Character string type	N	Y	N
	Date/time type	N	N	S (*1)

Y: Can be converted
S: Some data types can be converted
N: Cannot be converted

*1: The data types that can be converted for date/time types are listed below:

Table 9.4 Result value and default value date/time data types that can be converted by DECODE
		Other result values or default value
		DATE	TIME WITHOUT TIME ZONE	TIMESTAMP WITHOUT TIME ZONE	TIMESTAMP WITH TIME ZONE
Result value (any)	DATE	Y	N	Y	Y
	TIME WITHOUT TIME ZONE	N	Y	N	N
	TIMESTAMP WITHOUT TIME ZONE	Y	N	Y	Y
	TIMESTAMP WITH TIME ZONE	Y	N	Y	Y

Y: Can be converted
N: Cannot be converted

The data type of the return value will be the data type within the result or default value that is longest and has the highest precision.

Example

In the following example, the value of col3 in table t1 is compared and converted to a different value. If the col3 value matches search value 1, the result value returned is "one". If the col3 value doe not match any of search values 1, 2, or 3, the default value "other number" is returned.

SELECT col1,
       DECODE(col3, 1, 'one',
                    2, 'two',
                    3, 'three',
                    'other number') "num-word"
       FROM t1;
col1  | num-word
------+----------
 1001 | one
 1002 | two
 1003 | three
(3 rows)