Re: Can we go beyond the standard to make Postgres radically better?

From: "Daniel Verite" <daniel(at)manitou-mail(dot)org>
To: "Peter J(dot) Holzer" <hjp-pgsql(at)hjp(dot)at>
Cc: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Re: Can we go beyond the standard to make Postgres radically better?
Date: 2022-02-11 13:35:33
Message-ID: 2acec54b-4491-4f5a-aa48-4d4cda72b302@manitou-mail.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Peter J. Holzer wrote:

> > My use case for such a feature are tables which contain one column (or a
> > small number of columns) which you usually don't want to select: A bytea
> > column or a very wide text column. In a program I don't mind (in fact I
> > prefer) listing all the columns explicitely, but exploring a database
> > interactively with psql typing lots of column names is tedious
> > (especially since autocomplete doesn't work here).
>
> Forgot to add: I think that the syntax would have to be more explicit.
> It's too easy to mix up
> SELECT * - b.a_id FROM ...
> and
> SELECT *, - b.a_id FROM ...
>
> Maybe
> SELECT * EXCEPT b.a_id FROM ...

The solution to this by the SQL standard might be that it can be done with
a Polymorphic Table Function, introduced in SQL:2016.

https://webstore.iec.ch/preview/info_isoiec19075-7%7Bed1.0%7Den.pdf

A practical example with the Oracle implementation can be seen here:
https://blogs.oracle.com/sql/post/select-star-except-queries-in-oracle-database

Excluding all columns of a given type from a relation is also possible,
with both the relation and the type as parameters of the PTF.

Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: https://www.manitou-mail.org
Twitter: @DanielVerite

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Imre Samu 2022-02-11 13:37:55 Re: Can we go beyond the standard to make Postgres radically better?
Previous Message Ron 2022-02-11 08:15:58 Re: Can we go beyond the standard to make Postgres radically better?