PostgreSQL offers three types to work with the string data, i.e., TEXT, VARCHAR, and CHAR. Postgres allows us to create a string array using one of these data types. We can perform various operations on string arrays using different queries and built-in functions, such as insert, update, append, etc.

This write-up will teach you how to create and use string arrays in Postgres using practical examples. So, let’s start!

How to Create a String Array in Postgres?

To create a string array in Postgres, specify the column name followed by the data type and a set of square brackets:

CREATE TABLE st_information (
st_id SERIAL PRIMARY KEY, 
st_name TEXT[],
st_email VARCHAR[]
);

The st_information table with four columns has been created successfully.

How to Fetch/Display the Data of a String Array in Postgres?

Execute the “SELECT *” command to show the table’s data:

SELECT * FROM st_information;

The output shows that the st_information has three columns. Two of them are string-type arrays.

How Do I INSERT Data/Records Into a String Array in Postgres?

In Postgres, the ARRAY keyword, followed by a set of square brackets, is used to insert data into a string array. Alternatively, a set of curly braces enclosed within the single quotations can be used in Postgres. Let’s learn how to insert data into a string array using the INSERT INTO statement:

INSERT INTO st_information(st_name, st_email)
VALUES('{"Joe", "Root"}', '{"joe@xyz.com", "ceo@xyz.com"}'),
('{"Tim", "David"}', '{"tim@xyz.com"}');

The above snippet shows that the INSERT INTO query was executed successfully. Let’s verify the data insertion using the “st_information” table:

SELECT * FROM st_information;

The output clarifies that the data has been successfully inserted into the string arrays.

How to Update a String Array in Postgres?

Use the UPDATE query with the SET clause to modify the whole string array or a specific index of the string array:

UPDATE st_information
SET st_email[1] = 'hr@xyz.com '
WHERE st_id = 2;

Let’s run the SELECT query to see the modified value:

SELECT * FROM st_information;

The output proves that the targeted string array has been updated successfully.

How to Append an Element to String Array in Postgres?

You can use any array function to achieve various functionalities, such as the ARRAY_APPEND() function, the ARRAY_LENGTH() function, etc. In this example, we will use the append function to append “tim@xyz.com” at the end of the “st_email” array:

SELECT ARRAY_APPEND(st_email, 'tim@xyz.com')
FROM st_information
WHERE st_id = 2;

The above snippet shows that an element is appended at the end of the input string array.

That’s it from this Postgres guide!

Conclusion

PostgreSQL allows us to create a string array using one of three data types: CHAR, VARCHAR, and TEXT. We can perform various operations on string arrays using different queries and built-in functions, such as INSERT, UPDATE, ARRAY_APPEND(), etc. This write-up explained how to create a string array and perform various operations on that array using different queries and built-in functions.