PostgreSQL: Documentation: Manuals: PostgreSQL 7.3: SQL Key Words
It’s very annoying that ‘user’ is a reserved word in postgresql. You also get really crappy error messages (at least with the various forms of quoting I’ve tried to use) when you try to create a table called ‘user’
$ psql web
Welcome to psql 7.4.8, the PostgreSQL interactive terminal.Type:Â \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quitweb=# create table user (a int(10), b int); ERROR:Â syntax error at or near “user” at character 14
web=# create table “user” (a int(10), b int);
ERROR:Â syntax error at or near “(” at character 27
web=# create table ‘user’ (a int(10), b int);
ERROR:Â syntax error at or near “‘user'” at character 14
web=# create table `user` (a int(10), b int);
ERROR:Â syntax error at or near “`” at character 14
web=#
web=#
You will even get the “at character X” if you’re piping something into psql. Hrrm… a line number would be useful.
It also means that I can’t compare results from MySQL and Postgresql involving a table called ‘user’. Bummer.
Any postgresql gurus out there got a solution for me?
I’m not at all a PGSQL expert … but:
1st case: the served word USER is not allowed (character 14).
2nd case: “USER” seems fine, but INT(10) is not allowed (character 27). Try INTEGER (just a guess).
3rd case: ‘USER’ is wrong, which makes sense. Single quotes, in SQL, always denote string data.
4th case: `USER` is obviously wrong, given that only MySQL uses the backticks for quoting identifiers.
—
Martijn Tonies
Upscene Productions
I wonder where the int(10) comes from – if it’s ANSI or MySQL extension?
(now that’s a big question for the lazyweb – emphasis on the LAZY)
Must be MySQL. According to the docs:
“M indicates the maximum display width. The maximum legal display width is 255. Display width is unrelated to the storage size or range of values a type can contain…”
Display width has nothing to do with a DBMS as such, so I very much doubt it is accepted in the SQL Standard.
That being said, MySQL is the only one that I know of that supports “display width”. Others allow you to modify the domain (set of possible values) for a datatype (eg: Oracle NUMBER[(n)] etc…), which does make sense.
Anyway, got things working now? :)
—
Martijn Tonies
Upscene Productions
This post is not even a rant, just shows that you didn’t do your homework!
And note that current stable version for PostgreSQL is 8.1.3 (http://www.postgresql.org/docs/whatsnew).
I know it’s not a rant – it’s not filed in the “Inciting Hatred” category :)
7.4.8 is what’s shipped with my distro as default – and is serving well enough for the “i wonder what postgresql does” uses.
A good user interface means I don’t have to do all my homework – it’ll help me when I haven’t. Hence good error messages are a *good thing*. I’m not saying that MySQL doesn’t have its share of bad error messages either – just noting this postgresql one.
When the next release of Ubuntu is more stable and I upgrade my laptop to it – i’ll probably run postgresql 8.1 then (as it’s default AFAIK).
Thanks Martijn – I ended up just giving up unfortunately :(
I looked closer at the query and found the real problem – and it wasn’t a bug (which was good).
IMHO for something like int(10) here is a brilliant time to accept the syntax but do nothing with it (err.. produce a warning can be good). This allows much better interoperability between systems.
If I was feeling really adventurous (and had some more spare time) I’d patch postgresql to just accept the syntax.
Of course, less adventurous me would go and fix the mysqldump –compatible=postgresql option to actually be compatible with postgresql. Unfortunately, compatible just sets some options and other options don’t exist to get a mysqldump that’s actually going to restore into a psql db.
The other way is problematic too… I’ve found that one needs to edit the schema SQL and then run some sed over the data SQL to get any pg_dump to restore in mysql.
there’s probably a good project in there for somebody.
Of course, the MySQL Migration Toolkit could be the answer for going to mysql here… even though postgresql isn’t on the officially supported list, since it uses JDBC and there’s a psql JDBC driver, people have tried it and said it worked fairly well.
No, I don’t think it is annoying. If it is not reserved in MySQL, that is annyoing since it is a reserved word in SQL-92, SQL-99, and SQL-2003.
(see http://developer.mimer.com/validator/sql-reserved-words.tml)
/Fredrik
The massive number of reserved words in SQL (MUMPS was apparently a reserved word at one stage) is pretty irritating. I spent an entire weekend trying to work out why I couldn’t create a table called GENERAL in MS-SQL, and therefore now escape every table and column name as a matter of course.
Pingback: zillablog
> Of course, less adventurous me would go and fix the
> mysqldump –compatible=postgresql option to actually be
> compatible with postgresql.
How about a mysqldump –compatible=sql (or SQL-99/SQL-2003)
That would help for converting to almost anything (including PostgreSQL).
Good suggestion, aidan! But just to mention it… int is always int(13) on mysql, this could cause troubles… see in detail: http://www.sqlexikon.de, this site explains some sql syntax and has a mysql compatibility section – some content unfortunately in german.