10.5. UNION, CASE и связанные конструкции
Пред. НаверхГлава 10. Преобразование типовНачало След.

10.5. UNION, CASE и связанные конструкции

SQL-конструкция UNION взаимодействует с системой типов, так как ей приходится объединять значения возможно различных типов в единый результирующий набор. Алгоритм разрешения типов при этом применяется независимо к каждому выходному столбцу запроса. Конструкции INTERSECT и EXCEPT сопоставляют различные типы подобно UNION. По такому же алгоритму сопоставляют типы выражений и определяют тип своего результата некоторые другие конструкции, включая CASE, ARRAY, VALUES и функции GREATEST и LEAST.

Разрешение типов для UNION, CASE и связанных конструкций

  1. Если все данные одного типа и это не тип unknown, выбрать его.

  2. Если тип данных — домен, далее считать их типом базовый тип домена. [11]

  3. Если все данные типа unknown, выбрать для результата тип text (предпочитаемый для категории string). В противном случае значения unknown для остальных правил игнорируются.

  4. Если известные типы входных данных оказываются не из одной категории, констатировать неудачу.

  5. Выбрать первый известный тип данных в качестве типа-кандидата, затем рассмотреть все остальные известные типы данных, слева направо. [12] Если ранее выбранный тип может быть неявно преобразован к другому типу, но преобразовать второй в первый нельзя, выбрать второй тип в качестве нового кандидата. Затем продолжать рассмотрение последующих данных. Если на любом этапе этого процесса выбирается предпочитаемый тип, следующие данные больше не рассматриваются.

  6. Привести все данные к окончательно выбранному типу. Констатировать неудачу, если неявное преобразование из типа входных данных в выбранный тип невозможно.

Ниже это проиллюстрировано на примерах.

Пример 10.10. Разрешение типов с частичным определением в Union

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

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

В данном случае константа 'b' неизвестного типа будет преобразована в тип text.


Пример 10.11. Разрешение типов в простом объединении

SELECT 1.2 AS "numeric" UNION SELECT 1;

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

Константа 1.2 имеет тип numeric и целочисленное значение 1 может быть неявно приведено к типу numeric, так что используется этот тип.


Пример 10.12. Разрешение типов в противоположном объединении

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

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

Здесь значение типа real нельзя неявно привести к integer, но integer можно неявно привести к real, поэтому типом результата объединения будет real.


Пример 10.13. Разрешение типов во вложенном объединении

SELECT NULL UNION SELECT NULL UNION SELECT 1;

ERROR:  UNION types text and integer cannot be matched

Эта ошибка возникает из-за того, что Postgres Pro воспринимает множественные UNION как пары с вложенными операциями, то есть как запись 

(SELECT NULL UNION SELECT NULL) UNION SELECT 1;

Внутренний UNION разрешается как выдающий тип text, согласно правилам, приведённым выше. Затем внешний UNION получает на вход типы text и integer, что и приводит к показанной ошибке. Эту проблему можно устранить, сделав так, чтобы у самого левого UNION минимум с одной стороны были данные желаемого типа результата.

Операции INTERSECT и EXCEPT также разрешаются по парам. Однако остальные конструкции, описанные в этом разделе, рассматривают все входные данные сразу.



[11] Так же, как домены воспринимаются при выборе операторов и функций, доменные типы могут сохраняться в конструкции UNION или подобной, если пользователь позаботится о том, чтобы все входные данные приводились к этому типу явно или неявно. В противном случае будет использоваться базовый тип домена.

[12] По историческим причинам в конструкции CASE выражение в предложении ELSE (если оно есть) обрабатывается как «первое», а предложения THEN рассматриваются после. Во всех остальных случаях, «слева направо» означает порядок, в котором выражения действительно идут в тексте запроса.

Пред. Наверх След.
10.4. Хранимое значение Начало 10.6. Выходные столбцы SELECT
3.3. Foreign Keys
Prev UpChapter 3. Advanced FeaturesHome Next

3.3. Foreign Keys

Recall the weather and cities tables from Chapter 2. Consider the following problem: You want to make sure that no one can insert rows in the weather table that do not have a matching entry in the cities table. This is called maintaining the referential integrity of your data. In simplistic database systems this would be implemented (if at all) by first looking at the cities table to check if a matching record exists, and then inserting or rejecting the new weather records. This approach has a number of problems and is very inconvenient, so Postgres Pro can do this for you.

The new declaration of the tables would look like this: 

CREATE TABLE cities (
        name     varchar(80) primary key,
        location point
);

CREATE TABLE weather (
        city      varchar(80) references cities(name),
        temp_lo   int,
        temp_hi   int,
        prcp      real,
        date      date
);

Now try inserting an invalid record: 

INSERT INTO weather VALUES ('Berkeley', 45, 53, 0.0, '1994-11-28');


ERROR:  insert or update on table "weather" violates foreign key constraint "weather_city_fkey"
DETAIL:  Key (city)=(Berkeley) is not present in table "cities".

The behavior of foreign keys can be finely tuned to your application. We will not go beyond this simple example in this tutorial, but just refer you to Chapter 5 for more information. Making correct use of foreign keys will definitely improve the quality of your database applications, so you are strongly encouraged to learn about them.

Prev Up Next
3.2. Views Home 3.4. Transactions