Postgres syntax error at or near "IF"

Question

I am new to postgres and I am working on an assignment of mine. I had to create a table with only 1 column and, then I was given this statement to run in on pgadmin III:

BEGIN;
INSERT INTO mytable VALUES (1);
SAVEPOINT savepoint1;
INSERT INTO mytable VALUES (2);
ROLLBACK TO SAVEPOINT savepoint1;
INSERT INTO mytable VALUES (3);
SAVEPOINT savepoint2;
INSERT INTO mytable VALUES (4);
INSERT INTO mytable VALUES (5);
SAVEPOINT savepoint3;
SELECT * FROM mytable;
--NOTE: You need to run this IF statement as PGScript 
--(button next to the normal run button)
IF (CAST ((SELECT MAX(id) FROM mytable) AS INTEGER) = 4)
BEGIN
      RELEASE SAVEPOINT savepoint2;
END
ELSE
BEGIN
      INSERT INTO mytable VALUES(6);
END
--Run the next steps normally
SAVEPOINT savepoint2;
INSERT INTO mytable VALUES (7);
RELEASE SAVEPOINT savepoint2;
INSERT INTO mytable VALUES (8);
ROLLBACK TO savepoint2;
COMMIT;

when I run this I get this error: syntax error at or near "IF"

I have already take a look at this 38.6.2. Conditionals38.6.2. Conditionals , I dont understand this very well, Do I need to change the query to have

IF (CAST ((SELECT MAX(id) FROM mytable) AS INTEGER) = 4) THEN 
BEGiN

and then when it ends I should end it with:

END IF

Why there is an error after all??

You're missing THEN... It's IF ... THEN ... ELSE .... You've got IF ... ... ELSE. — Marc B
– Marc B, Commented Jan 6, 2014 at 19:05

mu is too short · Accepted Answer · 2014-01-06 19:28:25Z

84

IF and other PL/pgSQL features are only available inside PL/pgSQL functions. You need to wrap your code in a function if you want to use IF. If you're using 9.0+ then you can do use DO to write an inline function:

do $$
begin
  -- code goes here
end
$$

If you're using an earlier version of PostgreSQL then you'll have to write a named function which contains your code and then execute that function.

answered Jan 6, 2014 at 19:28

mu is too short

436k71 gold badges863 silver badges822 bronze badges

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

1 Comment

Travis Over a year ago

It wouldn't work in this particular instance, but it's worth mentioning that CASE behaves like IF and is available outside of PL/pgSQL functions. - Just something to consider in these situations.

nozem · Accepted Answer · 2022-11-08 12:52:38Z

7

Not the answer for the OP, but possibly the answer for some who end up here (like myself): If you DECLARE variables within the BEGIN-END block, you will get the same syntax error.

So this is wrong:

DO $$
BEGIN
  DECLARE my_var VARCHAR(50) := 'foo';
  IF my_var IS NULL THEN
     --some logic
  END IF;
END; 
$$

This should fix it:

DO $$
DECLARE my_var VARCHAR(50) := 'foo';
BEGIN
  IF my_var IS NULL THEN
     --some logic
  END IF;
END; 
$$

edited Nov 8, 2022 at 12:52

answered Jan 2, 2020 at 13:51

nozem

5068 silver badges11 bronze badges

1 Comment

G. Goncharov Over a year ago

Thanks, it helped me! But how can I learn that... Where did you get it?)

Collectives™ on Stack Overflow

Postgres syntax error at or near "IF"

2 Answers 2

1 Comment

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Linked

Related