存储到PostgreSQL TEXT中时有最大长度吗 [英] Is there a maximum length when storing into PostgreSQL TEXT

问题描述

我有一个字符串序列，长度变化很大:从30到282420个字符.我想使用jdbc将每个字符串存储到postgres数据库中.

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 ();

除了slocs以外，其他所有信息均存储在数据库中，该信息包含上述字符串，并存储在边界"列中.请参见下面的pdAdmin屏幕截图.

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.

边界定义为TEXT.如您所见，在大多数情况下，除非存储空间短(通常小于7000个字符)，否则不存储该空间.该代码包含一个print语句，部分输出如下所示:

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

在我发现的postgres手册中

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.

尽管pgAdmin屏幕中仅显示短文本，但似乎存在一定的长度限制.有人知道我在做什么错吗?

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?

编辑1

很抱歉，不清楚.我已经添加到代码示例中，并且包括了程序和pgAdmin的输出，以在表中显示数据.

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.

编辑2

我在示例中添加了executeBatch.它存在于原始代码中，输出不会更改.

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

推荐答案

有一些关于缺少示例的合理说明.在研究示例时，我偶然发现pgAdmin的一些杰出行为:它不显示存在的信息.只需在pgAdmin数据编辑器的输出中选择一些行，该行将显示在原始问题中，然后将其粘贴到编辑器中.然后，它确实显示在pgAdmin的数据显示中不可见的信息，请参见下文.

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...

因此，似乎pgAdmin的行为类似于您没有看到的就是您没有得到的. Java，jdbc和Postgres的行为符合预期，而pgAdmin则没有.

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.

这篇关于存储到PostgreSQL TEXT中时有最大长度吗的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！