4

When I create a table, it is placed in a default tablespace such as "USERS", unless I specify a user created tablespace, say, MyTableSpace.

For example,

create table test_tab(

name varchar2(100),
photo BLOB,
text   CLOB
)
tablespace "USERS"   --default
lob ("photo")  tablespace "MyTableSpace"   --user created
lob ("text")  tablespace "MyTableSpace"    --user created

Now, convert the above table to the counterpart in Postgres. Where could those columns be placed?

A default tablespace? User create tablespace? Or some proprietary logical structure in Postgres?

scott
  • 71
  • 2
  • 6
  • 1
    long bytea (the equivalent to BLOB) and text values are stored in the TOAST area. I believe that is always the same tablespace as the table is using. –  Jan 21 '16 at 15:25

1 Answers1

2

In PostgreSQL, if you don't specify a tablespace in your CREATE TABLE command, the value of parameter default_tablespace will be used.

If default_tablespace is not set, or has an invalid value, the table will be created in the same tablespace as the database.

https://demo.pganalyze.com/databases/6/config/default_tablespace

Andrew Brennan
  • 2,078
  • 12
  • 12