PostgreSQL Array
Introduction
When you use PostgreSQL, you’re able to take advantage of the variety of data types it provides. Take the PostgreSQL array, for example. Arrays organize elements in a logical way. These data structures enable data to be stored so that it can be retrieved quickly.
With arrays, you can create and store a list that contains a collection of data. Arrays also streamline search by allowing you to store values in a column instead of having to find them on separate tables. Find out how easy it is to work with arrays in PostgreSQL. Begin with this tutorial which shows you how to add array code, perform an insert array data operation, and then run a PostgreSQL array-based query.
Prerequisite
- PostgresSQL – Install the database software version for your type of OS.
NOTE: You should know how SQL works in general as well as how to execute basic queries in PostgreSQL.
Create a Sample Dataset with Array
Make a dataset to use with this tutorial. The notation ‘[]’ for brackets is what you’ll use to construct an array-filled table. Another way to create a table that has arrays in it is with the keyword “ARRAY.”
You’ll see the brackets [] and “ARRAY” throughout this tutorial because they mean the same thing.
If you haven’t already, go ahead and create a sample database in PostgreSQL. Name it
studentdb
.Use the
c studentdb
to make a database connection:
1 2 3 | postgres=# CREATE DATABASE studentdb; CREATE DATABASE postgres=# c studentdb; |
- You should see a similar result like this one below:
1 | You are now connected to database "studentdb" as user "postgres". |
- You’re now ready to create a table with a PostgreSQL array like this:
1 2 3 4 5 | CREATE TABLE student ( name text, grades INT[], contacts VARCHAR ARRAY ); |
Here’s an explanation of the code you just entered above.
You named the table
student
.The field ‘name’ corresponds to data type
text
.Since you’re going to use many grades and semesters for the students, you have an array of several integers for the grade field.
A character array has the data type
varchar
for the fieldcontacts
.Confirm that you made the table correctly with the
d student
command, and the result you get should be similar to the one here below:
1 2 3 4 5 6 7 | studentdb=# \d student Table "public.student" Column | Type | Collation | Nullable | Default ----------+---------------------+-----------+----------+--------- name | text | | | grades | integer[] | | | contacts | character varying[] | | | |
Insert Record with Arrays in PostgreSQL
- Try inserting a few records using arrays. Put them right into your sample PostgreSQL array database.
1 2 3 4 5 6 7 8 | INSERT INTO student (name, grades, contacts) VALUES ( 'John Doe', ARRAY [ 5,4,5,4], ARRAY [ '(022)-545-9081', '(022)-588-7421' ] ); |
Here’s an explanation of the above code:
You made an array for
varchar
and also for the arrayinteger
. This was possible when you used the keywordARRAY
.The table
student
is where the data will be inserted.Typing curly brackets ‘{}’ is an alternative way to insert data. Try this code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | INSERT INTO student (name, grades, contacts) VALUES ( 'Mark Doe', '{5,4,5,4}', ARRAY [ '(022)-545-9081', '(022)-588-7421' ] ), ( 'Gina Doe', ARRAY [ 5,5,5,5], '{ "(022)-545-9081", "(022)-588-7421" }' ); |
- You should see a confirmation response similar to this one:
1 | INSERT 0 2 |
Query PostgreSQL Arrays
- The method SELECT is how you can perform an array query for your PostgreSQL database.
1 2 3 4 | SELECT name, grades FROM student; |
- The response should resemble this one here:
1 2 3 4 5 6 | studentdb-# student; name | grades ----------+----------- Mark Doe | {5,4,5,4} Gina Doe | {5,5,5,5} (2 rows) |
- Try using subscript to perform an SQL query for the array. Make sure the brackets [] contain the argument:
1 2 3 4 | SELECT name, grades [2] FROM student; |
- You should see a result that looks similar to this:
1 2 3 4 5 6 | studentdb-# student; name | grades ----------+-------- Mark Doe | 4 Gina Doe | 5 (2 rows) |
Success! The element for the field grades
was retrieved as planned. In our table, the two students show their grades
.
Conclusion
When you have lots of different data to sort through, retrieve, and analyze in PostgreSQL, arrays simplify things. You can pull up what you need faster to get the answers that matter. In this tutorial about creating a PostgreSQL array, you discovered how to specify the information you want to review. The two ways to create arrays are with the keyword ARRAY
or by using brackets []. You have options in how you can query an array too. Use the SELECT
method or query with a subscript. The benefits are many when you decide to work with arrays, and then proceed to make it a habit of incorporating them into your daily data querying. Test it out. Start today.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started