How to find duplicates in arrays postgres?

There is a column in postgre, which keeps an array of intererov.
For example:
{1,2,3}
{4, 5}
{3, 6}
Need to find these line one and three where at least one item is repeated. Please tell me how to implement it?
April 7th 20 at 10:47
2 answers
April 7th 20 at 10:49
Solution
If it's possible to decompose up to 3rd normal form database, it is best to do this, otherwise you'll have to use the crutch of the 4 functions to enter values into these arrays and then work with them in queries.
select a.* from
(
 select b.id, b.split_value, count(b.id) over (partition by b.split_value) as v_count
from
(
 select c.id, cast(unnest(string_to_array(translate(c.value,'{}',"),',')) as INTEGER) as split_value
from
 ( -- simulated table data
 (select 0 as id, '{1,2,3}' as value) union all
 (select 1, '{4, 5}') union all
 (select 2, '{3, 6}') 
 ) c
 ) b
) a
where a.v_count > 1

Solution B (the original data - type array).
select a.* from
(
 select b.id, b.split_value, count(b.id) over (partition by b.split_value) as v_count
from
(
 select c.id, unnest(c.value) as split_value
from
 ( -- simulated table data
 (select 0 as id, array[1,2,3] as value) union all
 (select 1, array[4,5]) union all
 (select 2, array[3,6]) 
 ) c
 ) b
) a
where a.v_count > 1
select c.id, cast(unnest(string_to_array(translate(c.qualification_documents,'{}',"),',')) as INTEGER) as split_value
from
 ( -- simulated table data
 select id, qualification_documents from communication_sailorkeys
 ) c

Says this error
ERROR: function translate(integer[], unknown, unknown) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts.
- eriberto commented on April 7th 20 at 10:52
Instead of:
translate(c.qualification_documents,'{}',")
Use:
replace(replace(c.qualification_documents,'{',"),'}',")

I use this function to trim leading and trailing braces.
This: '{1,2,3}'
For this row the procedure is this:
1. translate - remove brackets -> '1,2,3'
2. string_to_array - convert a string to the data type of the array -> [1,2,3]
3. unnest - expand the array to multiple rows -> one row: 1, two: 2 ...
4. cast - in each row of the transformed column value to an integer.
It is now possible to use SQL.
PS: If the attribute already qualification_documents the data type is an array, then steps 1, 2 and 4 do not need a bunch of nested functions is significantly reduced.
Most likely, in your case, it is sufficient to use unnest (added solution B). - Ayla.Goyet commented on April 7th 20 at 10:55
April 7th 20 at 10:51
Relational algebra are almost not able to operate horizontal collections. That is it is. And SQL was created for other things.

The correct thing to do is to create a temporary sign. Pour your arrays with a turn of 90 degrees and perform the simplest (! really simple!) query with grouping.

This decision will be ideologically correct than loading an SQL non-inherent tasks.

Find more questions by tags PostgreSQLSQL