Search Results
| Search type | Search syntax |
|---|---|
| Tags | [tag] |
| Exact | "words here" |
| Author |
user:1234 user:me (yours) |
| Score |
score:3 (3+) score:0 (none) |
| Answers |
answers:3 (3+) answers:0 (none) isaccepted:yes hasaccepted:no inquestion:1234 |
| Views | views:250 |
| Code | code:"if (foo != bar)" |
| Sections |
title:apples body:"apples oranges" |
| URL | url:"*.example.com" |
| Saves | in:saves |
| Status |
closed:yes duplicate:no migrated:no wiki:no |
| Types |
is:question is:answer |
| Exclude |
-[tag] -apples |
| For more details on advanced search visit our help page | |
Results tagged with delete
Search options answers only
not deleted
user 3684
In the database structured query language (SQL), the DELETE statement removes one or more records from a table.
3
votes
Accepted
Copying Data into another table and removing older data should I do in batches and should I ...
Outgoing FK constraints in table game don't matter, the don't impose any additional work for DELETE. … Best way to delete millions of rows by ID
Basics in this related chapter of the manual: Populating a Database …
- 182k
1
vote
PostgreSQL: Delete Multiple Rows with multiple Conditions from the same table
Whether you DELETE and INSERT, or UPDATE (UPSERT), you produce a lot of dead tuples either way. So make sure to have appropriate autovacuum settings for the table. …
- 182k
1
vote
Accepted
Query between tables to delete similar records from another table
.*
FROM products01 t1
JOIN products02 t2 ON left(t1.volumeId, 10) = t2.volumeId;
To delete:
DELETE FROM products01 t1
USING products02 t2
WHERE left(t1.volumeId, 10) = t2.volumeId;
You can use … Now you can either select the whole statement to select or starting with DELETE to delete. See:
What does [FROM x, y] mean in Postgres?
The manual on DELETE …
- 182k
2
votes
How to remove partial duplicates from a table
For the information given, assuming all columns NOT NULL:
DELETE FROM pages p
WHERE label = '2018-12-15'
AND EXISTS (
SELECT FROM page
WHERE label = p.label
AND id_site = p.id_site … AND create_date > p.create_date
);
The logic, in plain English:
Delete rows with a given label where a row with the same label and id_site but later create_date exists. …
- 182k
2
votes
Accepted
Approaches for deleting unnecessary records from tables
You can always just use a plain DELETE. Considerably slower with big tables, but concurrent INSERT is not blocked at all. The autovacuuming daemon will have to do some more work, too. … SQL
The DELETE command for the slow and sure method could look like this:
DELETE FROM TABLE tbl t
USING (
SELECT created_at FROM tbl WHERE created_at < now() - (interval '3 month')
) d
LEFT JOIN …
- 182k
0
votes
Deleting rows from child table without deleting from parent
You got that backwards, the ONLY keyword has no effect here (is just noise):
DELETE FROM ONLY earthquakes ... … The manual on DELETE:
If ONLY is specified before the table name, matching rows are deleted from the named table only. …
- 182k
25
votes
Accepted
Automatic aging-out (deletion) of old records in Postgres
There is no feature built in to delete rows automatically on a time-based regime (that I would know of). … You could run a daily (you decide) cron-job to schedule simple DELETE commands or use pgAgent for the purpose.
Or you could use partitioning with weekly partitions. …
- 182k
4
votes
Get a real query plan for EXPLAIN DELETE
Running the actual DELETE query (with EXPLAIN ANALYZE wrapper or not) is considerably more expensive than checking with a SELECT whether any FK reference will prevent the operation. … DELETE will fail.
false ... No references. DELETE will succeed.
With concurrent write access, there is a possible race condition. Probably unimportant for your case. …
- 182k
14
votes
Accepted
How to delete rows from a table when we're joining and return data?
Very simple indeed, but you do need to include the other WHERE clause as well:
DELETE FROM batch bp
USING sender_log sl
WHERE bp.log_id = sl.id
AND bp.protocol = 'someprotocol'
RETURNING bp.*, sl …
- 182k
0
votes
Accepted
Delete old rows from a big table, keeping one per given time interval
See:
PostgreSQL Division In Query Not Working
Truncate timestamp to arbitrary intervals
The main DELETE removes all rows before the cut-off that aren't keepers, pinned down with NOT EXISTS. … It will be faster to just delete all of them and re-insert the few "keepers":
WITH del AS (
DELETE FROM "andaz-rkugf" a
WHERE snapshot_timestamp <= '2018-10-31'
RETURNING *
)
INSERT INTO " …
- 182k
45
votes
Accepted
DELETE rows which are not referenced in other table
Quoting the manual:
There are two ways to delete rows in a table using information
contained in other tables in the database: using sub-selects, or
specifying additional tables in the USING clause … Select rows which are not present in other table
... a NOT EXISTS anti-semi-join is probably simplest and most efficient for DELETE:
DELETE FROM link_group lg
WHERE NOT EXISTS (
SELECT FROM link_reply …
- 182k
3
votes
Accepted
How can I efficiently delete records when a counter reaches zero in PostgreSQL?
Targeting a single row, this avoids an "unneeded record change", i.e. writing a new row version without need:
DO
$$
BEGIN
DELETE FROM tbl WHERE … AND counter = 1; -- common case first! … You can avoid writing rows without need (like UPDATE + DELETE) in any case, though. …
- 182k
1
vote
Accepted
SELECT / DELETE rows where consecutive null value count is greater than N
To delete:
DELETE FROM test t
USING ( -- same as above
SELECT *, count(*) OVER (PARTITION BY receiver, grp) AS grp_count
FROM (
SELECT *, count(recv_time) OVER (PARTITION BY receiver ORDER …
- 182k
5
votes
Accepted
Cascade deletes from multiple tables
Some clarifications:
REFERENCES is the key word used for a FOREIGN KEY constraint (which allows to cascade DELETE or UPDATE). … Then you can have a FK constraint on each with ON DELETE CASCADE.
If you still need your current design, I have two ideas:
1. …
- 182k
3
votes
Accepted
Delete duplicate records with no change in between
Also pay special attention to avoid deadlocks and race conditions with concurrent deletes and inserts (which can affect which rows to delete!) … Since you have concurrent deletes it may be a good idea to run a separate delete per vendor to reduce the potential for race conditions and deadlocks. …
- 182k