Debian Question posted in
The oficial documentation can be found here.

Postgres syntax error while using insert on conflict – Debian

Given a table

CREATE TABLE balances
(
    username   varchar(255)     NOT NULL,
    currency   varchar(255)     NOT NULL,
    balance    numeric          NULL,
    CONSTRAINT balances_pkey PRIMARY KEY (username, currency)
);

After attempting

CREATE OR REPLACE FUNCTION merge_balance(username varchar(255), currency varchar(255), to_add numeric) RETURNS void
AS $$ BEGIN
    INSERT INTO balances(username, currency, to_add)
    ON CONFLICT balances_pkey DO UPDATE SET balance = OLD.balance + to_add
END; $$ LANGUAGE plpgsql;

I get

ERROR:  syntax error at or near "ON"

My PostgreSQL version (running in a container postgres:11.5)

SELECT version();
                                                             version
----------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 11.5 (Debian 11.5-3.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit
(1 row)

Those ;,$ things are so annoying and now I get this one =_=
Has anybody solved such a problem? Thanks!

Tags:

3

Answers


  1. 0

    Shouldn’t it be

    INSERT INTO balances VALUES(username, currency, to_add)
   ON CONFLICT balances_pkey DO UPDATE SET balance = OLD.balance + to_add
    Login or Signup to reply.
  2. 0

    Consider:

    create or replace function merge_balance(
    p_username varchar(255), 
    p_currency varchar(255), 
    p_to_add numeric
) returns void
as $$
begin
    insert into balances (username, currency, balance)
    values(p_username, p_currency, p_to_add)
    on conflict (username, currency) do update set balance = old.balance + p_to_add;
end; $$ language plpgsql;

    Rationale:

    • you need a VALUES() clause to enumerate the values to insert; it is also a good practice to surround list the target columns – for this, you would better not have function arguments that have the same name as the table columns

    • the conflict target must be surrounded with parentheses – you can use a constraint name, but I find it is clearer to use the column names

    Login or Signup to reply.
  3. 0

    You cannot use "old" as you are attempting. What you are doing is referencing the pseudo row OLD generated by DML statements for triggers. Even if could reference it would be NULL as all old columns are null on insert. This is not what you want the result would be null. But you do need to alias the table to avoid ambiguity on the update clause. Also it is not necessary to specify limits on the function header. Try:

    create or replace 
function merge_balance(
         p_username varchar  
       , p_currency varchar  
       , p_to_add   numeric
       )
  returns  void
  language sql
as $$
    insert into balances as bal (username, currency, balance)
    values (p_username, p_currency, p_to_add)
        on conflict (username, currency) 
        do update 
           set balance = bal.balance + excluded.balance;
$$;

    See full example here.

    As an after thought I guess you could use old.balance by using old as the table alias.

    insert into balances as old (username, currency, balance) ...

    But that doesn’t seem like a good idea, at least to me.,

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search