1
1
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.
I was able to export from pgAdmin, but not import. I have edited my question outlining further steps I have taken. Thank you for sharing that link. It was helpful. – Sirach Matthews – 2017-01-09T08:51:20.887
1
@SirachMatthews Following this post they recommend you import into a new table, and use existing SQL to replace entries in the target table. See also this post
– Goodbye StackExchange – 2017-01-09T11:21:46.857