How to make PostgreSQL default_tablespace work properly?

My question is, "Why isn't database 'screwy' created in tablespace 'screwy'?

I made the following script, with it's output following, to show the problem I am having :

```
#!/bin/bash
#
export OWNER=yourself
export OBJECT=screwy
export OTHER=not${OBJECT}
export PWD='seecret'
#
sudo -u postgres psql -tc "DROP DATABASE IF EXISTS ${OWNER};"
sudo -u postgres psql -tc "DROP DATABASE IF EXISTS ${OBJECT};"
sudo -u postgres psql -tc "DROP DATABASE IF EXISTS ${OTHER};"
sudo -u postgres psql -tc "DROP TABLESPACE IF EXISTS ${OBJECT};"
sudo -u postgres psql -tc "DROP ROLE IF EXISTS ${OWNER};"
#
str=$(cat /etc/lsb-release | grep DESC)
echo "Linux version is : ${str#*=}"
echo "Postgres version is :"
sudo -u postgres psql -qtc "SELECT version();"
#
echo "CREATE ROLE ${OWNER} WITH CREATEDB LOGIN PASSWORD '${PWD}'"
sudo -u postgres psql -tc "CREATE ROLE ${OWNER} WITH CREATEDB LOGIN PASSWORD '${PWD}';"
echo "CREATE TABLESPACE ${OBJECT} OWNER ${OWNER} LOCATION '/home/${OWNER}/data';"
sudo -u postgres psql -tc "CREATE TABLESPACE ${OBJECT} OWNER ${OWNER} LOCATION '/home/${OWNER}/data';"
sudo -u postgres psql -tc "SELECT 'Tablespace \"' || spcname || '\" is owned by ' || usename \
FROM pg_catalog.pg_tablespace, pg_catalog.pg_user \
WHERE spcname='${OBJECT}' AND usesysid = spcowner;"
echo "CREATE DATABASE ${OWNER} TABLESPACE ${OBJECT};"
sudo -u postgres psql -tc "CREATE DATABASE ${OWNER} TABLESPACE ${OBJECT};"
#
echo "ALTER ROLE ${OWNER} SET DEFAULT_TABLESPACE TO ${OBJECT};"
sudo -u postgres psql -tc "ALTER ROLE ${OWNER} SET DEFAULT_TABLESPACE TO ${OBJECT};"
echo "GRANT CREATE ON TABLESPACE ${OBJECT} TO ${OWNER};"
sudo -u postgres psql -tc "GRANT CREATE ON TABLESPACE ${OBJECT} TO ${OWNER};"
#
echo "## As ${OWNER}"
echo "::*:yourself:${PWD}" | cat > ~/.pgpass
chmod 600 ~/.pgpass
#
echo "SHOW DEFAULT_TABLESPACE;"
psql -tc "SHOW DEFAULT_TABLESPACE;"
#
echo "CREATE DATABASE ${OBJECT};"
psql -tc "CREATE DATABASE ${OBJECT};"
#
echo "C

Per the PostgreSQL tablespaces documentation:


If a database is created without specifying a tablespace for it, it
uses the same tablespace as the template database it is copied from.

Per the CREATE DATABASE documentation:


By default, the new database will be created by cloning the standard
system database template1.

You could either alter the template1 database's tablespace to screwy, or create a new template database that has a tablespace of screwy, then specify that new database as a template when creating your screwy database.

StackExchange Database Administrators Q#68753, answer score: 3