PostgreSQL trigger error: 'column "t" of relation "inventory_product" does not exist'

Question

I am trying to create a trigger that decrements the qty column on the inventory_product table based on the qty that was inserted into account_sale. The trigger works fine with MySQL (with different syntax), but I'm not sure whats wrong with the PostgreSQL version.

When I run an insert on inventory_sale I get:

error: column "t" of relation "inventory_product" does not exist

the trigger:

CREATE OR REPLACE FUNCTION update_inventory()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.product_id IS NOT NULL THEN
    UPDATE inventory_product AS t
    SET t.qty = t.qty - NEW.qty #error is thrown here
    WHERE t.id = NEW.product_id;
  END IF;
END; $$ LANGUAGE 'plpgsql';

CREATE TRIGGER after_insert_account_sale
AFTER INSERT ON account_sale
FOR EACH ROW
EXECUTE PROCEDURE update_inventory();

inventory_product:

CREATE TABLE public.inventory_product
(
    id integer NOT NULL DEFAULT nextval('inventory_product_id_seq'::regclass),
    upc character varying(45) COLLATE pg_catalog."default",
    sku character varying(45) COLLATE pg_catalog."default",
    asin character varying(45) COLLATE pg_catalog."default",
    ebay_sku character varying(45) COLLATE pg_catalog."default",
    tcgplayer_sku integer,
    qty integer NOT NULL,
    opt_qty integer NOT NULL,
    reserve integer NOT NULL,
    sell_price numeric(10,2) NOT NULL,
    buy_price numeric(10,2) NOT NULL,
    product_weight_g numeric(12,5) NOT NULL,
    dim_x_cm numeric(12,5) NOT NULL,
    dim_y_cm numeric(12,5) NOT NULL,
    dim_z_cm numeric(12,5) NOT NULL,
    stock_image_path character varying(75) COLLATE pg_catalog."default",
    CONSTRAINT inventory_product_pkey PRIMARY KEY (id),
    CONSTRAINT inventory_product_asin_key UNIQUE (asin)
,
    CONSTRAINT inventory_product_ebay_sku_key UNIQUE (ebay_sku)
,
    CONSTRAINT inventory_product_stock_image_path_key UNIQUE (stock_image_path)
,
    CONSTRAINT inventory_product_tcgplayer_sku_key UNIQUE (tcgplayer_sku)

)

account_sale:

CREATE TABLE public.account_sale
(
    id integer NOT NULL DEFAULT nextval('account_sale_id_seq'::regclass),
    unit_price numeric(10,2) NOT NULL,
    qty integer NOT NULL,
    order_id integer NOT NULL,
    product_id integer,
    CONSTRAINT account_sale_pkey PRIMARY KEY (id),
CONSTRAINT account_sale_order_id_product_id_8c7f2e6a_uniq UNIQUE (order_id, product_id)
,
CONSTRAINT account_sale_order_id_7724b965_fk_account_order_id FOREIGN KEY (order_id)
        REFERENCES public.account_order (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT account_sale_product_id_716f2cb2_fk_inventory_product_id FOREIGN KEY (product_id)
        REFERENCES public.inventory_product (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED
)

insert:

INSERT INTO account_sale (qty, unit_price, order_id, product_id)
SELECT $1::integer,$2::float,$3::integer,t.id FROM inventory_product 
AS t WHERE t.ebay_sku=$4 
UNION 
SELECT $5::integer,$6::float,$7::integer,t.id FROM inventory_product 
AS t WHERE t.ebay_sku=$8

insert params:

[
2, 79.98, 167, '1',
2, 19.98, 167, '2',
2, 79.98, 168, '1',
2, 79.98, 169, '3',
2, 79.98, 170, '4'
]

Note that the insert works fine when I remove the trigger.

Also I am running the insert from a Node.js server (but I don't think that's relevant).

What am I missing here?

@a_horse_with_no_name updated

Lord Elrond
– Lord Elrond

2019-09-02 19:25:56 +00:00
Commented Sep 2, 2019 at 19:25 — Lord Elrond
– Lord Elrond, Commented Sep 2, 2019 at 19:25

user330315 · Accepted Answer · 2019-09-02 19:29:19Z

3

Don't use the target's table alias on the left hand side of the SET assignment. It's always clear which table is meant there. Btw: the function language is an identifier and should not be quoted:

CREATE OR REPLACE FUNCTION update_inventory()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.product_id IS NOT NULL THEN
    UPDATE inventory_product AS t
       SET qty = t.qty - NEW.qty
       -- ^ here
    WHERE t.id = NEW.product_id;
  END IF;
END; $$ LANGUAGE plpgsql;

In fact, you don't need any alias at all in the UPDATE statement:

CREATE OR REPLACE FUNCTION update_inventory()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.product_id IS NOT NULL THEN
    UPDATE inventory_product 
       SET qty = qty - NEW.qty
    WHERE id = NEW.product_id;
  END IF;
END; $$ LANGUAGE plpgsql;

answered Sep 2, 2019 at 19:29

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL trigger error: 'column "t" of relation "inventory_product" does not exist'

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related