PostgreSQL Migration Guide
ReportBridge uses AI-powered conversion to migrate T-SQL queries in your SSRS reports to PostgreSQL — with automated validation and a 97% first-pass success rate.
On this page
Why PostgreSQL
ReportBridge migrates your report data layer from SQL Server to PostgreSQL. This eliminates the SQL Server license dependency while giving you a modern, cloud-native database that works seamlessly with Bold Report Viewer via the Npgsql connector.
No License Cost
PostgreSQL is open source. Eliminates per-core SQL Server licensing fees.
Cloud-Native
Runs on AWS RDS, Aurora, or EC2 with automated backups, failover, and scaling.
Npgsql Support
Bold Reports connects to PostgreSQL natively via the Npgsql .NET data provider.
Full SQL Compatibility
CTEs, window functions, aggregate expressions — all T-SQL report patterns have PostgreSQL equivalents.
T-SQL to PostgreSQL Conversion Patterns
ReportBridge's AI conversion pipeline handles the most common T-SQL to PostgreSQL patterns automatically. Here are the key transformations:
| T-SQL Pattern | PostgreSQL Equivalent | Notes |
|---|---|---|
ISNULL(x, y) | COALESCE(x, y) | Handles multiple arguments |
GETDATE() | NOW() | Returns timestamp with timezone |
TOP N | LIMIT N | Moves to end of query |
CONVERT(type, val) | CAST(val AS type) | Standard SQL syntax |
DATEADD(day, n, d) | d + INTERVAL 'n days' | Interval arithmetic |
DATEDIFF(day, a, b) | DATE_PART('day', b - a) | Or EXTRACT(EPOCH FROM ...) |
LEN(s) | LENGTH(s) | Direct equivalent |
CHARINDEX(sub, s) | POSITION(sub IN s) | 1-based index |
NVARCHAR / VARCHAR(MAX) | TEXT | PostgreSQL TEXT is unbounded |
BIT | BOOLEAN | true/false instead of 1/0 |
The AI conversion pipeline handles these patterns and many more, including complex CTEs, correlated subqueries, and SQL Server-specific string functions. Roughly 80–90% of queries convert on the first attempt.
Schema Mapping
When migrating from SQL Server to PostgreSQL, table and column names follow these conventions:
Naming Conventions
- Table names — Converted to lowercase with underscores (e.g.,
StationInfobecomesstation_info) - Column names — Preserved as-is but case-insensitive in PostgreSQL unless quoted
- Schema prefix —
dbo.prefix is removed (PostgreSQL usespublicschema by default) - Brackets —
[TableName]square brackets are converted to double quotes or removed
Data Source Mapping
Multiple SQL Server data source names can map to the same PostgreSQL database. ReportBridge maintains a ds_map in configuration that maps each RDL data source name to a PostgreSQL connection. Common patterns include case variants (e.g., VaVIP_Reporting, VAVIP_Reporting) all mapping to a single database.
Npgsql Connector
Bold Report Viewer connects to PostgreSQL using the Npgsql .NET data provider. This is configured automatically when you set up a database connection in the ReportBridge admin.
Connection Configuration
Provide the following in the ReportBridge Settings screen:
- Host — PostgreSQL server address (IP or hostname)
- Port — Default 5432
- Database — Target database name
- User / Password — Read-only credentials recommended for report queries
How It Works
When a report is published, ReportBridge rewrites the RDL data source with the PostgreSQL connection string and sets DataProvider to PostgreSQL. Bold Reports uses Npgsql to execute queries at render time.
Validation Workflow
After converting a report, ReportBridge validates the SQL against your live PostgreSQL database in two stages:
Stage 1: EXPLAIN Validation
Runs EXPLAIN on the converted SQL to verify syntax and table/column references without executing the query. This catches most conversion errors instantly.
Stage 2: Runtime Execution
Executes the query with LIMIT 1 to catch runtime errors (type mismatches, function argument issues) that EXPLAIN cannot detect.
Stage 3: Cross-Database Comparison
For full confidence, compare the original SQL Server results against PostgreSQL output for the same report — row counts and sample data (first 20 rows) are compared side-by-side.
If validation fails, the AI auto-fix loop attempts to correct the query (up to 5 attempts) using the error message and schema context.
Troubleshooting
Common issues encountered during migration and their solutions:
Query fails with 'column does not exist'
PostgreSQL identifiers are case-sensitive when quoted. Remove double quotes around column names, or ensure the case matches the actual schema.
CAST errors on date/time columns
SQL Server implicitly converts strings to dates. PostgreSQL requires explicit casting. Use CAST(value AS DATE) or TO_DATE(value, 'format').
Report parameters treated as wrong type
All Bold Reports parameters arrive as text. Add CAST to typed comparisons (e.g., WHERE id = CAST(@ParamId AS INTEGER)).
'operator does not exist: integer = text'
PostgreSQL is stricter about type matching than SQL Server. Cast the parameter or column to the correct type in the WHERE clause.