Warm tip: This article is reproduced from stackoverflow.com, please click
java jdbc postgresql

Create Statement Returning ERROR: syntax error at or near "RETURNING"

发布于 2020-03-27 10:24:19

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'
Questioner
caleb baker
Viewed
292
a_horse_with_no_name 2019-07-03 22:40

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)