how to create an array in vertica ??

ashu123

hello,
I need to query on arrays,how to create an array , and how to query on arrays ?

ashu123

i tried but im getting like this ):

ashu123

plzz provide solution

Jim_Knicely

Hi,

From your first post, here is a simple solution to get all the rows where the list contains a 2:

dbadmin=> select * from dmp;
  name   |  id  |   list
---------+------+-----------
 asha    | 2725 | [1,2,3,4]
 vertica |   27 | [1,3,4]
 jim     |   43 | [2]
(3 rows)

dbadmin=> select * from dmp where list ilike '%2,%' or list ilike '%2]%';
 name |  id  |   list
------+------+-----------
 asha | 2725 | [1,2,3,4]
 jim  |   43 | [2]
(2 rows)

ashu123

hii jim
can you please explain that query to me , is ( , ) taking as % ??

ashu123

thanku

Jim_Knicely

The data in the LIST column of your DMP table in your example appears to be VARCHAR. I simply retrieved the rows where the string value of the column matches a specified pattern.

See: https://my.vertica.com/docs/8.1.x/HTML/index.htm#Authoring/SQLReferenceManual/LanguageElements/Predicates/LIKE-predicate.htm

From the documentation notice that you can specify what strings to match. This pattern to match can include:

• Underscore (_) matches any single character.
• Percent sign (%) matches any string of zero or more characters.

ashu123

hii jim,
How do we store array of integers in vertica (not flex table) not in string form because for string form ilike operation working but it takes too much time.So if it possible to store array of integers please explain me with an example.
In the above example make that 'list' field as array

Jim_Knicely

Hi,

There is no array data type for a column in a standard table in Vertica.

Is the max size of the array known? You could create a separate INT column for each array element if you want to work with integers...

One Example:

dbadmin=> create table dmp (
dbadmin(>   name varchar(10),
dbadmin(>   id int,
dbadmin(>   list varchar(100),
dbadmin(>   list1 int default decode(split_part(translate(list, '[]', ''), ',', 1), '', null, split_part(translate(list, '[]', ''), ',', 1))::int,
dbadmin(>   list2 int default decode(split_part(translate(list, '[]', ''), ',', 2), '', null, split_part(translate(list, '[]', ''), ',', 2))::int,
dbadmin(>   list3 int default decode(split_part(translate(list, '[]', ''), ',', 3), '', null, split_part(translate(list, '[]', ''), ',', 3))::int,
dbadmin(>   list4 int default decode(split_part(translate(list, '[]', ''), ',', 4), '', null, split_part(translate(list, '[]', ''), ',', 4))::int
dbadmin(> );
CREATE TABLE
dbadmin=> insert into dmp (name, id, list) select 'asha', 2725, '[1,2,3,4]';
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into dmp (name, id, list) select 'vertica', 27, '[1,3,4]';
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into dmp (name, id, list) select 'jim', 43, '[2]';
 OUTPUT
--------
      1
(1 row)

dbadmin=> select * from dmp;
  name   |  id  |   list    | list1 | list2 | list3 | list4
---------+------+-----------+-------+-------+-------+-------
 asha    | 2725 | [1,2,3,4] |     1 |     2 |     3 |     4
 vertica |   27 | [1,3,4]   |     1 |     3 |     4 |
 jim     |   43 | [2]       |     2 |       |       |
(3 rows)

dbadmin=> select * from dmp where list1 = 2 or list2 = 2 or list3 = 2 or list4 = 2;
 name |  id  |   list    | list1 | list2 | list3 | list4
------+------+-----------+-------+-------+-------+-------
 asha | 2725 | [1,2,3,4] |     1 |     2 |     3 |     4
 jim  |   43 | [2]       |     2 |       |       |
(2 rows)

Note that you can use FLEX table to flatten a JSON array out into unique columns.

See:

https://my.vertica.com/docs/8.1.x/HTML/index.htm#Authoring/FlexTables/FJSONPARSERreference.htm?Highlight=flatten_arrays

Jim_Knicely

You can also create a view...

dbadmin=> select * from dmp;
  name   |  id  |   list
---------+------+-----------
 asha    | 2725 | [1,2,3,4]
 jim     |   43 | [2]
 vertica |   27 | [1,3,4]
(3 rows)

dbadmin=> create view dmp_view as
dbadmin-> select
dbadmin->   name,
dbadmin->   id,
dbadmin->   list,
dbadmin->   decode(split_part(translate(list, '[]', ''), ',', 1), '', null, split_part(translate(list, '[]', ''), ',', 1))::int list1,
dbadmin->   decode(split_part(translate(list, '[]', ''), ',', 2), '', null, split_part(translate(list, '[]', ''), ',', 2))::int list2,
dbadmin->   decode(split_part(translate(list, '[]', ''), ',', 3), '', null, split_part(translate(list, '[]', ''), ',', 3))::int list3,
dbadmin->   decode(split_part(translate(list, '[]', ''), ',', 4), '', null, split_part(translate(list, '[]', ''), ',', 4))::int list4
dbadmin-> from dmp;
CREATE VIEW

dbadmin=> select * from dmp_view where list1 = 2 or list2 = 2 or list3 = 2 or list4 = 2;
 name |  id  |   list    | list1 | list2 | list3 | list4
------+------+-----------+-------+-------+-------+-------
 asha | 2725 | [1,2,3,4] |     1 |     2 |     3 |     4
 jim  |   43 | [2]       |     2 |       |       |
(2 rows)