1

Trying to create this table in PGAdmin I get the following error message:

ERROR:  syntax error at or near "order"
LINE 8:   order VARCHAR(50),
          ^
SQL state: 42601
Character: 169

CREATE TABLE plantedata (
  gbifID INTEGER,
  datasetKey VARCHAR(200),
  occurrenceID VARCHAR(300),
  kingdom VARCHAR(50),
  phylum VARCHAR(50),
  class VARCHAR(50),
  order VARCHAR(50),
  family VARCHAR(50),
  genus VARCHAR(50),
  species VARCHAR(100),
  infraspecificEpithet VARCHAR(100),
  taxonRank VARCHAR(50),
  scientificName VARCHAR(200),
  verbatimScientificName VARCHAR(200),
  verbatimScientificNameAuthorship VARCHAR(200),
  countryCode VARCHAR(50),
  locality VARCHAR(200),
  stateProvince VARCHAR(100),
  occurrenceStatus VARCHAR(200),
  individualCount VARCHAR(100),
  publishingOrgKey VARCHAR(100),
  decimalLatitude NUMERIC(8,6),
  decimalLongitude NUMERIC(8,6),
  coordinateUncertaintyInMeters VARCHAR(100),
  coordinatePrecision VARCHAR(100),
  elevation VARCHAR(100),
  elevationAccuracy VARCHAR(100),
  depth VARCHAR(100),
  depthAccuracy VARCHAR(100),
  eventDate VARCHAR(100),
  day VARCHAR(100),
  month VARCHAR(100),
  year VARCHAR(100),
  taxonKey VARCHAR(100),
  speciesKey VARCHAR(100),
  basisOfRecord VARCHAR(100),
  institutionCode VARCHAR(100),
  collectionCode VARCHAR(100),
  catalogNumber VARCHAR(100),
  recordNumber VARCHAR(100),
  identifiedBy VARCHAR(100),
  dateIdentified VARCHAR(100),
  license VARCHAR(100),
  rightsHolder VARCHAR(100),
  recordedBy VARCHAR(100),
  typeStatus VARCHAR(100),
  establishmentMeans VARCHAR(100),
  lastInterpreted VARCHAR(100),
  mediaType VARCHAR(100),
  issue VARCHAR(100)
);
Adrian Klaver
  • 15,886
  • 2
  • 17
  • 28
Horst
  • 11
  • 1
  • `order` is a reserved word, see here [Key Words](https://www.postgresql.org/docs/current/sql-keywords-appendix.html). Best idea is to not use it. If you do then you need to double quote it e.g. "order". – Adrian Klaver Oct 06 '21 at 16:04
  • `order` is a reserved keyword. You need to quote it `"order"` or better find a different name –  Oct 06 '21 at 16:04
  • I'm voting to close this as a duplicate, even though the duplicate question is about MySQL. But the problem is the same: keywords are reserved, and you must delimit them. The linked question explains it very well. But PostgreSQL uses double-quotes `""` as identifier delimiters, intead of back-ticks as MySQL does. – Bill Karwin Oct 06 '21 at 16:05
  • Forgot to add, the real problem is that you will have to double quote it every time you use it. – Adrian Klaver Oct 06 '21 at 16:31

0 Answers0