I have a job in oracle which populates the table for frontend system. To populate this table we map many other other tables. The frontend refers this for any search they do. In order to make the data always available to frontend we have two copies of the table. A synonym refers to the active table. The other table gets populated as part of the job and once it completes the synonym is swapped. In case we have new changes like adding new column the inactive table gets populated and then synonym is swapped. Every day we truncate and load the inactive table. Once done swap the synonym. The frontend query always refer to synonym.
How to do this in postgres. One way i could think is put the active table name in another table and read this entry and use it for application. This means every where i have to make the query as dynamic or put IF/ELSE and write the same query twice with diff table names. Is there a better we have of doing this is postgres. I have 13.3 version
customer_table_1 (customer_id numeric, customer_name text, . . )
customer_table_2 (customer_id numeric, customer_name text, . . )