advice needed for update of a table

#1

Hi,

I want to update one table of my database and I would like to know
what is the fastest way to do it.

I'm using
postgresql version 8.2.4
slackware linux 12.0

Here's the situation.

table_old contains 17 fields. It's used as primary key for other
tables. Twelve of its fields need to be updated for an unknown number
of records.

table_new contains new information. Many records of table_old are
present in table_new, some of which have fresh information.

table_old also contains some records not present in table_new that
should be kept.

table_new contains some new records not present in table_old.

In sum, I need:
1- information present in table_old not present in table_new should be
kept;
2- information present in table_new not present in table_old should be
added;
3- information present in table_new and present in table_old should be
updated in table_old if new information is different from old one.

If I keep table_old as is, 1 is achieved by default.

Nr 2 is achieved by a INSERT statement with a WHERE clause, not done
yet.

But what is taking a long time is nr. 3. I tried this command:
UPDATE table_old set field_a =
(SELECT field_a FROM table_new
WHERE table_old.field_x = table_new.field_x )
WHERE field_x IN (SELECT field_x FROM table_new);

Well, this takes a long time and it has to be repeated for each of the
twelve fields that need to be updated.

table_new contains around 580.000 records and table_new around
600.000.

Other solution I've thought of is to delete all records of table_old
present in table_new and then add them again. This has a disadvantage
regarding foreign keys in other tables, but seems to me to be faster.

Perhaps I could create a function so that I could update all twelve
fields at once for each cycle in table_new?

What do you advise me to do in order to get the job done as quick as
possible?

Luis

#2

luislupe (AT) gmail (DOT) com <luislupe (AT) gmail (DOT) com> wrote:

Quote:

I want to update one table of my database and I would like to know
what is the fastest way to do it.

Here's the situation.

table_old contains 17 fields. It's used as primary key for other
tables. Twelve of its fields need to be updated for an unknown number
of records.

table_new contains new information. Many records of table_old are
present in table_new, some of which have fresh information.

In sum, I need:
1- information present in table_old not present in table_new should be
kept;
2- information present in table_new not present in table_old should be
added;
3- information present in table_new and present in table_old should be
updated in table_old if new information is different from old one.

table_new contains around 580.000 records and table_new around
600.000.

What do you advise me to do in order to get the job done as quick as
possible?

I would write a PL/pgSQL function that reads
table_old FULL OUTER JOIN table_new ON <equality on primary key columns>
in a FOR LOOP.

This should contain at most 1180000 records, and you can determine which of
your cases applies by checking the primary key columns of either table
for NULL.

Use INSERT or UPDATE on table_old as appropriate.

After your procedure has run, it may be a good idea to VACUUM table_old.

Yours,
Laurenz Albe

#3

luislupe (AT) gmail (DOT) com <luislupe (AT) gmail (DOT) com> wrote:

Quote:

I want to update one table of my database and I would like to know
what is the fastest way to do it.

Here's the situation.

table_old contains 17 fields. It's used as primary key for other
tables. Twelve of its fields need to be updated for an unknown number
of records.

table_new contains new information. Many records of table_old are
present in table_new, some of which have fresh information.

In sum, I need:
1- information present in table_old not present in table_new should be
kept;
2- information present in table_new not present in table_old should be
added;
3- information present in table_new and present in table_old should be
updated in table_old if new information is different from old one.

table_new contains around 580.000 records and table_new around
600.000.

What do you advise me to do in order to get the job done as quick as
possible?

I would write a PL/pgSQL function that reads
table_old FULL OUTER JOIN table_new ON <equality on primary key columns>
in a FOR LOOP.

This should contain at most 1180000 records, and you can determine which of
your cases applies by checking the primary key columns of either table
for NULL.

Use INSERT or UPDATE on table_old as appropriate.

After your procedure has run, it may be a good idea to VACUUM table_old.

Yours,
Laurenz Albe

#4

luislupe (AT) gmail (DOT) com <luislupe (AT) gmail (DOT) com> wrote:

Quote:

I want to update one table of my database and I would like to know
what is the fastest way to do it.

