Is there a way to create phantom tables in a postgres database?

user_14100754 0
Viewed 59

I have a table called groups in my postgres production server, they have a column called manager_id. What I'm trying to do is to create simulated look up tables base off the ids of the manager. For example, if I have the following rows for table groups:

|   id   |manager_id| ... |
|   1    |     1    | .
|   2    |     3    | .
|   4    |     1    |
|   5    |     2    |
|   7    |     2    |

I would like to access make believe tables like group-1

|   id   |manager_id| ... |
|   1    |     1    | .
|   4    |     1    | .

Or group-2

|   id   |manager_id| ... |
|   5    |     2    | .
|   7    |     2    | .

I am not sure if this is possible, and yes I'm aware I could query for it, but for the purpose of the question (and my very specific needs, which I am having trouble finding a workaround), can I do something like that? If yes, can I do it without duplicating data, just picking up references from the original one?

sql-view where-clause postgresql sql
1 Answers

Generally: just a use a where clause to filter on the manager you want the result from:

select *
from groups
where manager_id = 1

You could take one step forward and create views:

create view v_groups_1 as
select *
from groups
where manager_id = 1

create view v_groups_2 as
select *
from groups
where manager_id = 2

You can then run queries against the views just like you would do with a regular table.

user_106767160
Related