Re: TWO SAME TABLES, ONE UPDATED. HOW TO SYNC THE OTHER? - Mailing list pgsql-general

On May 11, 11:06 pm, "L. Berger" <straightfwd...@gmail.com> wrote:
> Hello
>
> I havetwotables-- A and B. The structure of both is thesame. Only,
> B has many indexes and is used for heavy duty SELECTs. On theother
> hand, A only accepts heavy duty INSERTs, so has onlyoneprimary key
> index.
>
> So my DB design is such that A is only an INSERT table. Periodically,
> say every 20 minutes or so, I would like to take all the new INSERTs
> from table A and put them into B.
>
> Is there any clever command to accomplish this? I'd rather not write a
> PHP script with SQL to take every single new record, and update every
> column of a new row in table B. For instance, can I do a replication
> of onlytables, not databases?
>
> Thanks for any pointers!!
>
> LB

Assuming ID is PK:

INSERT INTO b
    SELECT *
    FROM a
    WHERE NOT EXISTS (
        SELECT 1
        FROM b
        WHERE b.ID = a.ID
    )