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

91–100 of 142 posts

Re: Show HN: PRQL in PostgreSQL

#91

Post by dvdkon »

dangoldin wrote: post #55
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.

I'd like to do something like that too, if/when I ever get to replacing the DDL. In Postgres you could create custom types for tagged unions, but it might be better to translate table-level unions to a set of constraints, for performance and flexibility (you can't create referential integrity constraints using expressions IIRC).

Re: Show HN: PRQL in PostgreSQL

#92

Post by vendiddy »

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…

Do you have any links to a basic example on using CTEs and functions to keep SQL maintainable?

I've used CTEs, but I had not tried breaking up an SQL query into functions. Didn't know that was possible!

For whatever reason, I feel like I end up with a giant blob of SQL when writing SQL and it's incredibly frustrating.

Re: Show HN: PRQL in PostgreSQL

#93

Post by andix »

ttfkam wrote: post #65
andix wrote: post #44

Earlier quoted context omitted.

Because SQL can be cumbersome to write. It's often repetitive, requires nesting, aliases, and a specific order of statements.

1. Notice language is complicated for some tasks 2. Propose newer, simpler language to take care of these 3. Newer, simpler language lacks features of original language 4. Newer language adds features, making it more complicated 5. GOTO 1

That's why we stopped innovation of programming languages at C89? Why use sql at all if you can also do it in C?

Re: Show HN: PRQL in PostgreSQL

#95

Post by ako »

vendiddy wrote: post #15
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…

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…

Composition is available in sql, but works a bit different than in a procedural language. In sql you express sets of data, composition consists of defining subsets that you compose into more complex sets. Views and CTEs are the tools for composition in SQL.

Re: Show HN: PRQL in PostgreSQL

#96

Post by chobanilover »

mikpanko wrote: post #85

There are very interesting improvements to SQL, which are much more ergonomic, extend functionality, and provide higher-level abstractions. Also backward compatible. PRQL and Malloy immediately come to mind but there are more. Anybody has good explanations why they struggle to get wide adoption?

When it comes to data stack tooling, organizations aren't always optimizing for a better way to do things as much as minimizing the worst possible scenario.

New syntax is nice, but it means that analysts and engineers need to learn something new and are more likely to make mistakes that could bubble up to production. There's always an argument to be made why shiny new tool XYZ is better, but unless it's 100X better, organizations are reluctant to switch from something like vanilla PostgreSQL that they know works 100% of the time.

Re: Show HN: PRQL in PostgreSQL

#97

Post by brikym »

It looks a lot like Microsoft’s Kusto query language which is a pleasure to use. Piping is better than nesting and from-first is the way to go as it’s necessary for autocomplete.

Re: Show HN: PRQL in PostgreSQL

#98

Post by chaps »

chobanilover wrote: post #96
mikpanko wrote: post #85

There are very interesting improvements to SQL, which are much more ergonomic, extend functionality, and provide higher-level abstractions. Also backward compatible. PRQL and Malloy immediately come to mind but there are more. Anybody has good explanations why they struggle to get wide adoption?

When it comes to data stack tooling, organizations aren't always optimizing for a better way to do things as much as minimizing the worst possible scenario. New syntax is nice, but it means that analysts and engineers need to learn something new and are more likely to make mistakes that could bubble up to production. There's always an argument to be made why shiny new tool XYZ is better, but unless it's 100X better,…

Normally I would agree, except for the fact that this system works by converting PRQL to SQL. So it's not 100% throwing out the baby with the bath water, since there there are still means for newer engineers to learn SQL through this tooling!

Re: Show HN: PRQL in PostgreSQL

#99

Post by ako »

haspok wrote: post #18
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…

One notable reason is being database-agnostic. Like ORM's, if you can generate SQL you can generate database-specific SQL as well. SQL is also quite verbose in places (JOINs are the most trivial example), and lack a decent amount of abstraction (CTEs are relatively low level). Updating a large set of FK'd tables can be a nightmare (this is what ORMs shine at). Finally, some modern additions are quite unreadable, Post…

How are joins verbose? It’s pretty straight to the point: combine these 2 tables on these columns… what do you want to remove to make it less verbose?

Re: Show HN: PRQL in PostgreSQL

#100

Post by da_chicken »

OJFord wrote: post #88
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…

In the real world, probably something like: select album_id ,first_value(id order by milliseconds desc) as longest_track_id from tracks group by album_id I agree the PRQL's pretty nice here, but I think such a generalised example (chances you actually want to `select *`?) overstates the advantage.

This query would error: "Field `id` is neither in the GROUP BY nor in an aggregate function." Since `id` is a key in `tracks` and `album_id` is a foreign key and not unique in `tracks`, the RDBMS wouldn't be able to use any implicit GROUP BY determinism, either.

You could do this:

    select distinct
      album_id 
      ,first_value(id order by milliseconds desc) as longest_track_id
    from tracks
But this is unlikely to perform as well as the row_number() method because it will cause the RDBMS to generate a record for every track and then waste time sorting the intermediate results to find the unique records in the output.
Post reply on HN