Populating fields in existing records in postgresql using record ID to determine what goes where

Question

The Scenario

I have a postgresql database containing a list of product variants, which has been populated automatically, however, without the part numbers. I exported the table in order to run some formulas to generate the part numbers for each product variant. This gave me a table containing the relevant record ID and associated part number.

What I'm trying to accomplish

Now that I have my table with the information that's missing, how do I get that information back into the postgresql table?

Sample Data from Postgresql Table [product_product]

   id   create_date       weight    default_code     product_templ_id   ...
    1   2016-12-15 18:57       0    000-000000-000                  1   ...
    2   2016-12-16 19:00       0    000-000000-000                  2   ...
    3   2016-12-16 20:42       0    000-000000-000                 31   ...
    4   2016-12-16 20:43       0    000-000000-000                 31   ...
    5   2016-12-16 20:44       0    000-000000-000                 31   ...
    6   2016-12-16 20:45       0                                   31   ...
  ...   ................     ...    ..............                 31   ...
 1603   2016-12-16 21:51       0                                   31   ...
 1604   2016-12-16 21:52       0                                   31   ...

Sample List of Calculated Part Numbers

  id  default_code  
   6  000-000000-000
 ...  ..............
1604  000-000000-000

What I've Tried

Well, I setup an ODBC connection to the database, linking the table in MS Access 2010. I filtered to the product_templ_id field and sorted by id, in order to match what I had in my Excel spreadsheet. Then, I copied the part numbers from the default_code column and tried pasting at the starting point, but received an error stating I was trying to paste too much information. I gather, Access was trying to paste the clipboard contents into the single cell. Not the behaviour for which I was hoping.

What is the better route?

This particular product has 1440 variants, each with it's own part number and BOM. I really do not want to spend my time generating all that line by line. I'm always telling people that data is data, and you can do anything with data. It's just a matter of working out how.

--EDIT--

I can export my table from pgAdmin, but for some reason when I try to import, nothing happens.

As a result I tried the following command:

COPY product_product (default_code) from '/csv/file/location/file.csv' CSV HEADER delimiter ';' null '/n';

Which resulted in:

ERROR:  extra data after last expected column
CONTEXT:  COPY product_product, line 2: "203;000-000000-000"

I figure, ok, I mention one field, but my csv has two fields. This is because I want to make sure the imported default_code field is associated with the proper record.

Next, I tried:

COPY product_product FROM '/csv/file/location/file.csv' CSV HEADER delimiter ';' null '\n';

Resulting in:

ERROR:  duplicate key value violates unique constraint "product_product_pkey"
DETAIL:  Key (id)=(2) already exists.

So now, I figure I might as well drop the table and rebuild it. Unfortunately there are dependencies setup with this table, so it cannot be dropped.

If I knew how to edit a record value in postgresql, I could write a script to generate the necessary commands to edit all 1440 records accordingly.

Answer 1

You should export your data in CSV format, and use either pgAdmin or a raw COPY command in postgres to import your data back in:

See this post for info on how to do this: https://stackoverflow.com/questions/19400173/how-should-i-import-data-from-csv-into-a-postgres-table-using-pgadmin-3

--EDIT--

Following this post you can use your scripting skills to generate the following SQL query:

UPDATE product_product
    SET default_code = CASE id
        WHEN 6 THEN 000-000000-000
        ...
        ...
        WHEN 1603 THEN 000-000000-000
        WHEN 1604 THEN 000-000000-000
        ELSE default_code
        END;

Then using pgAdmin, connect to your database and run the query. This will parse through the records in the product_product table and update the default_code field of the record corresponding to the relative id value. If the id value is not in your list, it simply keeps the default_code value.

This post ends the query with an additional statement:

        ...
        END
    WHERE id IN(6,...,...,1603,1604);

In this particular case, it is not needed to list the specific id values. Besides, an IN list of upwards of 1440 values will generate an error. That's OK because the ELSE default_code statement acts as a catchall, handling id values not in our case list.

This solution has been tested and verified by the OP.