Skip to main content
Solved

How can I specify the tablespace for table creation in PostGIS?


Forum|alt.badge.img

In Oracle, you can assign a default tablespace to an owner (schema). We are moving all our spatial databases to PostGIS where a schema cannot have a default tablespace. Instead you have to specify the tablespace to use with the create table syntax.

With FME desktop, how can I specify what tablespace I want all these cloned tables to go into without first somehow creating empty tables myself and then appending to them?

Best answer by hallkbrdz

The answer (for all tables per worksheet) is to use the writer parameter "SQL to run before write" and then simply set the tablespace that will be used by default:

SET default_tablespace = xyz

Not ideal, but good enough for now.

Bryan

View original
Did this help you find an answer to your question?

7 replies

fmelizard
Safer
Forum|alt.badge.img+18
  • Safer
  • April 26, 2018
Hi @hallkbrdz, are you writing multiple tables to one tablespace? Or are there multiple tablespaces that you're writing to?

Forum|alt.badge.img
  • Author
  • May 1, 2018

Tia,

In Oracle we generally use one tablespace for each schema that holds a large number of tables, so yes. This helps keep a tablespace to a resonable size, and on Oracle it allowed us to backup/restore by tablespace if we wanted to clone data that way.

For PostGIS, I was just planning on mimicking the same layout.

 

Bryan

nielsgerrits
VIP
Forum|alt.badge.img+54

Table Qualifier is the field you are looking for.


jkr_wrk
Influencer
Forum|alt.badge.img+29
  • May 1, 2018
nielsgerrits wrote:

Table Qualifier is the field you are looking for.

In the postgis FeatureWriter there are two ways to specify the schema.

 

 

Splitted:

 

Table Name: table_a

 

Table Qualifier: schema_a

 

 

Or one field:

 

Table Name: schema_a.table_a

 

Table Qualifier: {leave emtpy}

 

 

Because the FeatureReader sets the fme_feature_type to schema_a.table_a the none splitted variant can come in handy.

 

 

 


Forum|alt.badge.img
  • Author
  • May 4, 2018
nielsgerrits wrote:

Table Qualifier is the field you are looking for.

Thanks, but setting the schema was not the question - setting the tablespace being used for the table creation is.

 

 


fmelizard
Safer
Forum|alt.badge.img+18
  • Safer
  • May 4, 2018
hallkbrdz wrote:

Tia,

In Oracle we generally use one tablespace for each schema that holds a large number of tables, so yes. This helps keep a tablespace to a resonable size, and on Oracle it allowed us to backup/restore by tablespace if we wanted to clone data that way.

For PostGIS, I was just planning on mimicking the same layout.

 

Bryan
Hi @hallkbrdz, you might be able to get away with using the SQLExecutor to do your inserts and provide the tablespace information there.

Forum|alt.badge.img
  • Author
  • Best Answer
  • May 7, 2018

The answer (for all tables per worksheet) is to use the writer parameter "SQL to run before write" and then simply set the tablespace that will be used by default:

SET default_tablespace = xyz

Not ideal, but good enough for now.

Bryan


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings