Syntax error at or near "IF" PostgreSQL

Question

I am trying to use subselect in my code but it gives me an error. I have found a lot of solution but it still does not work:

Here is my code:

BEGIN;
IF (SELECT COUNT(*) FROM table1 WHERE Z = 'aaaaa') = 0

    THEN
    INSERT INTO table2 (X, Y) VALUES ("abc", 7)

END IF;
END;

and error:

ERROR:  syntax error at or near "IF"
LINE 2: IF (SELECT COUNT(*) FROM table1 WHERE Path = 'aaaaa') = 0
        ^


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

ERROR: syntax error at or near "IF"
SQL state: 42601
Character: 8

Could you help me?

For anybody else reading this later, note that the poster has omitted the surrounding DO block or function definition. You cannot run anonymous PL/PgSQL blocks in PostgreSQL, they must be surrounded by a DO or CREATE [OR REPLACE] FUNCTION with a LANGUAGE clause. — Craig Ringer
– Craig Ringer, Commented Sep 4, 2012 at 13:22

araqnid · Accepted Answer · 2012-09-04 10:17:29Z

11

Drop the semicolon after "BEGIN"? That's if this is the body of a plpgsql function.

If this is a psql script, the IF statement needs to be given to plpgsql to execute, so it needs putting in a DO $$ ... $$ construct.

Or, of course, you could refactor like so:

INSERT INTO table2(x,y)
    SELECT 'abc', 7
    WHERE NOT EXISTS (SELECT 1 FROM table1 WHERE z = 'aaaaa')

answered Sep 4, 2012 at 10:17

araqnid

135k25 gold badges164 silver badges139 bronze badges

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

5 Comments

javaGirl Over a year ago

Oh, it looks like a solution..but I do not understand how it works...I want to check if record exists, if yes -do nothing, if no insert record - how it works your INSERT code ?

araqnid Over a year ago

INSERT does not have to have a fixed set of values, it can take as input any SELECT. So the SELECT part you can run on its own, and it produces ('abc', 7) iff the row does not exist. This result can then be given to the INSERT.

javaGirl Over a year ago

Hmmmm, so let's assume I have got: INSERT INTO Table (X, Y) Values (A, B) but only in case when value B does not exist in Y column. How will be look all code?

javaGirl Over a year ago

I have got one question more...how can I use it in MySQL ?

araqnid Over a year ago

@javaGirl sorry, I don't know about mysql.

Collectives™ on Stack Overflow

Syntax error at or near "IF" PostgreSQL

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related