UNION, EXCEPT, CASE, and Related Constructs

Type Resolution for UNION and Related Constructs

UNION is classified into UNION and UNION ALL. UNION aggregates data and deduplicates data. UNION ALL aggregates data but does not deduplicate data.

Example:

-- Create a table.
m_db=# CREATE TABLE test_union1 (union_col1 INT);
m_db=# CREATE TABLE test_union2 (union_col2 CHAR(5));

-- Insert data.
m_db=# INSERT INTO test_union1 VALUES (123);
m_db=# INSERT INTO test_union2 VALUES ('123');

-- Usage of UNION
m_db=# SELECT union_col1 FROM test_union1 UNION SELECT union_col2 FROM test_union2;
 union_col1 
------------
 123
(1 row)

-- Usage of UNION ALL
m_db=# SELECT union_col1 FROM test_union1 UNION ALL SELECT union_col2 FROM test_union2;
 union_col1 
------------
 123
 123
(2 rows)

-- UNION application of a non-column value
m_db=# SELECT '234' FROM test_union1 UNION SELECT '234';
 ?column? 
----------
 234
(1 row)

Type Resolution for EXCEPT and Related Constructs

EXCEPT is classified into EXCEPT, EXCEPT ALL, and EXCEPT DISTINCT. EXCEPT and EXCEPT DISTINCT remove the intersection of the two query results from the first query result and deduplicate the results. EXCEPT ALL does not deduplicate the results.

Example:

-- Create a table.
m_db=# CREATE TABLE data_types (
  col_int1 TINYINT,
  col_int2 SMALLINT,
  col_int4 INT,
  col_int8 BIGINT,
  col_uint1 TINYINT UNSIGNED, 
  col_uint2 SMALLINT UNSIGNED,
  col_uint4 INT UNSIGNED,
  col_uint8 BIGINT UNSIGNED,
  col_float FLOAT
);

-- Insert data.
INSERT INTO data_types
VALUES
  (
   1,
   2,
   3,
   4,
   1,
   2,
   3,
   4,
   1.1
);

-- Usage of except
m_db=# SELECT col_int4 FROM data_types EXCEPT SELECT col_float FROM data_types;
 col_int4
----------
        3
(1 row)

m_db=# SELECT PG_TYPEOF(col_int4) FROM (SELECT col_int4 FROM data_types EXCEPT SELECT col_float FROM data_types);
 pg_typeof
-----------
 double
(1 row)

-- Usage of except all
m_db=# SELECT col_int4 FROM data_types EXCEPT ALL SELECT col_float FROM data_types;
 col_int4
----------
        3
(1 row)

m_db=# SELECT PG_TYPEOF(col_int4) FROM (SELECT col_int4 FROM data_types EXCEPT ALL SELECT col_float FROM data_types);
 pg_typeof
-----------
 double
(1 row)

-- Usage of except distinct
m_db=# SELECT col_int4 FROM data_types EXCEPT DISTINCT SELECT col_float FROM data_types;
 col_int4
----------
        3
(1 row)

m_db=# SELECT PG_TYPEOF(col_int4) FROM (SELECT col_int4 FROM data_types EXCEPT DISTINCT SELECT col_float FROM data_types);
 pg_typeof
-----------
 double
(1 row)

-- except application of a non-column value
m_db=# SELECT col_int4 FROM data_types EXCEPT SELECT '4';
 col_int4
----------
 3
(1 row)

m_db=# SELECT PG_TYPEOF(col_int4) FROM (SELECT col_int4 FROM data_types EXCEPT SELECT '4');
 pg_typeof
-----------
 varchar
(1 row)

-- Drop the table.
m_db=# DROP TABLE data_types;

Type Resolution for CASE and Related Constructs

The CASE syntax has two expression modes, which have the same functions and meanings.

CASE WHEN col = A THEN ... ELSE ...

CASE col WHEN A THEN ... ELSE ...

Example:

-- Create a table.
m_db=# CREATE TABLE test_case1 (case_col1 INT);

