postgresql约束(字段a>字段b)

Question

我正在对我们的讲师给我们的数据做一份乏味的演讲计划。我使用SQL构建表，而不是(PgAdminIII的)图形用户界面。

我有两个：

12   MAX_ORDER_AMT Curr       8      4    max_order_amt > min_order_amt
13   MIN_ORDER_AMT.Curr       8      4    max_order_amt > min_order_amt
...
...

为了创建它，我使用了部分代码

"Max_Order_Amt" money,
"Min_Order_Amt" money,
...
...

为了添加Max_Order_amt > min_order_amt约束，我必须使用什么？

我尝试使用"max_order_amt > min_order_amt“，但失败了。

非常感谢你的帮助

使用的语言： PostegreSQL

图形用户界面 PgAdminIII

编辑:这是完整的创建表代码

CREATE TABLE oltp.customer
("Customer_ID" character (6) NOT NULL,
"Company_Name" character (40) NOT NULL,
"Contact_Name" character (30) NOT NULL,
"Address" character (60),
"City" character (15),
"Region" character(15),
"Postal_Code" character(10),
"Phone" character(24),
"Fax" character(24),
"Max_Order_Amt" money CHECK (Max_Order_Amt > Min_Order_Amt),
"Min_Order_Amt" money CHECK (Max_Order_Amt > Min_Order_Amt),
"Discount" numeric(2) CHECK (Discount >=0),
"Sales_Region" character(4),
CONSTRAINT customers_pkey PRIMARY KEY ("Customer_ID"))
WITH 
( OIDS = FALSE);
alter table oltp.customer
OWNER to postgres;

ERROR:  column "max_order_amt" does not exist

********** Error **********

ERROR: column "max_order_amt" does not exist
SQL state: 42703

下面是Tastrade

CUSTOMER
(Customer Information)
Beware of updating or deleting instances from this table as other tables refer to this table’s PK
Number of data records:       92      
Fd   Field Name    Type   Width    Dec   Notes
 1   CUSTOMER_ID   Char       6          PK, system generated, not null
 2   COMPANY_NAME  Char      40          not null
 3   CONTACT_NAME  Char      30          not null
 4   CONTACT_TITLE Char      40          
 5   ADDRESS       Char      60          
 6   CITY          Char      15          
 7   REGION        Char      15          
 8   POSTAL_CODE   Char      10          
 9   COUNTRY       Char      15          
10   PHONE         Char      24          
11   FAX           Char      24          
12   MAX_ORDER_AMT Curr       8      4    max_order_amt > min_order_amt
13   MIN_ORDER_AMT.Curr       8      4    max_order_amt > min_order_amt
14   DISCOUNT      Nume       2           discount >= 0
15   SALES_REGION  Char       4          
** Total **                       302   

在tastarade模式 Lines中，12-14给出了“不存在”错误。我遵守了8.1手册。它停止在Max_Order_Amt，但当我尝试其他，它给了我同样的错误

再来一次

Answer 1

在创建表时使用check约束，如

"Max_Order_Amt" money CHECK (Max_Order_Amt > Min_Order_Amt)

请参阅有关Check约束的更多信息，此处为http://www.postgresql.org/docs/8.1/static/ddl-constraints.html

编辑：

删除列名周围的"双引号。试着像下面这样

CREATE TABLE customer
(Customer_ID character (6) NOT NULL,
Company_Name character (40) NOT NULL,
Contact_Name character (30) NOT NULL,
Address character (60),
City character (15),
Region character(15),
Postal_Code character(10),
Phone character(24),
Fax character(24),
Max_Order_Amt money CHECK (Max_Order_Amt > Min_Order_Amt),
Min_Order_Amt money,
Discount numeric(2) CHECK (Discount >=0),
Sales_Region character(4),
 CONSTRAINT customers_pkey PRIMARY KEY (Customer_ID));

参见这里的演示小提琴，http://sqlfiddle.com/#!15/60767

EDIT1:

如果您确实希望将"保留在列名(出于某种原因)上，那么将检查约束设置为如下所示

"Max_Order_Amt" money CHECK ("Max_Order_Amt" > "Min_Order_Amt"),
"Min_Order_Amt" money CHECK ("Max_Order_Amt" > "Min_Order_Amt"),
"Discount" numeric(2) CHECK ("Discount" >=0),