Pg: Support SELECT DISTINCT clause

[zenghur]

I know ColumnExpr can achieve it. But I want a more elegant way .
Before,
db.Model(...).ColumnExpr("DISTINCT foo, bar").Select()
After,
db.Model(...).Distinct("foo, bar").Select() and
db.Model(...).DistinctOn("foo, bar").Select()

[vmihailenco]

What is the difference? Distinct("foo, bar") could call ColumnExpr("DISTINCT foo, bar") but I'd rather not expand API without a good reason.

[zenghur]

What is the difference? Distinct("foo, bar") could call ColumnExpr("DISTINCT foo, bar") but I'd rather not expand API without a good reason.

when you do multiple distinct columns, it seems that ColumnExpr("DISTINCT ON(a, b, c) a") is not that reasonable cuz I want to select all columns at the same time. In this situation, I want to use db.Model(...).DistinctOn("a, b, c").Select() to select all columns.

[vmihailenco]

Please explain how resulting query should look like - go-pg is responsible only for constructing the query and unmarshaling the result into Go structs.

[zenghur]

Please explain how resulting query should look like - go-pg is responsible only for constructing the query and unmarshaling the result into Go structs.

Suppose table t have three columns a, b, c. if we do db.Model(...).ColumnExpr("DISTINCT ON(a), a").Select(), this would result in select distinct on(a) a from t, but I want to generate select distinct on(a) a, b, c from t by using db.Model(...).DistinctOn("a").Select()

[zenghur]

any updates?

[vmihailenco]

What you are asking for makes sense but I am hesitant to further grow Query without evidence that it is widely applicable/useful. I will try to decide something this week.

[ahmadster]

I'd like to voice support for implementing this.

[Fyb3roptik]

I would +1 this as well!

[wavded]

I would like this as well, the current suggestion is awkward, in order for it to work I had to to make it the first ColumnExpr after db.Model and I needed to use a throwaway column to avoid it putting the comma in the wrong spot (causing a syntax error):

db.Model(...).ColumnExpr(`DISTINCT ON ("a", "b", "c") id`)

Would be nice to have a DistinctOn or similar to enable us to programmatically add that requirement when needed.

[vmihailenco]

Master branch should support for db.Model().Distinct() and db.Model().DistinctOn("foo, bar"). Thanks for waiting :)

[MohitKS5]

This isn't on releases yet ?

[vmihailenco]

Should be in v9.