liquibase - 使用 Postgres 从 CSV 文件加载数据

Question

我会很感激一些建议。我正在完成一个表的简单加载，其中包含来自 CSV 文件的五个记录。当我加载表格时，出现以下错误:

liquibase.exception.DatabaseException: org.postgresql.util.PSQLException: ERROR: zero-length delimited identifier at or near """"

这是 csv 文件

1,Nate Happy,natehappy1761@me.com,1761 Brookview Trail,(205) 555-1212
2,Brigette Happy,brigettehappy7507@me.com,7507 Meadowgate Lane,(704) 555-1212
3,Katie Happy,katiehappy7507@me.com,7507 Meadowgate Lane,(704) 555-1212
4,Lauren Happy,laurenhappy@me.com,7507 Meadowgate Lane,(704) 555-1212
5,Jackson Hope,jacksonhope@me.com,7507 Meadowgate Lane,(704) 555-1212

这是我加载数据的变更集

<changeSet id="6-loadData" author="liquibase" dbms="postgresql" >
<preConditions onErrorMessage="Failed Pre Conditions for table" onFail="HALT"> 
      <and>
      <tableExists schemaName="public" tableName="contact" />
      <sqlCheck expectedResult ="1">SELECT COUNT(*) contact</sqlCheck>
      </and>
</preConditions>
<comment>Adding Data...</comment>
   <loadUpdateData catalogName="pg_catalog"
        encoding="UTF-8"
        file="src/main/resources/data/contacts.csv"
        primaryKey="contact_id"
        quotchar="A String"
        schemaName="public"
        separator=","
        tableName="contact">
      <column name="contact_id" type="int" />
      <column name="contact_name" type="varchar(45)"/>
      <column name="email" type="varchar(45)" />
      <column name="address" type="varchar(45)" />
      <column name="telephone" type="varchar(45)" />
   </loadUpdateData>

这是我创建表的变更集:

   <changeSet id="4 Create Table" author="liquibase" runAlways="true">
   <preConditions onErrorMessage="Failed Pre Conditions for table" onFail="MARK_RAN"> 
       <not><tableExists schemaName="public" tableName="contact"/> </not>
   </preConditions>
   <comment>Creating Table named: Contact...</comment>
   <createTable tableName="contact" schemaName="public">
      <column name="contact_id" type="int" >
      <constraints primaryKey="true" nullable="false"/>
      </column>
      <column name="contact_name" type="varchar(45)">
         <constraints nullable="false"/>
      </column>
      <column name="email" type="varchar(45)">
         <constraints nullable="false"/>
      </column>
      <column name="address" type="varchar(45)">
         <constraints nullable="false"/>
      </column>
      <column name="telephone" type="varchar(45)">
         <constraints nullable="false"/>
      </column>
   </createTable>

这是我用于主键 (contact_id) 的序列

   <changeSet id="2-Create Sequence" author="liquibase" runAlways="true">
   <preConditions onErrorMessage="Failed Pre Conditions for sequence" onFail="MARK_RAN"> 
      <not><sequenceExists schemaName="public" sequenceName="contactid_seq" /></not>
    </preConditions>
   <comment>Creating Sequence...</comment>
   <createSequence sequenceName="contactid_seq"
                    incrementBy="1"
                    minValue="1"
                    maxValue="9223372036854775807"
                    startValue="1"
                    ordered="1"
                    schemaName="public"/>

这就是我使用约束的方式:

   <changeSet id="5-Add Constraint" author="liquibase">
     <comment>Adding contactid_seq sequence to Contact table...</comment> 
     <addDefaultValue catalogName="pg_catalog"
        columnDataType="int"
        columnName="contact_id"
        tableName="contact"
        schemaName="public"
        defaultValueSequenceNext="contactid_seq" />

感谢您花时间阅读我的帖子。

拉斯

Answer 1

我发现 CSV 文件缺少 HEADER 列名称。 LoadUpdateData 的列元素使用特定的 Java 类型，例如 String 而不是 VARCHAR(45) 和 NUMERIC 而不是“int”。一旦我纠正了这两个错误，我就成功了。