Hello,
I'm trying to execute an insert statement against the following table, in which subscriber_id is auto incremented.
subscriber_id | user_id | tenant_id | email_address | date_subscribed
---------------+------------+-----------+---------------+-----------------
1 | admin1234 | -1234 | | 1970-01-01
3 | admin12345 | -1234 | | 1970-01-01
Table was created using the following statement:
CREATE TABLE AM_SUBSCRIBER2 (
SUBSCRIBER_ID INTEGER DEFAULT nextval('am_subscriber_sequence'),
USER_ID VARCHAR(50) NOT NULL,
TENANT_ID INTEGER NOT NULL,
EMAIL_ADDRESS VARCHAR(256) NULL,
DATE_SUBSCRIBED DATE NOT NULL,
PRIMARY KEY (SUBSCRIBER_ID),
UNIQUE (TENANT_ID,USER_ID));
The insert query gets executed through a PreparedStatement, which returns the assigned subscriber_id for the newly inserted row.
String query = "INSERT" +
" INTO AM_SUBSCRIBER (USER_ID, TENANT_ID, EMAIL_ADDRESS, DATE_SUBSCRIBED)" +
" VALUES (?,?,?,?)";
PreparedStatement ps = conn.prepareStatement(query, new String[] {"SUBSCRIBER_ID"});
ps.setString(1, "admin1");
ps.setInt(2, -1234);
ps.setString(3, "");
ps.setTimestamp(4, new Timestamp(0));
ps.executeUpdate();
When executing the above cord segment I'm getting an SQLException saying that the "SUBSCRIBER_ID" doesn't exist.
org.postgresql.util.PSQLException: ERROR: column "SUBSCRIBER_ID" does not exist
Position: 128
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:363)
at ConnectionTest.TestAddSubscriber(ConnectionTest.java:48).
Simply by changing the case of SUBSCRIBER_ID to lower case, in return column array ,this error can be avoided. But the problem is that change is not feasible.
This problem occurred when trying to use a Postgre db where initially it had been using a MySql db.
Is it possible to solve this problem by changing the DDL statement or any connection parameters?
Regards,
Amila