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 Unnest

PostgreSQL Unnest

Updated May 22, 2023

PostgreSQL Unnest

Introduction to PostgreSQL Unnest

PostgreSQL unnest is the type of array functions; the unnest function in PostgreSQL is basically used to expand the array into rows. Unnest function is converting an array into a table-like structure; we can also generate a table structure of an array using unnest function in PostgreSQL. Unnest array function is very useful in PostgreSQL for expanding the array into the set of values or converting the array into the table structure. If we need a table-like structure of array simultaneously, we have to use the unnest function on the array on which we have converted array data into the table-like structure.

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:

Below is the syntax:

Unnest(Any_array_number (Any array element number which was we have used with unnest function)
Unnest (Any_array_text (Any array element text which was we have used with unnest function)
Select unnest (any_array (Number of text value which was used in array.))
Select unnest (any_array (Number of text value which was used in array.)) limit number;

Below is the parameter description syntax of Unnest array function:

  • Unnest: This function we defined in PostgreSQL to set the element in a table-like structure. We have used unnest element with text as well as number array. We must define the number or text element with the unnest array function in PostgreSQL.
  • Any array text: This is defined as using an array of text values to convert the array into the table-like structure in PostgreSQL by using unnest array function.
  • Any array number: This is defined as a number. Using the unnest array function, we have used any number values to convert an array into the table-like structure in PostgreSQL.
  • Select: This operation selects the array’s value using the unnest array function in PostgreSQL. Select operations are very useful and important while using unnest array function in PostgreSQL.
  • Limit: In PostgreSQL, we have also using the limit clause with unnest function in PostgreSQL. While using the limit clause will show the number as per the limit we have used with unnest function.

How does Unnest Function work in PostgreSQL?

  • It has two overloads. The first overload has obvious usefulness, and the second overload is exotic.
  • The first overload of usefulness aims to transform the values from an array into a single array.
  • In the old version of PostgreSQL, when we have to convert an array into the table structure, we have to use an array with the cross join. After joining the array with cross join, we generated the same series.
  • After generating the series, all the array elements will be structured into the table using cross join and generate series function.

The below example shows that in the old version, we used cross join with generate series function to convert the array into the table structure.

Code:

SELECT test_unnest[A] as array_element FROM  (SELECT ARRAY[11, 12, 13, 14, 15, 16, 17, 18, 19, 20] As test_unnest) as gen_ser CROSS JOIN generate_series(1, 10) As A;

Output:

PostgreSQL Unnest 1

  • The above example shows that we have used array element as [11, 12, 13, 14, 15, 16, 17, 18, 19, 20]; we have used this array element with generate series function for converting an array into the table like structure.
  • We have also used cross join with the array element and generated a series function for converting an array into a table-like structure.
  • By using unnest function, we do not need to use a cross join or generate series function to convert an array into a table like structure. We have simply using unnest function with an array.
  • In PostgreSQL, we have also utilised an array for a number or text to create a table-like structure.
  • We can also use unnest function with an order by clause. Using an unnest function, we have an order by clause with a 1 in it.
  • We have also use the limit clause with unnest function. After using a limit clause, it will show the output as per the number which was we have used with the limit.

Examples of PostgreSQL Unnest

Different examples are mentioned below:

Example #1 – Unnest function with array as a number.

Below example shows that unnest function with array as a number. In the below example, we use an array of numbers as [1, 2, 3, 4, 5, 6, 6, 7, 8, 9, 10].

Code:

SELECT unnest(ARRAY[1, 2, 3, 4, 5, 6, 6, 7, 8, 9, 10]);

Output:

PostgreSQL Unnest 2

Example #2 – Unnest function with array as text.

Below example shows that unnest function with array as text. In the below example, we are using an array of text as [‘ABC’, ‘PQR’, ‘XYZ’, ‘ABC’, ‘PQR’, ‘XYZ’, ‘ABC’, ‘PQR’, ‘XYZ’, ‘ABC’, ‘PQR’, ‘XYZ’].

Code:

SELECT unnest(ARRAY['ABC', 'PQR', 'XYZ', 'ABC', 'PQR', 'XYZ', 'ABC', 'PQR', 'XYZ', 'ABC', 'PQR', 'XYZ']);

Output:

PostgreSQL Unnest 3

Example #3 – Unnest function with an order by clause.

Below example shows that unnest function with an order by clause. In the below example, we use an array of numbers as [1, 2, 3, 4, 5, 6, 6, 7, 8, 9, 10].

Code:

SELECT unnest(ARRAY[1, 2, 3, 4, 5, 6, 6, 7, 8, 9, 10]) order by 1;

Output:

with order by clause

Example #4 – Unnest function with limit clause.

Below example shows that unnest function with a limit clause. In the below example, we use an array of numbers as [1, 2, 3, 4, 5, 6, 6, 7, 8, 9, 10].

Code:

SELECT unnest(ARRAY[1, 2, 3, 4, 5, 6, 6, 7, 8, 9, 10]) limit 5;
SELECT unnest(ARRAY[1, 2, 3, 4, 5, 6, 6, 7, 8, 9, 10]) limit 8;

Output:

with limit clause

Recommended Articles

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

  1. PostgreSQL Compare Strings
  2. Copy Database in PostgreSQL
  3. PostgreSQL DELETE
  4. PostgreSQL String Array
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

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