0
# PostgreSQL SQL Parsing
1

2
Specialized SQL parser for PostgreSQL with support for database-specific syntax including COPY operations, dollar quoting, and transaction control statements.
3

4
## Capabilities
5

6
### PostgreSQL Parser
7

8
SQL parser that handles PostgreSQL-specific syntax and statement types for migration script processing.
9

10
```java { .api }
11
/**
12
 * PostgreSQL SQL parser implementation
13
 */
14
public class PostgreSQLParser extends Parser {
15
    /**
16
     * Creates a new PostgreSQL parser
17
     * @param configuration Flyway configuration
18
     * @param parsingContext Parsing context with database information
19
     */
20
    public PostgreSQLParser(Configuration configuration, ParsingContext parsingContext);
21
    
22
    /**
23
     * Returns the alternative string literal quote character for PostgreSQL
24
     * Used for dollar-quoted strings ($tag$content$tag$)
25
     * @return '$' character
26
     */
27
    protected char getAlternativeStringLiteralQuote();
28
    
29
    /**
30
     * Creates a parsed SQL statement with PostgreSQL-specific handling
31
     * @param reader Input reader for SQL content
32
     * @param recorder Recording mechanism for parsing state
33
     * @param statementPos Position of statement start
34
     * @param statementLine Line number of statement start
35
     * @param statementCol Column number of statement start
36
     * @param nonCommentPartPos Position of non-comment content
37
     * @param nonCommentPartLine Line number of non-comment content
38
     * @param nonCommentPartCol Column number of non-comment content
39
     * @param statementType Type of SQL statement detected
40
     * @param canExecuteInTransaction Whether statement can run in transaction
41
     * @param delimiter Statement delimiter
42
     * @param sql Complete SQL text
43
     * @param tokens Parsed tokens
44
     * @param batchable Whether statement can be batched
45
     * @return Parsed statement (potentially PostgreSQLCopyParsedStatement for COPY)
46
     * @throws IOException if parsing fails
47
     */
48
    protected ParsedSqlStatement createStatement(PeekingReader reader, Recorder recorder,
49
        int statementPos, int statementLine, int statementCol, int nonCommentPartPos, 
50
        int nonCommentPartLine, int nonCommentPartCol, StatementType statementType, 
51
        boolean canExecuteInTransaction, Delimiter delimiter, String sql, 
52
        List<Token> tokens, boolean batchable) throws IOException;
53
    
54
    /**
55
     * Adjusts block depth for PostgreSQL-specific constructs
56
     * Handles BEGIN ATOMIC blocks and CASE statements within atomic blocks
57
     * @param context Parser context
58
     * @param tokens List of parsed tokens
59
     * @param keyword Current keyword being processed
60
     * @param reader Input reader
61
     */
62
    protected void adjustBlockDepth(ParserContext context, List<Token> tokens, 
63
                                   Token keyword, PeekingReader reader);
64
    
65
    /**
66
     * Detects PostgreSQL-specific statement types
67
     * @param simplifiedStatement Simplified SQL statement text
68
     * @param context Parser context
69
     * @param reader Input reader
70
     * @return Statement type (including COPY for COPY FROM STDIN)
71
     */
72
    protected StatementType detectStatementType(String simplifiedStatement, 
73
                                               ParserContext context, 
74
                                               PeekingReader reader);
75
    
76
    /**
77
     * Determines if a statement can execute within a transaction
78
     * @param simplifiedStatement Simplified SQL statement
79
     * @param keywords List of parsed keywords
80
     * @return true if can execute in transaction, false if must execute outside, null if default behavior
81
     */
82
    protected Boolean detectCanExecuteInTransaction(String simplifiedStatement, 
83
                                                   List<Token> keywords);
84
    
85
    /**
86
     * Handles PostgreSQL dollar-quoted string literals
87
     * @param reader Input reader
88
     * @param context Parser context
89
     * @param pos Position in input
90
     * @param line Line number
91
     * @param col Column number
92
     * @return String token for dollar-quoted literal
93
     * @throws IOException if parsing fails
94
     */
95
    protected Token handleAlternativeStringLiteral(PeekingReader reader, 
96
                                                   ParserContext context, 
97
                                                   int pos, int line, int col) throws IOException;
98
}
99
```
100

101
**Usage Examples:**
102

103
```java
104
import org.flywaydb.database.postgresql.PostgreSQLParser;
105
import org.flywaydb.core.internal.parser.ParsingContext;
106

107
// Create parser (typically done by PostgreSQLDatabaseType)
108
Configuration config = new ClassicConfiguration();
109
ParsingContext context = new ParsingContext();
110
PostgreSQLParser parser = new PostgreSQLParser(config, context);
111

112
// Parser automatically handles PostgreSQL-specific syntax
113
// when processing migration files through Flyway
114
```
115

116
### COPY Statement Support
117

118
The parser provides specialized handling for PostgreSQL COPY FROM STDIN statements:
119

120
```java { .api }
121
/**
122
 * Parsed statement for PostgreSQL COPY FROM STDIN operations
123
 */
124
public class PostgreSQLCopyParsedStatement extends ParsedSqlStatement {
125
    /**
126
     * Creates a COPY parsed statement
127
     * @param pos Position in source
128
     * @param line Line number
129
     * @param col Column number
130
     * @param sql SQL statement text
131
     * @param copyData The data portion of the COPY statement
132
     */
133
    public PostgreSQLCopyParsedStatement(int pos, int line, int col, 
134
                                        String sql, String copyData);
135
}
136
```
137

