Error on createTableIfNotExists (PgSQL)

[sergsoft]

This code does'n work if table "Citizens" already exixts

TableUtils.createTableIfNotExists(injector.getInstance(ConnectionSource.class), Citizen.class);

Error log: 10:24:34,354 ERROR [stderr](ServerService Thread Pool -- 23) java.sql.SQLException: SQL statement failed: CREATE SEQUENCE "Citizens_id_seq" 10:24:34,354 ERROR [stderr](ServerService Thread Pool -- 23) at com.j256.ormlite.misc.SqlExceptionUtil.create(SqlExceptionUtil.java:22) 10:24:34,354 ERROR [stderr](ServerService Thread Pool -- 23) at com.j256.ormlite.table.TableUtils.doStatements(TableUtils.java:468) 10:24:34,355 ERROR [stderr](ServerService Thread Pool -- 23) at com.j256.ormlite.table.TableUtils.doCreateTable(TableUtils.java:442) 10:24:34,355 ERROR [stderr](ServerService Thread Pool -- 23) at com.j256.ormlite.table.TableUtils.createTable(TableUtils.java:220) 10:24:34,355 ERROR [stderr](ServerService Thread Pool -- 23) at com.j256.ormlite.table.TableUtils.createTableIfNotExists(TableUtils.java:61)

Mapping: @Entity(name = "Citizens") public class Citizen { @Id @GeneratedValue private Long id; ... }

[j256]

What database type are you using here? And what version of ORMLite?

[sergsoft]

Database: PostgreSql 9.3 ORMLite: 4.48

[otaviofff]

It has happened to me as well.

Basically, if you run twice TableUtils.createTableIfNotExists(connectionSource, Citizen.class), on PostgreSQL, you get an exception java.sql.SQLException: SQL statement failed: CREATE SEQUENCE

BTW, this doesn't happen on MySQL.

Can you please check? Thanks a lot.

[j256]

Yeah it still exists. Can you help find an easy solution to this? Unfortunately Postgres does not allow easy table or sequence listing that I can find.

[sergsoft]

You can get list of all tables in "public" schema

select * from information_schema.tables where table_schema='public'

and get list of all sequences

select * from information_schema.sequences

Sequence name usually is <tableName>_id_seq

[j256]

Any idea how portable this is across postgres versions? I guess it is better than nothing.

[sergsoft]

All of this queries should work on PostgreSql 8.2 and above. Even PgSql 7.4 has information_schema.tables, but has not information_schema.sequences. If you want to add support for PgSql from 7.4 to 8.2 in case of sequences you can use follow query

SELECT c.relname FROM pg_class c WHERE c.relkind = 'S'

[otaviofff]

This appears to be a good approach indeed. And I believe this version range (from 8.2 to 9.5) should be acceptable for an ORM solution. Thanks for sharing!

[otaviofff]

Any news on this fix? Thanks a lot.

[wesblume23]

Any update? I'm running into this error as well.

Thanks

[wesblume23]

Is this the latest?

https://github.com/j256/ormlite-core/blob/master/src/main/java/com/j256/ormlite/table/TableUtils.java

[wesblume23]

Correct me if I'm wrong, but the problem appears to be in databaseType.appendColumnArg().

When we attempt to create a table, we call doCreateTable(), doStatements() and addCreateTableStatements(). For each column we attempt to execute code based on the specified annotations in the user's DAO class. I believe that appendColumnArg() attempts to create the sequence, however, I didn't notice anything specifying to check whether or not the entity exists (in the case of the entity being a sequence).

Thoughts?

[j256]

Dammit. I really don't have a good way of fixing this. There is no IF EXISTS in Postgres. All of the SO solutions that I see are very version dependent. Turning off the handling of exceptions on various statements just seems like a hack. I got nothing. Anyone?

[benedekh]

How about creating a Dao and checking how many rows the respective table has? If the table does not exist yet I think it may throw an SQL Exception. So my implementation is like this:

try {
    // test if the table already exists
    DaoManager.createDao(connectionSource, type).countOf();
} catch (SQLException ex) {
    // if not, then create the table
    TableUtils.createTable(connectionSource, type);
}

I close the connection at a later point in my code.

You may correct me if I am wrong with something.

[Bo98]

IF NOT EXISTS was added to CREATE SEQUENCE in PostgreSQL 9.5 (Jan 2016). At the very least we could add a version check to use IF NOT EXISTS for PostgreSQL >9.5.

