Move table and all the attached indices to a new tablespace

snippet.sql

-- Define the new tablespace name and the target table name
DO $$ 
DECLARE 
    new_tablespace TEXT := 'new_tablespace_name';
    target_table TEXT := 'your_table_name'; -- Replace with the table name you want to move
BEGIN
    -- Create a temporary table to store index names
    CREATE TEMP TABLE temp_indexes AS
    SELECT indexname
    FROM pg_indexes
    WHERE tablename = target_table;

    -- Loop through the indexes and move them to the new tablespace
    FOR idx_name IN (SELECT indexname FROM temp_indexes) LOOP
        EXECUTE 'ALTER INDEX ' || idx_name || ' SET TABLESPACE ' || new_tablespace;
    END LOOP;

    -- Move the table itself to the new tablespace
    EXECUTE 'ALTER TABLE ' || target_table || ' SET TABLESPACE ' || new_tablespace;

    -- Clean up the temporary table
    DROP TABLE temp_indexes;
END $$;