Скачать или смотреть How to Select Columns in PostgreSQL When They Include a Substring: A Guide to Sky Columns

Discover how to efficiently select columns in PostgreSQL with substrings, especially when dealing with multiple "sky" columns without manual entry.
---
This video is based on the question https://stackoverflow.com/q/62530655/ asked by the user 'Dong-gyun Kim' ( https://stackoverflow.com/u/10263818/ ) and on the answer https://stackoverflow.com/a/62531509/ provided by the user 'pifor' ( https://stackoverflow.com/u/10726850/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Postgresql select columns if the columns include substring

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Select Columns in PostgreSQL When They Include a Substring: A Guide to Sky Columns

When working with databases, especially with PostgreSQL, you might find yourself in a situation where you need to select multiple columns that share a common naming pattern. For instance, if you have several columns named sky1, sky2, all the way to sky15 in a table and you want to include those in your selection, doing so manually can be tedious and error-prone. In this guide, we will address how to effectively select these columns without having to type each one individually.

The Problem at Hand

Imagine you have two tables, Color and Fruit, with the following columns:

Color Table: yellow, orange, sky1, sky2, sky3, ..., sky15

Fruit Table: yellow, orange, apple, desk

You designed a query to get specific columns as follows:

[[See Video to Reveal this Text or Code Snippet]]

The objective was to retrieve all columns that start with "sky". However, PostgreSQL does not allow you to select columns with a wildcard directly in this way, leading to an error.

Solution: Using Dynamic SQL

To resolve this issue without having to painstakingly list each sky column, consider using a function in PostgreSQL that can generate the list of desired columns for you. Here is how to do it step-by-step.

Step 1: Use generate_series to Create Column Names

You can utilize generate_series to create an aggregation of the column names that start with sky. This allows you to generate a list of column names dynamically. Here's the SQL command you can run:

[[See Video to Reveal this Text or Code Snippet]]

Breakdown of the Command:

generate_series(1, 100): This function generates numbers from 1 to 100, which we use to create our column names (sky1, sky2, ..., sky100).

'sky' || i: We concatenate the string sky with each number to create the column names.

string_agg(..., ', '): This function aggregates all the generated strings into a single, comma-separated string.

Step 2: Incorporate the Generated Names into Your Query

Once you have generated the list of columns, you can manually include it in your original SQL query to select the columns. Assuming the previous step returns the colum names from sky1 to sky15, your final query should look like this:

[[See Video to Reveal this Text or Code Snippet]]

Conclusion

By using PostgreSQL's generate_series to automate the selection of multiple columns fitting a substring, you can save time and avoid potential errors in your SQL queries. This method is especially beneficial when dealing with a significant number of columns that follow a systematic naming convention. So next time you find yourself in a similar predicament, remember this technique to streamline your database queries effectively. Feel free to explore and adjust the range in generate_series to fit the size of your dataset.

Now that you're equipped with this knowledge, happy querying!