Xata•5d ago

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:

[
  {
    "add_column": {
      "table": "posts",
      "column": {
        "name": "userFullName",
        "type": "text",
        "nullable": false
      },
      "up": "select \"fullName\" from users where users.id = ???"
    }
  }
]

[
  {
    "add_column": {
      "table": "posts",
      "column": {
        "name": "userFullName",
        "type": "text",
        "nullable": false
      },
      "up": "select \"fullName\" from users where users.id = ???"
    }
  }
]

How can I make sure pgroll fills the new column with the data of the user? Can I do something like this?

select "fullName" from users where users.id = "userId"
-- meaning, match the user with the post's foreign key userId

select "fullName" from users where users.id = "userId"
-- meaning, match the user with the post's foreign key userId

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

exekias•4d ago

uhm this is a good one! can you try putting that same query between parentheses? (select \"fullName\" from users where users.id = userId)

Michael SchaufelbergerOP•4d ago

Oh, nice! It seems to work. Awesome 😀

exekias•4d ago

I wonder if pgroll should always add these () automatically 🤔

exekias•4d ago

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...

Michael SchaufelbergerOP•4d ago

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?

exekias•3d ago

uhm I don't think so, basically you have access to the whole schema

Michael SchaufelbergerOP•3d ago

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?

Gaming

Programming

How can I get data from a different table when adding a column? - Multi Schema Migrations