How can I get data from a different table when adding a column? - Multi Schema Migrations
Hi 👋
Say I want to store a user's full name inside a post. For this, I would add a column to the
posts table userFullName using pgroll:
How can I make sure pgroll fills the new column with the data of the user? Can I do something like this?
Edit:
I'm getting an error if I try this:
unable to execute start operation: failed to create trigger: pq: syntax error at or near "SELECT"7 Replies
uhm this is a good one! can you try putting that same query between parentheses?
(select \"fullName\" from users where users.id = userId)Oh, nice! It seems to work. Awesome 😀
I wonder if pgroll should always add these
() automatically 🤔thank you for confirming btw! I've created this issue to follow up: https://github.com/xataio/pgroll/issues/473
GitHub
Do not require parentesis
( ) in up/down functions · Issue #473 ·...This migration fails cause the up function is not using (), let's make it optional so it's less error-prone: [ { "add_column": { "table": "posts", "column...
Yeah, that would be great. Initially, I assumed the error occurrs because it wasn't supported to get data from a different table.
This is something you should definitely add in an example, imo - it's so awesome!
Because the
up field in most of the example migrations suggest, that you can only use data from the current table. (I think only #21 and #24 have a from clause and I assumed it's only working for constraints and the like) https://github.com/search?q=repo%3Axataio%2Fpgroll+path%3A%2F%5Eexamples%5C%2F%2F+from&type=code
Our case btw:
We are changing the relation of a table from a single FK to two FKs, because we need to more directly need to link the two tables to the one in question.
Some last question:
What are the scopes where the SQL statements run in? Is this anywhere documented?uhm I don't think so, basically you have access to the whole schema
Ah, okay, so the unprefixed fields in such an expression are always resolved to the table we are doing the migration operation on? And you can do whatever expression you like, as long as it's returning a single value, also select with joins, for example?