I have 2 tables in my Postgres database.

vehicles
- veh_id PK
- veh_number

positions
- position_id PK
- vehicle_id FK
- time
- latitude
- longitude
.... few more fields

I have multiple entries in Position table for every Vehicle. I would like to get all vehicle positions but the newest ones (where time field is latest). I tried query like this:

SELECT *
FROM positions
GROUP BY vehicle_id
ORDER BY time DESC

But there's an error:

column "positions.position_id" must appear in the GROUP BY clause or be used in an aggregate function

I tried to change it to:

SELECT *
FROM positions
GROUP BY vehicle_id, position_id
ORDER BY time DESC

but then it doesn't group entries.

I tried to found similiar problems e.g.:

PostgreSQL - GROUP BY clause or be used in an aggregate function

or

GroupingError: ERROR: column must appear in the GROUP BY clause or be used in an aggregate function

but I didn't really helped with my problem.

Could you help me fix my query?

Is simple if you have columns on the SELECT those should be also on the GROUP section unless they are wrapped with aggregated function

Also dont use * use the column names

  SELECT col1, col2, MAX(col3), COUNT(col4), AVG(col5) -- aggregated columns
                                                       -- dont go in GROUP BY
  FROM yourTable
  GROUP BY  col1, col2   -- all not aggregated field

Now regarding your query, looks like you want

SELECT *
FROM (
     SELECT * ,
            row_number() over (partition by vehicle_id order by time desc) rn
     FROM positions
     ) t
WHERE t.rn = 1;

more