`ERROR: cannot execute TRUNCATE TABLE in a read-only transaction` in Heroku PostgreSQL

user_9852082 0
Viewed 2049

I am getting the ERROR: cannot execute TRUNCATE TABLE in a read-only transaction in Heroku PostgreSQL. How could I fix it?

I am trying to TRUNCATE a table.

I am using the Heroku Postgres.

enter image description here

I have tried to figure out in the UI how I could change my permissions or something similar to be allowed to run not only the read-only transactions. But with no success.

heroku-postgres heroku postgresql
2 Answers

This is currently possible, you have to set the transaction to "READ WRITE" when creating a data clip. Here is an example:

begin;
set transaction read write;

Delete FROM mytable where id > 2130;

COMMIT;
user_53721880

The feature you're looking at (Heroku Dataclips docs here) is intentionally made read-only. Its a reporting tool, not a database management tool. The express purpose is to allow surfacing data to a wider group of people associated with a project without the risk of someone accidentally (or otherwise) deleting or modifying data improperly. There is no way to make Dataclips read-write.

If you want full control to delete/modify data you'll need to use an appropriate interface, psql or pgAdmin if you prefer a GUI.

user_54734500
Related