0

I am creating partition tables in postgreSQL. And the partition is on date column so its nearly high in number to show in schema. I want to show only my parent table for other users. I tried many searches but this kind of concept is not explained anywhere. This is my hierarchy,

Master_table
  child_01_2013
  child_02_2013
  ....
  child_07_2019

so child table is in Monthly partition. I dont want to show all child table in my database. I am using Pg-admin 4 and DB visualizer(Free) tool for my works. Kindly help me to resolve this.

  • What is the problem if users see the partitions? You could put them in a different schema to unclutter the display. – Laurenz Albe Aug 7 '19 at 5:58
  • ALTER TABLE <partition_name> SET SCHEMA <new_schema>; – Laurenz Albe Aug 12 '19 at 15:08
  • No, this won't affect anything, except direct access to partitions. Internally, tables are not referenced by name, but by object ID, and that does not change if you move the table to a different schema. – Laurenz Albe Aug 13 '19 at 9:12
0

If all you want is to remove the partitions from the schema view, you could simply move the partitions to another schema:

ALTER TABLE child_01_2013 SET SCHEMA otherschema;

Everything will continue to work as before, but the visual clutter will be gone.

| improve this answer | |
  • This works fine but I am trying to insert these child tables from different schema i.e parent table and trigger function were in schema 1 and child tables were in schema 2. It throws me an error while I am doing this. Child is not referenced to parent table from schema2. – prabhu Aug 19 '19 at 13:20
  • Can you ask a new question for this? Please show the SQL statement and the exact error you are getting. – Laurenz Albe Aug 19 '19 at 13:34
0

I don't know for dbvisualizer. For pgAdmin4 (tested on version 4.11) the partitions already show up in a folder under the main table in the object tree. Unless you open the folder, you aren't going to see them. You can even hide that folder altogether from the pgAdmin4 preferences setting, if you want.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.