3

I'm trying to add a column named order to my table. I realize that order is a reserved word in SQL. So, how do I do it? My command:

   alter table mytable add column order integer;

I've also tried:

   alter table mytable add column 'order' integer;

PostgreSQL 9.1.

ed_is_my_name
  • 601
  • 3
  • 9
  • 24
  • `'order'` is a string constant not a column name. See the manual for details: http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS –  Apr 15 '14 at 19:17
  • *Never* use [reserved words](http://www.postgresql.org/docs/current/interactive/sql-keywords-appendix.html) as identifiers. Only use legal, lower-case names in Postgres and live happily ever after (without the need for double-quoting). – Erwin Brandstetter Apr 15 '14 at 19:45
  • 1
    May I suggest "display_order" or "ordinality" as the column name? – Neil McGuigan Apr 15 '14 at 19:50

4 Answers4

6

Use this:

alter table mytable add column "order" integer;

But, you might want to consider using a non-reserved name instead, like sort_order or something similar that reflects what the column is used for (and isn't a reserved word).

jpw
  • 44,361
  • 6
  • 66
  • 86
  • Thanks. This one worked for me. StackOverflow wants me to wait for 9 minutes before I can resolve this. – ed_is_my_name Apr 15 '14 at 19:15
  • 1
    Huh, just realized that it is probably a bad idea to name columns after reserved words...maybe I'll rethink this. – ed_is_my_name Apr 15 '14 at 19:16
  • 2
    @user1344643 Indeed it is a bad practice to use reserved keywords - just don't do it and you'll save yourself a lot of headache at some point :) – jpw Apr 15 '14 at 19:17
0

I think you don't need "column". Plus "order" is a keyword in SQL, so you should use a different name for your column. Follow this syntax:

ALTER TABLE table_name ADD column_name datatype

Source: W3Schools

DerStrom8
  • 1,311
  • 2
  • 23
  • 45
0

You are using order which is a reserved keyword you should consider renaming that to something like orders. And the problem should go away.

David Innocent
  • 606
  • 5
  • 16
-1
ALTER TABLE table_name
ADD COLUMN "order" integer
DidIReallyWriteThat
  • 1,033
  • 1
  • 10
  • 39