Iterating through an array of strings in postgresql

Question

I am very new to SQL. Still learning. I know that postgres can store array data, but how would I go about performing operations on that data? I have an n-length array of strings supplied by the user. I want to check if each string exists on the table before inserting it.

I've made an attempt, but I don't know if I'm doing this properly. I am connecting to the db using Massive if that helps any

The query is made with array of strings passed in.

DECLARE
  a text[] = $1
FOREACH i IN ARRAY a
LOOP 

DO 
$do$
BEGIN
  IF NOT EXISTS (SELECT tag_id FROM test_table WHERE tag_name = i) THEN
    INSERT INTO test_table (tag_name) 
    VALUES ($1);
END IF;
END
$do$

END LOOP;

I've got an "error: syntax error at or near'text'", but that may be the least of my problems.

Nick · Accepted Answer · 2018-09-12 16:22:01Z

3

Instead of a LOOP, you can unnest an array then test to see if the value(s) are in the other table, like so:

INSERT INTO test (tag_name)
SELECT tag_name
FROM (SELECT unnest(a) AS tag_name) AS arr -- unnest the array and use like a table
WHERE NOT EXISTS (SELECT 1 FROM test_table WHERE tag_name = arr.tag_name) -- ensure tag_name is NOT in the test_table

This will insert only values from the array that are not already listed in test_table.

Documentation: https://www.postgresql.org/docs/current/static/functions-array.html

edited Sep 12, 2018 at 16:22

answered Sep 11, 2018 at 23:47

Nick

7,5012 gold badges25 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

flazzo Over a year ago

I'm not sure I understand entirely. I'm inputting the following INSERT INTO test_table (tag_name) SELECT tag_name FROM (SELECT unnest($1) AS tag_name) AS arr WHERE EXISTS (SELECT tag_name FROM test_table WHERE tag_name = arr.tag_name) If I understand (and I don't think I do), this treats the array as a one-column table and then inserts a row into the table if the array item is not found. That's great, but the query fails if there are any duplicates. If none of the items would be duplicates, then there is no error, but they're not inserted either.

Nick Over a year ago

If you are saying you need to insert values that don't match, then use WHERE NOT EXISTS. I've updated the answer to reflect that.

flazzo Over a year ago

Oh, I see. That's not too hard. Thank you very much.

Collectives™ on Stack Overflow

Iterating through an array of strings in postgresql

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related