Here's the situation.

table_old contains 17 fields. It's used as primary key for other
tables. Twelve of its fields need to be updated for an unknown number
of records.

table_new contains new information. Many records of table_old are
present in table_new, some of which have fresh information.

In sum, I need:
1- information present in table_old not present in table_new should be
kept;
2- information present in table_new not present in table_old should be
added;
3- information present in table_new and present in table_old should be
updated in table_old if new information is different from old one.

table_new contains around 580.000 records and table_new around
600.000.

What do you advise me to do in order to get the job done as quick as
possible?

I would write a PL/pgSQL function that reads
table_old FULL OUTER JOIN table_new ON <equality on primary key columns>
in a FOR LOOP.

This should contain at most 1180000 records, and you can determine which of
your cases applies by checking the primary key columns of either table
for NULL.

Use INSERT or UPDATE on table_old as appropriate.

After your procedure has run, it may be a good idea to VACUUM table_old.

Yours,
Laurenz Albe

#5

luislupe (AT) gmail (DOT) com <luislupe (AT) gmail (DOT) com> wrote:

Quote:

I want to update one table of my database and I would like to know
what is the fastest way to do it.

Here's the situation.

table_old contains 17 fields. It's used as primary key for other
tables. Twelve of its fields need to be updated for an unknown number
of records.

table_new contains new information. Many records of table_old are
present in table_new, some of which have fresh information.

In sum, I need:
1- information present in table_old not present in table_new should be
kept;
2- information present in table_new not present in table_old should be
added;
3- information present in table_new and present in table_old should be
updated in table_old if new information is different from old one.

table_new contains around 580.000 records and table_new around
600.000.

What do you advise me to do in order to get the job done as quick as
possible?

I would write a PL/pgSQL function that reads
table_old FULL OUTER JOIN table_new ON <equality on primary key columns>
in a FOR LOOP.

This should contain at most 1180000 records, and you can determine which of
your cases applies by checking the primary key columns of either table
for NULL.

Use INSERT or UPDATE on table_old as appropriate.

After your procedure has run, it may be a good idea to VACUUM table_old.

Yours,
Laurenz Albe

#6

luislupe (AT) gmail (DOT) com <luislupe (AT) gmail (DOT) com> wrote:

Quote:

I want to update one table of my database and I would like to know
what is the fastest way to do it.

Here's the situation.

table_old contains 17 fields. It's used as primary key for other
tables. Twelve of its fields need to be updated for an unknown number
of records.

table_new contains new information. Many records of table_old are
present in table_new, some of which have fresh information.

In sum, I need:
1- information present in table_old not present in table_new should be
kept;
2- information present in table_new not present in table_old should be
added;
3- information present in table_new and present in table_old should be
updated in table_old if new information is different from old one.

table_new contains around 580.000 records and table_new around
600.000.

What do you advise me to do in order to get the job done as quick as
possible?

I would write a PL/pgSQL function that reads
table_old FULL OUTER JOIN table_new ON <equality on primary key columns>
in a FOR LOOP.

This should contain at most 1180000 records, and you can determine which of
your cases applies by checking the primary key columns of either table
for NULL.

Use INSERT or UPDATE on table_old as appropriate.

After your procedure has run, it may be a good idea to VACUUM table_old.

Yours,
Laurenz Albe

#7

luislupe (AT) gmail (DOT) com <luislupe (AT) gmail (DOT) com> wrote:

Quote:

I want to update one table of my database and I would like to know
what is the fastest way to do it.

Here's the situation.

table_old contains 17 fields. It's used as primary key for other
tables. Twelve of its fields need to be updated for an unknown number
of records.

table_new contains new information. Many records of table_old are
present in table_new, some of which have fresh information.

In sum, I need:
1- information present in table_old not present in table_new should be
kept;
2- information present in table_new not present in table_old should be
added;
3- information present in table_new and present in table_old should be
updated in table_old if new information is different from old one.

table_new contains around 580.000 records and table_new around
600.000.

What do you advise me to do in order to get the job done as quick as
possible?

