On Mon, Sep 16, 2024, at 8:55 PM, Phil Stracchino wrote: > On 9/16/24 20:34, Dan Langille wrote: >> On Mon, Sep 16, 2024, at 5:46 PM, Phil Stracchino wrote: >>> On 9/16/24 15:17, Dan Langille wrote: >>> >>> delete from version order by version limit 1; >> >> bacula=*# delete from version order by version limit 1; >> ERROR: syntax error at or near "order" >> LINE 1: delete from version order by version limit 1; > > Doh, my bad. Forgot you can't use ORDER BY in a DELETE FROM. (Which > means you have to use shenanigans when you want to do that.)
I found a solution after some searching: use ctid >From >https://www.postgresql.org/docs/current/ddl-system-columns.html#DDL-SYSTEM-COLUMNS-CTID "The physical location of the row version within its table. Note that although the ctid can be used to locate the row version very quickly, a row's ctid will change if it is updated or moved by VACUUM FULL. Therefore ctid is useless as a long-term row identifier. A primary key should be used to identify logical rows." Before PostgreSQL-12, we had access to the oid column, which was my first resort when trying this. [19:10 pg03 dvl ~] % psql bacula psql (16.4) Type "help" for help. bacula=# select ctid, * from version; ctid | versionid --------+----------- (0,21) | 1026 (0,22) | 1026 (2 rows) bacula=# begin; BEGIN bacula=*# delete from version where ctid = '(0,22)'; DELETE 1 bacula=*# commit; COMMIT bacula=# -- Dan Langille d...@langille.org _______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
