EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Login
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL String Array

PostgreSQL String Array

Sohel Sayyad
Article bySohel Sayyad
Priya Pedamkar
Reviewed byPriya Pedamkar

Updated May 16, 2023

PostgreSQL String Array

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, the 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.

ADVERTISEMENT
Popular Course in this category
POSTGRESQL Course Bundle - 5 Courses in 1 | 1 Mock Test

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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 are declared with the data type of array and separated by a comma.

How to create a String Array in PostgreSQL?

  1. We must install PostgreSql in your system.
  2. Required basic knowledge about PostgreSql.
  3. 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 an 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.

PostgreSQL String Array output 1

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 the specified column name in the table which we need to display, and the table name is the 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.

PostgreSQL String Array output 2

  • 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.

PostgreSQL String Array output 3

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, the 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.

output 4

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.

output 5

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.

output 6

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.

output 7

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

We hope that this EDUCBA information on “PostgreSQL String Array” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. PostgreSQL Stored Procedures
  2. Extensions PostgreSQL
  3. PostgreSQL Clone Database
  4. PostgreSQL DDL
ADVERTISEMENT
MICROSOFT POWER BI Course Bundle - 8 Courses in 1
34+ Hours of HD Videos
8 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
CYBER SECURITY & ETHICAL HACKING Course Bundle - 13 Courses in 1 | 3 Mock Tests
64+ Hours of HD Videos
13 Courses
3 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
MICROSOFT AZURE Course Bundle - 15 Courses in 1 | 12 Mock Tests
63+ Hour of HD Videos
15 Courses
12 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
KALI LINUX Course Bundle - 6 Courses in 1
20+ Hours of HD Videos
6 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Extended Cyber Monday Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW