Matriz PostgreSQL: funções, tipo, exemplo

O que é matriz PostgreSQL?

No PostgreSQL, podemos definir uma coluna como um array de tipos de dados válidos. O tipo de dados pode ser integrado, definido pelo usuário ou enumerado. Fora isso, os arrays desempenham um papel importante no PostgreSQL.

Cada tipo de dados PostgreSQL correspondente vem com um tipo de array relevante. Por exemplo, o tipo de dados inteiro tem o tipo de matriz inteiro[], o tipo de dados de caractere tem o tipo de matriz caractere[], etc.

Criando matrizes PostgreSQL

Na sequênciawing Por exemplo, criaremos uma tabela chamada Funcionários com a coluna de contato definida como um array de texto:

CREATE TABLE Employees (
   id int PRIMARY KEY,
   name VARCHAR (100),
   contact TEXT []
);

O comando deve ser executado com sucesso.

Criando matrizes PostgreSQL

Inserindo valores de array PostgreSQL

Deixe-nos agora inserir valores na tabela acima:

INSERT INTO Employees 
VALUES
   (
      1,
      'Alice John',
      ARRAY [ '(408)-743-9045',
      '(408)-567-7834' ]
   );

A inserção deve ser executada com sucesso.

Inserindo valores de array PostgreSQL

Os valores da terceira coluna, ou seja, contato, foram inseridos como um array. Isso foi conseguido usando o construtor ARRAY.

Neste exemplo, nós os colocamos entre colchetes []. Temos dois contatos da funcionária Alice John.

Ainda é possível usar chaves {} conforme mostrado abaixo:

INSERT INTO Employees 
VALUES
   (
    2,
      'Kate Joel',
      '{"(408)-783-5731"}'
   ),
   ( 
      3,
      'James Bush',
      '{"(408)-745-8965","(408)-567-78234"}'
   );

O comando deve ser executado com sucesso.

Inserindo valores de array PostgreSQL

As instruções acima irão inserir duas linhas na tabela Funcionários. Ao usar chaves, a matriz é colocada entre aspas simples ('), enquanto os itens da matriz de texto são colocados entre aspas duplas (“).

Consultando dados da matriz

Para consultar os elementos de um array, usamos a instrução SELECT.

Para ver o conteúdo da nossa tabela Employees, executamos o seguintewing comando:

SELECT * FROM Employees;

Isso retorna o seguintewing:

Consultando dados da matriz

Os elementos da coluna da matriz, ou seja, contato, foram colocados entre chaves {}.

Para acessar os próprios elementos do array, adicionamos um subscrito entre colchetes []. O primeiro elemento de um array está na posição 1.

Por exemplo, precisamos obter os nomes dos funcionários e apenas o primeiro contato para os funcionários com mais de um contato. Podemos acessar isso como contato[1].

Vejamos isto:

SELECT name, contact[1]
FROM Employees;

Isso retornará o seguintewing:

Consultando dados da matriz

Podemos usar a instrução SELECT junto com a cláusula WHERE para filtrar linhas com base na coluna do array.

Por exemplo, para ver o funcionário com (408)-567-78234 como segundo contato, podemos executar o seguintewing comando:

SELECT
   name
FROM
   Employees
WHERE
   contact [ 2 ] = '(408)-567-78234';

Isso retornará o seguintewing:

Consultando dados da matriz

Modificando a matriz PostgreSQL

Você pode atualizar todos ou um único elemento de uma matriz.

Aqui está o conteúdo da tabela Funcionários:

Modificando a matriz PostgreSQL

Vamos atualizar o segundo número de telefone do funcionário James Bush, cujo id é 3:

Modificando a matriz PostgreSQL

Execute o seguintewing comando:

UPDATE Employees
SET contact [ 2 ] = '(408)-589-89347'
WHERE
   id = 3;

O comando deve ser executado com sucesso:

Modificando a matriz PostgreSQL

Vamos consultar a tabela para verificar se a alteração foi bem-sucedida:

Modificando a matriz PostgreSQL

A mudança foi bem sucedida.

Pesquisando em um array PostgreSQL

Atualmente, nossa tabela de Funcionários é a seguinte:

Pesquisando em um array PostgreSQL

Suponha que precisamos saber quem é o proprietário do contato (408)-783-5731, independentemente da posição na matriz de contatos, podemos usar a função ANY() conforme mostrado abaixo:

SELECT
   name,
   contact
FROM
   Employees
WHERE
   '(408)-783-5731' = ANY (contact);

Isso retornará o seguintewing:

Pesquisando em um array PostgreSQL

Expandindo matrizes

Podemos dividir os valores de um array em linhas. Este processo é conhecido como expansão de array.

No exemplo da tabela Funcionários, existem alguns funcionários com dois contatos na matriz de contatos. Podemos dividi-los em linhas separadas.

PostgreSQL fornece a função unnest() que pode ser usada para isso.

Por exemplo:

SELECT
   name,
   unnest(contact)
FROM
   Employees;

Isso retornará o seguintewing:

Expandindo matrizes

Os funcionários Alice John e James Bush têm dois contatos. Podemos dividir em linhas separadas.

Usando pgAdmin

Criando matrizes PostgreSQL

Para fazer o mesmo por meio do pgAdmin, faça o seguinte:

Passo 1) Faça login em sua conta pgAdmin.

