2

I'm trying to create a database and tables in a custom tablespace, but instead 'pg_default' tablespace is used for tables. Here's example:

mkdir /data

chown postgres /data

CREATE TABLESPACE mytspace OWNER postgres LOCATION '/data';

CREATE DATABASE mydb WITH OWNER = postgres ENCODING = 'UTF8' LC_COLLATE = 'ru_RU.utf8' LC_CTYPE = 'ru_RU.utf8' TABLESPACE = mytspace  CONNECTION LIMIT = -1;

CREATE TABLE foo(i int) TABLESPACE mytspace;

psql's \db command shows that tablespace is created

          List of tablespaces
    Name    |  Owner   |   Location    
------------+----------+---------------
 mytspace   | postgres | /data
 pg_default | postgres | 
 pg_global  | postgres | 
(3 rows)

Database created in appropriate tablespace. Here's SQL i see in pgAdmin:

CREATE DATABASE mydb
    WITH 
    OWNER = postgres
    ENCODING = 'UTF8'
    LC_COLLATE = 'ru_RU.utf8'
    LC_CTYPE = 'ru_RU.utf8'
    TABLESPACE = mytspace
    CONNECTION LIMIT = -1;

But table created in 'pg_default" tablespace

CREATE TABLE foo
(
    i integer
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

What should I do to create a 'foo' table in 'mytspace' tablespace?

Cœur
  • 37,241
  • 25
  • 195
  • 267
dcpt
  • 81
  • 1
  • 7
  • Does the tablespace show up in `pg_tables`, maybe it's just a pgAdmin bug? Also which exact Postgres version are you using? To rule out the obvious: did you change the connection to the new database _before_ creating the table? Or was the table maybe created in the default database which does not have a default tablespace? –  Oct 19 '16 at 06:16
  • To avoid confusion: `pg_tables.tablespace` will be null for tables stored in the _default_ tablespace (of the database). So if that is null for your table, this sounds more like a pgAdmin bug –  Oct 19 '16 at 06:23
  • 1
    You have to connect (\c) to `mydb` before creating tables. – klin Oct 19 '16 at 06:24
  • I connected using `psql -d mydb` and tried to create a table: `create table foo2(id int) tablespace mytspace;`. After that i checked `select * from pg_tables where tablename='foo2';`. The _tablespace_ column was empty. I also tried to give a wrong tablespace name, like `create table foo3(id int) tablespace randomname;` and got error. Seems like psql just ignores my tablespace argument. pgAdmin version 4, Postgresql version 9.3. – dcpt Oct 19 '16 at 06:48
  • As I said: if `pg_tables.tablespace` is empty, that means the table is using the default tablespace of the database. So everything is fine. Does pgAdmin **3** display the correct SQL source? –  Oct 19 '16 at 06:54
  • To clarify: if i created a database in tablespace `mytspace` and then created a table `foo` in that database in the same tablespace `mytspace` (like above), the table will be stored in that database's default tablespace `mytspace`, but the name of tablespace `mytspace` will be omitted in `pg_tables.tablespace` as this tablespace is now default for that database. In the end, the data of table `foo` will be stored at `mytspace` location? EDIT:: **pgAdmin 3 displays correctly**. – dcpt Oct 19 '16 at 07:05

1 Answers1

0

Seems like it's a bug in pgAdmin 4 v 1.0. The GUI and SQL source display wrong tablespace for created tables, but correct tablespace for created databases. pgAdmin 3 v 1.22.1 displays all of them correctly.

dcpt
  • 81
  • 1
  • 7