Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Show HN: PRQL in PostgreSQL

github.com

Post reply on HN
Print view View original

51–60 of 142 posts

Re: Show HN: PRQL in PostgreSQL

#51

Post by fforflo »

Nice work. A few months back, I experimented with having a DSL like PRQL in Postgres, but back then, I found the language a bit cumbersome; however, it was great as an idea. IMHO, the best "data transformation" language is jq and awk is second.

PRQL and EdgeQL (EdgeDB) are the most interesting ones to watch how they evolve, though.

I've also written a PG extension to make jq available in Postgres [0]

I believe Postgres, in general, will flourish as a host for DSL languages [1].

0: https://github.com/Florents-Tselai/pgJQ 1: https://tselai.com/pgjq-dsl-database.html

Re: Show HN: PRQL in PostgreSQL

#52

Post by buremba »

abirch wrote: post #50
snthpy wrote: post #7

Earlier quoted context omitted.

This comes up every time PRQL makes it onto HackerNews and is a fair question: Short answer: DX Slightly longer answer: Developer productivity and experience, especially for EDA and interactively writing complex analytical queries. Most people that have tried PRQL just find it more convenient to write their analytical queries in it. PRQL compiles to SQL so it can't express anything you can't already do in SQL, but yo…

This would have been awesome for me 2 years ago. Currently much of my complicated SQL is generated by a LLM.

Hmm, I would think that LLM helps adoption for the semantic layers such as PRQL, Malloy, and dbt since it's possible to generate/validate/iterate 5 lines of PRQL compared to 25 lines of SQL but considering none of them widely adopted yet, you might indeed be correct in a way that LLM makes it harder for the new tools to gain adoption by helping you to suffer less from the verboseness of SQL.

Re: Show HN: PRQL in PostgreSQL

#54

Post by gigatexal »

orlp wrote: post #45
gigatexal wrote: post #22

Earlier quoted context omitted.

select id from tracks qualify row_number() over partition by (album_id order by milliseconds desc) =1 That should work Look I’m a diehard SQL just use it guy but open to improvements. But I’m loathe to use abstractions for things when the underlying thing is so expressive. Autocomplete of fields in a good editor, schema help, etc go a long way to making SQL being written raw very nice.

QUALIFY is not part of the SQL standard.

Then a subselect with the window function and an outer where clause where the window function column = 1

Re: Show HN: PRQL in PostgreSQL

#55

Post by dangoldin »

dvdkon wrote: post #48

Damn, now my bachelor's thesis will be less unique :) I'm working on a new language that compiles directly to Postgres' post-analysis structs. It's working out pretty well so far, but my chosen "universal set" (aggregation/array/subquery/... as one thing) semantics are sometimes a pain to encode.

Not to change your direction but something I've been toying around is being able to support Algebraic types when defining tables. That way you can offload a lot of the error checking to the database engine's type system and keep application code simpler.

Re: Show HN: PRQL in PostgreSQL

#56

Post by staticautomatic »

buremba wrote: post #52
abirch wrote: post #50

Earlier quoted context omitted.

This would have been awesome for me 2 years ago. Currently much of my complicated SQL is generated by a LLM.

Hmm, I would think that LLM helps adoption for the semantic layers such as PRQL, Malloy, and dbt since it's possible to generate/validate/iterate 5 lines of PRQL compared to 25 lines of SQL but considering none of them widely adopted yet, you might indeed be correct in a way that LLM makes it harder for the new tools to gain adoption by helping you to suffer less from the verboseness of SQL.

It’s a tough call. I run a small analytics team and am starting to train some analysts to code. Just the other day I basically told one of my reports to focus on learning Python and let ChatGPT teach him SQL by example because I think it’ll be easier to grok the explanations. Now I’m looking at PRQL and Malloy and asking myself if it’s really a path I should send them down, and I’m not sure it’s a good idea.

Re: Show HN: PRQL in PostgreSQL

#57

Post by tyre »

audunw wrote: post #43
GordonS wrote: post #13

Earlier quoted context omitted.

> How long would it take you to write the SQL for that? I don't want to appear rude, but unless I'm missing something, this is a pretty simple SQL query, of the kind anyone with mimimal SQL experience could write off the top of their head in seconds. I like the idea of PRQL, but I think a better example is needed to sell it.

