create type if not exists fails

[nicolasparada]

Version

1.14.0

What happened?

Wanted to create a new enum only IF NOT EXISTS. This might not be supported in postgres (dunno), but is supported in cockroachdb https://www.cockroachlabs.com/docs/v22.1/create-type and its a very nice feature for having an easy migration.

Relevant log output

schema.sql:1:1: syntax error at or near "NOT"

Database schema

CREATE TYPE IF NOT EXISTS community_role AS ENUM ('creator', 'admin');

SQL queries

No response

Configuration

version: "2"
sql:
  - schema: "schema.sql"
    queries: "queries.sql"
    engine: "postgresql"
    gen:
      go:
        package: "nakama"
        out: "."
        output_models_file_name: "models_gen.go"
        output_db_file_name: "db_gen.go"
        output_files_suffix: "_gen.go"

Playground URL

No response

What operating system are you using?

macOS

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[akutschera]

When I look at the postgresql docs CREATE TYPE does not support the "IF NOT EXISTS" syntax, so I believe the syntax error is expected. What I wonder is why you would add this to the sqlc schema.sql This file is interpreted while generating the code. It is not sent to any real database, therefore the type cannot exist (unless you already defined it earlier in your schema.sql, in which case you could just remove one of the definitions).

[nicolasparada]

It is supported in CockroachDB.

I'm using the schema.sql file as the migration file. So in that sense, all statements have IF NOT EXISTS so the migration can be run multiple times without issue.

[akutschera]

I understand that you want to migrate from CockroachDB to PostgreSQL. I suggest you try running the schema.sql against a real PostgreSQL database and tweak it until it creates the tables and types you want it to create. Try this without sqlc. Once you got the schema working, try it again with sqlc. The advantage of that approach is that any syntax errors in your code will be caught by the database and you will find out quickly whether the database does not support it or whether it's sqlc.

[nicolasparada]

No no. I'm talking about the application schema one "migrates" to create the tables on the database.

So, the same schema.sql file is being used by sqlc to generate queries, and to create the tables when the application starts. And all statements have IF NOT EXISTS so it doesn't fail on a second run.

[akutschera]

I understand that you don't want them to fail on the second run. But before I want them to succeed on the second run, I want them to succeed on the first run (which right now won't work because of syntax errors). Does your schema.sql run successfully when the application starts (because that has nothing to do with sqlc)?

[nicolasparada]

Yes, I'm using Cockroach so its a supported feature.

[akutschera]

Ah, I think I misunderstood you before. Let me try again: You are using CockroachDB. You want to generate code for your CockroachDB using sqlc. Sqlc does not support CockroachDB. You want to use the fact that CockroachDB understands Postgresql commands, which is why you are using the "postgresql" engine. The DDL command you are using is supported in CockroachDB, but not in Postgresql, which is why you get the error.

In order for this to work I can see the following alternatives:

• the postgresql engine needs to support CockroachDB commands (which sounds a little like #151)
• sqlc needs to support a "cockroachdb" engine (see #1389)
• use two schema.sql files, one for the application, one for sqlc (it would solve the problem of the syntax error, it would not solve the problem of using one file for both purposes)
• write a little script that parses the CockroachDB-schema.sql and turns it into a Postgresql-schema.sql before running sqlc generate (this could be done as part of your "generate my SQL code with sqlc"-build step)

The last two solutions are something you could easily do yourself, for the others I'm afraid my knowledge of sqlc and cockroachDB are not good enough yet, so I cannot help you there (I am just a user of sqlc myself).

[nicolasparada]

Thanks.

I don't think supporting cockroachdb as a full engine is a good idea for sqlc.

Cockroach is very similar to postgres. It uses the same protocol. And for the most part, they have the same features. Maybe cockroach support less.

But in this specific case, cockroach supports CREATE TYPE IF NOT EXISTS while postgres doesn't. And sqlc only caring about supporting postgres...

[ram02z]

I am using a workaround to the IF NOT EXISTS syntax for creating types (postgres doesn't support it yet) and this results in the generated type to be interface{} rather than CfTypes. If I remove the workaround, sqlc generates the correct enum correctly but at the cost of the migration not working.

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT
            1
        FROM
            pg_type
        WHERE
            typname = 'cf_types') THEN
    CREATE TYPE cf_types AS ENUM (
        'typology',
        'sub_typology',
        'item'
);
END IF;
END
$$;

Is there a workaround to IF NOT EXISTS that doesn't trip up sqlc?

I am using the pgx/v4 database engine.

[prochac]

Actually Postgres supports CREATE TABLE IF NOT EXISTS since version 9.1 https://www.postgresql.org/docs/9.1/sql-createtable.html

[nicolasparada]

TABLE is different from TYPE.

[prochac]

TABLE is different from TYPE.

My bad, I have overlooked it. It doesn't work with create table either. That's how I found this issue.

[nicolasparada]

I'm pretty sure it does 🤔