存储到 PostgreSQL TEXT 时是否有最大长度
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39965834/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is there a maximum length when storing into PostgreSQL TEXT
提问by Arnold
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.
我有一个长度变化很大的字符串序列：从 30 到 282420 个字符。我想使用 jdbc 将每个字符串存储到 postgres 数据库中。
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.
信息存储在数据库中slocs，除了，它包含提到的字符串并存储在列`borders。请参阅下面的 pdAdmin 屏幕截图。
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:
边框被定义为文本。正如您所看到的，在大多数情况下它不会被存储，除非它很短，通常小于 7000 个字符。代码包含一条print语句，部分输出如下所示：
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
在 postgres 手册中我发现
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?
尽管 pgAdmin 屏幕中只显示短文本，但似乎存在一些长度限制。有谁知道我做错了什么？
Edit 1
编辑 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.
抱歉不清楚。我已添加到代码示例中，并包含了程序和 pgAdmin 的输出以显示表中的数据。
Edit 2
编辑 2
I have added an executeBatchto the example. It was present in the original code, the output does not change.
我executeBatch在示例中添加了一个。它存在于原始代码中，输出不会改变。
采纳答案by Arnold
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 doesdisplay the information which is not visible in the data display of pgAdmin, see below.
有一些关于缺乏示例的合理评论。在玩这些例子时，我偶然发现了 pgAdmin 的一些显着行为：它不显示存在的信息。只需在原始问题中显示的 pgAdmin 数据编辑器的输出中选择一些行并将其粘贴到编辑器中。然后它确实显示在 pgAdmin 的数据显示中不可见的信息，见下文。
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.
所以看起来 pgAdmin 的行为就像你看不到的东西一样。Java、jdbc 和 Postgres 的行为符合预期，而 pgAdmin 则不然。
回答by Bohemian
According to the Postgres character type documentation, textis variable unlimited length.
And further down:
根据Postgres 字符类型文档，text是variable unlimited length.
再往下：
... PostgreSQL provides the
texttype, which stores strings of any length.
... PostgreSQL 提供了
text存储任意长度字符串的类型。
However
然而
According to the Postgres Wiki, there is a hard limit of 1GB for a single column value, so the practicallimit for the "unlimited" length textvalue is limited to 1GB by the architecture.
根据Postgres Wiki，单个列值有 1GB 的硬限制，因此“无限”长度值的实际限制text被架构限制为 1GB。
