Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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