There comes a time when a database table needs to be changed. In an ideal world we would avoid doing that by having a robust discovery phase and making sure we capture all the requirements upfront, to use some business / product management speak, but in reality things change as time marches forward.
As an example, let's suppose that after we launch our ecommerce store with the products table tracking the id, name, price, and sku of a product, the government introduces a law that says that each product needs to be classified into one of many different cateogires, and customers would need to pay tax on top of the base price depending on what category the product is in.
If that sounds familiar, I don't blame you. In the United Kingdom, where I am, this is the system of VAT. Different products have different rates, and occasionally the same products will have different rates depending on where and how you use them.
Variable VATs on items based on what they are and how you use them
HMRC (The UK's tax authority) has this guidance on what VAT you need to charge on food going into details as to what "hot" means, what "premises" means, and a bunch of tests to figure out what category selling that sandwich falls into.
More broadly here's the page you would use to figure out what product / service incurs what rate of VAT.
This would mean that we now need to add another column to our database which likely already has data in it, so dropping it and recreating it is not an option.
So let's add a column here that will house a VAT rate. For this column the value is going to be a numeric type with both a precision and scale. The column is going to be nullable, because the law makes a difference between "exempt" and "0%". In this case a null value means there is no value specified, with the implication that it's going to be exempt. A value of "0.00" will mean there IS a rate for the product, but that rate just happens to be 0%. Here's the entire alter table command that will achieve what we need. Notice that I've included the schema name here too:
alter table public.products
add column vat_rate numeric(5, 2) null;Here's the colour coded breakdown of what it does:
Specifying the schema is useful, but for our purposes not required. If it's not there, postgres will search through the schemas you have access to in a set order and applies the changes to the first table it finds in whichever schema.
When schemas are required
If you have a table present in multiple schemas, which we will have shortly, specifying the schema along with the table name is super important to avoid nasty surprises. You don't want to accidentally drop the wrong users table just because you forgot to specify you want to drop the testing.users table, where testing is a new schema you created.
To use an analogy, say you're at your neighbour's house because they invited you over for dinner, and you're helping them with preparing food. You have a super nice turkey carving knife at home, and would love to use that, and your neighbour is helpful, so you ask them: "Hey, can you get me the turkey carving knife from the kitchen?"
Because you're in their kitchen though, your neighbour, rightly, assumes that you want the turkey carving knife from their kitchen, and brings you their, which isn't as nice as yours.
If however you asked your neighbour "Hey, can you get me the turkey carving knife from my kitchen?" and gave them your keys, they would return with precisely what you wanted.
Runing the above query in the query window in supabase should yield a new "Success. No rows returned." message.
Gotcha!
Heads up, supabase will run ALL queries currently present in the query window. If you still have the create product table query there, it will try to run that, as well as the alter table query, which will possibly fail because you already have a table with that name.
It is best to either open a new query window, or delete the contents of the existing one, or comment out the contents of the existing one.
In SQL, lines that start with a double dash, like this: -- select ..., are comments, and will be ignored.
We can also use the alter table to drop a column we no longer need.
To drop the freshly created vat_rates column, you'd need to run this query:
alter table public.products
drop column vat_rate;Alter table on postgres
The documentation on alter table for postgres can be found here: https://www.postgresql.org/docs/13/sql-altertable.html.
Next up, let's learn how to copy a table structure.