Tablespace column value null on select * from pg_tables - Mailing list pgsql-general

Hi,

Server Version 9.5

I found this old thread on something similar to the results I'm getting: https://www.postgresql.org/message-id/1308615192339-4508750.post%40n5.nabble.com

But in my case, I have a database that's in a user-defined tablespace (data2) and all the tables/indexes there are also in data2 and I want to do a select into a table the results of all the tables / tablespaces they are in that database...when doing this:

SELECT distinct tablespace

FROM pg_tables;

I get 2 rows: null and pg_global (I think to expect null for pg_default, but if the table is in a user-defined tablespace, should we expect it to show it, in my case, data2?)

or

SELECT distinct tablespace

FROM pg_indexes

I get 3 rows: null, pg_global and pg_default

and this: SELECT * FROM pg_tablespace;

I get 3 rows: pg_default, pg_global and data2.

Using pgadmin, getting properties for the DB / tables, it shows data2.

What I want to do is move all the tables / indexes from data2 to pg_default (we added more space to the pg_default mount.)

Now, I did a pg_dump/restore for one database which took a long time and we now know the process for that, so on the next database we have we want to do it where we use the following commands:

ALTER DATABASE mydatabase SET TABLESPACE pg_default;

alter table all in tablespace data2 set tablespace pg_default;

But, what I'm trying to accomplish here is, aside from checking the filesystem, like df- h, to see it was moved or properties on each table (too many) I just want to run a query that will insert into a table all the tables and their tablespace names and when the above two commands (3rd will be moving indexes) run the query again and verify everything has moved from data2 to pg_default.

Thanks for your help in advance.

Alex