Skip to main content
Ctrl+K

Tantor Basic Edition 15.12 documentation

Table of contents:

  • Preface
    • 1.  What Is Tantor BE?
    • 2. A Brief History of PostgreSQL
    • 3. What are the differences between Tantor BE 15 and PostgreSQL 15
    • 4. Conventions
    • 5. Bug Reporting Guidelines
  • Part I. Tutorial
    • Chapter 1. Synopsis
    • Chapter 2. The SQL Language
    • Chapter 3. Advanced Features
  • Part II. The SQL Language
    • Chapter 4. SQL Syntax
    • Chapter 5. Data Definition
    • Chapter 6. Data Manipulation
    • Chapter 7. Queries
    • Chapter 8. Data Types
    • Chapter 9. Functions and Operators
    • Chapter 10. Type Conversion
    • Chapter 11. Indexes
    • Chapter 12. Full Text Search
    • Chapter 13. Concurrency Control
    • Chapter 14. Performance Tips
    • Chapter 15. Parallel Query
  • Part III. Server Administration
    • Chapter 16. Installation from Binaries
    • Chapter 17. Server Setup and Operation
    • Chapter 18. Server Configuration
    • Chapter 19. Client Authentication
    • Chapter 20. Database Roles
    • Chapter 21. Managing Databases
    • Chapter 22. Localization
    • Chapter 23. Routine Database Maintenance Tasks
    • Chapter 24. Backup and Restore
    • Chapter 25. High Availability, Load Balancing, and Replication
    • Chapter 26. Monitoring Database Activity
    • Chapter 27. Monitoring Disk Usage
    • Chapter 28. Reliability and the Write-Ahead Log
    • Chapter 29. Logical Replication
    • Chapter 30. Just-in-Time Compilation (JIT)
  • Part IV. Client Interfaces
    • Chapter 31. libpq — C Library
    • Chapter 32. Large Objects
    • Chapter 33. ECPG — Embedded SQL in C
    • Chapter 34. The Information Schema
  • Part V. Server Programming
    • Chapter 35. Extending SQL
    • Chapter 36. Triggers
    • Chapter 37. Event Triggers
    • Chapter 38. The Rule System
    • Chapter 39. Procedural Languages
    • Chapter 40. PL/pgSQL — SQL Procedural Language
    • Chapter 41. PL/Tcl — Tcl Procedural Language
    • Chapter 42. PL/Perl — Perl Procedural Language
    • Chapter 43. PL/Python — Python Procedural Language
    • Chapter 44. Server Programming Interface
    • Chapter 45. Background Worker Processes
    • Chapter 46. Logical Decoding
    • Chapter 47. Replication Progress Tracking
    • Chapter 48. Archive Modules
  • Part VI. Reference
    • SQL Commands
    • PostgreSQL Client Applications
    • PostgreSQL Server Applications
  • Part VII. Internals
    • Chapter 49. Overview of PostgreSQL Internals
    • Chapter 50. System Catalogs
    • Chapter 51. System Views
    • Chapter 52. Frontend/Backend Protocol
    • Chapter 53. PostgreSQL Coding Conventions
    • Chapter 54. Native Language Support
    • Chapter 55. Writing a Procedural Language Handler
    • Chapter 56. Writing a Foreign Data Wrapper
    • Chapter 57. Writing a Table Sampling Method
    • Chapter 58. Writing a Custom Scan Provider
    • Chapter 59. Genetic Query Optimizer
    • Chapter 60. Table Access Method Interface Definition
    • Chapter 61. Index Access Method Interface Definition
    • Chapter 62. Generic WAL Records
    • Chapter 63. Custom WAL Resource Managers
    • Chapter 64. B-Tree Indexes
    • Chapter 65. GiST Indexes
    • Chapter 66. SP-GiST Indexes
    • Chapter 67. GIN Indexes
    • Chapter 68. BRIN Indexes
    • Chapter 69. Hash Indexes
    • Chapter 70. Database Physical Storage
    • Chapter 71. System Catalog Declarations and Initial Contents
    • Chapter 72. How the Planner Uses Statistics
    • Chapter 73. Backup Manifest Format
  • Part VIII. Appendixes
    • Appendix A. Tantor BE Error Codes
    • Appendix B. Date/Time Support
    • Appendix C. SQL Key Words
    • Appendix D. SQL Conformance
    • Appendix E. Release Notes
    • Appendix F. Additional Supplied Modules
    • Appendix G. Additional Supplied Programs
    • Appendix H. Additional External Modules
    • Appendix I. External Projects
    • Appendix J. Tantor BE Limits
    • Appendix K. Acronyms
    • Appendix L. Glossary
    • Appendix M. Color Support
    • Appendix N. Obsolete or Renamed Features
  • Bibliography
  • Index

