我在PostgreSQL数据库中创建了一个包含42列的表。其中5例为bigint型，其余为character varying型。然后我尝试使用一些java代码和spring框架中的jdbc库来插入数据。
我遇到了一些需要定位的错误，这是很难的，因为表包含了太多的列。
jdbc中插入该表的语法：
String sql = "insert into t_reconcile_stage values("
+ " ?::bigint," // 1 id
+ "?::bigint," // 2 m_participant_id
+ "?::bigint," // 3 m_status_data_id
+ "?," // 4 reconcile_file
+ "?::date," // 5 reconcile_date
+ "?," // 6 transaction_date
+ "?," // 7 transaction_time
+ "?::bigint," // 8 stan
+ "?," // 9 rrn
+ "?," // 10 merchant_type
+ "?," // 11 terminal_id
+ "?," // 12 pan
+ "?," // 13 debit_account
+ "?," // 14 credit_account
+ "?::bigint," // 15 amount
+ "?," // 16 customer_id
+ "?," // 17 participant_reference
+ "?," // 18 data_key
+ "?," // 19 dealer_code
+ "?," // 20 biller_code
+ "?," // 21 product_code
+ "?," // 22 feature_code
+ "?::bigint," // 23 acquire_fee
+ "?::bigint," // 24 issuer_fee
+ "?::bigint," // 25 biller_fee
+ "?::bigint," // 26 switching_fee
+ "?::bigint," // 27 merchant_fee
+ "?," // 28 transaction_type
+ "?," // 29 trfree
+ "?," // 30 free_data1
+ "?," // 31 free_data2
+ "?," // 32 free_data3
+ "?," // 33 free_data4
+ "?," // 34 free_data5
+ "?," // 35 free_data6
+ "?," // 36 free_data7
+ "?," // 37 free_data8
+ "?," // 38 free_data9
+ "?," // 39 free_data10
+ "?::bigint," // 40 settlement_amount
+ "?::bigint," // 41 charge_amount
+ "?" // 42 status
+ ")"; //
simpleJdbcTemplate.update( sql,
d[0],d[1],d[2],d[3],d4,
d[5],d[6],d[7],d[8],d[9],
d[10],d[11],d[12],d[13],d[14],
d[15],d[16],d[17],d[18],d[19],
d[20],d[21],d[22],d[23],d[24],
d[25],d[26],d[27],
d[28],d[29],d[30],d[31],d[32],d[33],d[34],d[35],d[36],d[37],d[38],
d[39],d[40],d[41]
);
我得到的错误是：
[2014-08-06 10:48:01,753] [ INFO] - org.springframework.jdbc.support.SQLErrorCodesFactory - SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase]
[2014-08-06 10:48:01,769] [ERROR] - dwidasa.reconcile.service.etl.PreReconcileImpl - error in -> etl(6000007777-20140608-gabung.txt) error in inserting data = 101932,0608,00006003110,5136,534110565309,201406,05280698950B312D9FF000,000000078281,00000078281,0000000000,000000000,0020 into tabel t_reconcile_stage
org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [insert into t_reconcile_stage values( ?::bigint,?::bigint,?::bigint,?,?::date,?,?,?::bigint,?,?,?,?,?,?,?::bigint,?,?,?,?,?,?,?,?::bigint,?::bigint,?::bigint,?::bigint,?::bigint,?,?,?,?,?,?,?,?,?,?,?,?,?::bigint,?::bigint,?)]; ERROR: invalid input syntax for integer: ""; nested exception is org.postgresql.util.PSQLException: ERROR: invalid input syntax for integer: ""
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:101)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:602)
at org.springframework.jdbc.core.JdbcTe
我不知道在哪一列会发生这个错误。因此，我需要使用java中的
try-catch语句逐列将数据插入到该表中。在Postgres中可以进行这样的INSERT查询吗？或者其他方法来定位这个错误？或者我错过了什么？
（我是处理数据库的初学者。）
最佳答案
INSERT是一个全或无操作。将插入一个新行，并且需要为每一列指定值或空值，或者使用显式输入，或者使用列默认值。所以不，你要的是不可能的。
来自Postgres的错误是：
错误：整数的输入语法无效：“”
问题的根源在于您试图将空字符串作为bigint数字出售，这是不可能的。将字符数据转换为整数时，必须将空字符串转换为某种有效形式。0或NULL（不带引号）。