Re: [GENERAL] Copy From csv file with double quotes as null
Hi All, I am apparently totally misreading how to import data using the COPY FROM command, can someone give assistance ? I have two issues, both dealing with double quotes as NULL. The data is CSV with NULL being represented by a double quote (e.g. "") in all columns of the table. ISSUE A) The following command bombs: COPY testdata FROM 'c:/temp/test.csv' CSV HEADER; with the following error: ERROR: invalid input syntax for type double precision: "" CONTEXT: COPY testdata, line 7, column latitude: "" ** Error ** ERROR: invalid input syntax for type double precision: "" SQL state: 22P02 Context: COPY testdata, line 7, column latitude: "" So, latitude is a double precision column and I think that PostgreSQL is interpreting the double quote as a NULL string and then it can not be placed into that column because it is a double precision column. Issue B) I have an associated issue with a text value where the NULL in the data being represented by a double quote (e.g. "") is being inputed as a quote. I can not use the switch NULL AS '"' because PostgreSQL says "the quote character must not appear in the NULL specification" Given the file sizes are huge, I would rather not have to try to preprocess the data. Is there anyway the COPY FROM command can handle this data smoothly ? -- -Don Don Catanzaro, PhD Landscape Ecologist dgcatanz...@gmail.com 16144 Sigmond Lane Lowell, AR 72745 479-751-3616
Re: [GENERAL] Moving postgresql data to another computer
Hi All, Well, needless to say I naively copied the data over. First off I did not stop the database server (thus violating http://www.postgresql.org/docs/8.4/interactive/backup-file.html) and I am not near my desktop. Does that mean my copy is in essence useless and I have to start over ? -Don Richard Broersma wrote: On Tue, May 11, 2010 at 1:16 PM, Donald Catanzaro, PhD wrote: I am a newbie to PostgreSQL and I recently installed it on my desktop (Windows XP) I created a database fine and worked a bunch of queries. I am traveling now and I needed access to my database on my laptop (Windows Vista). So before I left, I installed PosgreSQL on the laptop and then copied the data in the PostgreSQL data directory over to my laptop. I put the data directory from my desktop in a different place on my laptop and now I would like to access the data but I can not seem to figure out how to do this. The easiest (and therefore safest) way to tranfer the databases from one server cluster to another is to dump the entire contents of the database to a text file and then load the text file onto the second server instance. This methods works as long as the second server instance postgresql version >= to the initial instance. Its best if both versions are the same if you want to transfer the data in both directions. http://www.postgresql.org/docs/8.4/interactive/backup-dump.html http://www.postgresql.org/docs/8.4/interactive/app-pg-dumpall.html http://www.postgresql.org/docs/8.4/interactive/app-pgrestore.html I naively tried to just replace all the files in the data directory on my laptop with the files from the data directory of the desktop but when I do so, I get the following error: "could not connect to server: Connection refused (0x274D/10061) Is the server running on host "127.0.0.1" and accepting TCP/IP connections on port 5432? " Transferring the actual binary files is more risky since much more care is needed to insure a consistent and functional copy. Notice the method of creating a consistent copy: http://www.postgresql.org/docs/8.4/interactive/backup-file.html If you have a consistent copy, you might need to point PostgreSQL to work from the copied data directory. http://www.postgresql.org/docs/8.4/interactive/server-start.html -- -Don Don Catanzaro, PhD Landscape Ecologist dgcatanz...@gmail.com 16144 Sigmond Lane Lowell, AR 72745 479-751-3616 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Moving postgresql data to another computer
Good Day All, I am a newbie to PostgreSQL and I recently installed it on my desktop (Windows XP) I created a database fine and worked a bunch of queries. I am traveling now and I needed access to my database on my laptop (Windows Vista). So before I left, I installed PosgreSQL on the laptop and then copied the data in the PostgreSQL data directory over to my laptop. I put the data directory from my desktop in a different place on my laptop and now I would like to access the data but I can not seem to figure out how to do this. I naively tried to just replace all the files in the data directory on my laptop with the files from the data directory of the desktop but when I do so, I get the following error: "could not connect to server: Connection refused (0x274D/10061) Is the server running on host "127.0.0.1" and accepting TCP/IP connections on port 5432? " I figure I have at least two options Option 1) Point PostgreSQL on laptop to the directory that desktop data lives in Option 2) Replace the data directory on the laptop with the data directory that came from the desktop. While I am travelling now (about a month) I eventually will want to move my database BACK to the desktop so a solution that makes that transition simple would be ideal. I've tried searching the documentation, archives, and the FAQs but to no avail. I either found it and did not understand how to do it or I could not find it properly. Could someone point me in the correct direction ? Thanks in advance ! -Don -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