I would write a PL/pgSQL function that reads
table_old FULL OUTER JOIN table_new ON <equality on primary key columns>
in a FOR LOOP.

This should contain at most 1180000 records, and you can determine which of
your cases applies by checking the primary key columns of either table
for NULL.

Use INSERT or UPDATE on table_old as appropriate.

After your procedure has run, it may be a good idea to VACUUM table_old.

Yours,
Laurenz Albe

#8

luislupe (AT) gmail (DOT) com <luislupe (AT) gmail (DOT) com> wrote:

Quote:

I want to update one table of my database and I would like to know
what is the fastest way to do it.

Here's the situation.

table_old contains 17 fields. It's used as primary key for other
tables. Twelve of its fields need to be updated for an unknown number
of records.

table_new contains new information. Many records of table_old are
present in table_new, some of which have fresh information.

In sum, I need:
1- information present in table_old not present in table_new should be
kept;
2- information present in table_new not present in table_old should be
added;
3- information present in table_new and present in table_old should be
updated in table_old if new information is different from old one.

table_new contains around 580.000 records and table_new around
600.000.

What do you advise me to do in order to get the job done as quick as
possible?

I would write a PL/pgSQL function that reads
table_old FULL OUTER JOIN table_new ON <equality on primary key columns>
in a FOR LOOP.

This should contain at most 1180000 records, and you can determine which of
your cases applies by checking the primary key columns of either table
for NULL.

Use INSERT or UPDATE on table_old as appropriate.

After your procedure has run, it may be a good idea to VACUUM table_old.

Yours,
Laurenz Albe

#9

luislupe (AT) gmail (DOT) com <luislupe (AT) gmail (DOT) com> wrote:

Quote:

I want to update one table of my database and I would like to know
what is the fastest way to do it.

Here's the situation.

table_old contains 17 fields. It's used as primary key for other
tables. Twelve of its fields need to be updated for an unknown number
of records.

table_new contains new information. Many records of table_old are
present in table_new, some of which have fresh information.

In sum, I need:
1- information present in table_old not present in table_new should be
kept;
2- information present in table_new not present in table_old should be
added;
3- information present in table_new and present in table_old should be
updated in table_old if new information is different from old one.

table_new contains around 580.000 records and table_new around
600.000.

What do you advise me to do in order to get the job done as quick as
possible?

I would write a PL/pgSQL function that reads
table_old FULL OUTER JOIN table_new ON <equality on primary key columns>
in a FOR LOOP.

This should contain at most 1180000 records, and you can determine which of
your cases applies by checking the primary key columns of either table
for NULL.

Use INSERT or UPDATE on table_old as appropriate.

After your procedure has run, it may be a good idea to VACUUM table_old.

Yours,
Laurenz Albe

#10

On 26 Fev, 15:18, Laurenz Albe <inv... (AT) spam (DOT) to.invalid> wrote:

Quote:

luisl... (AT) gmail (DOT) com <luisl... (AT) gmail (DOT) com> wrote:
I want to update one table of my database and I would like to know
what is the fastest way to do it.

Here's the situation.

table_old contains 17 fields. It's used as primary key for other
tables. Twelve of its fields need to be updated for an unknown number
of records.

table_new contains new information. Many records of table_old are
present in table_new, some of which have fresh information.

In sum, I need:
1- information present in table_old not present in table_new should be
kept;
2- information present in table_new not present in table_old should be
added;
3- information present in table_new and present in table_old should be
updated in table_old if new information is different from old one.

table_new contains around 580.000 records and table_new around
600.000.

What do you advise me to do in order to get the job done as quick as
possible?

I would write a PL/pgSQL function that reads
table_old FULL OUTER JOIN table_new ON <equality on primary key columns
in a FOR LOOP.

This should contain at most 1180000 records, and you can determine which of
your cases applies by checking the primary key columns of either table
for NULL.

Use INSERT or UPDATE on table_old as appropriate.

After your procedure has run, it may be a good idea to VACUUM table_old.

Yours,
Laurenz Albe

Thank you, once again, Laurenz!

Luis