2

I have an issue with the following script:

CREATE DOMAIN shop.amount AS numeric(8,2) DEFAULT 0 NOT NULL CHECK (value > 0::numeric);

CREATE TYPE shop.money AS (
   m_amount shop.amount,
   m_currency shop.currency
 );


CREATE TABLE shop.order_item
 (
   oi_id bigserial NOT NULL,
   oi_order_id bigint NOT NULL,
   oi_article_id bigint NOT NULL,
   oi_article_price shop.money NOT NULL,
   CONSTRAINT oi_pk_id PRIMARY KEY (oi_id),
   CONSTRAINT oi_fk_article_id FOREIGN KEY (oi_article_id)
       REFERENCES shop.article (a_id) MATCH SIMPLE
       ON UPDATE NO ACTION ON DELETE NO ACTION,
   CONSTRAINT oi_pk_order_id FOREIGN KEY (oi_order_id)
       REFERENCES shop."order" (o_id) MATCH SIMPLE
       ON UPDATE NO ACTION ON DELETE NO ACTION,
   CONSTRAINT price_cannot_be_less_than_ten CHECK (oi_article_price.m_amount >= 10::numeric)
 );

When I execute the script using PGAdmin I get

ERROR:  missing FROM-clause entry for table "oi_article_price"

I guess that the error is related to the constraint, somehow. What's wrong?

3

You need to put the domain name in parentheses:

CONSTRAINT price_cannot_be_less_than_ten 
  CHECK ( (oi_article_price).m_amount >= 10)
  • True, only this! Can you explain why the parenthesis is neeed? – mat_boy Sep 12 '16 at 19:34
  • 2
    @mat_boy: To avoid ambiguity in the syntax. As you can see from the error message, Postgres expects the identifier to be a table without parentheses. Read the manual here: postgresql.org/docs/current/static/… – Erwin Brandstetter Sep 13 '16 at 0:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.