0

Why do I see the error ?

begin insert into math_qage values(20,40);insert into math_qage(30,79); end;

ERROR: syntax error at or near "insert" LINE 1: begin insert into math_qage values(20,40);

this is all I'm trying to do:

Insert multiple records at one go using begin and end o/p a single txid for all...

Also how do i add the raise notice in the following without it being errored out ?

Miss J.
  • 351
  • 1
  • 5
  • 15

2 Answers2

1

I see multiple errors... 

begin; -- semicolon here!
    insert into math_qage values(20,40); -- it's ok
    insert into math_qage values(30,79); -- forgot 'values' here!
commit; -- not 'end'

And as mentioned above you can do do it in one request even without transaction:

insert into math_qage values (20,40), (30,79);
red_led
  • 343
  • 4
  • 7
  • This works thanks! also how do I use raise notice here ? i.e RAISE NOTICE 'transaction id is :%', TXID_CURRENT(); – Miss J. Sep 09 '15 at 20:56
  • [Here](http://stackoverflow.com/questions/18828127/how-to-raise-a-notice-in-postgresql) you can find answer about notice. It can be used only inside ```PL/pgSQL``` code block; – red_led Sep 09 '15 at 21:00
  • I can't use a pl/pgsql code block unfortunately but have to retrive the transactionid – Miss J. Sep 09 '15 at 21:08
  • is there a way I can do it ? – Miss J. Sep 09 '15 at 21:18
  • 1
    Inside transaction you can just ```select txid_current();```. Not sure if it's possible in second method. Postgres gives a new transaction id for every single query outside transactions. You can try use ```returning```, but there will be as many rows in result as you have inserted. Or just combine: start transaction, insert everything in one query, get transaction id, then commit. – red_led Sep 09 '15 at 21:25
0

It's most probably because of BEGIN .. END which you can use only in a procedural body like stored procedure or stored function. Remove that begin and end keyword and you can modify your INSERT statement like below for multiple rows insertion

insert into math_qage values (20,40), (30,79);
Rahul
  • 76,197
  • 13
  • 71
  • 125