Thoughts?

[Bo98]

There was a similar situation for tables only having IF NOT EXISTS from 9.1:

https://github.com/j256/ormlite-jdbc/blob/f11ffef98649d32ad70d1a78ed14b194b6f30c91/src/main/java/com/j256/ormlite/db/PostgresDatabaseType.java#L124-L128

[Bo98]

Alternatively, we can move to the SERIAL datatype for generated IDs which have wider support, though this means swapping out the data type which will result in implementation being spread around the type appending methods.

Or there is now an SQL standard way of representing auto increment columns, though this is only supported in PostgreSQL 10 and above. Conditionals can easily be added if desired however to isIdSequenceNeeded etc.

One thing I just realised to be careful on is simply adding IF NOT EXISTS may well just silence problems with sequence name conflicts. You could change the API to only do this when createTableIfNotExists is called but that doesn't wholly fix that problem. Perhaps one of the above may be better solutions.

[Bo98]

Some prototypes.

---

Example 1:

import com.j256.ormlite.db.PostgresDatabaseType;
import com.j256.ormlite.field.FieldType;

import java.util.List;

public class SerialPostgresDatabaseType extends PostgresDatabaseType
{
    @Override
    public boolean isIdSequenceNeeded()
    {
        return false;
    }

    // THIS REQUIRES AN API CHANGE - usually this is private
    @Override
    protected void appendIntegerType(StringBuilder sb, FieldType fieldType, int fieldWidth)
    {
        sb.append(fieldType.isGeneratedId() ? "SERIAL" : "INTEGER");
    }

    @Override
    protected void appendLongType(StringBuilder sb, FieldType fieldType, int fieldWidth)
    {
        sb.append(fieldType.isGeneratedId() ? "BIGSERIAL" : "BIGINT");
    }

    @Override
    protected void configureGeneratedId(String tableName, StringBuilder sb, FieldType fieldType,
                                        List<String> statementsBefore, List<String> statementsAfter,
                                        List<String> additionalArgs, List<String> queriesAfter)
    {
        // do nothing extra
        configureId(sb, fieldType, statementsBefore, additionalArgs, queriesAfter);
    }
}

Supports PostgreSQL 7.2 and later (older if you remove BIGSERIAL).

Requires a small API change.

Could also add shorts though this is not currently allowed for generated IDs. If it were supported, that could use SMALLSERIAL on 9.2 and later, and just use the bigger SERIAL on older versions.

Will ignore BigInteger without error (does this even work anyway as strings?? It's one of the whitelisted generatedID types.).

---

Example 2:

import com.j256.ormlite.db.PostgresDatabaseType;
import com.j256.ormlite.field.FieldType;

import java.util.List;

public class IdentityPostgresDatabaseType extends PostgresDatabaseType
{
    @Override
    public boolean isIdSequenceNeeded()
    {
        return driver.getMajorVersion() < 10;
    }

    @Override
    protected void configureGeneratedId(String tableName, StringBuilder sb, FieldType fieldType,
                                        List<String> statementsBefore, List<String> statementsAfter,
                                        List<String> additionalArgs, List<String> queriesAfter)
    {
        if (fieldType.isAllowGeneratedIdInsert())
            sb.append("GENERATED BY DEFAULT AS IDENTITY ");
        else
            sb.append("GENERATED ALWAYS AS IDENTITY ");
        configureId(sb, fieldType, statementsBefore, additionalArgs, queriesAfter);
    }
}

Still retains support for all PostgreSQL versions but the IF NOT EXISTS functionality only will work on PostgreSQL 10+ (still better than now).

Bonus: database-level support for allowGeneratedIdInsert. Also would support shorts (SMALLINT) if it were added to the whitelist.

Errors on BigInteger, but as a SQLException (see note above).

[freedom1b2830]

[main] INFO com.j256.ormlite.table.TableUtils - creating table 'users'
java.sql.SQLException: SQL statement failed: CREATE SEQUENCE "users_id_seq"
    at com.j256.ormlite.misc.SqlExceptionUtil.create(SqlExceptionUtil.java:25)
    at com.j256.ormlite.table.TableUtils.doStatements(TableUtils.java:423)
    at com.j256.ormlite.table.TableUtils.doCreateTable(TableUtils.java:399)
    at com.j256.ormlite.table.TableUtils.doCreateTable(TableUtils.java:383)
    at com.j256.ormlite.table.TableUtils.createTableIfNotExists(TableUtils.java:75)
(MY CODE)
Caused by: org.postgresql.util.PSQLException: ERROR: relation "users_id_seq" already exists
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:181)
    at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:170)
    at com.j256.ormlite.jdbc.JdbcCompiledStatement.runExecute(JdbcCompiledStatement.java:71)
    at com.j256.ormlite.table.TableUtils.doStatements(TableUtils.java:417)
    ... 8 more

