Error on createTableIfNotExists (PgSQL) #20
Comments
|
What database type are you using here? And what version of ORMLite? |
|
Database: PostgreSql 9.3 |
|
It has happened to me as well. Basically, if you run twice BTW, this doesn't happen on MySQL. Can you please check? Thanks a lot. |
|
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. |
|
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.sequencesSequence name usually is |
|
Any idea how portable this is across postgres versions? I guess it is better than nothing. |
|
All of this queries should work on PostgreSql 8.2 and above. SELECT c.relname FROM pg_class c WHERE c.relkind = 'S' |
|
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! |
|
Any news on this fix? Thanks a lot. |
|
Any update? I'm running into this error as well. Thanks |
|
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? |
|
Dammit. I really don't have a good way of fixing this. There is no |
|
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. |
|
Thoughts? |
|
There was a similar situation for tables only having |
|
Alternatively, we can move to the 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 One thing I just realised to be careful on is simply adding |
|
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 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 Will ignore 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 Bonus: database-level support for Errors on |
maven: postgresql(server) 14.3 |
|
The same problem occurs on HSQLDB: HSQLDB version: 2.7.1 Stack trace: |
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;
...
}
The text was updated successfully, but these errors were encountered: