0
# PostgreSQL Schema Management
1

2
Comprehensive schema operations for PostgreSQL including creation, cleanup, and management of database objects like tables, views, types, functions, and extensions.
3

4
## Capabilities
5

6
### PostgreSQL Schema
7

8
Schema implementation providing full lifecycle management for PostgreSQL schemas and their contained objects.
9

10
```java { .api }
11
/**
12
 * PostgreSQL schema implementation
13
 */
14
public class PostgreSQLSchema extends Schema<PostgreSQLDatabase, PostgreSQLTable> {
15
    /**
16
     * Creates a new PostgreSQL schema instance
17
     * @param jdbcTemplate JDBC template for database operations
18
     * @param database PostgreSQL database instance
19
     * @param name Schema name
20
     */
21
    protected PostgreSQLSchema(JdbcTemplate jdbcTemplate, 
22
                              PostgreSQLDatabase database, String name);
23
    
24
    /**
25
     * Checks if the schema exists in the database
26
     * @return true if schema exists
27
     * @throws SQLException if check fails
28
     */
29
    protected boolean doExists() throws SQLException;
30
    
31
    /**
32
     * Checks if the schema is empty (contains no objects)
33
     * @return false if schema contains tables, views, types, or functions
34
     * @throws SQLException if check fails
35
     */
36
    protected boolean doEmpty() throws SQLException;
37
    
38
    /**
39
     * Creates the schema in the database
40
     * @throws SQLException if creation fails
41
     */
42
    protected void doCreate() throws SQLException;
43
    
44
    /**
45
     * Drops the schema and all its objects using CASCADE
46
     * @throws SQLException if drop fails
47
     */
48
    protected void doDrop() throws SQLException;
49
    
50
    /**
51
     * Cleans all objects from the schema in dependency order
52
     * Removes materialized views, views, tables, types, functions, sequences, etc.
53
     * @throws SQLException if cleanup fails
54
     */
55
    protected void doClean() throws SQLException;
56
    
57
    /**
58
     * Returns all tables in the schema (excluding views and child tables)
59
     * @return Array of PostgreSQLTable instances
60
     * @throws SQLException if table enumeration fails
61
     */
62
    protected PostgreSQLTable[] doAllTables() throws SQLException;
63
    
64
    /**
65
     * Creates a PostgreSQL table instance for the given name
66
     * @param tableName Name of the table
67
     * @return PostgreSQLTable instance
68
     */
69
    public Table getTable(String tableName);
70
    
71
    /**
72
     * Creates a PostgreSQL type instance for the given name
73
     * @param typeName Name of the type
74
     * @return PostgreSQLType instance
75
     */
76
    protected Type getType(String typeName);
77
}
78
```
79

80
**Usage Examples:**
81

82
```java
83
import org.flywaydb.database.postgresql.PostgreSQLSchema;
84

85
// Get schema instance (typically from connection)
86
Schema schema = connection.getSchema("my_schema");
87

88
// Check schema existence and state
89
if (schema.exists()) {
90
    System.out.println("Schema exists");
91
    if (schema.empty()) {
92
        System.out.println("Schema is empty");
93
    }
94
} else {
95
    // Create schema if it doesn't exist
96
    schema.create();
97
}
98

99
// Clean schema contents (preserves schema, removes all objects)
100
schema.clean();
101

102
// Get all tables in schema
103
Table[] tables = schema.allTables();
104
for (Table table : tables) {
105
    System.out.println("Found table: " + table.getName());
106
}
107

108
// Work with specific table
109
Table userTable = schema.getTable("users");
110
if (userTable.exists()) {
111
    userTable.drop();
112
}
113
```
114

115
### PostgreSQL Table
116

117
Table implementation providing PostgreSQL-specific table operations.
118

