1. Join the correct tables
In the last lesson, you picked up two tools for finding the tables you need to build your queries. In practice, the data you need is oftentimes found in multiple tables and must not only be found but correctly joined.
In this lesson you will learn how to utilize another system table to create a tool for rapidly exploring the tables & columns that exist in your database.
2. All tables & columns
The system table is called information_schema.columns and it contains a row for every column in every table in your database. All databases have some version of this table and for the 3 examples shown all refer to the system table in the same manner.
3. All tables & columns
For postgres the table looks like this. The result of this query will contain the columns of every table in every schema of the database.
This will also include system schemas like the one you see here called pg_catalog.
Since the system tables and their columns will not be useful for any of your data queries you may want to consider limiting this query to only return entries for the schemas that hold your actual data.
4. All tables & columns
In this database, all of the data is stored in the schema called public which we simply need to filter for using a WHERE statement.
As far as using this as a tool you can see that with one row for every column and table combination, it isn't very convenient to explore.
Instead, let's transform this table a bit to make it easier to use.
5. Aggregate the columns
It would be much easier to have each row in this query aggregate the columns into a single string.
To prepare this result we need to use the STRING_AGG function. As you can recall from chapter one, the STRING_AGG function accepts two parameters, first is the column you would like to aggregate and second is the string you will use to separate the entries. In this case, we want to aggregate the values in the column_name field.
6. Aggregate the columns
These columns are retrieved FROM the information_schema-columns table.
7. Aggregate the columns
And, since STRING_AGG is an aggregate function a GROUP BY statement must be specified. Since we would like to have one entry for each table_name we will use the table_name column to group the result.
The result of this query has one entry for every table in our database like you see here. This is much easier to work with.
Now, let's make this tool easier to use by saving it as a VIEW in our database.
8. A VIEW of tables and columns
A VIEW is a virtual table that you can create from a SQL statement.
Once a VIEW is created it can be queried just like a real table in the database.
The syntax for creating a view is as simple as adding the following line at the top of the query you would like to create a view from. You simply need to specify the name of this new VIEW.
Which means now you can create a new VIEW called table_columns like so.
9. table_columns
Once this view is created it is now a part of your database and you can query it just like any other table.
10. Let's find some data!
Now you'll have a chance to create this new tool called table_columns in your database and use it to find data that you need.