sql - 让 PostgreSQL 更容错一点？

Question

这是在多种情况下出现的一般性问题，下面的示例具有代表性但并不详尽。我对任何学习在不完美(但足够接近)数据源上使用 Postgres 的方法都很感兴趣。

具体情况——我将 Postgres 与 PostGIS 结合使用，以处理以 shapefile 和 xml 形式发布的政府数据。使用与 PostGIS 一起分发的 shp2pgsql 模块(例如在 this 数据集上)我经常得到这样的模式:

   Column   |         Type          | 
------------+-----------------------+-
 gid        | integer               |
 st_fips    | character varying(7)  | 
 sfips      | character varying(5)  | 
 county_fip | character varying(12) | 
 cfips      | character varying(6)  | 
 pl_fips    | character varying(7)  | 
 id         | character varying(7)  | 
 elevation  | character varying(11) | 
 pop_1990   | integer               | 
 population | character varying(12) | 
 name       | character varying(32) | 
 st         | character varying(12) | 
 state      | character varying(16) | 
 warngenlev | character varying(13) | 
 warngentyp | character varying(13) | 
 watch_warn | character varying(14) | 
 zwatch_war | bigint                | 
 prog_disc  | bigint                | 
 zprog_disc | bigint                | 
 comboflag  | bigint                | 
 land_water | character varying(13) | 
 recnum     | integer               | 
 lon        | numeric               | 
 lat        | numeric               | 
 the_geom   | geometry              |

我知道这些 varchar 中至少有 10 个——fips、elevation、population 等应该是 int；但是当我试图这样转换它们时，我得到了错误。总的来说，我认为我可以通过允许 Postgres 在更改列和更改类型时接受空字符串作为列的默认值来解决我的大部分问题——比如 0 或 -1 用于 int 类型。这可能吗？

如果我在导入之前使用从原始数据源生成的类型声明创建表，我会得到比 shp2pgsql 更好的类型，并且可以遍历将它们提供给数据库的源条目，丢弃任何失败的插入。根本问题是，如果我有 1% 的坏字段，均匀分布在 25 列上，我将丢失 25% 的数据，因为如果任何字段坏，给定的插入都会失败。我希望能够尽最大努力插入并在以后解决任何问题，而不是丢失那么多行。

任何处理过类似问题的人的意见都是受欢迎的——我不是一个 MySQL 的人试图打击 PostgreSQL 犯我习惯的所有相同的错误——只是处理我无法完全控制的数据.

Answer 1

您能否从 shp2pgsql 生成一个 SQL 文件并在执行之前对数据进行一些处理？如果数据是 COPY 格式，应该很容易解析并将列的“”更改为“\N”(插入为空)。

另一种可能性是使用 shp2pgsql 将数据加载到临时表中，其中所有字段都定义为“文本”类型，然后使用 INSERT...SELECT 语句将数据复制到您的最终位置，可以在 SELECT 中处理数据以将空白字符串转换为 null 等。

我不认为有一种方法可以覆盖字符串如何转换为整数等的行为:也许你可以创建自己的类型或域，并定义一个更宽松的隐式转换......但是这个听起来很讨厌，因为类型实际上只是您的数据如何到达系统的产物，而不是您希望在之后保留的东西。

您询问了在更改列类型时是否修复它:您也可以这样做，例如:

steve@steve@[local] =# create table test_table(id serial primary key, testvalue text not null);
NOTICE:  CREATE TABLE will create implicit sequence "test_table_id_seq" for serial column "test_table.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_table_pkey" for table "test_table"
CREATE TABLE
steve@steve@[local] =# insert into test_table(testvalue) values('1'),('0'),('');
INSERT 0 3
steve@steve@[local] =# alter table test_table alter column testvalue type int using case testvalue when '' then 0 else testvalue::int end;
ALTER TABLE
steve@steve@[local] =# select * from test_table;
 id | testvalue
----+-----------
  1 |         1
  2 |         0
  3 |         0
(3 rows)

这几乎等同于我上面建议的“登台表”想法，只是现在登台表是您的最终表。像这样更改列类型无论如何都需要重写整个表:因此实际上，使用暂存表并一次重新格式化多个列可能更有效。