PostgreSQL Array: Funktioner, Type, Eksempel

Hvad er PostgreSQL Array?

In PostgreSQL, kan vi definere en kolonne som en række gyldige datatyper. Datatypen kan være indbygget, brugerdefineret eller opregnet type. Bortset fra dette spiller arrays en vigtig rolle i PostgreSQL.

Hver tilsvarende PostgreSQL datatypen leveres med en relevant matrixtype. For eksempel har heltalsdatatypen integer[] matrixtypen, karakterdatatypen har karakter[] matrixtypen osv.

Oprettelse af PostgreSQL Arrays

I folloenwing for eksempel vil vi oprette en tabel med navnet Medarbejdere med kontaktkolonnen defineret som et tekstarray:

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

Kommandoen skal køre med succes.

Oprettelse af PostgreSQL Arrays

Indsættelse PostgreSQL Array værdier

Lad os nu INSERT værdier i ovenstående tabel:

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

Indsættelsen skal køre med succes.

Indsættelse PostgreSQL Array værdier

Værdierne i den tredje kolonne, det vil sige kontakt, er blevet indsat som en matrix. Dette er opnået ved brug af ARRAY-konstruktøren.

I dette eksempel har vi omsluttet dem i kvadrat brackets []. Vi har to kontakter til medarbejderen Alice John.

Det er stadig muligt for os at bruge krøllede seler {} som vist nedenfor:

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

Kommandoen skal køre med succes.

Indsættelse PostgreSQL Array værdier

Ovenstående udsagn vil indsætte to rækker i tabellen Medarbejdere. Når du bruger krøllede klammeparenteser, er arrayet pakket ind i enkelte anførselstegn ('), mens tekstarray-elementerne er pakket ind i double citater (“).

Forespørgsel på matrixdata

For at forespørge på elementerne i et array bruger vi SELECT-sætningen.

For at se indholdet af vores Medarbejdertabel kører vi folloenwing kommando:

SELECT * FROM Employees;

Dette returnerer folloenwing:

Forespørgsel på matrixdata

Elementerne i matrixkolonnen, det vil sige kontakt, er blevet omsluttet af krøllede klammeparenteser {}.

For at få adgang til selve array-elementerne tilføjer vi et underskrift i kvadratet brackets []. Det første element i et array er på position 1.

For eksempel skal vi få navne på medarbejdere og kun deres første kontakt for de medarbejdere med mere end én kontakt. Vi kan få adgang til dette som kontakt[1].

Lad os se dette:

SELECT name, contact[1]
FROM Employees;

Dette vil returnere folloenwing:

Forespørgsel på matrixdata

Vi kan bruge SELECT-sætningen sammen med WHERE-sætningen til at filtrere rækker baseret på array-kolonnen.

For for eksempel at se medarbejderen med (408)-567-78234 som den anden kontakt, kan vi køre following kommando:

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

Dette vil returnere folloenwing:

Forespørgsel på matrixdata

Ændring PostgreSQL Array

Du kan opdatere hele eller et enkelt element i et array.

Her er indholdet af tabellen Medarbejdere:

Ændring PostgreSQL Array

Lad os opdatere det andet telefonnummer på medarbejderen James Bush, hvis id er 3:

Ændring PostgreSQL Array

Kør folloenwing kommando:

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

Kommandoen skal køre med succes:

Ændring PostgreSQL Array

Lad os forespørge i tabellen for at kontrollere, om ændringen lykkedes:

Ændring PostgreSQL Array

Ændringen lykkedes.

Searching i en PostgreSQL Array

I øjeblikket ser vores tabel med medarbejdere ud som følger:

Searching i en PostgreSQL Array

Antag, at vi har brug for at vide, hvem der ejer kontakten (408)-783-5731 uanset positionen i kontaktgruppen, kan vi bruge ANY()-funktionen som vist nedenfor:

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

Dette vil returnere folloenwing:

Searching i en PostgreSQL Array

Udvidelse af arrays

Vi kan opdele værdierne af en matrix i rækker. Denne proces er kendt som matrixudvidelse.

I eksemplet med tabellen Medarbejdere er der nogle medarbejdere med to kontakter i kontaktarrayet. Vi kan opdele disse i separate rækker.

PostgreSQL giver unnest()-funktionen, der kan bruges til dette.

For eksempel:

SELECT
   name,
   unnest(contact)
FROM
   Employees;

Dette vil returnere folloenwing:

Udvidelse af arrays

Medarbejderne Alice John og James Bush, har to kontakter. Vi kan opdele i separate rækker.

Bruger pgAdmin

Oprettelse af PostgreSQL Arrays

For at opnå det samme gennem pgAdmin, gør dette:

Trin 1) Log ind på din pgAdmin-konto.

