2

I'm using Postgres. I'm in the middle of the development. I need to create a replica of table ABC. I will name it as XYZ. I tried create table XYZ () inherits(ABC). It works ok but when I insert values on XYZ, table ABC is also populated (virtually). I am getting duplicate values when I run query something like this select * from ABC where id = 1. I get the correct record when I run query like this select * from only ABC where id = 1.

The this is, I don't want to use 'only'. So, I don't think inheritance is what I need. I just want to ask if there is a way to add column automatically on XYZ when I alter(add column) ABC?

I don't want to create two queries of alter for ABC and XYZ. I need the two tables to have the same columns always. Any idea?

Raj Saxena
  • 852
  • 10
  • 18
Eleven
  • 339
  • 2
  • 6
  • 20
  • "*I need the two tables to have same columns always*" - I fail to see why you don't simply use a single table then plus a view. It would better if you describe the real underlying problem you are trying to solve rather than the problem you have with a technical implementation –  Feb 27 '15 at 06:47
  • I want to use first the second table. Here's the scenario of what I am doing. – Eleven Feb 27 '15 at 06:52
  • I want to use first the second table. Here's the scenario of what I am doing:
    there's a link on page 1. by clicking that link, single record on table ABC will be inserted on table XYZ (by this: 'insert into XYZ as select * from ABC') then the page redirects to page 2. The record inserted on XYZ will be the one to be used on page2. if 'edited', XYZ will be edited. If 'saved and completed', the value on XYZ will modify the record residing on ABC. So basically, table XYZ is just a temporary table. I have three buttons on page 2.     – Eleven Feb 27 '15 at 07:06
  • 'Edit', 'Save' and 'Save and Complete'. Clicking Edit or Save will just modify XYZ (not ABC). Clicking 'Save and Complete' will modify XYZ then ABC. – Eleven Feb 27 '15 at 07:06
  • Create a flag `completed` which distinguishes between "saved" and "completed". Then include a `where not completed` or `where completed` in your queries. (or create two views: `completed_items` and `open_items` that do this) –  Feb 27 '15 at 07:10
  • I forgot. there's also a button named 'revert changes' which can revert changes after saving that's why I need two tables. – Eleven Feb 27 '15 at 07:35
  • Then use a version number that is part of the primary key and gets incremented each time you save it. –  Feb 27 '15 at 07:40
  • sorry. I was not that clear and Im too broad with the specs. There are 6 sections. Every section has 'save' button. There is one 'complete' button. one 'revert changes'. Im dont think that version will work here. – Eleven Feb 27 '15 at 08:54

1 Answers1

0

The kind of trigger you're talking about is generally known as a DDL trigger. In PostgreSQL, they're known as event triggers. I'm not sure whether you can execute an alter table statement in a PostgreSQL event trigger, though.

I'm not confident that this is really the solution you're looking for, though. I think you might be in the middle of an X-Y problem.

Community
  • 1
  • 1
Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
  • my team leader also thinks that I have to search on how to create a trigger on 'alter table'. For the mean time, I created an 'alter table' function on my code to get all the names and types of table1 then add the non-existing columns on table2. – Eleven Mar 05 '15 at 03:11