I have a moderate amount of SQL experince, but I could not write that query at the top of my head. Maybe you misunderstand what the PRQL query is doing? Here's the SQL it generates: WITH table_0 AS ( SELECT , ROW_NUMBER() OVER ( PARTITION BY album_id ORDER BY milliseconds DESC ) AS _expr_0 FROM tracks ) SELECT FROM table_0 WHERE _expr_0 If I understand PRQL correctly, it finds the longest song for each album? A simpl…

SELECT DISTINCT ON (album_id) * FROM tracks GROUP BY album_id ORDER BY milliseconds DESC;

For those unfamiliar with Postgres, DISTINCT ON takes only one row for each of the groups based on the supplied columns. So in this case, it will return only one row per album_id.

Without an ORDER BY, DISTINCT ON chooses a random row (not actually, but you can’t rely on it.) Since we ORDER BY milliseconds DESC, the first row of each group will be the longest one.

Re: Show HN: PRQL in PostgreSQL

#58

Post by adrian_b »

lkuty wrote: post #3

I don't understand the need for libraries that abstract away SQL when you could just write SQL directly and have full access to the power of the language which is quite rich (recursive CTE, windowing, ... aka Modern SQL). You could also use stored procedures/functions for more complex stuff and e.g. JSON (or native types) to transfer data between the database and the application. Why limit ourselves with a sub-optima…

In my opinion SQL is the sub-optimal language.

Whenever I am writing SQL I am not thinking in SQL, but I am thinking in what I consider to be the mathematical sound way, which I translate into SQL while writing. I consider thinking in SQL a much greater mental handicap than having to translate mentally into it.

I would prefer to write directly in what I would consider as a good query language and have it translated automatically into SQL, for compatibility with what is, for unfortunate historical reasons, the standard.

I have not attempted previously to do or use something like this, but work like that discussed here seems like a step in the right direction.

Re: Show HN: PRQL in PostgreSQL

#59

Post by jpgvm »

CuriouslyC wrote: post #21
vendiddy wrote: post #15

Earlier quoted context omitted.

The thing I hate most about SQL is lack of composability. In most languages it's easy to pull out functions. In SQL you end up with a giant hard to comprehend mess. I think the underlying relational concepts in SQL are sound but I'd love to see ideas like PRQL that aim make SQL easier to write and maintain. Stored procedures and functions are nice but don't allow the basic idea of breaking a large query apart into sm…

Your statement about breaking large queries apart is wrong. You can write queries with CTES to improve readability, and extract CTEs into functions that can be selected as queries get too large and unwieldly. SQL is just as composable as any other language. The thing that's lacking right now is the tooling for managing/testing/deploying database code. There are solutions out there and the supabase folks have been wor…

One issue with functions though is that they can change performance in unpredictable ways. For example a colleague of mine recently altered a function I had written that was used in multiple hot-path queries. The change he made accidentally caused the function to no longer be inline-able on PostgreSQL. Once the function couldn't be inlined then the PostgreSQL planner wasn't able to select the appropriate indices and the performance of several of the queries exploded by about 100x.

So while it's true it can be composed etc the current state of the art planners struggle except under very simple/constrained scenarios.

Re: Show HN: PRQL in PostgreSQL

#60

Post by CuriouslyC »

jpgvm wrote: post #59
CuriouslyC wrote: post #21

Earlier quoted context omitted.

Your statement about breaking large queries apart is wrong. You can write queries with CTES to improve readability, and extract CTEs into functions that can be selected as queries get too large and unwieldly. SQL is just as composable as any other language. The thing that's lacking right now is the tooling for managing/testing/deploying database code. There are solutions out there and the supabase folks have been wor…

One issue with functions though is that they can change performance in unpredictable ways. For example a colleague of mine recently altered a function I had written that was used in multiple hot-path queries. The change he made accidentally caused the function to no longer be inline-able on PostgreSQL. Once the function couldn't be inlined then the PostgreSQL planner wasn't able to select the appropriate indices and…

If a function is marked stable it should not impact the query plan at all, since stable functions are essentially in-lined before planning. If logic is unstable a view is probably going to be a better abstraction than a function.
Post reply on HN