Обсуждение: Copying records from TABLE_A to TABLE_B (in the same database)

AWS RDS Postgresql 12.10

There are no indices or constraints (except for NOT NULL) on table_a.

The two ways that I know are:
     INSERT INTO table_a SELECT * FROM table_b;
and
     \COPY table_a TO '/tmp/table_a.tsv' WITH (FORMAT BINARY);
     \COPY table_b FROM '/tmp/table_a.tsv' WITH (FORMAT BINARY);

Is there a faster/better way?

-- 
Angular momentum makes the world go 'round.

AWS RDS Postgresql 12.10

There are no indices or constraints (except for NOT NULL) on table_a.

The two ways that I know are:
    INSERT INTO table_a SELECT * FROM table_b;
and
    \COPY table_a TO '/tmp/table_a.tsv' WITH (FORMAT BINARY);
    \COPY table_b FROM '/tmp/table_a.tsv' WITH (FORMAT BINARY);

Is there a faster/better way?

On 8/2/22 11:37 AM, Ron wrote:
> AWS RDS Postgresql 12.10
> 
> There are no indices or constraints (except for NOT NULL) on table_a.
> 
> The two ways that I know are:
>      INSERT INTO table_a SELECT * FROM table_b;
> and
>      \COPY table_a TO '/tmp/table_a.tsv' WITH (FORMAT BINARY);
>      \COPY table_b FROM '/tmp/table_a.tsv' WITH (FORMAT BINARY);
> 
> Is there a faster/better way?
> 
Does table_a have existing records?

If so do you care if there are duplicates?

How large a data set are you talking about?

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 8/2/22 12:37, Ron wrote:

AWS RDS Postgresql 12.10

There are no indices or constraints (except for NOT NULL) on table_a.

The two ways that I know are:
    INSERT INTO table_a SELECT * FROM table_b;
and
    \COPY table_a TO '/tmp/table_a.tsv' WITH (FORMAT BINARY);
    \COPY table_b FROM '/tmp/table_a.tsv' WITH (FORMAT BINARY);

Is there a faster/better way?

create view ?

Ron schrieb am 02.08.2022 um 20:37:
> AWS RDS Postgresql 12.10
>
> There are no indices or constraints (except for NOT NULL) on table_a.
>
> The two ways that I know are:
>      INSERT INTO table_a SELECT * FROM table_b;
> and
>      \COPY table_a TO '/tmp/table_a.tsv' WITH (FORMAT BINARY);
>      \COPY table_b FROM '/tmp/table_a.tsv' WITH (FORMAT BINARY);
>
> Is there a faster/better way?

The INSERT is most probably faster then \copy

Another option is to have a trigger on table_a
to automatically replay all DML on table_b


Logical replication might be another option.
Although I am not sure if that is even possible inside
the samme database.
I know it's tricky inside the same server
(between different databases)

On 8/2/22 13:41, Adrian Klaver wrote:
> On 8/2/22 11:37 AM, Ron wrote:
>> AWS RDS Postgresql 12.10
>>
>> There are no indices or constraints (except for NOT NULL) on table_a.
>>
>> The two ways that I know are:
>>      INSERT INTO table_a SELECT * FROM table_b;

Argh, I got the tables backwards.  Should be:

INSERT INTO table_b SELECT * FROM table_a;

>> and
>>      \COPY table_a TO '/tmp/table_a.tsv' WITH (FORMAT BINARY);
>>      \COPY table_b FROM '/tmp/table_a.tsv' WITH (FORMAT BINARY);
>>
>> Is there a faster/better way?
>>
> Does table_a have existing records?

Yes.  Just before the copy, table_b was created using:
     CREATE TABLE table_b (LIKE table_a INCLUDING CONSTRAINTS INCLUDING 
DEFAULTS);

The only constraints on table_a are NOT NULL on various fields.

> If so do you care if there are duplicates?

TABLE_A (the source) has a UNIQUE index.  I'll be adding a similar PK on 
TABLE_B after the copy.

