Le 15/10/2021 à 09:47, Aleksander Alekseev a écrit :
Just to remind here, there was recently a proposal to handle this
problem another way - provide a list of columns to skip for "star
selection" aka "SELECT * EXCEPT col1...".
https://postgrespro.com/list/id/d51371a2-f221-1cf3-4a7d-b2242d4da...@gmail.com
[...]
I feel using EXCEPT would be a lot clearer, no one is likely to be
mislead into thinking that its is a security feature unlike 'HIDDEN'.
Also you know that SELECT * will select all columns.
If this kind of feature were to be added, then I'd give a +1 to use the
EXCEPT syntax.
+1 to that, personally I would love to have SELECT * EXCEPT ... syntax
in PostgreSQL. Also, I discovered this feature was requested even
earlier, in 2007 [1]
I don't think that the EXCEPT syntax will be adopted as it change the
SQL syntax for SELECT in a non standard way. This is not the case of the
hidden column feature which doesn't touch of the SELECT or INSERT syntax.
HIDDEN columns affect SELECT and INSERT behaviour in the same
non-standard way, although maybe without changing the syntax.
Personally, I believe this is even worse. The difference is that with
`SELECT * EXCEPT` you explicitly state what you want, while HIDDEN
columns do this implicitly. Extending the syntax beyond standards in a
reasonable way doesn't seem to be a problem. As a recent example in
this thread [2] the community proposed to change the syntax in
multiple places at the same time.
`SELECT * EXCEPT` solves the same problem as HIDDEN columns, but is
much easier to implement and maintain. Since it's a simple syntax
sugar it doesn't affect the rest of the system.
That's not true, this is not the same feature. the EXCEPT clause will
not return column that you don't want in a specific request. I have
nothing against that but you have to explicitly name them. I think about
kind of bad design that we can find commonly like a table with
attribute1 ... attribute20. If we can use regexp with EXCEPT like
'attribute\d+' that could be helpful too. But this is another thread.
The hidden column feature hidden the column for all queries using the
wilcard on the concerned table. For example if I have to import a
database with OID enabled from an old dump and I want to prevent the OID
column to be returned through the star use, I can turn the column hidden
and I will not have to modify my old very good application. I caricature
but this is the kind of thing that could happen. I see several other
possible use of this feature with extensions that could use a technical
column that the user must not see using the wildcard. Also as Vik or
Dave mention being able to hide all tsvector columns from query without
having to specify it as exception in each query used can save some time.
IMHO this is definitively not the same feature.
--
Gilles Darold
