Search code examples
sqldatabasepostgresqlwhile-loopsyntax-error

How to execute WHILE LOOP in SQL (PostgreSQL/pgAdmin 4)?

I am trying to run this simple WHILE loop in pgAdmin's query tool:

DECLARE @counter INT = 1;
    
WHILE @counter <= 5
BEGIN
  PRINT @counter;
  SET @counter = @counter + 1;
END

The desired result is just a list from 1 to 5, but my pgAdmin is returning this error:

ERROR: syntax error at or near "@"
LINE 1: DECLARE @counter INT = 1;

I'm an undergrad in a non-IT course, so I hope you consider this in your explanations. Thank you! (code source)

Solution

  • Your example is from SQL Server, not PostgreSQL. You should try something like this:

    do $$
declare 
   counter integer := 0;
begin
   while counter < 5 loop
      raise notice 'Counter %', counter;
      counter := counter + 1;
   end loop;
end$$;