138
**COPY Statement Example:**
139

140
```sql
141
-- This PostgreSQL-specific syntax is automatically handled
142
COPY users (name, email, age) FROM STDIN;
143
John Doe	john@example.com	30
144
Jane Smith	jane@example.com	25
145
\.
146
```
147

148
## Supported PostgreSQL Syntax
149

150
### Dollar Quoting
151

152
PostgreSQL's dollar-quoted strings are fully supported:
153

154
```sql
155
-- Standard dollar quoting
156
CREATE FUNCTION example() RETURNS text AS $$
157
BEGIN
158
    RETURN 'This is dollar-quoted content';
159
END;
160
$$ LANGUAGE plpgsql;
161

162
-- Labeled dollar quoting  
163
CREATE FUNCTION tagged() RETURNS text AS $func$
164
BEGIN
165
    RETURN 'Content with $$ inside';
166
END;
167
$func$ LANGUAGE plpgsql;
168
```
169

170
### Transaction Control Detection
171

172
The parser automatically detects statements that cannot execute within transactions:
173

174
```sql
175
-- These statements are detected as non-transactional
176
CREATE DATABASE mydb;
177
CREATE TABLESPACE mytablespace LOCATION '/data';
178
CREATE SUBSCRIPTION mysub CONNECTION 'host=localhost' PUBLICATION mypub;
179
ALTER SYSTEM SET work_mem = '64MB';
180
CREATE INDEX CONCURRENTLY idx_name ON table_name (column);
181
DROP INDEX CONCURRENTLY idx_name;
182
REINDEX DATABASE mydb;
183
VACUUM FULL table_name;
184
DISCARD ALL;
185

186
-- Version-specific handling
187
ALTER TYPE status_type ADD VALUE 'new_status';  -- Non-transactional in PostgreSQL < 12
188
```
189

190
### BEGIN ATOMIC Blocks
191

192
Support for PostgreSQL's atomic blocks in functions and procedures:
193

194
```sql
195
CREATE FUNCTION atomic_example(x int) RETURNS int
196
LANGUAGE SQL
197
BEGIN ATOMIC
198
    CASE x
199
        WHEN 1 THEN RETURN x + 1;
200
        WHEN 2 THEN RETURN x + 2;
201
        ELSE RETURN x;
202
    END CASE;
203
END;
204
```
205

206
## Parser Configuration
207

208
### Statement Type Detection
209

210
The parser uses regular expressions to identify PostgreSQL-specific statements:
211

212
```java
213
// Internal patterns (for reference)
214
private static final Pattern COPY_FROM_STDIN_REGEX = 
215
    Pattern.compile("^COPY( .*)? FROM STDIN");
216
private static final Pattern CREATE_DATABASE_TABLESPACE_SUBSCRIPTION_REGEX = 
217
    Pattern.compile("^(CREATE|DROP) (DATABASE|TABLESPACE|SUBSCRIPTION)");
218
private static final Pattern ALTER_SYSTEM_REGEX = 
219
    Pattern.compile("^ALTER SYSTEM");
220
private static final Pattern CREATE_INDEX_CONCURRENTLY_REGEX = 
221
    Pattern.compile("^(CREATE|DROP)( UNIQUE)? INDEX CONCURRENTLY");
222
```
223

224
### Version-Specific Behavior
225

226
The parser adapts to different PostgreSQL versions:
227

228
```java
229
// Example version-specific handling
230
if (parsingContext.getDatabase().getVersion().isAtLeast("12")) {
231
    // PostgreSQL 12+ allows ALTER TYPE ADD VALUE in transactions
232
    return null;  // Use default transaction behavior
233
} else {
234
    // Pre-12 versions require non-transactional execution
235
    return false;
236
}
237
```
238

239
## Error Handling
240

241
The parser provides detailed error information for syntax issues:
242

243
```java
244
try {
245
    ParsedSqlStatement statement = parser.parse(migrationSql);
246
} catch (FlywayParseException e) {
247
    System.err.println("SQL parsing failed at line " + e.getLineNumber() + 
248
                      ", column " + e.getColumnNumber());
249
    System.err.println("Error: " + e.getMessage());
250
}
251
```
252

253
## Integration with Flyway
254

255
The parser is automatically used when Flyway processes PostgreSQL migration files:
256

257
```java
258
// Migration file: V1__Create_tables.sql
259
Flyway flyway = Flyway.configure()
260
    .dataSource("jdbc:postgresql://localhost:5432/mydb", "user", "password")
261
    .locations("classpath:db/migration")
262
    .load();
263

264
// PostgreSQLParser automatically handles PostgreSQL-specific syntax
265
flyway.migrate();
266
```
267

268
## Advanced Features
269

270
### Custom Token Handling
271

272
The parser provides sophisticated token handling for complex PostgreSQL constructs:
273

274
- **Dollar-quoted strings**: Proper nesting and label matching
275
- **Block depth tracking**: Accurate parsing of nested BEGIN/END blocks  
276
- **Comment handling**: PostgreSQL-style comments (`--` and `/* */`)
277
- **Statement boundaries**: Proper detection in complex multi-statement files