PostgreSQL-不带引号的查询语法 [英] PostgreSQL - query syntax without quotes

问题描述

我有一个愚蠢的问题.我已经安装了PostgreSQL数据库服务器，但是运行查询时，没有引号的列标识符存在问题.我不知道为什么需要在标识符周围加上引号.我的查询:

I have a little silly question. I have installed a PostgreSQL DB Server, but when I run query, there is a problem with column identifier without quotes. I don't know why the quotes around identifiers are needed. My query:

SELECT vc."CAR_ID"
  FROM "VEL_CAR" vc, "VEL_DRIVER" vd, "VEL_DRIVER_CAR" vdc
WHERE vc."CAR_ID" = vdc."CAR_ID" and
      vdc."DRIVER_ID" = vd."DRIVER_ID";

我在Oracle DB中的做法是不使用.因此在Oracle中:

My practice from Oracle DB is not to use ". So in Oracle:

SELECT vc.CAR_ID
  FROM VEL_CAR vc, VEL_DRIVER vd, VEL_DRIVER_CAR vdc
WHERE vc.CAR_ID = vdc.CAR_ID and
      vdc.DRIVER_ID = vd.DRIVER_ID;

当我在PostgreSQL中运行不带引号的查询时，会引发语法错误:

When I run this query without quotes in PostgreSQL it throws error about syntax:

ERROR:  column vc.car_id does not exist
LINE 1: SELECT vc.CAR_ID

你知道为什么吗?

-已解决- 谢谢，现在我解决了问题！这是关于表创建的.我使用pgAdminIII创建了表对象，并以大写形式写了表名和列名. pgAdminIII使用配额创建了查询-因为名称是大写的.因此查询必须写有配额.

--SOLVED-- Thank you, now I solved the problem! It was about table creation. I created table objects using pgAdminIII and i wrote table name and column names uppercased. pgAdminIII created query with quotas - because of the names was uppercased. So query had to be written with quotas.

推荐答案

使用双引号创建表时，列名和表名区分大小写.因此"car_id"是与"CAR_ID"

When you create your tables using double quotes, column and table names become case sensitive. So "car_id" is a different name than "CAR_ID"

您需要在不使用双引号的情况下创建表，因此名称不区分大小写:car_id与CAR_ID相同(请注意缺少引号！)

You need to create your tables without using double quotes, then the names are not case sensitive: car_id is the same as CAR_ID (note the missing quotes!)

有关详细信息，请参见手册:

See the manual for details:

http://www. postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

Oracle 的行为是相同的.唯一的区别是，Oracle用大写字母存储名称，而Postgres用小写字母存储名称.但是使用引号时的行为是相同的.

Oracle behaves just the same way. The only difference is that Oracle stores names in upper case and Postgres stores them in lower case. But the behaviour when using quotes is identical.

这篇关于PostgreSQL-不带引号的查询语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！