Re: Tablespace column value null on select * from pg_tables

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