tminglei/slick-pg

Slick extensions for PostgreSQL

Tom Wang

@TomWang41841655_twitter

Thanks!

František Kocun

@fokot

@TomWang41841655_twitter we aslo use slick pg with 9.6 - array support, window functions (partition by), json support, date support and all works

nafg

@nafg

What's new in 10 that's relevant?

Andrzej Sołtysik

@asoltysik

Hi, does anyone know how to use aggregate functions like mode with a groupedBy query?

mode().within expects ColumnOrdered but I have a Seq from the groupBy

Andrzej Sołtysik

@asoltysik

In short I want to translate a sql like this:

SELECT a.id, mode() WITHIN GROUP (ORDER BY b.id) 
FROM a INNER JOIN b on a.id = b.a_id
GROUP BY a.id

into Slick

Tom Wang

@TomWang41841655_twitter

@nafg I do not think there's anything in pg 10 that is relevant for slick-pg. It's just that slick-pg is tested with pg 10.

nafg

@nafg

But what's your concern

Tom Wang

@TomWang41841655_twitter

My original concern was that the newest slick-pg would not work with pg 9.6. I think that concern has been addressed.

Łukasz Dycjan

@dycu

Hi, do you know if there is a way of using date support while having value classes as column types rather than pure date types (case class extending AnyVal)?

Tor Holm

@holm.tor_gitlab

Hi, is it possible to have an array of jsonb elements?

I use play-json for serde

nafg

@nafg

@holm.tor_gitlab did you have any particular trouble?

Hmm I see, it seems array support is hardcoded for specific element types, although it looks easy to add more

nafg

@nafg

But why would you use array when you can you json array?

nafg

@nafg

@holm.tor_gitlab here is what I did:

  implicit val componentKindsMapping: SlickDriver.SimpleArrayJdbcType[ComponentKind] =
    new SimpleArrayJdbcType[String]("text").mapTo[ComponentKind](ComponentKind(_), _.name)

Pyry-Samuli Lahti

@Pyppe

Howdy! I'm trying to write a join based on array using slick-pg:

SELECT * FROM foos f JOIN bars b ON b.identifiers @> array[f.identifier]::text[]

But I cannot find a way to do that array[f.identifier]::text[] part in Slick. Any tips?

So in the example above, column bars.identifiers is of type text[], and column foos.identifier is type varchar(254).

Gavin Bisesi

@Daenyth

you could always use sql if you don't need to compose Query objects

Pyry-Samuli Lahti

@Pyppe

Yeah, that would be my fallback. But in my use-case I’m joining many tables. Would like to use type-safe approach, if at all possible.

Francesco Levorato

@flevour

Hi everyone!
I'm trying to upgrade my project to slick 3.3.0, so I set slick-pg to 0.17.2 but unfortunately in slick-pg 0.17.1 a few deps including json4s were upgraded to latest version. Alas json4s 3.6.x versions have a longstanding bug for case classes with type constructors (json4s/json4s#507). My project also dependes on json4s and thus breaks when upgrading. Is there anyway this dependency hell can be circumvented?

Gavin Bisesi

@Daenyth

@flevour if slick-pg is using only things that are binary compatible with the old one, it should work to add an exclude to your build.sbt

but also you might not need to bump slick-pg

Francesco Levorato

@flevour

@Daenyth even if a bit late, I want to send you a wholehearted thanks for pointing towards SBT's exclude directive! That's what I needed to get this sorted out. Thanks a lot!

Gavin Bisesi

@Daenyth

ochmist

@ochmist

hey guys, is there any good documentation on how to get started with slick-pg?

If i use the sample on the readme i get a lot of not defined types

ochmist

@ochmist

`import com.github.tminglei.slickpg._

trait MyPostgresDriver
extends ExPostgresProfile
with PgArraySupport
with PgDateSupportJoda
with PgEnumSupport
with PgRangeSupport
with PgHStoreSupport
with PgSearchSupport
with PgPostGISSupport {

override val api = new MyAPI {}

trait MyAPI
extends API
with ArrayImplicits
with DateTimeImplicits
with RangeImplicits
with HStoreImplicits
with SearchImplicits
with PostGISImplicits
with SearchAssistants
}

object MyPostgresDriver extends MyPostgresDriver`

Bascially none of the PG types here are found.

Gavin Bisesi

@Daenyth

Did you import MyPostgresDriver.api._ ?

ochmist

@ochmist

No i did not ... but the api is defined in this file itself no?

Gavin Bisesi

@Daenyth

sure, but your db tables etc won't be

queries, etc

ochmist

@ochmist

I see, I will import them in the tables.

ochmist

@ochmist

hey guys, so i used the examples form github and kinda got things to compile

However, when actually querying, i run into a problem

org.postgresql.util.PSQLException: ERROR: function st_dwithin(point, bytea, double precision) does not exist
[info]   Hint: No function matches the given name and argument types. You might need to add explicit type casts.
[info]   Position: 140

Here is my call into slick-pg:

def byDistance(point: Point, distance: Double): Future[Seq[House]] =
    db run {
      houses
        .filter(r => r.location.dWithin(point.bind, distance.bind))
        .result
        .map(t => t)
    }

Can anyone please help me figure out what is going on?

ochmist

@ochmist

Another error I am getting when writing to the db is shown below. Can anyone please point out if i am doing anything wrong:

due to: org.postgresql.util.PSQLException: ERROR: column "location" is of type point but expression is of type bytea
[info]   Hint: You will need to rewrite or cast the expression.
[info]   Position: 157

Is my column type in the database not supposed to be of type point?

Nader Ghanbari

@naderghanbari

Hi, thanks for creating this awesome project! I'm using it and everything works fine just a minor question. Is there a way to get ST_Distance(geom1, geom2, spheroid=true) with slick-pg? The distance function does not support the boolean flag.

def distance[P2, R](geom: Rep[P2])(implicit om: o#to[Float, R]) = {
  om.column(GeomLibrary.Distance, n, geom.toNode)
}

I coudln't find any GH issue related to this. If this is the case, is a PR welcome?

Rutvik Patel

@heyrutvik

Hey folks, need your help with tsquery/tsvector. My question is related to postgresql, than slick-pg. But I assume, you folks can help me with that as well. :)
Rather than repeating myself here, I'll post my https://twitter.com/heyrutvik/status/1182521309991002112 which describes the problem of using <-> with hyphened string in to_tsquery function.
Please take a look, thanks!

jdcohen220

@jdcohen220

does the makePoint() function use lat,lon or lon,lat ordering of the coordinates?

Yakov

@yabushraber

hello, is there a way I use postgis ST_Extent?

slava

@slmzig_gitlab

hi can I do with slick such query

drop table if exists orders cascade;
CREATE TABLE orders (
  ID serial NOT NULL PRIMARY KEY,
  info json NOT NULL
);

INSERT INTO orders (info)
VALUES
('{"interestedIn":[11],"countries":["IT", "UK"]}'),
('{"interestedIn":[12],"countries":["US", "UK"]}'),
('{"interestedIn":[1,2,3],"countries":["UK"]}'),
('{"interestedIn":[14,15,16],"countries":["DE", "UK"]}');


select *
from orders
where info::jsonb -> 'countries' ? 'US'
;

Where communities thrive

People

Repo info

Activity