0

I have the following migration file which I want to execute using go migrate on a postgres database.

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE user 
(
    id UUID DEFAULT uuid_generate_v4 () PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL,
    created_at TIMESTAMP without time zone NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP without time zone
);

I get an error

Details: pq: syntax error at or near "user"

I have tried different online SQL syntax validators but couldn't find the issue.

Questions:

  • What's the syntax error?
  • How can I better debug it myself next time?
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
siva
  • 1,183
  • 3
  • 12
  • 28
  • 3
    User is [reserved keyword](https://www.postgresql.org/docs/current/sql-keywords-appendix.html). Try other table name (maybe `USERS`?). – markalex Mar 26 '23 at 16:35
  • That was it. Thank you! Does it make a difference if the table name is uppercase or not in the migration file? Can you write a separate answer then I'll mark your answer as correct. – siva Mar 26 '23 at 16:44
  • If you don't enclose table name in double quotes - it doesn't matter: dbms will automatically make it upper case. – markalex Mar 26 '23 at 16:49
  • @markalex: Is this answer no longer valid then ? https://stackoverflow.com/a/21798517/724039 (and the comment: [but PostgreSQL folds them to lower case](https://stackoverflow.com/questions/21796446/postgres-case-sensitivity/21798517#comment32988199_21798517) ) – Luuk Mar 26 '23 at 16:59
  • @Luuk, sorry wasn't aware of this postgres behavior, my bad. – markalex Mar 26 '23 at 17:08
  • @markalex I did not know tooo, but happened to read it 12 minutes ago – Luuk Mar 26 '23 at 17:11
  • Just don't use it period as per [Key Words](https://www.postgresql.org/docs/current/sql-keywords-appendix.html): *USER reserved* and *Labeled “reserved” are those tokens that are not allowed as column or table names.*. While you can force it by double quoting the name, you will be fighting with that for the life of the table. Just avoid the hassle and pick another name. – Adrian Klaver Mar 26 '23 at 17:32
  • Note that since Postgres 13, [gen_random_uuid](https://www.postgresql.org/docs/current/functions-uuid.html) is already built-in. No need to install an extension –  Mar 26 '23 at 18:36

1 Answers1

1

Problem is caused by the table name: User is reserved keyword.

Try other table name (maybe USERS?).

markalex
  • 8,623
  • 2
  • 7
  • 32