I want to create a database table with a CREATE statement. I get an error Saying
Error: syntax error at or near "RETURNING".
I understand there is some sort of bug with the JDBC driver. As I found this. Postgres JDBC driver: PSQLException: syntax error at or near RETURNING
It seems that I need to set Quirk Mode. But I'm not sure how to do that.
Right Now I have
Connection dbConnection;
dbConnection = DriverManager.getConnection(connectionString,username,password);
Statement st = dbConnection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
st.executeUpdate(query,Statement.RETURN_GENERATED_KEYS);
I dont know where I am supposed to set quirk mode
EDIT:
I'm sorry for missing details. It took me through the guided way.
The statement is made through
st.executeUpdate(query,Statement.RETURN_GENERATED_KEYS);
And the SQL is
String employeeTable="CREATE TABLE \"employee\" (\n" +
" \"employee_id\" serial,\n" +
" \"employee_first_name\" TEXT,\n" +
" \"employee_middle_name\" TEXT,\n" +
" \"employee_last_name\" TEXT,\n" +
" \"employee_dob\" date ,\n" +
" \"employee_ssn\" TEXT NOT NULL DEFAULT '0',\n" +
" \"employee_wages\" FLOAT(30) NOT NULL DEFAULT '0',\n" +
" \"employee_password\" TEXT DEFAULT '0',\n" +
" \"employee_issupervisoer\" BOOLEAN NOT NULL DEFAULT 'false',\n" +
" \"employee_hassupervisoer\" BOOLEAN NOT NULL DEFAULT 'false',\n" +
" \"employee_supervisor_id\" integer,\n" +
" \"employee_clockstatus\" BOOLEAN DEFAULT 'false',\n"+ // True = IN : False = OUT
" \"employee_lastpunch\" timestamp, \n"+
" \"employee_isactive\" BOOLEAN, \n"+
" CONSTRAINT employee_pk PRIMARY KEY (\"employee_id\")\n" +
") WITH (\n" +
" OIDS=FALSE\n" +
");\n";
Honestly I don't think that SQL matters though. This statement worked for me about 6 months back. I dont know what changed but I changed from Postgresql 9.4 to 10.7? whatever the most recent 10.x stable is. and I updated my Gradle to
// https://mvnrepository.com/artifact/org.postgresql/postgresql
compile group: 'org.postgresql', name: 'postgresql', version: '42.2.6'
You can not combine a CREATE TABLE
statement with a RETURNING clause (as it does not "return" anything).
When you call executeUpdate(query,Statement.RETURN_GENERATED_KEYS)
you are requesting the generated keys from a DML statement to be returned. The Postgres JDBC driver does this by adding a RETURNING
clause to the query - which obviously makes no sense with a DDL statement.
Use execute(query)
instead.
Or executeUpdate(query)
(without requesting generated keys)
Thank you for taking the time to read the question and the supporting link's and formulate a response that answers my question and fixes my problem. I wish I could up vote you like 10 more times
Technically a driver should support it and handle it as a normal statement. From the apidoc: "The driver will ignore the flag if the SQL statement is not an INSERT statement, or an SQL statement able to return auto-generated keys (the list of such statements is vendor-specific)." So while you normally wouldn't execute a create table statement this way, the driver should handle it correctly.