10.5. UNION, CASE, and Related Constructs

10.5. UNION, CASE, and Related Constructs#

10.5. UNION, CASE, and Related Constructs
10.5. UNION, CASE, and Related Constructs
Prev UpChapter 10. Type ConversionHome Next

10.5. UNION, CASE, and Related Constructs

SQL UNION constructs must match up possibly dissimilar types to become a single result set. The resolution algorithm is applied separately to each output column of a union query. The INTERSECT and EXCEPT constructs resolve dissimilar types in the same way as UNION. Some other constructs, including CASE, ARRAY, VALUES, and the GREATEST and LEAST functions, use the identical algorithm to match up their component expressions and select a result data type.

Type Resolution for UNION, CASE, and Related Constructs

  1. If all inputs are of the same type, and it is not unknown, resolve as that type.

  2. If any input is of a domain type, treat it as being of the domain's base type for all subsequent steps. [12]

  3. If all inputs are of type unknown, resolve as type text (the preferred type of the string category). Otherwise, unknown inputs are ignored for the purposes of the remaining rules.

  4. If the non-unknown inputs are not all of the same type category, fail.

  5. Select the first non-unknown input type as the candidate type, then consider each other non-unknown input type, left to right. [13] If the candidate type can be implicitly converted to the other type, but not vice-versa, select the other type as the new candidate type. Then continue considering the remaining inputs. If, at any stage of this process, a preferred type is selected, stop considering additional inputs.

  6. Convert all inputs to the final candidate type. Fail if there is not an implicit conversion from a given input type to the candidate type.

Some examples follow.

Example 10.10. Type Resolution with Underspecified Types in a Union

SELECT text 'a' AS "text" UNION SELECT 'b';

 text
------
 a
 b
(2 rows)

Here, the unknown-type literal 'b' will be resolved to type text.


Example 10.11. Type Resolution in a Simple Union

SELECT 1.2 AS "numeric" UNION SELECT 1;

 numeric
---------
       1
     1.2
(2 rows)

The literal 1.2 is of type numeric, and the integer value 1 can be cast implicitly to numeric, so that type is used.


Example 10.12. Type Resolution in a Transposed Union

SELECT 1 AS "real" UNION SELECT CAST('2.2' AS REAL);

 real
------
    1
  2.2
(2 rows)

Here, since type real cannot be implicitly cast to integer, but integer can be implicitly cast to real, the union result type is resolved as real.


Example 10.13. Type Resolution in a Nested Union

SELECT NULL UNION SELECT NULL UNION SELECT 1;

ERROR:  UNION types text and integer cannot be matched

This failure occurs because Tantor BE treats multiple UNIONs as a nest of pairwise operations; that is, this input is the same as

(SELECT NULL UNION SELECT NULL) UNION SELECT 1;

The inner UNION is resolved as emitting type text, according to the rules given above. Then the outer UNION has inputs of types text and integer, leading to the observed error. The problem can be fixed by ensuring that the leftmost UNION has at least one input of the desired result type.

INTERSECT and EXCEPT operations are likewise resolved pairwise. However, the other constructs described in this section consider all of their inputs in one resolution step.




[12] Somewhat like the treatment of domain inputs for operators and functions, this behavior allows a domain type to be preserved through a UNION or similar construct, so long as the user is careful to ensure that all inputs are implicitly or explicitly of that exact type. Otherwise the domain's base type will be used.

[13] For historical reasons, CASE treats its ELSE clause (if any) as the “first” input, with the THEN clauses(s) considered after that. In all other cases, “left to right” means the order in which the expressions appear in the query text.


Prev Up Next
10.4. Value Storage Home 10.6. SELECT Output Columns
Commercial use is possible only with written permission from the company LLC “Tantor Labs”
© LLC "Tantor Labs"
  • Technical Support Policy
  • Cookie Processing Policy
  • Website User Agreement
Products
  • Tantor DBMS
  • Tantor Platform
  • Documentation
Contacts
  • +7 495 369-48-16
  • info@tantorlabs.ru
15.12.0 - 3069e28c - 2025-04-10 15:13:16