advice needed for update of a table

#11

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

#12

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

#13

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

#14

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

#15

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

#16

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

#17

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

advice needed for update of a table

comp.databases.postgresql comp.databases.postgresql

Re: advice needed for update of a table - 02-26-2008 , 06:19 PM

Re: advice needed for update of a table - 02-26-2008 , 06:19 PM

Re: advice needed for update of a table - 02-26-2008 , 06:19 PM

Re: advice needed for update of a table - 02-26-2008 , 06:19 PM

Re: advice needed for update of a table - 02-26-2008 , 06:19 PM

Re: advice needed for update of a table - 02-26-2008 , 06:19 PM

Re: advice needed for update of a table - 02-26-2008 , 06:19 PM