maven: ormlite-jdbc 5.0 postgresql 42.4.0

postgresql(server) 14.3

[ivorhine]

The same problem occurs on HSQLDB:

HSQLDB version: 2.7.1 ORMLite version: 6.1

Stack trace:

java.sql.SQLException: SQL statement failed: CREATE SEQUENCE "FOLDER_ID_SEQ" AS BIGINT START WITH 1
    at ormlite.jdbc@6.1/com.j256.ormlite.table.TableUtils.doStatements(TableUtils.java:395)
    at ormlite.jdbc@6.1/com.j256.ormlite.table.TableUtils.doCreateTable(TableUtils.java:371)
    at ormlite.jdbc@6.1/com.j256.ormlite.table.TableUtils.doCreateTable(TableUtils.java:356)
    at ormlite.jdbc@6.1/com.j256.ormlite.table.TableUtils.createTableIfNotExists(TableUtils.java:74)
    (my code)
    at javafx.graphics@19/com.sun.javafx.application.LauncherImpl.lambda$launchApplication1$9(LauncherImpl.java:847)
    at javafx.graphics@19/com.sun.javafx.application.PlatformImpl.lambda$runAndWait$12(PlatformImpl.java:484)
    at javafx.graphics@19/com.sun.javafx.application.PlatformImpl.lambda$runLater$10(PlatformImpl.java:457)
    at java.base/java.security.AccessController.doPrivileged(Native Method)
    at javafx.graphics@19/com.sun.javafx.application.PlatformImpl.lambda$runLater$11(PlatformImpl.java:456)
    at javafx.graphics@19/com.sun.glass.ui.InvokeLaterDispatcher$Future.run(InvokeLaterDispatcher.java:96)
    at javafx.graphics@19/com.sun.glass.ui.gtk.GtkApplication._runLoop(Native Method)
    at javafx.graphics@19/com.sun.glass.ui.gtk.GtkApplication.lambda$runLoop$11(GtkApplication.java:316)
    at java.base/java.lang.Thread.run(Thread.java:829)
Caused by: java.sql.SQLSyntaxErrorException: object name already exists: FOLDER_ID_SEQ in statement [CREATE SEQUENCE "FOLDER_ID_SEQ" AS BIGINT START WITH 1]
    at org.hsqldb/org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
Caused by: java.sql.SQLSyntaxErrorException: object name already exists: FOLDER_ID_SEQ in statement [CREATE SEQUENCE "FOLDER_ID_SEQ" AS BIGINT START WITH 1]
    at org.hsqldb/org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb/org.hsqldb.jdbc.JDBCPreparedStatement.fetchResult(Unknown Source)
    at org.hsqldb/org.hsqldb.jdbc.JDBCPreparedStatement.execute(Unknown Source)
    at ormlite.jdbc@6.1/com.j256.ormlite.jdbc.JdbcCompiledStatement.runExecute(JdbcCompiledStatement.java:73)
    at ormlite.jdbc@6.1/com.j256.ormlite.table.TableUtils.doStatements(TableUtils.java:389)
    ... 16 more
Caused by: org.hsqldb.HsqlException: object name already exists: FOLDER_ID_SEQ
    at org.hsqldb/org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb/org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb/org.hsqldb.SchemaObjectSet.checkAdd(Unknown Source)
    at org.hsqldb/org.hsqldb.SchemaManager.checkSchemaObjectNotExists(Unknown Source)
    at org.hsqldb/org.hsqldb.StatementSchema.setOrCheckObjectName(Unknown Source)
    at org.hsqldb/org.hsqldb.StatementSchema.getResult(Unknown Source)
    at org.hsqldb/org.hsqldb.StatementSchema.execute(Unknown Source)
    at org.hsqldb/org.hsqldb.Session.executeCompiledStatement(Unknown Source)
    at org.hsqldb/org.hsqldb.Session.execute(Unknown Source)