Equivalent PostgreSQL query from SQLite

Question

I'm a mobile developer, but I've been doing some server work lately, so please excuse my terminology etc. I've been running into what might (??) be a difference between SQLite (which is what I use on iOS/Android) and PostgreSQL (which I use in APIs).

In SQLite, I could SELECT * from a table and then GROUP BY a single column without any aggregate functions at all. However, in PostgreSQL, it keeps complaining that it needs columns in the group by or an agg function.

Suppose I have a stop_times table which has these columns: stop_id, trip_id, stop_sequence, and departure_time. My trips table has a trip_id column and route_id. In SQLite, I have a query that looks like this:

SELECT * FROM "stop_times" 
  INNER JOIN "trips" ON "stop_times"."trip_id" = "trips"."trip_id" 
  WHERE "stop_times"."stop_id" IN (?, ?, ?) 
  GROUP BY "stop_times"."stop_id", "trips"."route_id"

(I'm trying to determine the stop_sequences of a particular set of stops along any routes that stop at them.) In PostgreSQL, however, this doesn't appear to work, as it complains until I put every column of stop_times into either the group by clause or an aggregate function. When I do, what I get back from the db is useless (in Rails, it's just a bunch of empty objects: #<StopTime >).

How can I achieve the same thing as above, but with Postgres?

Lukasz Szozda · Accepted Answer · 2018-10-28 09:16:59Z

1

You need to match column list with GROUP BY:

SELECT "stop_times"."stop_id", "trips"."route_id" 
FROM "stop_times" 
INNER JOIN "trips" ON "stop_times"."trip_id" = "trips"."trip_id" 
WHERE "stop_times"."stop_id" IN (?, ?, ?) 
GROUP BY "stop_times"."stop_id", "trips"."route_id"

or distinct on:

SELECT DISTINCT ON("stop_times"."stop_id", "trips"."route_id") *
FROM "stop_times" 
INNER JOIN "trips" ON "stop_times"."trip_id" = "trips"."trip_id" 
WHERE "stop_times"."stop_id" IN (?, ?, ?)

edited Oct 28, 2018 at 9:16

answered Oct 28, 2018 at 9:09

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Equivalent PostgreSQL query from SQLite

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related