119
```java { .api }
120
/**
121
 * PostgreSQL table implementation
122
 */
123
public class PostgreSQLTable extends Table<PostgreSQLDatabase, PostgreSQLSchema> {
124
    /**
125
     * Creates a new PostgreSQL table instance
126
     * @param jdbcTemplate JDBC template for database operations
127
     * @param database PostgreSQL database instance  
128
     * @param schema PostgreSQL schema containing the table
129
     * @param name Table name
130
     */
131
    protected PostgreSQLTable(JdbcTemplate jdbcTemplate, 
132
                              PostgreSQLDatabase database, 
133
                              PostgreSQLSchema schema, String name);
134
    
135
    /**
136
     * Drops the table using CASCADE to handle dependencies
137
     * @throws SQLException if drop fails
138
     */
139
    protected void doDrop() throws SQLException;
140
    
141
    /**
142
     * Checks if the table exists in the database
143
     * @return true if table exists
144
     * @throws SQLException if check fails
145
     */
146
    protected boolean doExists() throws SQLException;
147
    
148
    /**
149
     * Locks the table for exclusive access using SELECT FOR UPDATE
150
     * @throws SQLException if locking fails
151
     */
152
    protected void doLock() throws SQLException;
153
}
154
```
155

156
**Usage Examples:**
157

158
```java
159
import org.flywaydb.database.postgresql.PostgreSQLTable;
160

161
// Get table instance
162
PostgreSQLTable table = (PostgreSQLTable) schema.getTable("users");
163

164
// Check table existence
165
if (table.exists()) {
166
    // Lock table for exclusive access
167
    table.lock();
168
    
169
    // Perform operations while locked
170
    // ...
171
    
172
    // Drop table if needed
173
    table.drop();  // Uses CASCADE
174
}
175
```
176

177
### PostgreSQL Type
178

179
Type implementation for managing PostgreSQL custom types.
180

181
```java { .api }
182
/**
183
 * PostgreSQL type implementation
184
 */
185
public class PostgreSQLType extends Type<PostgreSQLDatabase, PostgreSQLSchema> {
186
    /**
187
     * Creates a new PostgreSQL type instance
188
     * @param jdbcTemplate JDBC template for database operations
189
     * @param database PostgreSQL database instance
190
     * @param schema PostgreSQL schema containing the type
191
     * @param name Type name
192
     */
193
    public PostgreSQLType(JdbcTemplate jdbcTemplate, 
194
                         PostgreSQLDatabase database, 
195
                         PostgreSQLSchema schema, String name);
196
    
197
    /**
198
     * Drops the type from the database
199
     * @throws SQLException if drop fails
200
     */
201
    protected void doDrop() throws SQLException;
202
}
203
```
204

205
## Schema Cleanup Operations
206

207
The schema cleanup process follows a specific order to handle object dependencies:
208

209
### Cleanup Order
210

211
1. **Materialized Views** (PostgreSQL 9.3+)
212
2. **Views** 
213
3. **Tables** (with CASCADE to handle foreign keys)
214
4. **Base Types** (first pass for recreation)
215
5. **Functions/Procedures/Aggregates**
216
6. **Enums**
217
7. **Domains**
218
8. **Sequences**
219
9. **Base Types** (second pass for final cleanup)
220
10. **Collations** (OSS edition only)
221
11. **Extensions**
222

223
### Cleanup Examples
224

225
```java
226
// Full schema cleanup
227
schema.clean();
228

229
// Manual cleanup of specific object types
230
PostgreSQLSchema pgSchema = (PostgreSQLSchema) schema;
231

232
// Clean views
233
List<String> viewNames = jdbcTemplate.queryForStringList(
234
    "SELECT relname FROM pg_catalog.pg_class c " +
235
    "JOIN pg_namespace n ON n.oid = c.relnamespace " +
236
    "WHERE c.relkind = 'v' AND n.nspname = ?", schema.getName());
237
    
238
for (String viewName : viewNames) {
239
    jdbcTemplate.execute("DROP VIEW IF EXISTS " + 
240
                        database.quote(schema.getName(), viewName) + " CASCADE");
241
}
242
```
243

244
## Object Type Support
245

246
### Tables and Views
247

