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

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_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
thedb 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),
butthe 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.klaver@aklaver.com