PostgreSQL Matriz: Funções, Tipo, Exemplo

O que é o PostgreSQL Variedade?

In PostgreSQL, podemos definir uma coluna como uma matriz 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 na PostgreSQL.

Cada correspondente PostgreSQL tipo de dados 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.

Criar PostgreSQL Arrays

No exemplo a seguir, 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.

Criar PostgreSQL Arrays

inserindo PostgreSQL Valores de matriz

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 PostgreSQL Valores de matriz

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 PostgreSQL Valores de matriz

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 seguinte comando:

SELECT * FROM Employees;

Isso retorna o seguinte:

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 seguinte:

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 seguinte comando:

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

Isso retornará o seguinte:

Consultando dados da matriz

Modificando PostgreSQL Ordem

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

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

Modificando PostgreSQL Ordem

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

Modificando PostgreSQL Ordem

Execute o seguinte comando:

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

O comando deve ser executado com sucesso:

Modificando PostgreSQL Ordem

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

Modificando PostgreSQL Ordem

A mudança foi bem sucedida.

Pesquisando em um PostgreSQL Ordem

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

Pesquisando em um PostgreSQL Ordem

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 seguinte:

Pesquisando em um PostgreSQL Ordem

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 seguinte:

Expandindo matrizes

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

Usando pgAdmin

Criar PostgreSQL Arrays

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

Criar PostgreSQL Matrizes 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.

Criar PostgreSQL Matrizes usando pgAdmin

inserindo PostgreSQL Valores de matriz

Passo 1) Digite a seguinte 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 PostgreSQL Valores de matriz

Passo 3)

Para usar chaves na consulta

Passo 1) Digite a seguinte 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 PostgreSQL Valores de matriz

Consultando dados da matriz

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

SELECT * FROM Employees;

Passo 2) Clique no botão Executar:

Consultando dados de array usando pgAdmin

Deve retornar o seguinte:

Consultando dados de array usando pgAdmin

Passo 3) Para ver os primeiros contatos dos colaboradores:

  1. Digite a seguinte 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 seguinte:

Consultando dados de array usando pgAdmin

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

  1. Digite o seguinte 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 seguinte:

Consultando dados de array usando pgAdmin

Modificando PostgreSQL Ordem

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

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

Passo 2) Clique no botão Executar.

Modificando PostgreSQL Matriz usando pgAdmin

Passo 3)

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

SELECT * FROM Employees;

2.Clique no botão Executar.

Modificando PostgreSQL Matriz usando pgAdmin

Deve retornar o seguinte:

Modificando PostgreSQL Matriz usando pgAdmin

Pesquisando em um PostgreSQL Ordem

Passo 1) Digite a seguinte 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 PostgreSQL Matriz usando pgAdmin

Deve retornar o seguinte:

Pesquisando em um PostgreSQL Matriz usando pgAdmin

Expandindo matrizes

Passo 1) Digite a seguinte 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 seguinte:

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