PostgreSQL Array: Functies, Type, Voorbeeld

Wat is PostgreSQL Array?

In PostgreSQL, kunnen we een kolom definiëren als een array van geldige gegevenstypen. Het gegevenstype kan een ingebouwd, door de gebruiker gedefinieerd of opgesomd type zijn. Daarnaast spelen arrays een belangrijke rol PostgreSQL.

Elke corresponderende PostgreSQL gegevenstype wordt geleverd met een relevant arraytype. Het datatype integer heeft bijvoorbeeld het arraytype integer[], het datatype character heeft het arraytype character[], enz.

Wij creëren PostgreSQL arrays

In het volgende voorbeeld maken we een tabel met de naam Medewerkers, waarbij de kolom Contactpersonen is gedefinieerd als een tekstarray:

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

De opdracht zou met succes moeten worden uitgevoerd.

Wij creëren PostgreSQL arrays

invoegen PostgreSQL Arraywaarden

Laat het ons weten invoegen waarden in de bovenstaande tabel:

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

Het invoegen zou succesvol moeten verlopen.

invoegen PostgreSQL Arraywaarden

De waarden van de derde kolom, dat wil zeggen contact, zijn als array ingevoegd. Dit is bereikt door gebruik te maken van de ARRAY-constructor.

In dit voorbeeld hebben we ze tussen vierkante haken [] geplaatst. We hebben twee contactpersonen voor de werknemer Alice John.

Het is nog steeds mogelijk dat we accolades {} gebruiken, zoals hieronder weergegeven:

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

De opdracht zou met succes moeten worden uitgevoerd.

invoegen PostgreSQL Arraywaarden

De bovenstaande statements voegen twee rijen toe aan de Employees-tabel. Wanneer u accolades gebruikt, wordt de array omsloten door enkele aanhalingstekens ('), terwijl de tekst-array-items omsloten worden door dubbele aanhalingstekens (“).

Arraygegevens opvragen

Om de elementen van een array te bevragen, gebruiken we de SELECT-instructie.

Om de inhoud van onze tabel Werknemers te bekijken, voeren we de volgende opdracht uit:

SELECT * FROM Employees;

Dit geeft het volgende terug:

Arraygegevens opvragen

De elementen van de arraykolom, dat wil zeggen contact, staan ​​tussen accolades {}.

Om toegang te krijgen tot de array-elementen zelf, voegen we een subscript toe tussen vierkante haken []. Het eerste element in een array staat op positie 1.

We hebben bijvoorbeeld de namen van werknemers nodig en alleen hun eerste contact voor werknemers met meer dan één contact. Wij hebben hier toegang toe als contactpersoon[1].

Laten we dit eens zien:

SELECT name, contact[1]
FROM Employees;

Dit retourneert het volgende:

Arraygegevens opvragen

We kunnen de SELECT-instructie samen met de WHERE-clausule gebruiken om rijen te filteren op basis van de arraykolom.

Om bijvoorbeeld de werknemer met (408)-567-78234 als tweede contactpersoon te zien, kunnen we de volgende opdracht uitvoeren:

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

Dit retourneert het volgende:

Arraygegevens opvragen

Het wijzigen PostgreSQL reeks

U kunt alle elementen of een enkel element van een array bijwerken.

Dit is de inhoud van de tabel Werknemers:

Het wijzigen PostgreSQL reeks

Laten we het tweede telefoonnummer van de werknemer James Bush, wiens id 3 is, bijwerken:

Het wijzigen PostgreSQL reeks

Voer het volgende commando uit:

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

De opdracht zou met succes moeten worden uitgevoerd:

Het wijzigen PostgreSQL reeks

Laten we de tabel doorzoeken om te controleren of de wijziging succesvol was:

Het wijzigen PostgreSQL reeks

De verandering was succesvol.

Zoeken in een PostgreSQL reeks

Momenteel ziet onze tabel Medewerkers er als volgt uit:

Zoeken in een PostgreSQL reeks

Stel dat we moeten weten wie de eigenaar is van het contact (408)-783-5731, ongeacht de positie binnen de contactarray, dan kunnen we de functie ANY() gebruiken, zoals hieronder weergegeven:

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

Dit retourneert het volgende:

Zoeken in een PostgreSQL reeks

Arrays uitbreiden

We kunnen de waarden van een array in rijen splitsen. Dit proces staat bekend als array-uitbreiding.

In het voorbeeld van de tabel Werknemers zijn er enkele werknemers met twee contactpersonen in de contactarray. We kunnen deze in afzonderlijke rijen opsplitsen.

PostgreSQL biedt de functie unnest() die hiervoor kan worden gebruikt.

Bijvoorbeeld:

SELECT
   name,
   unnest(contact)
FROM
   Employees;

Dit retourneert het volgende:

Arrays uitbreiden

De medewerkers Alice John en James Bush hebben twee contactpersonen. We kunnen ons opsplitsen in afzonderlijke rijen.

Gebruik pgAdmin

Wij creëren PostgreSQL arrays

Om hetzelfde te bereiken via pgAdmin, doet u het volgende:

Stap 1) Log in op uw pgAdmin-account.

