CODEWITHSUNDEEP
javasqlpostgresqljdbc
Mohammad Fajar
Mohammad Fajar

Reputation: 1007

Insert data into row in PostgreSQL column by column

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.)

Upvotes: 1

Views: 784

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656636

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).

Upvotes: 2

Jorgeejgonzalez
Jorgeejgonzalez

Reputation: 351

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.

Upvotes: 0

Related Questions