Thread: Copy Data Question
a while back i had to take data from a spreadsheet (*.ods, openoffice) and copy it into a table. i figured it out and i remember posting what i did on here - so time to go find it so i can do it again. ;-) however, this time i have a different situation. i've hand entered 100+ assemblies. i have a list of ~1,000 assemblies that need to be entered. how can i add *only* nonexisting entries to my db tables (iow, if it is one of the 100+ there now, i don't want to mess with it)? also, how do i update multiple tables with related info? do i create a view and then copy to the view? will that automatically update the tables? tia... __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
> how can i add *only* nonexisting entries to my db > tables (iow, if it is one of the 100+ there now, i > don't want to mess with it)? I will take a stab at the easy question. Out of test solutions, this is the one that I know. 1. create a temp table using destination table schema using the LIKE clause. http://www.postgresql.org/docs/8.1/interactive/sql-createtable.html 2. import external data into the temp table. 3. select into destinationtable from temptable where temptable.primarykey not exists ( select destinationtable.primarykey from destination table ); > also, how do i update multiple tables with related > info? do i create a view and then copy to the view? > > will that automatically update the tables?
> > how can i add *only* nonexisting entries to my db > > tables (iow, if it is one of the 100+ there now, i > > don't want to mess with it)? > > I will take a stab at the easy question. Out of test > solutions, this is the one that I know. > > 1. create a temp table using destination table > schema using the LIKE clause. > http://www.postgresql.org/docs/8.1/interactive/sql-createtable.html > > 2. import external data into the temp table. > > 3. select into destinationtable from temptable where > temptable.primarykey not exists > ( select destinationtable.primarykey > from destination table > ); Richard, thanks. in my case, the primary key isn't relevant, however, the product_number is (it is unique). iiuc, in my case i should use product_number instead of primarykey. it makes sense - thanks for the enlightenment. __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
> Richard, thanks. in my case, the primary key isn't > relevant, however, the product_number is (it is > unique). iiuc, in my case i should use product_number > instead of primarykey. Sure, the "primarykey" that I showed was just to express the general idea. Any unique column ( or unique column combinations) would work perfectly as well. Regards, Richard Broersma Jr.
----- Original Message ----- Subject: Re: [NOVICE] Copy Data Question From: operationsengineer1@yahoo.com Date: Thu, June 22, 2006 15:54 > > how can i add *only* nonexisting entries to my db > > tables (iow, if it is one of the 100+ there now, i > > don't want to mess with it)? > > I will take a stab at the easy question. Out of test > solutions, this is the one that I know. > > 1. create a temp table using destination table > schema using the LIKE clause. > http://www.postgresql.org/docs/8.1/interactive/sql-createtable.html > > 2. import external data into the temp table. > > 3. select into destinationtable from temptable where > temptable.primarykey not exists > ( select destinationtable.primarykey > from destination table > ); Richard, thanks. in my case, the primary key isn't relevant, however, the product_number is (it is unique). iiuc, in my case i should use product_number instead of primarykey. it makes sense - thanks for the enlightenment. __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings A few additional thoughts: Create a temp table that is the same struct as the tartet table. Insert the 100+ rows into this new table. Next run an insert into <target_tab> from <temp_table> where <row values> not in (select <row_value> from <target_table>) If you need to check multiple columns for uniqueness in the target tab then compare like this: insert into <target_tab> from <temp_table> where col_a || col_b || col_c || col_d || col_e not in (select col_a || col_b || col_c || col_d || col_e from <target_table>) ======================================= also, how do i update multiple tables with related info? Try creating a series of sql stmts that insert the data in the correct order for the constraints and wrap it in a transaction so you can rollback if something goes wrong. If you created the constraints for the related tables as deferrable (not the default) then you could run: begin work; set constraints all deferred; <insert / update statements in any order> commit; /Kevin
> i Start -> Run-> type "cmd" and press enter > i then type.... > COPY t_temp FROM C:\parts.csv' > and i get the following error: > "The system cannnot find the file specified." > parts.csv is located in the C: directory.> > can anyone help out? Try it again in a directory owned by postgres maybe that will work.
>> i Start -> Run-> type "cmd" and press enter
>> i then type....
>> COPY t_temp FROM C:\parts.csv'
>> and i get the following error:
>> "The system cannnot find the file specified."
I'll bet doubling the backslash would help.
BTW, Postgres itself would certainly have echoed back the filename it
was trying to open, which would have helped you:
regression=# copy tenk1 from 'C:\parts.csv';
ERROR: could not open file "C:parts.csv" for reading: No such file or directory
Whatever software actually produced that message needs to be fixed.
regards, tom lane
<BLOCKQUOTE style="PADDING-LEFT: 8px; MARGIN-LEFT: 8px; BORDER-LEFT: blue 2px solid" name="wmMessageComp"> > 3. select into destinationtable from temptable where> temptable.primarykey not exists> ( select destinationtable.primarykey> from destination table> ); Richard, thanks. in my case, the primary key isn'trelevant, however, the product_number is (it isunique). iiuc, in my case i should use product_numberinstead of primarykey.it makes sense - thanks for the enlightenment. (slightly off topic) You might consider changing your table structure so product_number is your primary key, if it always unique. That way you don't need an additional primary key field. Just a suggestion.
> >> i Start -> Run-> type "cmd" and press enter > >> i then type.... > >> COPY t_temp FROM C:\parts.csv' > > >> and i get the following error: > >> "The system cannnot find the file specified." > > I'll bet doubling the backslash would help. okay, i canned using cmd in windows and i went with the query tool in pgadmin3. i have it so it almost works, but... i have 11 columns total. column 1, 2, 3, 4, 5 and 6 are NOT NULL. the rest can be null and, in the case of the imported data set, are null. how do i manage this in the spreadsheets? i tried leaving the extra cells empty - i received an invalid data type for the first column without data. i tried putting in '' each of the 5 empty columns and i received the following error: ERROR: value too long for type character varying(4) CONTEXT: COPY t_temp, line 1, column test_procedures_revision: "''''''" i'm using tab delimited for the field delimiter. as always, any guidance is much appreciated. __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
> i have 11 columns total. column 1, 2, 3, 4, 5 and 6 > are NOT NULL. the rest can be null and, in the case > of the imported data set, are null. From: http://www.postgresql.org/docs/8.1/interactive/sql-copy.html COPY tablename ( column [, ...] ) FROM 'filename' WITH NULL AS 'null string' ;
OOPS, I forgot something else. --- Richard Broersma Jr <rabroersma@yahoo.com> wrote: > > i have 11 columns total. column 1, 2, 3, 4, 5 and 6 > > are NOT NULL. the rest can be null and, in the case > > of the imported data set, are null. > > From: > http://www.postgresql.org/docs/8.1/interactive/sql-copy.html > > COPY tablename ( column [, ...] ) > > FROM 'filename' > > WITH NULL AS 'null string' DELIMITER AS 'delimiter' CSV QUOTE AS 'quote' FORCE NOT NULL column1, column2, column3, column4, column5, column6 > ; > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend >
> > i have 11 columns total. column 1, 2, 3, 4, 5 and > 6 > > are NOT NULL. the rest can be null and, in the > case > > of the imported data set, are null. > > From: > http://www.postgresql.org/docs/8.1/interactive/sql-copy.html > > COPY tablename ( column [, ...] ) > > FROM 'filename' > > WITH NULL AS 'null string' > ; my code is as follows: COPY t_temp ( columns here... ) FROM 'C:\\Parts.csv' WITH NULL AS 'nuls' ; sorry to drag on here... i know this s/b simple, but... i'm still getting errors. as an example, i had a varchar(4) columns rejected 'null string' as being too long. now, i thought 'null string' represented NULL - which isn't any characters long. i changed it to 'nuls" instead and still got the error (it might've counted six characters ('Nuls'). okay, so i took away the max 4 character restriction... it passed through that column fine, but now i get... ERROR: invalid input syntax for integer: "'Nuls'" CONTEXT: COPY t_temp, line 1, column assembly_instructions_revision: "'Nuls'" again, it seems to want to actually insert 'Nuls' instead of interpret it as NULL value. also notice the error capitalizes Nuls, even though i call it as lowercase. tia... __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
http://www.postgresql.org/docs/8.1/interactive/sql-copy.html > > > > COPY tablename ( column [, ...] ) > > > > FROM 'filename' > > > > WITH NULL AS 'null string' > DELIMITER AS 'delimiter' > > CSV QUOTE AS 'quote' > FORCE NOT NULL column1, column2, column3, column4, > column5, column6 > > > ; the delimiter is the tab, which is the default. i don't think i need to include it. i don't what i'd put in place of 'delimiter' to represent tab, anyhow. i'm not getting CSV QUOTE. should by statement look like: CSV QUOTE AS ''' if i'm using a ' as my quote? tia... __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
> > CSV QUOTE AS 'quote' > > FORCE NOT NULL column1, column2, column3, column4, > > column5, column6 > > > > > ; > > the delimiter is the tab, which is the default. i > don't think i need to include it. i don't what i'd > put in place of 'delimiter' to represent tab, anyhow. > > i'm not getting CSV QUOTE. should by statement look > like: > > CSV QUOTE AS ''' You can open the file in a text editor just to verify how it is stored as a CSV. If the data does not use quotes in the text.file then just drop this argument. Also, if your text file does have quotes, it might help to remove quotes numeric data being pushed to integer datatypes in the temp table. Regards, Richard Broersma Jr.
> okay, so i took away the max 4 character > restriction... it passed through that column fine, > but now i get... > ERROR: invalid input syntax for integer: "'Nuls'" > CONTEXT: COPY t_temp, line 1, column > assembly_instructions_revision: "'Nuls'" > again, it seems to want to actually insert 'Nuls' > instead of interpret it as NULL value. > also notice the error capitalizes Nuls, even though i > call it as lowercase. Can you give an example of a record that has "Nuls" stored in the CVS file? Regards, Richard Broersma Jr.
first off, thanks for the education - i did learn a lot about COPY and its options. my problem was that openoffice.org asks for a text delimiter and offers two choices, ' and ". COPY chokes on both these options (at least in this case). i think using the text delimiter cause nuls to be fed in as text - since it was delimited. in any case, one can over ride the two options by highlighting one or the other (whichever is selected) and then deleting it altogether. voila, no text delimiter, no problem. i had this issue before, but i found it unintuitive to highlight and delete from a drop down box so it didn't register right away. sorry for the confusion and i hope this exercise helps someone else who is importing OOo data into psgsql. thanks again. oe1 __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
> 3. select into destinationtable from temptable where > temptable.primarykey not exists > ( select destinationtable.primarykey > from destination table > ); Richard, thanks for the lead... this worked... select product_number, product_name, product_description, quality_standard, product_entry_date --into t_product from t_temp where not exists ( select product_number from t_product where t_temp.product_number = t_product.product_number ) ; i did have to add a where clause in the subselect to get what i was after. "select into" appears to work only with a new table. iow i can't select into t_product since it already exists. that leaves me needing to combine t_products and t_select_into_output_table. any ideas? ps - maybe select into can work with existing tables, but i don't know how. __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
> Can you give an example of a record that has "Nuls" > stored in the CVS file? > > Regards, > > Richard Broersma Jr. Richard, i'm not sure if you have seen one of my many posts today, but OOo was enclosing everything in ''. while unintuitive, i finally figured out i could delete either of two text delimiter options so that the text wasn't delimited - and the problem went away. thanks for all the help. __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
> Richard, i'm not sure if you have seen one of my many > posts today, but OOo was enclosing everything in ''. > while unintuitive, i finally figured out i could > delete either of two text delimiter options so that > the text wasn't delimited - and the problem went away. Yes that is true. But for the sake of argument, the copy command sould have worked for values enclosed in quotes as well. Perhaps the QUOTE [ AS ] 'quote', is your case the quote was a "'" , possibly you needed to specify QUOTE AS ''''. Maybe quote escaping was required in the copy command. Regards, Richard Broersma Jr.