-- Insert data.
m_db=# INSERT INTO test_case1 VALUES (1), (23), (45), (1);

-- Usage of case
m_db=# SELECT CASE WHEN case_col1 = 1 THEN 'a' ELSE 'b' END FROM test_case1;
 case 
------
 a
 b
 b
 a
(4 rows)

m_db=# SELECT CASE case_col1 WHEN 1 THEN 'a' ELSE 'b' END FROM test_case1;
 case 
------
 a
 b
 b
 a
(4 rows)

In the precision transfer scenario, when the CASE WHEN statement is used, type conversion and precision recalculation are performed. As a result, trailing zeros may be inconsistent with those in the output result of the CASE clause.

• More trailing zeros: The precision of the CASE node is calculated based on the precision of the CASE clause. If the precision of the THEN clause is lower than that of the CASE node, zeros are added to the end of the CASE node. For a user variable in the CASE WHEN statement, the default precision of the saved type is used as the precision of the user variable.
• Less trailing zeros: When multiple layers of CASE WHEN are nested, only the precision of the inner CASE is retained after the inner CASE performs type conversion. The outer CASE cannot obtain the precision information of the THEN clause. Therefore, the outer CASE performs type conversion based on the precision calculated according to that of the inner CASE. When the outer CASE clause is converted, if the precision of the inner CASE clause is less than that of the THEN clause, zeros are reduced from the end of the result.

Example:

-- More trailing zeros
m_db=# SELECT 15.6 AS result;
 result 
--------
   15.6
(1 row)

m_db=# SELECT CASE WHEN 1 < 2 THEN 15.6 ELSE  23.578 END AS result;
 result 
--------
 15.600
(1 row)

m_db=# SELECT  greatest(12, 3.4, 15.6) AS result;
 result 
--------
   15.6
(1 row)

m_db=# SELECT CASE WHEN 1 < 2 THEN greatest(12, 3.4, 15.6) ELSE greatest(123.4, 23.578, 36) END AS result;
 result 
--------
 15.600
(1 row)

-- Less trailing zeros
m_db=# CREATE TABLE t1 AS SELECT (false/-timestamp '2008-12-31 23:59:59.678') AS result;
INSERT 0 1
m_db=# desc t1;
 Field  |    Type     | Null | Key | Default | Extra 
--------+-------------+------+-----+---------+-------
 result | double(8,7) | YES  |     |         | 
(1 row)

m_db=# SELECT (false/-timestamp '2008-12-31 23:59:59.678') AS result;
   result   
------------
 -0.0000000
(1 row)

m_db=# cREATE TABLE t1 AS SELECT (case when 1<2 THEN false/-timestamp '2008-12-31 23:59:59.678' ELSE 0016.11e3/'22.2' END) AS result;
INSERT 0 1
m_db=# DESC t1;
 Field  |  Type  | Null | Key | Default | Extra 
--------+--------+------+-----+---------+-------
 result | double | YES  |     |         | 
(1 row)

m_db=# SELECT (CASE WHEN 1<2 THEN false/-timestamp '2008-12-31 23:59:59.678' ELSE 0016.11E3/'22.2' END) AS result;
 result 
--------
     -0
(1 row)

m_db=# DROP TABLE t1;
DROP TABLE
m_db=# CREATE TABLE t1 AS SELECT (CASE WHEN 1+1=2 THEN CASE WHEN 1<2 THEN false/-timestamp '2008-12-31 23:59:59.678' ELSE 0016.11e3/'22.2' END ELSE 'test' END) AS result;
INSERT 0 1
m_db=# DESC t1;
 Field  |    Type     | Null | Key | Default | Extra 
--------+-------------+------+-----+---------+-------
 result | varchar(23) | YES  |     |         | 
(1 row)

m_db=# SELECT (CASE WHEN 1+1=2 THEN CASE WHEN 1<2 THEN false/-timestamp '2008-12-31 23:59:59.678' ELSE 0016.11e3/'22.2' END ELSE 'test' END) AS result;
 result 
--------
 -0
(1 row)