Using IF Exists function in postgreSQL pgadmin3 (syntax error ,SQL state: 42601 Character: 1)

Question

I am trying to execute an SQL statement in pgadmin3 that do the following:

If a student with a specific name and age already exists then get the student_id else insert a new record with the specified name and age and then get the created student_id

I have tried this code:

 IF EXISTS (SELECT 1 FROM main.student WHERE studentname='hhh' and  studentage=15) 
   BEGIN
       SELECT student_id FROM main.student WHERE studentname='hhh' and   studentage=15
  END
 ELSE
   BEGIN
 INSERT INTO main.student(studentname,studentage) VALUES('hhh',15)
   END;
   END IF; 

But I am always getting this error:

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

Can you please tell me what I am doing wrong. Also how can I get the student_id after the insert statement?

Answer 1

Actually your statement is not SQL. it is PL/PGSQL statement.

So obviously when you send this to Postgres as SQL query it will throw an exception.

If you are doing this from pgAdmin3 it has nice feature to run pl/psql scripts. use it

Answer 2

IF EXISTS (SELECT 1 FROM main.student WHERE studentname='hhh' and studentage=15) THEN
    SELECT student_id FROM main.student WHERE studentname='hhh' and studentage=15;
ELSE
    INSERT INTO main.student(studentname,studentage) VALUES('hhh',15);
END IF;

Some points you need to consider:

• For IF statement, you need to use THEN
• To run just one syntax, you don't need the BEGIN/END
• Use ; at the end of each statement

Also if you are running an ad-hoc statement, you need to run it within DO command

DO
$do$
IF EXISTS (SELECT 1 FROM main.student WHERE studentname='hhh' and studentage=15) THEN
    SELECT student_id FROM main.student WHERE studentname='hhh' and studentage=15;
ELSE
    INSERT INTO main.student(studentname,studentage) VALUES('hhh',15);
END IF;
END
$do$

For the last part of your question, you can return the id you are inserting

INSERT INTO main.student(studentname,studentage) VALUES('hhh',15) RETURNING student_id