PostgREST
exclude columns in select *
My problem seems very simple.
I want something like: 'http://localhost:3000/my-table?select=all_except_col_name' How to make such a request?
That's not possible right now. There's no support in PostgreSQL for a SELECT.. EXCEPT(see this and this).
In theory we could implement such a feature at the PostgREST level, by taking advantage of our schema cache and adding an except query parameter.
GET /mytable?select=*&except=col_name
``
I think we already had a question/issue like this - but I can't find it right now.
There's some discussion on -hackers about a feature with some similarity to this: https://www.postgresql.org/message-id/flat/be81d947-f4fe-c062-f107-0f05f8f87ca8%40migops.com
Not exactly the same, but SELECT ... EXCEPT was also mentioned as part of the discussion.
That's not possible right now. There's no support in PostgreSQL for a
SELECT.. EXCEPT(see this and this).In theory we could implement such a feature at the PostgREST level, by taking advantage of our schema cache and adding an
exceptquery parameter.GET /mytable?select=*&except=col_name ``
I was thinking of a simpler syntax like:
GET /mytable?select=-col_name
Some observations:
- The use case in https://github.com/PostgREST/postgrest/discussions/4071, describes selecting all but a
tsvectorcolumn that was used for filtering. - On the mailing list thread shared above:
- There's a similar case for a PostGIS geometry column (ref).
- Another one for tsvector (ref)
- The HIDDEN column feature looks really bad for logical data independence IMO. It will break apps.
- The EXCEPT syntax is the clear winner, the only real opposing argument is that it's not part of the SQL standard.
- The EXCEPT syntax is the clear winner, the only real opposing argument is that it's not part of the SQL standard.
Although PostgREST is obviouly tied to PostgresQL, I don't think it should be limited by PostgresQL or SQL standard. One of the strong points of PostgREST is precisely that it can be used without having to write a single line of SQL. The implementation might be a bit more complicated now, but it can be simplified later when/if this becomes part of native PostgresQL.