Trin 2)

  1. Fra navigationslinjen til venstre - Klik på rbases.
  2. Klik på Demo-knappen

Oprettelse af PostgreSQL Arrays ved hjælp af pgAdmin

Trin 3) Indtast forespørgslen i forespørgselseditoren for at oprette tabellen Medarbejdere:

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

Trin 4) Klik på knappen Udfør.

Oprettelse af PostgreSQL Arrays ved hjælp af pgAdmin

Indsættelse PostgreSQL Array værdier

Trin 1) Skriv following forespørgsel i forespørgselseditoren:

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

Trin 2) Klik på knappen Udfør:

Indsættelse PostgreSQL Array værdier

Trin 3)

For at bruge krøllede seler i forespørgslen

Trin 1) Skriv following forespørgsel i forespørgselseditoren:

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

Trin 2) Klik på knappen Udfør:

Indsættelse PostgreSQL Array værdier

Forespørgsel på matrixdata

Trin 1) For at se indholdet af tabellen Medarbejdere skal du skrive following forespørgsel på forespørgselseditoren:

SELECT * FROM Employees;

Trin 2) Klik på knappen Udfør:

Forespørgsel på matrixdata ved hjælp af pgAdmin

Det burde returnere folloenwing:

Forespørgsel på matrixdata ved hjælp af pgAdmin

Trin 3) For at se de første kontakter med medarbejdere:

  1. Skriv following forespørgsel i forespørgselseditoren:
    SELECT name, contact[1]
    FROM Employees;
    
  2. Klik på knappen Udfør.

Forespørgsel på matrixdata ved hjælp af pgAdmin

Det burde returnere folloenwing:

Forespørgsel på matrixdata ved hjælp af pgAdmin

Trin 4) For at kombinere SELECT-sætningen med WHERE-sætningen:

  1. Skriv following kommando på forespørgselseditoren:
    SELECT
       name
    FROM
       Employees
    WHERE
       contact [ 2 ] = '(408)-567-78234';
    
  2. Klik på knappen Udfør.

Forespørgsel på matrixdata ved hjælp af pgAdmin

Det burde returnere folloenwing:

Forespørgsel på matrixdata ved hjælp af pgAdmin

Ændring PostgreSQL Array

Trin 1) For at opdatere brugerens anden kontaktperson med id 3 skal du køre following kommando:

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

Trin 2) Klik på knappen Udfør.

Ændring PostgreSQL Array ved hjælp af pgAdmin

Trin 3)

1. Indtast folloenwing kommando på forespørgselseditoren for at kontrollere, om ændringen lykkedes:

SELECT * FROM Employees;

2. Klik på knappen Udfør.

Ændring PostgreSQL Array ved hjælp af pgAdmin

Det burde returnere folloenwing:

Ændring PostgreSQL Array ved hjælp af pgAdmin

Searching i en PostgreSQL Array

Trin 1) Skriv following forespørgsel i forespørgselseditoren:

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

Trin 2) Klik på knappen Udfør.

Searching i en PostgreSQL Array ved hjælp af pgAdmin

Det burde returnere folloenwing:

Searching i en PostgreSQL Array ved hjælp af pgAdmin

Udvidelse af arrays

Trin 1) Skriv following forespørgsel i forespørgselseditoren:

SELECT
   name,
   unnest(contact)
FROM
   Employees;

Trin 2) Klik på knappen Udfør.

Udvidelse af arrays ved hjælp af pgAdmin

Det burde returnere folloenwing:

Udvidelse af arrays ved hjælp af pgAdmin

Resumé

  • PostgreSQL giver os mulighed for at definere en tabelkolonne som en matrixtype.
  • Arrayet skal være gyldigt datatype såsom heltal, tegn eller brugerdefinerede typer.
  • For at indsætte værdier i en matrixkolonne bruger vi ARRAY-konstruktøren.
  • Hvis der er mere end ét element i den samme række i en matrixkolonne, er det første element på position 1.
  • Hver værdi kan tilgås ved at sende et underskrift inden for kvadratet brackets [].
  • Elementerne i arrayet kan hentes ved hjælp af SELECT-sætningen.
  • Værdierne af matrixkolonnen kan omsluttes i kvadrat brackets [] eller krøllede seler {}.
  • Vi kan søge efter matrixkolonneværdier ved hjælp af ANY()-funktionen.

Download databasen brugt i denne vejledning