2

Background: I have a database of significant geographical locations downloaded from GeoNames open database. The create script for the table is as follows

CREATE TABLE populargeonameswiki (
geonameid integer NOT NULL,
name character varying(200),
asciiname character varying(200),
alternatenames text,
latitude double precision,
longitude double precision,
feature_class character(1),
feature_code character varying(10),
country_code character(2),
alternate_country_names character(200),
admin1 character varying(20),
admin2 character varying(80),
admin3 character varying(20),
admin4 character varying(20),
population bigint,
elevation integer,
digital_elevation_model integer,
timezone character varying(40),
modify_date character varying(50),
wikiid bigint,
popularity90days bigint,
flag60 integer
);

I'm using Postgres 9.4 with Pgadmin III on my local machine. Everything works great till here.

Problem: Now, I'm trying to export this table so that it can be imported by others. I have a couple of options to do this. I can export it as a self-contained SQL or I can export the schema and then export data as a CSV or TSV separately. I'll get into this later. The core of the problem is the alternatenames field in the table. As some of my data contains non-English characters I have used UTF-8 encoding. alternatenames field is text as I did not know the length of the field before importing the data.

When I try to export the data as a self-contained SQL file, the insert statements in the SQL file break when it comes to a record containing a huge entry for alternatenames (about 12 million characters). I understand that this is because non-English characters take more then one "character space" Here's an example of the insert statements block from the generated SQL file.

INSERT INTO populargeonameswiki VALUES (5117143, 'Fir Mountain', 'Fir Mountain', NULL, 42.027589999999996, -74.469040000000007, 'T', 'MT', 'US', NULL, 'NY', '111', NULL, NULL, 0, 1106, 1108, 'America/New_York', '2006-01-15', 15977095, 1, NULL);
INSERT INTO populargeonameswiki VALUES (5117152, 'Otis Pike Fire Island High Dune Wilderness', 'Otis Pike Fire Island High Dune Wilderness', NULL, 40.711770000000001, -72.928439999999995, 'L', 'RES', 'US', NULL, 'NY', '103', NULL, NULL, 0, 4, 4, 'America/New_York', '2010-02-20', 22065104, 1036, NULL);
INSERT INTO populargeonameswiki VALUES (5018756, 'Blue Earth County', 'Blue Earth County', 'Blju-Ert,Blu rt,Blu-Ehrt,Blue Earth Comitatus,Blue Earthin piirikunta,Comte de Blue Earth,Comté de Blue Earth,Condado de Blue Earth,Condado han Blue Earth,Contea di Blue Earth,Hrabstwo Blue Earth,Okrug Blu Ert,Quan Blue Earth,Quận Blue Earth,blu artha ka''unti,bu lu e si xian,buruasu jun,Блу Ърт,Блу-Эрт,Блю-Ерт,Округ Блу Ерт,Բլու Էրթ շրջան,بلو ارتھ کاؤنٹی,مقاطعة بلو إرث، مينيسوتا,ব্লু আর্থ কাউন্টি,ブルーアース郡,布盧厄斯縣  44.03459    -94.06703   A   ADM2    US      MN  013         64013   305 307 America/Chicago 2013-03-07
5018757 Blue Earth County Courthouse    Blue Earth County Courthouse        44.16386    -93.9994    S   BLDG    US      MN  013         0   251 254 America/Chicago 2006-01-15

The first two statements work fine and are exported cleanly. But it seems like during exporting the third one, postgres just gives up formatting the insert statement which makes it overflow into the next records which can be seen as the record containing 5018757 Blue Earth County Courthouse... in this case. It doesn't just overflow into one record but thousands of them. The fun (not really) part of all this is that this record shows up great when I query the database for it. It just truncates the alternatenames entry to a limited number of characters and appends (...) to it so as to say to be continued

5018756;"Blue Earth County";"Blue Earth County";"Blju-Ert,Blu rt,Blu-Ehrt,Blue Earth Comitatus,Blue Earthin piirikunta,Comte de Blue Earth,Comté de Blue Earth,Condado de Blue Earth,Condado han Blue Earth,Contea di Blue Earth,Hrabstwo Blue Earth,Okrug Blu Ert,Quan Blue Earth,Quận Blue Earth,blu artha ka'u (...)";40.74871;-73.98597;"S";"BLDG";"US";"";"NY";"061";"";"";0;15;58;"America/New_York";"2014-07-28";94995;128;

Fun fact: The length of the field is 12084003. I found that out by running the query

select length(alternatenames) from popularity.populargeonameswiki where geonameid = 5018756

What I've looked into and possible solutions

  • The ultimate bypass solution is to export the schema separately and the data separately as a CSV or a TSV which "shouldn't" cause anything to break. I have tried this and unfortunately it does break while exporting as a csv too. It breaks on the same record where the SQL export breaks. Here's a snippet from the CSV where it breaks (delimiter ; quotes for strings ") I've tried with multiple delimiters and with/without quotes. Breaks every time.

    5018756;"Blue Earth County";"Blue Earth County";"Blju-Ert,Blu rt,Blu-Ehrt,Blue Earth Comitatus,Blue Earthin piirikunta,Comte de Blue Earth,Comté de Blue Earth,Condado de Blue Earth,Condado han Blue Earth,Contea di Blue Earth,Hrabstwo Blue Earth,Okrug Blu Ert,Quan Blue Earth,Quận Blue Earth,blu artha ka'unti,bu lu e si xian,buruasu jun,Блу Ърт,Блу-Эрт,Блю-Ерт,Округ Блу Ерт,Բլու Էրթ շրջան,بلو ارتھ کاؤنٹی,مقاطعة بلو إرث، مينيسوتا,ব্লু আর্থ কাউন্টি,ブルーアース郡,布盧厄斯縣 44.03459 -94.06703 A ADM2 US MN 013 64013 305 307 America/Chicago 2013-03-07

  • Ignore the records where this breaks and export everything else I can't do this as all entries are very important and loss of even one of them decreases my recall which I don't want to do

  • I do not know if the datatype text is causing this issue. Please let me know if it is and what can done about it.

  • I also don't know if there's a hard limit on the number of characters that can be present on one line of a file. Even if there is such a limit, I don't know if that is what is causing the problem.

What I'm Looking for I'm looking for one of two things

  • A way to make this solution work as it is

  • A way to create a dump (it can be a self-contained SQL, a combo of schema and CSV or something else) of my table from postgres without any losses which can be imported easily (without using any additional scripts) on another machine hosting postgres

  • SIDEQUEST: Why is this happening in the first place? If a solution cannot be found from vanilla methods, is this a shortcoming (or a bug) in postgres? And does this work in other RDBMSs?

  • I got the answer. Ill post it in a few hours – Shreyas Chavan Jul 28 '15 at 0:47
  • The truncation of the value is not done by Postgres, but by pgAdmin (the client application). Other SQL clients might be better at this. I don't use pgAdmin, but I recall other questions regarding this, and I think there is an option somewhere that limits the amount of data displayed to the user. pg_dump can create a valid plain SQL dump for this. – a_horse_with_no_name Jul 28 '15 at 6:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.