PostgreSQL 从 STDIN 表达式复制
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10216891/
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
PostgreSQL COPY FROM STDIN Expressions
提问by elynnaie
I am attempting to use COPY FROM STDIN to import data into my table. One of the columns in my table is of type geometry. My command looks something like this...
我正在尝试使用 COPY FROM STDIN 将数据导入我的表中。我的表中的一列是几何类型。我的命令看起来像这样......
COPY "WeatherStations" ("Station_ID", "Station_Code", "Station_Name", "Station_Location") FROM stdin;
1 KAVP WILKES-BARRE ST_GeomFromText('POINT(41.338055 -75.724166)')
2 KOKV WINCHESTER ST_GeomFromText('POINT(39.143333 -78.144444)')
3 KSHD SHENANDOAH ST_GeomFromText('POINT(38.263611 -78.896388)')
...
However, I think it is attempting to insert the text "ST_GeomFromText('POINT..." and failing instead of evaluating the expression and inserting the result of the expression. Does anyone know what might be going on here and how I can get the actual geoms inserted?
但是，我认为它试图插入文本“ST_GeomFromText('POINT...”并失败而不是评估表达式并插入表达式的结果。有谁知道这里可能发生什么以及我如何获得实际几何插入？
回答by Fulvio
I had a bad time figuring out how to bulk copy/load geometry data into PostGIS using the COPY FROM STDIN command, I couldn't find official documentation on this topic.
我很难弄清楚如何使用 COPY FROM STDIN 命令将几何数据批量复制/加载到 PostGIS，我找不到关于这个主题的官方文档。
Altering the column during the bulk load (the ALTER TABLE / SET DATA TYPE / USING) was not an option to me because it is only supported in PostGIS 2.0+for the Geometry type, nor was acceptable the use of a temporary table.
在批量加载期间更改列（ALTER TABLE / SET DATA TYPE / USING）对我来说不是一个选项，因为它仅在 PostGIS 2.0+ 中支持Geometry 类型，也不可接受使用临时表。
There is indeed a direct way to do it (at least in PostGIS 1.5.2+). You can simply rewrite the data for your copy statement this way, using a simple WKT (Well-known text) representation for your Geometry data:
确实有一种直接的方法可以做到（至少在 PostGIS 1.5.2+ 中）。您可以通过这种方式简单地重写复制语句的数据，对几何数据使用简单的 WKT（众所周知的文本）表示：
1 KAVP WILKES-BARRE POINT(41.338055 -75.724166)
2 KOKV WINCHESTER POINT(39.143333 -78.144444)
3 KSHD SHENANDOAH POINT(38.263611 -78.896388)
If you have enforced a SRID constraint on the geometry column you'll have to use the following syntax (in this example the SRID is 4326) known as EWKT (Extended Well-Known Text, which is a PostGIS specific format):
如果您已对几何列强制执行 SRID 约束，则必须使用以下语法（在此示例中，SRID 为 4326）称为 EWKT（扩展众所周知的文本，它是PostGIS 特定格式）：
1 KAVP WILKES-BARRE SRID=4326;POINT(41.338055 -75.724166)
2 KOKV WINCHESTER SRID=4326;POINT(39.143333 -78.144444)
3 KSHD SHENANDOAH SRID=4326;POINT(38.263611 -78.896388)
Closing note: there must be no space between "POINT" and the opening parenthesis "(", or the COPY will still return error saying your geometry data has an invalid format.
结束语：“POINT”和左括号“(”之间不能有空格，否则 COPY 仍会返回错误，指出您的几何数据格式无效。
回答by kgrittn
You could omit the function wrapping the text, import into a temporary table with text column, and then run INSERT/SELECT into the permanent table with the function doing the conversion in that step.
您可以省略包装文本的函数，导入带有文本列的临时表，然后在该步骤中执行转换的函数在永久表中运行 INSERT/SELECT。
INSERT INTO "WeatherStations"
("Station_ID", "Station_Code", "Station_Name", "Station_Location")
SELECT "Station_ID", "Station_Code", "Station_Name",
ST_GeomFromText("Station_Location")
FROM "TempWeatherStations";
回答by vyegorov
Point's value looks something like this: 0101000020E6100000DA722EC555552B40CDCCCCCCCC0C4840.
Point的价值看起来是这样的：0101000020E6100000DA722EC555552B40CDCCCCCCCC0C4840。
I typically keep latitudeand longitudecolumns in my tables and build spatial data with triggers.
我通常在我的表中保留latitude和longitude列并使用触发器构建空间数据。
I don't know how to copy POINTs from stdin otherwise.
POINT否则我不知道如何从 stdin复制s 。
回答by Dhananjayan K
You will keep all the values in .csvfile and try like this:
您将保留.csv文件中的所有值并尝试如下操作：
CAT /path/file/demo.csv | psql -u <username> -h <localhost> -d<database>
-c "COPY "WeatherStations" ("Station_ID", "Station_Code", "Station_Name",
"Station_Location") FROM stdin;"
This will work.
这将起作用。
