Re: Tablespace column value null on select * from pg_tables

Hi Adrian,

"Not if you did: CREATE DATABASE name ... [ TABLESPACE [=] tablespace_name ] ALTER DATABASE name SET TABLESPACE
new_tablespaceThis makes the tablespace the default for the database and the default shows up as null in pg_tables:
https://www.postgresql.org/docs/9.5/view-pg-tables.htmltablespace name pg_tablespace.spcname Name of tablespace
containingtable (null if default for database)" 

Thanks, but I didn't do that. I have an existing database that's on data2 and haven't ran any command yet to change the
dbtablespace. When the db was created two years ago, it went directly to data2 along with any table/indexes to data2.
Thesecond command is the command I want to run but haven't ran it yet since I want to get the tablespaces for the
tableson the db inserted into a table prior, so I can make sure all the tables in data2 go into pg_default by running
thequery again and seeing what tablespace they are in (at this point, it should probably be null for the tablespace
namesignifying it's pg_default.) 

PgAdmin seems has the proper query to get the db and table tablespace names (right click table/select properties), but
thequeries I've used from various sources like stackoverflow don't provide the correct named tablespace. 

Thanks,

Alex


Sent with ProtonMail Secure Email.

‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Monday, July 15, 2019 3:22 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

> On 7/15/19 11:35 AM, Alex Williams wrote:
>
> > 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?)
>
> Not if you did:
>
> CREATE DATABASE name ... [ TABLESPACE [=] tablespace_name ]
>
> ALTER DATABASE name SET TABLESPACE new_tablespace
>
> This makes the tablespace the default for the database and the default
> shows up as null in pg_tables:
>
> https://www.postgresql.org/docs/9.5/view-pg-tables.html
>
> tablespace name pg_tablespace.spcname Name of tablespace containing
> table (null if default for database)
>
> > 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
> > Sent with ProtonMail https://protonmail.com Secure Email.
>
> --
>
> Adrian Klaver
> adrian.klaver@aklaver.com