Статьи

Введение в массивы PostgreSQL, основные понятия и вопросы нормализации

Июль 4, 2018

Это часть серии из двух частей. Второй будет обсуждать индексирование массивов, что является сложной темой.

PostgreSQL имеет очень хорошую поддержку для массивов других типов данных. Можно иметь вложенные типы данных, где атрибут отношения хранит массив кортежей, и каждый из этих кортежей может содержать массивы других кортежей или примитивов.

Массивы чрезвычайно мощны в PostgreSQL, но они также создают ряд существенных проблем. Они могут использоваться способами, которые нарушают первую нормальную форму, но простое использование массива не обязательно нарушает требование атомарности первой нормальной формы, потому что массивы существенно отличаются от отношений. Понимание массивов в PostgreSQL может помочь избежать дорогостоящих ошибок. Отношения и массивы подчиняются принципиально другим ограничениям и поэтому эквивалентны только в нескольких очень особых случаях.

Массивы получают свои преимущества в PostgreSQL благодаря тому, что у них есть строгие требования к определениям.

Массив — это математическая матрица

Массивы PostgreSQL соответствуют базовому определению математической матрицы. Массив должен быть прямоугольным, поэтому, если у одного элемента есть массив, все остальные элементы должны иметь массив с такими же размерами. Все члены массива должны иметь одинаковые типы данных.

Таким образом, следующие допустимые целочисленные массивы:

  • ‘{1,2,3,4,5}’
  • ‘{{1,2}, {3,4}, {5,6}, {7,8}, {9,10}}’

Следующие недопустимые целочисленные массивы:

  • ‘{1,2,3, {4, 5}}’
  • ‘{{1,2}, {3,4}, {5}}’

Эти ограничения делают массивы особенно полезными в PostgreSQL. Например, можно использовать текстовые массивы для представления наборов кортежей, вводимых в хранимые процедуры. Можно также написать функции для выполнения математических матриц на числовых массивах (мы рассмотрим пример ниже). Если в PostgreSQL не было истинной математики произвольной точности через числовой тип, мы могли бы реализовать нечто подобное, используя массивы. Все эти проверки делают массивы безопасными для работы так, как это не делает большинство языков программирования.

Массивы против отношений против кортежей (и первая нормальная форма)

Массивы имеют определенное отношение к отношениям и кортежам, но все они существенно различаются, что означает, что они лучше всего решают разные проблемы.

Как и кортежи, массивы упорядочены. Однако:

  • В отличие от кортежей каждый элемент массива должен иметь одинаковый тип данных. Это означает, что массивы текста могут представлять кортежи любого типа. 
  • В отличие от кортежей, массивы не имеют фиксированного количества элементов. Элементы могут быть добавлены без нарушения основного типа.

Как и массивы, отношения в основном прямоугольные и с открытым концом (элементы могут быть добавлены или удалены с конца). Однако:

  • Массивы упорядочены, отношения нет. Это означает, что значением массива является домен, а значением отношения является набор или набор доменов (в зависимости от ограничений).
  • Все типы данных в массиве должны быть идентичны. Отношения не имеют этого ограничения.

В основном это означает, что простое использование массива в столбце таблицы не обязательно нарушает 1NF, если массив используется таким образом, который предполагает, что ординальность имеет значение.

Например, мы можем хранить матрицы для матричной арифметики в виде числовых [] массивов. Мы могли бы хранить некоторые проблемы, которые преобразуются в числовые [] матрицы, также в БД (например, линейные уравнения одновременности), и они не обязательно нарушают 1NF.

Пример: решение одновременных линейных уравнений в PL / PGSQL

Я оставлю понимание этого как упражнение читателю. Это, однако, маленький пример. Это очень простая программа, которую я изначально написал на BASIC на своем C64, когда я был моложе как программа для решения домашних заданий, и с тех пор написал несколько других воплощений в качестве демонстрации. он решает одновременные линейные уравнения, размещая их в матрице и соответственно уменьшая матрицу. Программа будет принимать только n на n + 1 матрицу, а затем будет вычислять.

Этот пример полезен, потому что он показывает, что все, что вы можете решить с помощью математической математики, вы можете решить, используя массивы PostgreSQL ….

 create or replace function solve_linear(numeric[]) returns numeric[]
language plpgsql as
$$
declare retval numeric[];
        c_outer int; -- counters
        c_inner int;
        c_working int;
        upper1 int;
        upper2 int;
        ratio numeric; --caching of calculation
begin
    IF array_upper($1, 1) <> array_upper($1, 2) - 1 THEN
        RAISE EXCEPTION 'bad input, must be n x n+1 matrix';
    END IF;
    upper1 := array_upper($1, 1);
    upper2 := array_upper($1, 2);
    FOR c_outer IN 1 .. upper1 LOOP
        FOR c_inner IN 1 .. upper1 LOOP
            IF c_inner = c_outer THEN CONTINUE;
            END IF;
            ratio := $1[c_inner][c_outer]/$1[c_outer][c_outer];
            for c_working in 1 .. upper2 loop
                $1[c_inner][c_working] := $1[c_inner][c_working]
                                    - ($1[c_outer][c_working] * ratio);
            end loop;
        end loop;
    end loop;
    retval := '{}';
    for c_outer in 1 .. array_upper($1,1) loop
       retval := retval || $1[c_outer][array_upper($1,1) + 1]/ $1[c_outer][c_outer];
    end loop;
    return retval;
end;
$$;

 Например:

 select * from solve_linear (‘{{1,2,3}, {2,3,4}}’ :: numeric []);
  solve_linear 
————————————————- ———
 {-1.00000000000000000000000000000000,2.0000000000000000}
(1 строка)

Другими словами, если мы имеем 1x + 2y = 3 и 2x + 3y = 4, то они будут пересекаться, где x равен -1, а y равен 2.

Да, это не зависит от порядка.

 select * from solve_linear (‘{{2,3,4}, {1,2,3}}’ :: numeric []);
  solve_linear 
————————————————- ———————
 {-1.0000000000000000000000000000000000000000,2.00000000000000000000}
(1 строка)

Однако упорядочение внутри подмассива важно:

выберите * из solve_linear (‘{{2, 4,3}, {1,3,2}} ‘:: цифровая []);
  solve_linear 
————————————————- ———————
 {0.5000000000000000000000000000000000000000,0.50000000000000000000}
(1 строка)

Одна небольшая проблема с приведенным выше кодом заключается в том, что он выдает деление на 0, если строки параллельны:

select * from solve_linear (‘{{1,2,3}, {1,2,6}}’ :: numeric []);
ОШИБКА: деление на ноль.
КОНТЕКСТ: PL / pgSQL функция «solve_linear» строка 19 при назначении

Следовательно, обработка ошибок может быть улучшена, но это не так уж плохо для демонстрации.

Как оказалось, это довольно обобщенный класс задач и может быть особенно полезен при выполнении геометрической математики.

Я надеюсь, что это поможет показать некоторые из использования и значения типов массивов PostgreSQL. В следующем посте мы поговорим о массивах и индексах …..  

65
Share: