PostgreSQL JDBC - can drop but not recreate indexes

Question

Hi, I've been searching for an answer, but no luck so far...

I want to perform bulk operations on a database with potentially millions of records, reading the PostgreSQL guide: '13.4 Populating a Database' 1, it suggests removing indexes and foreign-key constraints to speed up the copy operation.

I'm trying to accomplish this using JDBC statements, I'm finding that I can drop the indexes without any issue, but recreating them after populating the database has problems. I get a syntax error 'at or near' the name of the index that I am creating:

Statement stmt = connection.createStatement();
String query = "CREATE UNIQUE INDEX type_uk ON cell (field1, field2, field3, field4) WHERE field3 AND field4 IS NOT NULL TABLESPACE lcindex";

stmt.executeUpdate(query);
connection.commit();

If I execute this query in psql, it successfully creates the index though, so I'm a bit confused...

Any help, insights, suggestions, etc. would be much appreciated :) Thanks in advance.

Answer 1

Can't comment on the answer like Michal Niklas did, but your sql statement is illegal in postgres and cannot work. My guess:

CREATE UNIQUE INDEX type_uk ON cell (field1, field2, field3, field4) TABLESPACE lcindex 
  where field3 is not null and field4 is not null;

• tablespace should come before where
• every field in there where clause has to have "is not null"