Is there a maximum length when storing into Postgr

问题:

I have a sequence of string that vary a lot in length: from 30 to 282420 chars . I want to store each string into a postgres database using jdbc.

     String query = "INSERT INTO " + tableName + 
                       " (cbsID, ownerID, naam, soortRegio, jaar, centroidLat, centroidLon, borders) " + 
                       " VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
     PreparedStatement ps = connection.prepareStatement(query); 
     for (Marker marker: gemeenteMarkers)
     {
        Area gemeente = (Area) marker.getProperty("area");
        String sloc = "";

        // ... do some computations
        // Compute sloc, a string that may contain over 250.000 characters
        ps.setInt (1, gemeente.getAreaId ());
        ps.setInt (2, gemeente.getParentId ());
        ps.setString (3, gemeente.getAreaName ());
        ps.setString (4, "GM");
        ps.setInt (5, 2014);
        ps.setFloat (6, gemeente.getCentroid ().getLat ());
        ps.setFloat (7, gemeente.getCentroid ().getLon ());
        ps.setString (8, slocs);
        System.out.printf ("%s - %d locations, len = %d\n", gemeente.getAreaName (), locs.size (), slocs.length ());

        ps.addBatch ();
     } // for
     int [] affected = ps.executeBatch ();

The information is stored in the database all right except for slocs, which contains the mentioned string and is stored in the column `borders. See the screenshot of pdAdmin below.

Borders is defined as TEXT. As you can see in most cases it is not stored, except when it is short, typical smaller than 7000 chars. The code contains a printstatement and a part of the output is shown below:

Appingedam - 1649 locations, len = 32980
Bedum - 1210 locations, len = 24200
Bellingwedde - 1500 locations, len = 30000
Ten Boer - 1186 locations, len = 23720
Delfzijl - 16 locations, len = 320
Groningen - 2662 locations, len = 53240
Grootegast - 4843 locations, len = 96860
Haren - 1940 locations, len = 38800
Hoogezand-Sappemeer - 1481 locations, len = 29620
Leek - 2575 locations, len = 51500
Loppersum - 2991 locations, len = 59820
Marum - 1936 locations, len = 38720
Almere - 213 locations, len = 4260
Stadskanaal - 2701 locations, len = 54020
Slochteren - 1555 locations, len = 31100
Veendam - 1098 locations, len = 21960
Vlagtwedde - 2621 locations, len = 52420
Zeewolde - 25 locations, len = 500
Winsum - 2992 locations, len = 59840
Zuidhorn - 5282 locations, len = 105640
Dongeradeel - 256 locations, len = 5120
Achtkarspelen - 4644 locations, len = 92880
Ameland - 158 locations, len = 3160
het Bildt - 2337 locations, len = 46740
Franekeradeel - 50 locations, len = 1000
Harlingen - 50 locations, len = 1000
Heerenveen - 5195 locations, len = 103900

In the postgres manual I found

If you desire to store long strings with no specific upper limit, use text or character varying without a length specifier, rather than making up an arbitrary length limit.

There appears to be some length limit though as only the short texts show up in the pgAdmin screen. Does anyone have any idea what I am doing wrong?

Edit 1

Sorry for being not clear. I have added to the code example and I have included output from the program and from pgAdmin to show the data in the table.

Edit 2

I have added an executeBatch to the example. It was present in the original code, the output does not change.

回答1:

According to the Postgres character type documentation, text is variable unlimited length.
And further down:

... PostgreSQL provides the text type, which stores strings of any length.

However

According to the Postgres Wiki, there is a hard limit of 1GB for a single column value, so the practical limit for the "unlimited" length text value is limited to 1GB by the architecture.

回答2:

There were some justified remarks about the lack of examples. While playing with the examples I stumbled upon some remarkable behavior of pgAdmin: it does not display the information which is present. Just select some rows in the output from the pgAdmin data editor which is shown in the original question and paste it in an editor. It then does display the information which is not visible in the data display of pgAdmin, see below.

3;0;"Appingedam";"GM";2014;53.3172;6.84957;"53.345131 6.885408  53.344925 6.885422  ...
5;0;"Bedum";"GM";2014;53.2949;6.59717;"53.255638 6.587573  53.255646 6.587514  53.25...
7;0;"Bellingwedde";"GM";2014;53.0986;7.12118;"53.097076 7.038233  53.097099 7.038254...
9;0;"Ten Boer";"GM";2014;53.2788;6.69145;"53.312183 6.740470  53.312176 6.740486  53...
10;0;"Delfzijl";"GM";2014;53.3227;7.09945;"53.322491 7.100943  53.322414 7.100940  5...
14;0;"Groningen";"GM";2014;53.2222;6.5634;"53.198223 6.515755  53.198238 6.515728  5...
15;0;"Grootegast";"GM";2014;53.2086;6.27322;"53.203838 6.318381  53.203819 6.318180 ...
17;0;"Haren";"GM";2014;53.1545;6.63106;"53.165165 6.560897  53.165207 6.560884  53.1...
18;0;"Hoogezand-Sappemeer";"GM";2014;53.1473;6.7468;"53.116516 6.743022  53.116371 6...
22;0;"Leek";"GM";2014;53.1633;6.36926;"53.211250 6.362241  53.211262 6.362327  53.21...

So it seems that pgAdmin behaves like what you don't see is what you don't get. Java, jdbc and Postgres behave as expected, pgAdmin does not.