postgres datatype array - ghdrako/doc_snipets GitHub Wiki

Array type

• https://www.postgresql.org/docs/devel/arrays.html
• https://www.w3resource.com/PostgreSQL/postgresql-array-operators-and-functions.php

PostgreSQL has built-in support for arrays with ability to process array elements from SQL directly. This capability includes indexing facilities thanks to GIN indexing.

Arrays can be used to denormalize data and avoid lookup tables. A good rule of thumb for using them that way is that you mostly use the array as a whole, even if you might at times search for elements in the array. Heavier processing is going to be more complex than a lookup table.

Indexing in PostgreSQL arrays starts at one, not at zero, which may differ from what you are used to in other programming languages.

Creating

A classic example of a good use case for PostgreSQL arrays is user-defined tags.

create table hashtag 
 ( 
 id bigint primary key, 
 date timestamptz, 
 uname text, 
 message text, 
 location point, 
 hashtags text[] 
 );

create table array_test
(
id serial PRIMARY KEY, 
myarray INTEGER[]
);
insert into array_test(myarray) values(array[1,2,3,4,5]); 
select *
from array_test
where array[1,3,4,6,8]::INTEGER[]= myarray; 


select id, unnest(myarray) as unnested
from array_test;
1 1
1 2
1 3
1 4
1 5

Inserting

• Using ARRAY constructor

INSERT INTO students (id, name, age, home_coordinates) VALUES (1, 'John', 15, ARRAY[40.7, 74.0]);

• Directly inserting values

INSERT INTO students (id, name, age, home_coordinates) VALUES (1, 'John', 15, {40.7, 74.0});

Update Remove Append Preppend

• UPDATE statement can be used to remove element(s) from the array. To remove the value 74.0 from the array

UPDATE students SET home_coordinates = array_remove(home_coordinates, 74.0);

• Append data to the array. To add the value 80.0 at the end of home_coordinates array:

UPDATE students SET home_coordinates = home_coordinates || ARRAY[80.0];

To append or prepend (inserting before the current values) an array you can use the ARRAY_APPEND and ARRAY_PREPEND functions accordingly.

-- update by appending and prepending
UPDATE  shopping_cart SET  products = ARRAY_APPEND(products, 'product_x') WHERE  cart_id = 1;
UPDATE   shopping_cart SET  products = ARRAY_PREPEND('product_x', products) WHERE  cart_id = 2;

• update arrays

UPDATE  shopping_cart SET products = ARRAY['product_a','product_b','product_e'] WHERE  cart_id = 1;
UPDATE  shopping_cart SET products[1] = 'product_f' WHERE cart_id = 2;

Quering and displaying data

SELECT home_coordinates[1] FROM students; # First element in array - index start from 1 not 0!!!

• ANY - spawdzenie czy element wystepuje w tablicy Display the whole row where the latitude or longitude of a home is above 40 degrees

SELECT * FROM students WHERE 40 < ANY (home_coordinates);

-- return if  product in array
SELECT cart_id,  products FROM shopping_cart WHERE 'product_c' = ANY (products);

Contains Operator @>

SELECT cart_id, products FROM shopping_cart WHERE products  @> ARRAY['product_a', 'product_b'];

Slicing

Slices work with the [start:end]-syntax. Array item access through slicing for all rows with more than two array elements.

-- return slice of array
SELECT
  cart_id,
  products [1:2] AS first_two_products
FROM
  shopping_cart
WHERE
  CARDINALITY(products) > 2;

Cardinality - Num element in array

CARDINALITY keyword. This will return the number of items in the array as an integer.

-- return length of array
SELECT
  cart_id,
  CARDINALITY(products) AS num_products
FROM
  shopping_cart;

Array functions

• array_cat function concatenates or merges multiple arrays and returns the merged array:

SELECT array_cat(array[1, 2], array[3, 4]);
array_cat
----------
{1,2,3,4}

• array_dims function returns the dimensions of an array:

SELECT array_dims(array[1, 2, 3], [4, 5, 6](/ghdrako/doc_snipets/wiki/1,-2,-3],-[4,-5,-6));
array_cat
----------
[1:2][1:3]

• array_length function returns the length of an array:

SELECT array_length(home_coordinates, 1) AS total_coordinates FROM students;
total_coordinates
-----------------
2

Indexing

It is possible to establish indexes on array columns in PostgreSQL. Gist (Generalized Search Tree) or GIN (Generalized Inverted Index) methods can be used to specify indexes on array columns.

regexp_matches(message, '(#[^ ,]+)', 'g') as match

• regexp_matches() with the g flag to return every match found and not just the first tag in a message. Those multiple matches are returned one per rów.

array_agg(match[1] order by match[1]) as hashtags

• unnest() function allows processing the array’s content as if it were just another relation.

select tag, count(*) 
 from hashtag, unnest(hashtags) as t(tag) 
 group by tag 
 order by count desc 
 limit 10;

create index on hashtag using gin (hashtags);

Gin index access method allows PostgreSQL to index the contents of the arrays, the tags them-selves, rather than each array as an opaque value. Replacement for array is lookup table if array Will be inefficient.

Using in pg/plsql

DECLARE  my_array INTEGER[];

You can initialize an array with values using curly braces:

DECLARE  my_array INTEGER[] := '{1, 2, 3}';

You can access individual elements of an array using square brackets. The following is a simple example:

postgres=# DO  
$$  
DECLARE  
  my_array INTEGER[] := '{1, 2, 3}';
  second_element INTEGER;
BEGIN
  second_element := my_array[2];
  RAISE NOTICE 'second element from my array is: %', second_element;
END;
$$;