CODEWITHSUNDEEP
postgresql
flazzo
flazzo

Reputation: 253

Iterating through an array of strings in postgresql

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.

Upvotes: 3

Views: 1295

Answers (1)

Nick
Nick

Reputation: 7431

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

Upvotes: 3

Related Questions