0

I have a psql script that has lots of inserts, and I want one of the numbers I insert into a table to be relative to a specific number that I draw from a table.

I tried to do it as following:

select @max_a = max(a) + 1 from tableA;
insert into ... values (@max_a + 5)

but I get an error on the first select statement (ERROR: syntax error at or near "@").

I tried to replace it with

  set @max_a = (select max(a) + 1 from tableA);

but that didn't work either (I get ERROR: syntax error at or near "@").

Any ideas?

Tal Avissar
  • 10,088
  • 6
  • 45
  • 70
kloop
  • 4,537
  • 13
  • 42
  • 66
  • Please share your error – GavinCattell May 09 '16 at 18:27
  • Why cant you do it like this `insert into yourtable(column) select max(a) + 1 from tableA` – cableload May 09 '16 at 18:33
  • @GavinCattell done. cableload, don't want to do multiple selects like this, I want to set that value as a variable to use multiple times. – kloop May 09 '16 at 18:47
  • I've not seen that use of variables in SQL before. In the above case I'd create dynamic SQL, or use a temporary table to store the value during execution. – GavinCattell May 09 '16 at 18:59
  • 2
    If you want to reuse the variable, you probably want to consider using plpgsql The type of assignment you are looking is not available in postgresql (that is within sql) – cableload May 09 '16 at 19:05

0 Answers0