> How large a data set are you talking about?

It's varied.  The biggest have up to 20M rows with a bytea field, and others 
with 50M rather large (but no bytea) fields.

INSERT INTO is good enough for the small tables.

-- 
Angular momentum makes the world go 'round.

Ron schrieb am 02.08.2022 um 20:37:

AWS RDS Postgresql 12.10

There are no indices or constraints (except for NOT NULL) on table_a.

The two ways that I know are:
     INSERT INTO table_a SELECT * FROM table_b;
and
     \COPY table_a TO '/tmp/table_a.tsv' WITH (FORMAT BINARY);
     \COPY table_b FROM '/tmp/table_a.tsv' WITH (FORMAT BINARY);

Is there a faster/better way?

The INSERT is most probably faster then \copy

Another option is to have a trigger on table_a
to automatically replay all DML on table_b


Logical replication might be another option.
Although I am not sure if that is even possible inside
the samme database.
I know it's tricky inside the same server
(between different databases)

On 8/2/22 13:51, Thomas Kellerer wrote:
> Ron schrieb am 02.08.2022 um 20:37:
>> AWS RDS Postgresql 12.10
>>
>> There are no indices or constraints (except for NOT NULL) on table_a.
>>
>> The two ways that I know are:
>>      INSERT INTO table_a SELECT * FROM table_b;
>> and
>>      \COPY table_a TO '/tmp/table_a.tsv' WITH (FORMAT BINARY);
>>      \COPY table_b FROM '/tmp/table_a.tsv' WITH (FORMAT BINARY);
>>
>> Is there a faster/better way?
>
> The INSERT is most probably faster then \copy

That's what I figured, since COPY means moving data over the (admittedly 
very fast) wire, onto disk, and then back over the wire into the new table.

>
> Another option is to have a trigger on table_a
> to automatically replay all DML on table_b

How would you originally populate it?

> Logical replication might be another option.
> Although I am not sure if that is even possible inside
> the samme database.
> I know it's tricky inside the same server
> (between different databases)

-- 
Angular momentum makes the world go 'round.

On 8/2/22 12:51, Thomas Kellerer wrote:

Ron schrieb am 02.08.2022 um 20:37:

AWS RDS Postgresql 12.10

There are no indices or constraints (except for NOT NULL) on table_a.

The two ways that I know are:
     INSERT INTO table_a SELECT * FROM table_b;
and
     \COPY table_a TO '/tmp/table_a.tsv' WITH (FORMAT BINARY);
     \COPY table_b FROM '/tmp/table_a.tsv' WITH (FORMAT BINARY);

Is there a faster/better way?

The INSERT is most probably faster then \copy

Another option is to have a trigger on table_a
to automatically replay all DML on table_b


Logical replication might be another option.
Although I am not sure if that is even possible inside
the samme database.
I know it's tricky inside the same server
(between different databases)

If you can get outside sql, the bulk copy facilities (CopyManager in java) is blindingly fast for me.

Logical replication might be another option.
Although I am not sure if that is even possible inside
the samme database.
I know it's tricky inside the same server
(between different databases)

If you can get outside sql, the bulk copy facilities (CopyManager in java) is blindingly fast for me.

??

On 2022-08-02 13:08:41 -0600, Rob Sargent wrote:
>         If you can get outside sql, the bulk copy facilities (CopyManager in
>         java) is blindingly fast for me.
>
>
>     ??
>
>
> I meant using tools other than sql (and psql).   I have java code using
> org.postgresql.copy package

I don't know that package but I'm pretty sure it's just using the
PostgreSQL COPY command.

> because straight insert was too slow for large numbers of rows.

The OP already has the data in the database. In my experience[1] copying
from one table to another is quite a bit faster that copying into the
database (and therefore also copying out AND copying in).

        hp

[1] https://github.com/hjp/dbbench/blob/master/import_pg_comparison/results/akran.2019-12-15/results.png


--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"