PostgreSQL中关于关键字(保留字)在表名和字段名中的应用文件解决

标识符和关键词

受限标识符或被引号修饰的标识符。它是由双引号（"）包围的一个任意字符序列。一个受限标识符总是一个标识符而不会是一个关键字。因此"select"可以用于引用一个名为“select”的列或者表，而一个没有引号修饰的select则会被当作一个关键词，从而在本应使用表或列名的地方引起解析错误。在上例中使用受限标识符的例子如下：UPDATE "my_table" SET "a" = 5;

在PostgreSQL关系型数据库中存在关键字的使用的问题，例如user 做表名,create table user (id int, name,varchar(20));创建的时候需要给表名user加上双引号"user";

[postgres@node1 bin]$ ./psql
psql (9.5.1)
Type "help" for help.

postgres=# create table user(id int,name varchar(20));
ERROR: syntax error at or near "user" at character 14
STATEMENT: create table user(id int,name varchar(20));
ERROR: syntax error at or near "user"
LINE 1: create table user(id int,name varchar(20));

postgres=# create table "user"(id int,name varchar(20));
CREATE TABLE

postgres=# insert into user values (1,'PostgreSQL');
ERROR: syntax error at or near "user" at character 13
STATEMENT: insert into user values (1,'PostgreSQL');
ERROR: syntax error at or near "user"
LINE 1: insert into user values (1,'PostgreSQL');

postgres=# insert into "user" values (1,'PostgreSQL');
INSERT 0 1

postgres=# insert into "user" values (1,'PostgreSQL');
INSERT 0 1
postgres=# select * from user;
current_user
--------------
postgres
(1 row)

postgres=# select * from "user";
id | name
----+------------
1 | PostgreSQL
(1 row)

postgres=# create table user_info(order int,name varchar(20));
ERROR: syntax error at or near "order" at character 24
STATEMENT: create table user_info(order int,name varchar(20));
ERROR: syntax error at or near "order"
LINE 1: create table user_info(order int,name varchar(20));

postgres=# create table user_info("order" int,name varchar(20));
CREATE TABLE

postgres=# insert into user_info values (1,'PostgreSQL');
INSERT 0 1

表中字段大小写问题

hotel_db_p7=# d hotel_db_p.gt10_business_area;
Table "hotel_db_p.gt10_business_area"
Column | Type | Modifiers
----------+-----------------------+-----------
Code | character varying(20) | not null
CityCode | character varying(10) |
Name | character varying(50) |
hot_flag | character(1) |
Indexes:
"gt10_business_area_pkey" PRIMARY KEY, btree ("Code")

hotel_db_p7=# select CityCode from hotel_db_p.gt10_business_area;
ERROR: column "citycode" does not exist
LINE 1: select CityCode from hotel_db_p.gt10_business_area;
^
HINT: Perhaps you meant to reference the column "gt10_business_area.CityCode".

hotel_db_p7=# select "CityCode" from hotel_db_p.gt10_business_area;
CityCode
----------
1101
1101
1101
1101
1101
1101
1101
1101
1101
1101
1101
1101
1101
1101
1101