代码之家 › 专栏 › 技术社区 › wfgeo

psqlcopy命令无法正确解释空值

psql postgresql

0

wfgeo · 技术社区 · 8 年前

this answer 它说要指定空值,但它似乎对我没有影响。这就是我所拥有的:

CREATE TABLE stops
(
  stop_id           text PRIMARY KEY,
  --stop_code         text NULL,
  stop_name         text NOT NULL,
  --stop_desc         text NULL,
  stop_lat          double precision NOT NULL,
  stop_lon          double precision NOT NULL,
  zone_id           integer NULL,
  stop_url          text NULL,
  location_type     boolean NULL,
  parent_station    text NULL
);

\copy stops from './stops.txt' with csv header NULL AS ''

我也试过用 \N 这样的性格:

\copy stops from './stops.txt' with csv header NULL AS '\N'

但似乎没有效果。

我还尝试了一种解决方案 here

\copy agency from './agency.txt' WITH (FORMAT csv header, FORCE_NULL(zone_id))

csv_header 部分。

版本是9.6。

stop_id,stop_name,stop_lat,stop_lon,zone_id,stop_url,location_type,parent_station
"de:07334:1714:1:1","WÃ¶rth Alte Bahnmeisterei","49.048742345982","8.26622538039577","","","","Parent1714"
"de:07334:1714:1:2","WÃ¶rth Alte Bahnmeisterei","49.0484420719247","8.26673742010779","","","","Parent1714"
"de:07334:1721:1:1","Maximiliansau EisenbahnstraÃe","49.0373071007148","8.29789997731824","","","","Parent1721"
"de:07334:1721:2:2","Maximiliansau EisenbahnstraÃe","49.0371363175998","8.29896897250649","","","","Parent1721"

1 回复 | 直到 8 年前

1

Matt 8 年前

但这似乎在csv\u头部分抛出了一个语法错误。

在csv后加逗号:

\copy agency from './agency.txt' WITH (FORMAT csv, HEADER, FORCE_NULL(zone_id,location_type))

显然 FORCE_NULL 当使用空字符串指定NULL时,非文本列需要。