All Questions
Tagged with truncate postgresql
15 questions
0
votes
1
answer
83
views
Truncate partitions with foreign key to a different partitioned table
I am using PostgreSQL with two tables, called records and flags, that are partitioned exactly the same -- by range. I want to truncate old partitions in both.
We use pg_partman, and they are created ...
1
vote
1
answer
4k
views
What permissions are needed to truncate a table in PostgreSQL?
I’ve just started with a web host which supports PostgreSQL.
When setting up a database user, I have the choice of the following privileges:
■ ALTER ■ CREATE ■ DELETE
■ EXECUTE ■ DROP ■ EVENT
■ INDEX ■...
- 3,177
2
votes
1
answer
1k
views
How to disable TRUNCATE on whole database or for particular table?
Is it possible to disable TRUNCATE operation on the entire PostgreSQL database, or on a specific table?
- 121
3
votes
2
answers
6k
views
Add foreign key constraint to an existing table column, referencing a column in a newly created table
Let's say I have one table defined as this
CREATE TABLE sales (
agent_id integer references agents(agent_id),
sale_date date,
amout numeric(10,2)
);
Then an ETL process fills this ...
5
votes
3
answers
14k
views
Speed difference between Drop table and Truncate table in Postgres
I currently have a program that inserts into a database by creating temp tables, filling the tables, then merging that data into the main tables. Then dropping the tables and doing it all again.
I'm ...
- 150
7
votes
2
answers
3k
views
Foreign Keys with ON DELETE SET NULL are still deleted when TRUNCATE CASCADE is called on the foreign table in Postgres
Maybe I am missing something here:
CREATE TABLE public.example_table (
id integer UNIQUE
);
CREATE TABLE public.foreign_table (
id integer,
example_table_id integer,
CONSTRAINT ...
- 173
45
votes
1
answer
94k
views
PostgreSQL - Truncate a table on cascade and reset all hierarchic sequences with 1
Is there any way to reset all the sequences of tables, when truncate a table on cascade.
I already read this post How to reset sequence in postgres and fill id column with new data?
ALTER SEQUENCE ...
- 553
25
votes
2
answers
25k
views
Postgres suggests "Truncate table ... at the same time"
When running:
TRUNCATE TABLE YYYYY RESTART IDENTITY
I am seeing this error message:
ERROR: cannot truncate a table referenced in a foreign key constraint
DETAIL: Table "XXXXX" references &...
- 953
2
votes
2
answers
3k
views
External size of table still not released (as disk space) after issuing truncate command
In PostgreSQL 9.3.10 (Ubuntu 14.04 OS), I have a table called groupseen whose size is as follows:
Table | Size | External Size
----------------------------------+------...
- 2,079
6
votes
1
answer
17k
views
Releasing disk space by using DELETE or TRUNCATE?
More than a week ago, I deleted all the rows in a postgresql table (not via truncate, instead with delete from ...). Select count (*) reveals table rows are now 0.
When I now run queries to query ...
- 2,079
7
votes
1
answer
3k
views
Truncate parent table but not children
I have a table in PostgreSQL 9.4 that I partitioned so that all inserts to the parent actually go into one of the child partitions. The problem is that I already have hundreds of millions of rows of ...
- 345
4
votes
2
answers
5k
views
Is there a way to "SELECT and TRUNCATE" without losing new data?
I have an application that writes raw log files to a table called new in a PostgreSQL database -- lots of rows and lots of duplicate data. Via a cron job running every minute, I normalize the raw data ...
- 417
1
vote
2
answers
2k
views
Approaches for deleting unnecessary records from tables
We have a database which stores the temperatures of 20 thermometer every 7 seconds. We want to delete all records in the way that every minute holds just one tempereature instead of 8 which are older ...
- 155
3
votes
1
answer
3k
views
"Truncate" or "Delete/Vacuum Full" for deleting some of the table rows
I have a db which has 223 tables and I have to delete some of the records from 10 of them, each has apprx. 1.5million records. Those tables are storing the temperatures every 7seconds. We have decided ...
- 155
16
votes
1
answer
8k
views
PostgreSQL: Disk space not released after TRUNCATE
I haveTRUNCATEd a huge (~120Gb) table called files:
TRUNCATE files;
VACUUM FULL files;
The table size is 0, but no disk space was released. Any ideas how to reclaim my lost disk space?
UPDATE:
The ...
- 12.1k