Insert data into row in PostgreSQL column by column

Question

I created a table in a PostgreSQL database with 42 columns. Five of them are bigint type, whereas others are character varying type. I then tried to insert data into using some java code and the jdbc library from spring framework.

I encountered some error that I need to locate, which is hard because the table contains so many columns.
The syntax in jdbc to insert into that table:

        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]
        );

And the error I got:

[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

I don't know in what column that error occurs. So I need to insert data into that table column by column using try-catch statement in java. Is it possible to make an INSERT query like this in Postgres?
Or any other approach to locate that error? Or something I missed?
(I am a beginner with handling the database.)

Erwin Brandstetter · Accepted Answer · 2014-08-06 07:43:54Z

2

INSERT is an all-or-nothing operation. A new row is inserted and every column needs to be assigned, either with a value or NULL, either with explicit input or from a column default. So no, what you are asking for is not possible.

The error coming from Postgres is:

ERROR: invalid input syntax for integer: ""

The root of the problem is that you are trying to sell an empty string as a bigint number, which is not possible. When converting character data to integer, you have to convert empty strings into some valid form. Either 0 or NULL (without quotes).

edited Aug 6, 2014 at 7:43

answered Aug 6, 2014 at 4:55

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

JamesENL · Accepted Answer · 2014-08-06 05:49:54Z

0

One workaround for this error is to assign default values to your columns (not always a good practice though). In that way if you are missing a data in your insert statement it should work with the default value.

Although I would recommend you design a revision of your update method, as the poster above noted, your whole problem comes from one of the values in your array being empty, and that could be fixed with proper data validation.

edited Aug 6, 2014 at 5:49

JamesENL

6,5486 gold badges43 silver badges64 bronze badges

answered Aug 6, 2014 at 5:43

Jorgeejgonzalez

3513 silver badges4 bronze badges

Collectives™ on Stack Overflow

Insert data into row in PostgreSQL column by column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related