Postgresql – Mirrored tables on PostgreSQL

database-designpostgresql

I have some values under "account" and I need to have the same values under "invoice", so, I'm trying to find a way to symlink my tables, so every time one gets updated, the another have the new value too. I'm developing under OpenERP, I can't trow that update directly on code, I need to write the rule using PgAdmin or something like.

I need to junction symlink 2 tables under Postgres.

ACCOUNT                INVOICE  
|ID|payment_type|@@@@@@|ID|account_payment_type|  
|#1|cc          |@@@@@@|#1|cc                  |  
|#2|cash        |@@@@@@|#2|cash                |  
|#3|cash        |@@@@@@|#3|cash                |

Best Answer

From your example it seems like invoice can be replaced with a simple VIEW, which is similar to a "symlink" in nature:

CREATE VIEW invoice AS
SELECT id, account_payment_type FROM account;

All done.
Else you need to be more specific in your question. Start with providing the complete table definition for both tables and add more information what is covered in each ...

Related Solutions

Postgresql – good way to run a trigger for each record in a postgres table

It can be done, using the following template:

CREATE TABLE tablename ( ... );

/* for direct invocation */
CREATE FUNCTION propagate_data(newrow tablename) RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO other_table VALUES (newrow.a, newrow.b, ...);
END:
$$;

/* trigger function wrapper */
CREATE FUNCTION propagate_data_trg() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    PERFORM propagate_data(NEW);
END;
$$;

CREATE TRIGGER propagate_data AFTER INSERT ON tablename FOR EACH ROW
    EXECUTE PROCEDURE propagate_data_trg();

PostgreSQL – pgAdmin Error When Creating Server

You need to enter the password of the database user, not the OS user. The "root password" is not applicable. Do you know the password of the database user postgres?

Most systems are set up allowing peer authentication for local connections, meaning a system user can log into the database with a database user of the same name without password. localhost is not a local connection. Its privileges are determined by host entries in your pg_hba.conf file.

Since you seem to be running Postgres and pgAdmin on the same server, I suggest you try a local connection without password. Quoting the pgAdmin manual:

On Unix based systems, the address field may be left blank to use the default PostgreSQL Unix Domain Socket on the local machine, or be set to an alternate path containing a PostgreSQL socket.

Bold emphasis mine.
Of course, you have to run pgAdmin as the system user postgres for this to work.

This related answer on SO has a lot more details and links.

Best Answer

Related Solutions

Postgresql – good way to run a trigger for each record in a postgres table

PostgreSQL – pgAdmin Error When Creating Server

Related Question