248
```java
249
// Get all tables (excludes views and child tables)
250
PostgreSQLTable[] tables = schema.doAllTables();
251

252
// Tables are filtered to exclude:
253
// - Views (relkind = 'v')  
254
// - Child tables (those with inheritance)
255
// - Extension-dependent objects
256
```
257

258
### Custom Types
259

260
```java
261
// Work with custom types
262
Type statusType = schema.getType("status_type");
263
if (statusType.exists()) {
264
    statusType.drop();
265
}
266

267
// Enum types
268
jdbcTemplate.execute("CREATE TYPE status_type AS ENUM ('active', 'inactive')");
269

270
// Domain types  
271
jdbcTemplate.execute("CREATE DOMAIN email_domain AS text CHECK (VALUE ~ '^[^@]+@[^@]+$')");
272
```
273

274
### Functions and Procedures
275

276
```java
277
// Functions are cleaned up automatically during schema.clean()
278
// Supports PostgreSQL 11+ procedures vs functions distinction
279

280
// Manual function cleanup
281
jdbcTemplate.execute("DROP FUNCTION IF EXISTS my_function(int, text) CASCADE");
282
jdbcTemplate.execute("DROP PROCEDURE IF EXISTS my_procedure(int) CASCADE");  // PostgreSQL 11+
283
jdbcTemplate.execute("DROP AGGREGATE IF EXISTS my_aggregate(int) CASCADE");
284
```
285

286
### Extensions
287

288
```java
289
// Extensions are cleaned up if owned by current user
290
// Check for extensions in schema
291
List<String> extensions = jdbcTemplate.queryForStringList(
292
    "SELECT e.extname FROM pg_extension e " +
293
    "LEFT JOIN pg_namespace n ON n.oid = e.extnamespace " + 
294
    "LEFT JOIN pg_roles r ON r.oid = e.extowner " +
295
    "WHERE n.nspname=? AND r.rolname=?", 
296
    schema.getName(), database.getCurrentUser());
297

298
for (String extension : extensions) {
299
    jdbcTemplate.execute("DROP EXTENSION IF EXISTS " + 
300
                        database.quote(extension) + " CASCADE");
301
}
302
```
303

304
### Sequences
305

306
```java
307
// Sequences are cleaned up automatically
308
// Manual sequence operations
309
jdbcTemplate.execute("CREATE SEQUENCE user_id_seq");
310
jdbcTemplate.execute("DROP SEQUENCE IF EXISTS user_id_seq");
311
```
312

313
## Error Handling
314

315
```java
316
try {
317
    schema.clean();
318
} catch (SQLException e) {
319
    System.err.println("Schema cleanup failed: " + e.getMessage());
320
    // Handle specific error cases
321
    if (e.getMessage().contains("permission denied")) {
322
        System.err.println("Insufficient privileges for cleanup");
323
    }
324
}
325

326
try {
327
    schema.create();
328
} catch (SQLException e) {
329
    System.err.println("Schema creation failed: " + e.getMessage());
330
    // Schema might already exist
331
}
332
```
333

334
## Version-Specific Features
335

336
### PostgreSQL 9.3+ Features
337

338
```java
339
// Materialized views (9.3+)
340
if (database.getVersion().isAtLeast("9.3")) {
341
    // Materialized views are included in cleanup
342
    List<String> matViews = jdbcTemplate.queryForStringList(
343
        "SELECT relname FROM pg_catalog.pg_class c " +
344
        "JOIN pg_namespace n ON n.oid = c.relnamespace " +
345
        "WHERE c.relkind = 'm' AND n.nspname = ?", schema.getName());
346
}
347
```
348

349
### PostgreSQL 11+ Features  
350

351
```java
352
// Procedures vs functions distinction (11+)
353
if (database.getVersion().isAtLeast("11")) {
354
    // Uses 'prokind' column instead of deprecated 'proisagg'
355
    String isProcedure = "pg_proc.prokind = 'p'";
356
    String isAggregate = "pg_proc.prokind = 'a'";
357
} else {
358
    // Legacy detection for older versions
359
    String isProcedure = "FALSE";  
360
    String isAggregate = "pg_proc.proisagg";
361
}
362
```