PostgreSQL中关于关键字(保留字)在表名和字段名中的应用文件解决
2016-02-29 09:16 DataBases 阅读(22897) 评论(0) 编辑 收藏 举报标识符和关键词
受限标识符或被引号修饰的标识符。它是由双引号(")包围的一个任意字符序列。一个受限标识符总是一个标识符而不会是一个关键字。因此"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