Practical introduction to relational databases
Fetch only some columns
In the previous section we looked at the generic simple select query, which looked like this. Notice the star:
SELECT * FROM public.products;
That star means everything from the table: all columns of data. Because there's no where clause there (we'll get into that shortly), it will also try to return all rows in the table.
For a practical example here, we will need to copy some data from the users table that supabase itself uses to keep track of the users that interact with its authentication libraries. If you haven't done so, on the example site I had you cloned and spun up, there's a Sign up / Log in button in the top right corner. Sign up or log in. Note that you had to give an email address, and choose a password.
Those details are stored somewhere, but if you look at your tables in the supabase UI (screenshot below), you don't have a users table.
Supabase has a table called users in the auth schema. Here are a few ways to figure that out.
List a record of all tables in all schemas
Every postgres database will have a few built in schemas and tables or views to hold its own record of what's where: how many tables there are, what those tables look like, what they are called, what constraints exist on them, and so on.
A view and a table are very similar to each other in that you can query the data out of them using the same syntax, and pretend that a view was a table.
The difference however is that technically a view does not exist. It's not a table, in which it doesn't have its data stored somewhere, like a table. It's a virtual construct that may depend on many other tables, or even other views.
The easiest analogy to React or Vue is that a View is kind of like a computed property: it exists, but it depends on other properties, and it changes when the underlying data changes too.
Here are some useful links for more info if you want to dive deeper:
One of the tables and schemas that are common across both MySQL and Postgres is the information_schema concept. To grab a list of all tables in that schema, we can use this query:
SELECT * FROM information_schema.tables where table_schema = 'information_schema';
You can go ahead, open a new query window, paste that in there, and run it. You should get a lot of rows of information along with a LOT of columns. For our purposes we only care about the following columns
Let's suppose we only care about three columns: C2, C3, and C4. These ones:
To achieve that we have to replace the * with the column names we want: C2, C3, and C4.
That will result in a small dataset that is exactly what we want to see in this case:
To translate it back to list the tables from the information_schema view, we can use the following query, which you're welcome to run in your supabase instance:
SELECT table_schema, table_name, table_type FROM information_schema.tables WHERE table_schema='information_schema';
That will give you every single table and view (see the table_type column) in the information_schema schema.
Because we're going to use this to figure out the table structure of the users table that supabase uses to keep track of, and figure out where it stores when you sign up, modify the query to be this:
SELECT table_schema, table_name, table_type FROM information_schema.tables WHERE table_schema = 'auth';
You should have a result set that looks something like this:
| table_schema | table_name | table_type |
|---|
| auth | schema_migrations | BASE_TABLE |
| auth | refresh_tokens | BASE_TABLE |
| auth | audit_log_entries | BASE_TABLE |
| auth | instances | BASE_TABLE |
| auth | users | BASE_TABLE |
Of these we're only going to work with the users table, but feel free to experiment figuring out what's in the others. As a hint, here's how to get the audit log entries:
SELECT * FROM auth.audit_log_entries;
When you're ready, let's move on to renaming columns in the results.