Passo 2)

  1. Na barra de navegação à esquerda, clique em rbases.
  2. Clique no botão Demonstração

Criando arrays PostgreSQL usando pgAdmin

Passo 3) Digite a consulta no editor de consultas para criar a tabela Funcionários:

CREATE TABLE Employees (
   id int PRIMARY KEY,
   name VARCHAR (100),
   contact TEXT []
);

Passo 4) Clique no botão Executar.

Criando arrays PostgreSQL usando pgAdmin

Inserindo valores de array PostgreSQL

Passo 1) Digite o seguintewing consulta no editor de consultas:

INSERT INTO Employees 
VALUES
   (
      1,
      'Alice John',
      ARRAY [ '(408)-743-9045',
      '(408)-567-7834' ]
   );

Passo 2) Clique no botão Executar:

Inserindo valores de array PostgreSQL

Passo 3)

Para usar chaves na consulta

Passo 1) Digite o seguintewing consulta no editor de consultas:

INSERT INTO Employees 
VALUES
   (
    2,
      'Kate Joel',
      '{"(408)-783-5731"}'
   ),
   ( 
      3,
      'James Bush',
      '{"(408)-745-8965","(408)-567-78234"}'
   );

Etapa 2) Clique no botão Executar:

Inserindo valores de array PostgreSQL

Consultando dados da matriz

Passo 1) Para ver o conteúdo da tabela Funcionários, digite o seguintewing consulta no editor de consultas:

SELECT * FROM Employees;

Passo 2) Clique no botão Executar:

Consultando dados de array usando pgAdmin

Deve retornar o seguintewing:

Consultando dados de array usando pgAdmin

Passo 3) Para ver os primeiros contatos dos colaboradores:

  1. Digite o seguintewing consulta no editor de consultas:
    SELECT name, contact[1]
    FROM Employees;
    
  2. Clique no botão Executar.

Consultando dados de array usando pgAdmin

Deve retornar o seguintewing:

Consultando dados de array usando pgAdmin

Passo 4) Para combinar a instrução SELECT com a cláusula WHERE:

  1. Digite o seguintewing comando no editor de consultas:
    SELECT
       name
    FROM
       Employees
    WHERE
       contact [ 2 ] = '(408)-567-78234';
    
  2. Clique no botão Executar.

Consultando dados de array usando pgAdmin

Deve retornar o seguintewing:

Consultando dados de array usando pgAdmin

Modificando a matriz PostgreSQL

Passo 1) Para atualizar o segundo contato do usuário com id 3, execute o seguintewing comando:

UPDATE Employees
SET contact [ 2 ] = '(408)-589-89347'
WHERE
   id = 3;

Passo 2) Clique no botão Executar.

Modificando array PostgreSQL usando pgAdmin

Passo 3)

1. Digite o seguintewing comando no editor de consultas para verificar se a alteração foi bem-sucedida:

SELECT * FROM Employees;

2.Clique no botão Executar.

Modificando array PostgreSQL usando pgAdmin

Deve retornar o seguintewing:

Modificando array PostgreSQL usando pgAdmin

Pesquisando em um array PostgreSQL

Passo 1) Digite o seguintewing consulta no editor de consultas:

SELECT
   name,
   contact
FROM
   Employees
WHERE
   '(408)-783-5731' = ANY (contact);

Passo 2) Clique no botão Executar.

Pesquisando em um array PostgreSQL usando pgAdmin

Deve retornar o seguintewing:

Pesquisando em um array PostgreSQL usando pgAdmin

Expandindo matrizes

Passo 1) Digite o seguintewing consulta no editor de consultas:

SELECT
   name,
   unnest(contact)
FROM
   Employees;

Passo 2) Clique no botão Executar.

Expandindo matrizes usando pgAdmin

Deve retornar o seguintewing:

Expandindo matrizes usando pgAdmin

Resumo

  • PostgreSQL nos permite definir uma coluna da tabela como um tipo de array.
  • A matriz deve ser válida tipo de dados como número inteiro, caractere ou tipos definidos pelo usuário.
  • Para inserir valores em uma coluna de array, usamos o construtor ARRAY.
  • Se houver mais de um elemento na mesma linha de uma coluna do array, o primeiro elemento estará na posição 1.
  • Cada valor pode ser acessado passando um subscrito entre colchetes [].
  • Os elementos do array podem ser recuperados usando a instrução SELECT.
  • Os valores da coluna da matriz podem ser colocados entre colchetes [] ou chaves {}.
  • Podemos pesquisar valores de colunas de array usando a função ANY().

Baixe o banco de dados usado neste tutorial