Thread: Importing/Appending to Existing Table
I have an existing table with 15,492 rows and want to add additional rows from a .csv file. If I use 'COPY <tablename> from <filename> with delimiter as ":" csv quote as "'" ' will this overwrite existing rows in the table or append rows? Rich
On 2 February 2011 03:28, Rich Shepard <rshepard@appl-ecosys.com> wrote: > I have an existing table with 15,492 rows and want to add additional rows > from a .csv file. If I use 'COPY <tablename> from <filename> with delimiter > as ":" csv quote as "'" ' will this overwrite existing rows in the table or > append rows? No it wont overwrite, it will append rows. > > Rich > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sergey Konoplev Blog: http://gray-hemp.blogspot.com / Linkedin: http://ru.linkedin.com/in/grayhemp / JID/GTalk: gray.ru@gmail.com / Skype: gray-hemp
Rich -- > > > I have an existing table with 15,492 rows and want to add additional rows > from a .csv file. If I use 'COPY <tablename> from <filename> with delimiter > as ":" csv quote as "'" ' will this overwrite existing rows in the table or > append rows? It will not overwrite any existing data; if the table has constraints that will prevent duplicates then the entire load will fail if any item fails (it is a single transaction). If the table doesn't have such constraints then duplicate data in the copy file will result in duplicate rows. HTH, Greg Williamson
On Wed, 2 Feb 2011, Sergey Konoplev wrote: > No it wont overwrite, it will append rows. Thank you, Sergey. Rich
On Wed, 2 Feb 2011, Greg Williamson wrote: > It will not overwrite any existing data; if the table has constraints that > will prevent duplicates then the entire load will fail if any item fails > (it is a single transaction). > > If the table doesn't have such constraints then duplicate data in the copy file > will result in duplicate rows. Greg, These data originate in highly undisciplined Excel spreadsheets over the past 10+ years. They need a lot of preprossing in emacs and python scripts I've written but there are no primary keys or other constraints until all the data are entered then moved to the actual application tables. I will not be surprised to discover duplicate data because of data-entry errors, but there should not be any duplicates in these permit compliance monitoring results. Thanks, Rich
You also don't have to import the source files directly into the live table. Instead you could create a "staging" table that has no constraints where you can import everything, do some review and updates, then merge that table over to the live one. Depending on how many files you are dealing with and whether you want or need to pre-combine them a more forgiving staging table may come in quite handy. Especially consider the fact that you likely will not know the maximum length used for various "text" fields you can make the staging table fields varchar(255) (or even text) and then do some length checks before importing the data into the accurately sized live table. Dave -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Rich Shepard Sent: Wednesday, February 02, 2011 9:30 AM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Importing/Appending to Existing Table On Wed, 2 Feb 2011, Greg Williamson wrote: > It will not overwrite any existing data; if the table has constraints > that will prevent duplicates then the entire load will fail if any > item fails (it is a single transaction). > > If the table doesn't have such constraints then duplicate data in the > copy file will result in duplicate rows. Greg, These data originate in highly undisciplined Excel spreadsheets over the past 10+ years. They need a lot of preprossing in emacs and python scripts I've written but there are no primary keys or other constraints until all the data are entered then moved to the actual application tables. I will not be surprised to discover duplicate data because of data-entry errors, but there should not be any duplicates in these permit compliance monitoring results. Thanks, Rich -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general