When I insert I am getting integer out of range because my id/primary key was mistakenly created as an int instead of a bigint or bigserial. I tried:
ALTER TABLE tbl ALTER COLUMN id TYPE BIGINT;
But I get the following error because my free disk space isn't big enough.
ERROR: could not extend file "base/16401/3275205": No space left on device
HINT: Check free disk space.
SQL state: 53100
I can't increase the disk space right now, for frustrating reasons I won't go into.
I also tried reusing the ids (I delete a lot of records from this table so there are big gaps) by doing these to start my seq over:
https://dba.stackexchange.com/questions/111823/compacting-a-sequence-in-postgresql
But for solution #1 in that link:
I assume I don't have the disk space. The table is 117GB and I have about 24GB available in ...data/base. I do have 150GB available where my temp files are being stored (a different mount), which is not the default configuration, but was done so I could conserve space for database storage in ...data/base. If I could create the table in Temp file location, that might work, but I don't know how to do that.
for solution #2 in that link:
When I get to the update part, I get this in pgAdmin4:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2 Final//EN">
<title>500 Internal Server Error</title>
<h1>Internal Server Error</h1>
<p>The server encountered an internal error and was unable to complete your request. Either the server is overloaded or there is an error in the application.</p>
However the query is still running when I run:
select pid,query,state,wait_event,* from pg_stat_activity where state <> 'idle'
And I get no server log for the update query that fails.
I eventually killed that update query thinking that it would fail eventually anyway. (I am running this one again and will let it run with html error above unless someone else has a better idea.)
for solution #3 in that link: I have 16GM of RAM, so not enough.
Next from here: How to reset sequence in postgres and fill id column with new data?
I tried this:
UPDATE table SET id = DEFAULT;
ALTER SEQUENCE seq RESTART;
UPDATE table SET id = DEFAULT;
ERROR: integer out of range
this creates a duplicate key when you try to insert:
ALTER SEQUENCE seq RESTART WITH 1;
UPDATE t SET idcolumn=nextval('seq');
Anything else I can try?
PostgreSQL 9.6
