Getting error trying to create table with User as name

#1

I’m not sure why but I’m getting the following error:

PostgresException: 42601: syntax error at or near ""user""

using the following:

        db.CreateTableIfNotExists<Tenant>();
        db.CreateTableIfNotExists<Environment>();
        db.CreateTableIfNotExists<User>();
        db.CreateTableIfNotExists<File>();

All but the User one works. Why is that? I’m able to create a table named “user” directly in PostgreSQL so I’m sure it’s a legal name. Outside of that, I’m not sure if this is a problem with OrmLite or Npgsql.

Strange thing is, I’m 100% positive I have done this before with OrmLite, so I’m not sure what changed and where.

#2

It’s working without issue for me:

OrmLiteUtils.PrintSql();
using (var db = OpenDbConnection())
{
    db.DropTable<User>();
    db.CreateTableIfNotExists<User>();
}

Which executes the following SQL:

SQL: SELECT COUNT(*) FROM pg_class JOIN pg_catalog.pg_namespace n ON n.oid = pg_class.relnamespace WHERE relname = 'user' AND relkind = 'r' AND nspname = 'public'
SQL: DROP TABLE "user"
SQL: SELECT COUNT(*) FROM pg_class JOIN pg_catalog.pg_namespace n ON n.oid = pg_class.relnamespace WHERE relname = 'user' AND relkind = 'r' AND nspname = 'public'
SQL: CREATE TABLE "user" 
(
  "id" INTEGER PRIMARY KEY, 
  "name" TEXT NULL 
);

You can use OrmLiteUtils.PrintSql() to log what SQL is being executed, then copy the SQL and run it PostgreSQL to see what the issue is.

#3

That helped. Results are a bit strange though. Seems to be related to the UniqueConstraints attribute.

This works:

[UniqueConstraint(nameof(Environment), nameof(Name))]
public class User2
{
    [AutoId]
    public Guid Id { get; set; }
    public Guid Environment { get; set; }
    public string Name { get; set; }
}

This doesn’t:

[UniqueConstraint(nameof(Environment), nameof(Name))]
public class User
{
    [AutoId]
    public Guid Id { get; set; }
    public Guid Environment { get; set; }
    public string Name { get; set; }
}

Only difference is name of class. Seems that OrmLite is producing some malformed SQL for the CONSTRAINT name when class name is User:

CREATE TABLE "user"
(
  "id" UUID PRIMARY KEY DEFAULT (uuid_generate_v4()),
  "environment" UUID NOT NULL,
  "name" TEXT NOT NULL,
CONSTRAINT UC_"user"_Environment UNIQUE ("environment")
);

Notice the quotes around the user value in the constraint? Postgres doesn’t seem to like this.

User2 produces:

CREATE TABLE "user2"
(
  "id" UUID PRIMARY KEY DEFAULT (uuid_generate_v4()),
  "environment" UUID NOT NULL,
  "name" TEXT NOT NULL,
CONSTRAINT UC_user2_Environment UNIQUE ("environment")
);

So I thought, maybe because the class file name was also User.cs. So I changed it to User2.cs, but same issue.

So it seems that for whatever reason OrmLite is adding quotes around “user” in the constraint, but not around “user2” in the constraint. Very odd. When I remove the constraint attribute, user works.

Is this something that can be fixed?

#4

This should be resolved from this commit. This change is available from v5.6.1 that’s now available on MyGet.

#5

Sorry, but it didn’t seem to fix the problem. Started new project, installed 5.6.1 from MyGet. Same problem.

Same sql being generated:

SQL: CREATE TABLE "user"
(
  "id" UUID PRIMARY KEY DEFAULT (uuid_generate_v4()),
  "environment" UUID NOT NULL,
  "name" TEXT NOT NULL,
  "created" timestamp NOT NULL DEFAULT (now() at time zone 'utc'),
  "created_by" UUID NULL,
CONSTRAINT UC_"user"_Environment_Name UNIQUE ("environment","name")
);

I’m also unclear why this is only happening in the case of “user” and seemingly no other names. Your commit seems to do nothing special for “user” so this is completely odd to me. Can you explain?

#6

“user” is a reserved word in PostgreSQL which needs to be quoted whenever it’s referenced, my commit strips quotes off reserved words when constructing the constraint name which resolves this issue.

If you already had the pre-release version installed (i.e. v5.6.1) you always need to clear your NuGet packages cache.

#7

That was it! Thanks for the quick response.

1 Like