Introduction to PostgreSQL String Array
PostgreSQL provides different types of data types. String Array is one of the data types provided by PostgreSQL, array is a user-defined data type or built-in data type. PostgreSQL arrays play an important task in the database system, PostgreSQL provides a facility to define columns as an array with any valid data type, array should be integer [] type, character [] type. Sometimes we need to create our own data type. At that time, PostgreSQL created an equivalent array data type in the backend. We can perform different operations on strings using an array of data types.
Syntax
create table table_name (column 1 data type [], column 2 data type []……….., column n data type[]);
Explanation
In the above syntax, we can declare a String Array data type at the time of table creation. Where table name is the specified table name that we need to create and column 1, column 2, and column n declared with the data type of array and it separated by using a comma.
How to create a String Array in PostgreSQL?
- We must install PostgreSql in your system.
- We required basic knowledge about PostgreSql.
- We must require tables to perform String Array operation.
Operations of String Array
We can perform different operations on String Array as follows.
To perform an Array operation we need a table, so first, we create a table using the following statement.
CREATE TABLE customer (cust_id serial PRIMARY KEY,
cust_name VARCHAR (100), cust_phones TEXT []);
Explanation:
In the above statement, we create a table name as a customer with three columns such as cust_id with data type is an integer, cust_name with data type is varchar, and cust_phones with data type is text (string).
1. Insert String Array Operation
In this operation, we can insert a String Array into the table using the following syntax.
Syntax
Insert into table name (column name1, column name 2, ………….column nameN) values (value1 ARRAY[], value2 ARRAY[],…………valueN );
Explanation
In the above syntax where insert into is a keyword, column name1, column name2 is a specified column name in the table, and values mean actual string array values which we need to insert into the table.
Example
Now perform the insertion operation on the above table using the following statement.
Insert into customer ( cust_name, cust_phones)
values ('John', ARRAY [ '(206)-678-1233', '(303)-567-9876' ]);
Explanation
In the above statement, we insert a record into the table. See the customer name is john and we insert two phone numbers for john customers. The phone is actually an integer value but in this example, we declared it as a string (Text).
We can perform insertion operation with String Array using curly braces using the following statement.
Insert into customer (cust_name, cust_phones)
Values ('Jacson','{"(204)-123-3452"}'), ('Paul','{"(222)-654-0979","(205)-756-13345"}');
select * from customer;
Explanation
In the above statement, the customer Jacson has a single phone number and the customer Paul has two phone numbers and we use curly braces to construct String Array. Illustrate the result of the above declaration by using the use of the following snapshot.
2. Display String Array Operation
In this operation, we display the Array String using the following syntax.
Syntax
select column name from table name ;
Explanation
In the above syntax, we use a select clause to retrieve the data from the table where the column name is specified column name in the table which we need to display and table name is specified table name from the database.
Example
- Suppose users need to show a single phone number of a customer, so at that time we use the following statement.
select cust_name, cust_phones [ 1 ] from customer;
Explanation
We can access any single array by using subscript brackets []. But the default value of the subscript bracket is one in PostgreSQL. Illustrate the result of the above declaration by using the use of the following snapshot.
- In some cases, users need a second phone number from the table so use the following statement.
select cust_name, cust_phones [ 2 ] from customer;
Illustrate the result of the above declaration by using the use of the following snapshot.
3. Update String Array Operation
PostgreSQL also provides a facility to update String Array (single or whole array).
Syntax
update tableName set columnName[] =’value’
where condition;
Explanation
In the above syntax, we use the update command where table name means specified table name, set is the keyword, the column name is the specified column name which we need to update and where is used to condition.
Example
Single String Array Update
update customer set cust_phones [1] = '(707)-111-2222'
where cust_id = 1;
select * from customer;
Explanation
In the above example, we update the first phone number in String Array whose cust_id is 1. Illustrate the result of the above declaration by using the use of the following snapshot.
Similarly, we can update the second phone number of a customer using the following statement.
update customer set cust_phones [2] = '(606)-555-7643'
where cust_id = 1;
select * from customer;
Illustrate the result of the above declaration by using the use of the following snapshot.
4. Whole String Array Update
In this operation, we update the whole String Array.
Example
Suppose we need to update the whole String in the table at that time we use the following statement to update the whole string in PostgreSQL.
update customer set cust_phones = '{"(909)-555-11111"}'
where cust_id = 1;
select * from customer;
Explanation
In the above example, we update the whole String Array. Illustrate the result of the above declaration by using the use of the following snapshot.
5. Expand String Array Operation
PostgreSQL provides the unnest array function to expand the whole String Array.
Syntax
select column name1, column name2, unnest(String Array column) from table name;
Example
Suppose we need to expand all customers’ phone numbers at that time we use the following statement.
select cust_id , cust_name, unnest (cust_phones) from customer;
Illustrate the result of the above declaration by using the use of the following snapshot.
Conclusion
We hope from this article you have understood about PostgreSQL String Array statement. From the above article, we learn the basic syntax of String Array as well as different String Array operations with different examples. From this article, we learn how we can implement a String Array operation.
Recommended Articles
This is a guide to PostgreSQL String Array. Here we discuss How to create a String Array in PostgreSQL along with syntax, examples, and explanation. You may also have a look at the following articles to learn more –
