2

Can we create hidden column in the table. It should be listed only when I specify column name explicitly in select statement.

Improve this question
5
  • 3
    What problem are you trying to solve? You clearly think that a "hidden column" is the solution but the solution to what problem? Commented Jul 7, 2014 at 6:55
  • I have been asked to confirm that does postgresql has such feature as part of my solution. So, just wanted to know whether it has or not. and to my knowledge I have also never heard this feature is in postgresql. Or What can be the convincing answer to them. Commented Jul 7, 2014 at 7:10
  • 1
    @Pavunkumar You should go back to whoever's asking and ask them "why? What for? What problem is that going to solve for you?" Commented Jul 7, 2014 at 7:42
  • A simple use case is getting a more flexible schema design into your database. For example, if application A does a SELECT * today and application B wants to add a new field, application A would most likely fail because it gets an additional column back it doesn't expect. The app shouldn't do a SELECT * in the first place but such things happen and when you don't have control over the code, such as with a third-party application, you get a lot of problems and then even views can't help. docs.oracle.com/database/122/ADMIN/… Commented Sep 22, 2017 at 22:47
  • I don't why the two of them care so much why OP needs a problem for this solution, but here's an answer from Spatie's blog. Commented Feb 26 at 16:23

2 Answers 2

Reset to default
5

You can effectively do this by creating a view and selecting only the columns you wish to show.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

headdesk. I didn't think to suggest that. In PostgreSQL, simple views are updatable too, so they appear just like a table to most applications.
But that column would still show up when you run a select * on the base table.
@a_horse_with_no_name Not if you don't GRANT the SELECT right on the base table. Look into security_barrier views. 9.4 adds support for simply updatable security barrier views; in older versions you can use rule- or trigger-based updatable views. Or if you don't mind the potential information leakage via malicious predicates, just use an ordinary view - if a user has the right to select from a view, they implicitly gain the right to select the columns of the underlying table via the view, so you don't have to explicitly GRANT access to the table.
Have you tried this? Postgres does not seem to support views without all the columns.
@Pyrolistical I'm not sure I understand your comment - under what scenario could you not create a view with whatever columns you choose to include? (And yes, I have tried this :-) )
|
5

No, there is no supported and safe way to create a hidden column that's listed only when you specify the column name explicitly. PostgreSQL doesn't provide any user-accessible way to hide user-defined columns from the * wildcard.

You could use any user interface layer / query builder of your choice to do this, though.

(PostgreSQL actually does have hidden columns, as you'll see if you select ctid,xmin,xmax from some_table, but it doesn't allow users to add to the set of hidden columns. It is possible to modify the system catalogs directly to trick PostgreSQl into thinking that a user defined column is a hidden system column, but it's a really bad idea to mess directly with the catalogs, so I won't explain how in detail. If you insist on doing this, read the documentation on pg_attribute ... but understand that you're creating a giant foot gun.).

You can set column permissions so a user can only select some columns, though again you can't say "all except this one", you have to say "I want them to be able to see these ones".

Update: @maybeWeCouldStealAVan has the most sensible suggestion: you probably want a view. Mark that answer as correct, not mine.

Improve this answer

5 Comments

Is it being supported by any other database.?
@Pavunkumar I don't have an encyclopedic knowledge of other databases. I don't know of any with the feature, but then, I've never wanted it either so I wouldn't have looked.
@Pavunkumar, MariaDB now includes support for invisible columns. (But MariaDB didn't exist when you asked that question, and its forerunner MySQL didn't support this feature.)
Hidden (invisible) columns are available in MySQL as of version 8.0.23.
@Pavunkumar Invisible columns are available in Oracle since at least version 12.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.