Stap 2)

  1. Klik in de navigatiebalk aan de linkerkant op rbases.
  2. Klik op de demoknop

Wij creëren PostgreSQL Arrays met pgAdmin

Stap 3) Typ de query in de query-editor om de tabel Werknemers te maken:

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

Stap 4) Klik op de knop Uitvoeren.

Wij creëren PostgreSQL Arrays met pgAdmin

invoegen PostgreSQL Arraywaarden

Stap 1) Typ de volgende query in de query-editor:

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

Stap 2) Klik op de knop Uitvoeren:

invoegen PostgreSQL Arraywaarden

Stap 3)

Accolades gebruiken in de query

Stap 1) Typ de volgende query in de query-editor:

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

Stap 2) Klik op de knop Uitvoeren:

invoegen PostgreSQL Arraywaarden

Arraygegevens opvragen

Stap 1) Om de inhoud van de tabel Werknemers te bekijken, typt u de volgende query in de query-editor:

SELECT * FROM Employees;

Stap 2) Klik op de knop Uitvoeren:

Arraygegevens opvragen met pgAdmin

Het volgende zou moeten worden geretourneerd:

Arraygegevens opvragen met pgAdmin

Stap 3) Om de eerste contacten van medewerkers te zien:

  1. Typ de volgende query in de query-editor:
    SELECT name, contact[1]
    FROM Employees;
    
  2. Klik op de knop Uitvoeren.

Arraygegevens opvragen met pgAdmin

Het volgende zou moeten worden geretourneerd:

Arraygegevens opvragen met pgAdmin

Stap 4) Om de SELECT-instructie te combineren met de WHERE-clausule:

  1. Typ de volgende opdracht in de query-editor:
    SELECT
       name
    FROM
       Employees
    WHERE
       contact [ 2 ] = '(408)-567-78234';
    
  2. Klik op de knop Uitvoeren.

Arraygegevens opvragen met pgAdmin

Het volgende zou moeten worden geretourneerd:

Arraygegevens opvragen met pgAdmin

Het wijzigen PostgreSQL reeks

Stap 1) Om het tweede contact van gebruiker met id 3 bij te werken, voert u de volgende opdracht uit:

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

Stap 2) Klik op de knop Uitvoeren.

Het wijzigen PostgreSQL Array met behulp van pgAdmin

Stap 3)

1. Typ de volgende opdracht in de query-editor om te controleren of de wijziging succesvol is:

SELECT * FROM Employees;

2.Klik op de knop Uitvoeren.

Het wijzigen PostgreSQL Array met behulp van pgAdmin

Het volgende zou moeten worden geretourneerd:

Het wijzigen PostgreSQL Array met behulp van pgAdmin

Zoeken in een PostgreSQL reeks

Stap 1) Typ de volgende query in de query-editor:

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

Stap 2) Klik op de knop Uitvoeren.

Zoeken in een PostgreSQL Array met behulp van pgAdmin

Het volgende zou moeten worden geretourneerd:

Zoeken in een PostgreSQL Array met behulp van pgAdmin

Arrays uitbreiden

Stap 1) Typ de volgende query in de query-editor:

SELECT
   name,
   unnest(contact)
FROM
   Employees;

Stap 2) Klik op de knop Uitvoeren.

Arrays uitbreiden met pgAdmin

Het volgende zou moeten worden geretourneerd:

Arrays uitbreiden met pgAdmin

Samenvatting

  • PostgreSQL stelt ons in staat een tabelkolom als arraytype te definiëren.
  • De array moet geldig zijn data type zoals gehele getallen, tekens of door de gebruiker gedefinieerde typen.
  • Om waarden in een arraykolom in te voegen, gebruiken we de ARRAY-constructor.
  • Als er meer dan één element in dezelfde rij van een arraykolom staat, bevindt het eerste element zich op positie 1.
  • Elke waarde kan worden benaderd door een subscript tussen vierkante haken [] te plaatsen.
  • De elementen van de array kunnen worden opgehaald met behulp van de SELECT-instructie.
  • De waarden van de matrixkolom kunnen tussen vierkante haken [] of accolades {} worden geplaatst.
  • We kunnen zoeken naar array-kolomwaarden met behulp van de functie ANY().

Download de database die in deze zelfstudie wordt gebruikt