Postgres Update Multiple Rows with Multiple Returns Preserving Order

step-by-step demo:db<>fiddle

Joining the records from table_b on table_c needs a common join condition.

Assuming, the ids are correctly increasing with the entrys (so a newer records has a higher id), they can be used as order criterion. If not, you'll need one, because there's nothing like an insert order. Inserting a record does not ensure any query order.

Well, to create the common join condition, you can use the row_number() window function, which creates a row count within an ordered group (= partition). In your case, the partition is the traveler, the order is the id (here table_b):

     SELECT 
         *,
         row_number() OVER (PARTITION BY traveler ORDER BY id)
     FROM table_b;

which yields (notice the last column, which was created)

> id | uuid                                 | idx | location   | traveler                                      | row_number
> -: | :----------------------------------- | --: | :--------- | :-------------------------------------------- | ---------:
>  2 | 676143d6-abe1-48d6-bf78-c72b3186ede3 |   0 | Pittsburgh | 13df1b18-5c6e-4879-9b2f-c78ea215c809          |          1
>  3 | b0cb5e4f-3070-4403-89dd-6063864233c4 |   0 | Denver     | 21f5e0ee-1e1b-4d8d-8768-d0acc276efd5          |          1
>  6 | b0cb5e4f-3070-4403-89dd-6063864233c4 |   1 | New York   | 21f5e0ee-1e1b-4d8d-8768-d0acc276efd5          |          2
>  5 | 676143d6-abe1-48d6-bf78-c72b3186ede3 |   1 | Las Vegas  | aeae809f-13df1b18-5c6e-4879-9b2f-c78ea215c809 |          1
>  1 | b972acfc-d5b2-4e65-bf31-0a515ee1a2f1 |   0 | Chicago    | f9b0de79-3f38-4cbb-9275-6d68b472e943          |          1
>  4 | b972acfc-d5b2-4e65-bf31-0a515ee1a2f1 |   1 | Charlotte  | f9b0de79-3f38-4cbb-9275-6d68b472e943          |          2

The same can be done with table_c.

Now, you created a normalizing row count on every traveler's records. Because the record numbers equal (which you presumed in your question), this is your join condition:

SELECT
    *
FROM (
    SELECT                                                       -- 1
        *,
        row_number() OVER (PARTITION BY traveler ORDER BY id)
    FROM table_c
) c LEFT JOIN (
    SELECT
        *,
        row_number() OVER (PARTITION BY traveler ORDER BY id)    -- 2
    FROM table_b
) b ON b.traveler = c.traveler AND b.row_number = c.row_number   -- 3

1. table_c with row counts
2. table_b with row counts
3. Join on traveler and row count

Well, if you want to add the table_b's columns uuid and idx to table_c, you need new columns:

ALTER TABLE table_c 
ADD COLUMN b_uuid text,
ADD COLUMN b_idx int;

And then you have to fill them using an UPDATE statement, which uses the query above:

UPDATE table_c c
SET b_uuid = s.b_uuid, b_idx = s.b_idx
FROM (
    SELECT
        c.id,
        b.uuid as b_uuid,
        b.idx as b_idx
    FROM (
        SELECT 
            *,
            row_number() OVER (PARTITION BY traveler ORDER BY id)
        FROM table_c
    ) c LEFT JOIN (
        SELECT
            *,
            row_number() OVER (PARTITION BY traveler ORDER BY id)
        FROM table_b
    ) b ON b.traveler = c.traveler AND b.row_number = c.row_number
) s
WHERE s.id = c.id;