{"id":661,"date":"2006-04-19T20:16:47","date_gmt":"2006-04-19T10:16:47","guid":{"rendered":"http:\/\/www.flamingspork.com\/blog\/2006\/04\/19\/postgresql-73-sql-key-words\/"},"modified":"2006-04-19T20:16:47","modified_gmt":"2006-04-19T10:16:47","slug":"postgresql-73-sql-key-words","status":"publish","type":"post","link":"https:\/\/www.flamingspork.com\/blog\/2006\/04\/19\/postgresql-73-sql-key-words\/","title":{"rendered":"PostgreSQL 7.3: SQL Key Words"},"content":{"rendered":"

PostgreSQL: Documentation: Manuals: PostgreSQL 7.3: SQL Key Words<\/a><\/p>\n

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’<\/p>\n

$ psql web
\nWelcome to psql 7.4.8, the PostgreSQL interactive terminal.<\/p>\n

Type:\u00c2\u00a0 \\copyright for distribution terms
\n\\h for help with SQL commands
\n\\? for help on internal slash commands
\n\\g or terminate with semicolon to execute query
\n\\q to quit<\/p>\n

web=# create table user (a int(10), b int); ERROR:\u00c2\u00a0 syntax error at or near “user” at character 14
\nweb=# create table “user” (a int(10), b int);
\nERROR:\u00c2\u00a0 syntax error at or near “(” at character 27
\nweb=# create table ‘user’ (a int(10), b int);
\nERROR:\u00c2\u00a0 syntax error at or near “‘user'” at character 14
\nweb=# create table `user` (a int(10), b int);
\nERROR:\u00c2\u00a0 syntax error at or near “`” at character 14
\nweb=#
\nweb=#<\/p><\/blockquote>\n

You will even get the “at character X” if you’re piping something into psql. Hrrm… a line number would be useful.<\/p>\n

It also means that I can’t compare results from MySQL and Postgresql involving a table called ‘user’. Bummer.<\/p>\n

Any postgresql gurus out there got a solution for me?<\/p>\n","protected":false},"excerpt":{"rendered":"

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 … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[14],"tags":[],"class_list":["post-661","post","type-post","status-publish","format-standard","hentry","category-mysql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p5a6n8-aF","jetpack-related-posts":[{"id":2307,"url":"https:\/\/www.flamingspork.com\/blog\/2011\/03\/01\/alter-table-rename-rename-rename\/","url_meta":{"origin":661,"position":0},"title":"ALTER TABLE RENAME RENAME RENAME","author":"Stewart Smith","date":"2011-03-01","format":false,"excerpt":"Here's a nice challenge for you. What does the following do (or error out on?): CREATE TABLE t1 (a int); CREATE TABLE t2 (b int); ALTER TABLE t1 RENAME t3, RENAME t2, RENAME t4; I'd be interested to know what a) you think it does and then b) if you\u2026","rel":"","context":"In "drizzle"","block_context":{"text":"drizzle","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/drizzle-work-et-al\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":2282,"url":"https:\/\/www.flamingspork.com\/blog\/2011\/02\/04\/implicit-commit-considered-harmful\/","url_meta":{"origin":661,"position":1},"title":"Implicit COMMIT considered harmful.","author":"Stewart Smith","date":"2011-02-04","format":false,"excerpt":"If you execute the following, what does your RDBMS do? CREATE TABLE t1 (a int); START TRANSACTION; INSERT INTO t1 (a) VALUES (1); START TRANSACTION; INSERT INTO t1 (a) VALUES (2); ROLLBACK; SELECT * FROM t1; The answer may surprise you.","rel":"","context":"In "drizzle"","block_context":{"text":"drizzle","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/drizzle-work-et-al\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1292,"url":"https:\/\/www.flamingspork.com\/blog\/2008\/12\/10\/stewart-learns-sql-oddities\/","url_meta":{"origin":661,"position":2},"title":"Stewart learns SQL oddities…","author":"Stewart Smith","date":"2008-12-10","format":false,"excerpt":"What would you expect the following to fail with? CREATE TABLE t1 (a int, b int); insert into t1 values (100,100); CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a)); BEGIN; INSERT INTO t2 values(100,100); CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from\u2026","rel":"","context":"In "mysql"","block_context":{"text":"mysql","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":2310,"url":"https:\/\/www.flamingspork.com\/blog\/2011\/03\/03\/sql-oddity-alter-table-and-default-values\/","url_meta":{"origin":661,"position":3},"title":"SQL Oddity: ALTER TABLE and default values","author":"Stewart Smith","date":"2011-03-03","format":false,"excerpt":"\u00ef\u00bb\u00bfSo, the MySQL (and Drizzle) ALTER TABLE syntax allows you to easily change the default value of a column. For example: CREATE TABLE t1 (answer int); ALTER TABLE t1 ALTER answer SET DEFAULT 42; So, you create a TIMESTAMP column and forgot to set the default value to CURRENT_TIMESTAMP. Easy,\u2026","rel":"","context":"In "drizzle"","block_context":{"text":"drizzle","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/drizzle-work-et-al\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":345,"url":"https:\/\/www.flamingspork.com\/blog\/2005\/01\/18\/mysql-port-of-memberdb\/","url_meta":{"origin":661,"position":4},"title":"MySQL port of MemberDB","author":"Stewart Smith","date":"2005-01-18","format":false,"excerpt":"Spent probably about 3 hours today porting the database schema to MySQL 5 along with finding some bugs in the process. Pretty minor ones, mainly to do with how things could be improved to improve compatibility with schemas written with postgresql in mind. While chasing up some stuff on why\u2026","rel":"","context":"In "linux-aus"","block_context":{"text":"linux-aus","link":"https:\/\/www.flamingspork.com\/blog\/category\/linux-aus\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":4386,"url":"https:\/\/www.flamingspork.com\/blog\/2018\/08\/13\/optimizing-database-access-in-django-a-patchwork-story\/","url_meta":{"origin":661,"position":5},"title":"Optimizing database access in Django: A patchwork story","author":"Stewart Smith","date":"2018-08-13","format":false,"excerpt":"tl;dr: I made Patchwork a lot faster by looking at what database queries were being generated and optimizing them either by making Django produce better queries or by adding better indexes. Introduction to Patchwork One of the key bits of infrastructure a bunch of maintainers of Open Source Software use\u2026","rel":"","context":"In "code"","block_context":{"text":"code","link":"https:\/\/www.flamingspork.com\/blog\/category\/code\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.flamingspork.com\/blog\/wp-content\/uploads\/2018\/08\/Screenshot-from-2018-08-13-14-32-28.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.flamingspork.com\/blog\/wp-content\/uploads\/2018\/08\/Screenshot-from-2018-08-13-14-32-28.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/www.flamingspork.com\/blog\/wp-content\/uploads\/2018\/08\/Screenshot-from-2018-08-13-14-32-28.png?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/www.flamingspork.com\/blog\/wp-content\/uploads\/2018\/08\/Screenshot-from-2018-08-13-14-32-28.png?resize=700%2C400&ssl=1 2x, https:\/\/i0.wp.com\/www.flamingspork.com\/blog\/wp-content\/uploads\/2018\/08\/Screenshot-from-2018-08-13-14-32-28.png?resize=1050%2C600&ssl=1 3x"},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/661","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/comments?post=661"}],"version-history":[{"count":0,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/661\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/media?parent=661"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/categories?post=661"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/tags?post=661"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}