Re: Tablespace column value null on select * from pg_tables

Hi Thomas and Adrian,

I'm sorry on my part, you both are correct, thanks again for your help.

What I did today that worked to move everything from data2 to pg_default was:

1. postgres=# ALTER DATABASE mydatabase SET TABLESPACE pg_default;

ERROR: some relations of database "mydatabase" are already in tablespace "pg_default"

HINT: You must move them back to the database's default tablespace before using this command.

2. Ran this to get the objects not in data2:
SELECT t.relname, t.reltablespace, sp.spcname
FROM pg_class t LEFT JOIN
pg_tablespace sp ON sp.oid = t.reltablespace where spcname is not null

3. Ran this on those objects not on data2 (the current default TS)
alter index public.my_index set tablespace data2;

4. postgres=# ALTER DATABASE mydatabase SET TABLESPACE pg_default;

Viola, no issues, and the tables and indexes are now on pg_default tablespace.

Thanks again to both of you!

Alex

(Just a note: The name of the actual DB / objects manually moved were renamed for this public post)

Sent with ProtonMail Secure Email.

‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Monday, July 15, 2019 8:33 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:

> On 7/15/19 12:53 PM, Alex Williams wrote:
>
> > Hi Adrian,
> > "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)"
> > 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 db tablespace. When the db was created two years ago, it
>
> So someone else ran the command the end result is the same, data2 is the
> default tablespace for the db so you get NULL in the tablespace column
> in pg_tables.
>
> went directly to data2 along with any table/indexes to data2. The second
> command is the command I want to run but haven't ran it yet since I want
> to get the tablespaces for the tables on the db inserted into a table
> prior, so I can make sure all the tables in data2 go into pg_default by
> running the query again and seeing what tablespace they are in (at this
> point, it should probably be null for the tablespace name signifying
> it's pg_default.)
>
> That is where you are getting confused, there are two defaults in play;
> pg_default and the db default.
>
> pg_default:
>
> https://www.postgresql.org/docs/9.5/manage-ag-tablespaces.html
> "Two tablespaces are automatically created when the database cluster is
> initialized. The pg_global tablespace is used for shared system
> catalogs. The pg_default tablespace is the default tablespace of the
> template1 and template0 databases (and, therefore, will be the default
> tablespace for other databases as well, unless overridden by a
> TABLESPACE clause in CREATE DATABASE)."
>
> db default:
>
> From same link above.
>
> "The tablespace associated with a database is used to store the system
> catalogs of that database. Furthermore, it is the default tablespace
> used for tables, indexes, and temporary files created within the
> database, if no TABLESPACE clause is given and no other selection is
> specified by default_tablespace or temp_tablespaces (as appropriate). If
> a database is created without specifying a tablespace for it, it uses
> the same tablespace as the template database it is copied from."
>
> In either case that tablespace becomes the default for the db and shows
> up as NULL in pg_tables.
>
> > PgAdmin seems has the proper query to get the db and table tablespace names (right click table/select properties), but the queries I've used from various sources like stackoverflow don't provide the correct named tablespace.
>
> It is probably doing something like:
>
> SELECT
> datname, spcname
> FROM
> pg_database AS pd
> JOIN
> pg_tablespace AS pt
> ON
> pd.dattablespace = pt.oid;
>
> > Thanks,
> > Alex
> > Sent with ProtonMail Secure Email.
>
> --
>
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com