Beam: Indexes/unique cols + select in join? - Haskell

I have two questions regarding beam :):

1) I want to add create indexes and unique constraints on multiple columns.

• Can I do this with beam? (somehow I assume not)
• If not, can I maybe execute raw SQL as part of a migration?

2) Somehow I dont know how translate the following query into beam syntax:

SELECT *
FROM vehicle AS b
LEFT JOIN vehicle_position as bp
ON (b.id = bp.position_vehicle_id__id AND bp.position_id =
    (SELECT bp_.position_id FROM vehicle_position AS bp_ ORDER BY position_updated DESC limit 1));

In essence: I want to combine every entry in the main table with the latest value (regarding time) of another table. If there is no entry present, the entry should be combined with Nothing.
How can I achieve this?

Matthias Putz said:

I have two questions regarding beam :):

1) I want to add create indexes and unique constraints on multiple columns.

• Can I do this with beam? (somehow I assume not)
• If not, can I maybe execute raw SQL as part of a migration?

2) Somehow I dont know how translate the following query into beam syntax:

SELECT *
FROM vehicle AS b
LEFT JOIN vehicle_position as bp
ON (b.id = bp.position_vehicle_id__id AND bp.position_id =
    (SELECT bp_.position_id FROM vehicle_position AS bp_ ORDER BY position_updated DESC limit 1));

In essence: I want to combine every entry in the main table with the latest value (regarding time) of another table. If there is no entry present, the entry should be combined with Nothing.
How can I achieve this?

Hm, d**n, thought especially the first one is an easy one since anybody doing something "serious" with a SQL database needs these features, not?
May I "summon" you @Ben Kolera and ask how you are managing this since you at OS apparently prefer using beam :D (I gave beam another try because of what you said :) ). Sorry for bothering :D.

Not sure, actually. I currently work on a project that's not IV or even obelisk, lol. I know from poking around that we are on a forked copy of beam migrate (and maybe beam / beam postgres too), though, so it may well be that we've fixed that but not quite upstreamed such things yet.