By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
copy (select) to program $sh$
mkdir /tmp/new_tablespace/
$sh$;
COPY 1
create tablespace new_tablespace location '/tmp/new_tablespace';
CREATE TABLESPACE
begin;
create schema company2;
set local search_path='company2';
create table test(a int);
create index on test(a);
commit;
BEGIN
CREATE SCHEMA
SET
CREATE TABLE
CREATE INDEX
COMMIT
select * from pg_tables where schemaname='company2';
do $do_block$
declare record_ record;
begin
for record_ in select schemaname,tablename from pg_tables
where coalesce(tablespace,'')<>'new_tablespace'
and schemaname='company2'
loop
execute format ('alter table %I.%I set tablespace new_tablespace',record_.schemaname,record_.tablename);
end loop;
end $do_block$;
select * from pg_tables where schemaname='company2';
| schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity |
|---|---|---|---|---|---|---|---|
| company2 | test | postgres | null | t | f | f | f |
SELECT 1
DO
| schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity |
|---|---|---|---|---|---|---|---|
| company2 | test | postgres | new_tablespace | t | f | f | f |
SELECT 1