fraiseql_db/dialect/
postgres.rs

1//! PostgreSQL SQL dialect implementation.
2
3use std::{borrow::Cow, fmt::Write};
4
5use super::trait_def::{RowViewColumnType, SqlDialect, UnsupportedOperator};
6use crate::types::sql_hints::ScalarFieldType;
7
8/// PostgreSQL dialect for [`GenericWhereGenerator`].
9///
10/// [`GenericWhereGenerator`]: crate::where_generator::GenericWhereGenerator
11pub struct PostgresDialect;
12
13impl SqlDialect for PostgresDialect {
14    fn name(&self) -> &'static str {
15        "PostgreSQL"
16    }
17
18    fn quote_identifier(&self, name: &str) -> String {
19        format!("\"{}\"", name.replace('"', "\"\""))
20    }
21
22    fn json_extract_scalar(&self, column: &str, path: &[String]) -> String {
23        use crate::path_escape::{escape_postgres_jsonb_path, escape_postgres_jsonb_segment};
24
25        if path.len() == 1 {
26            let escaped = escape_postgres_jsonb_segment(&path[0]);
27            format!("{column}->>'{escaped}'")
28        } else {
29            let escaped_path = escape_postgres_jsonb_path(path);
30            let mut result = column.to_owned();
31            for (i, segment) in escaped_path.iter().enumerate() {
32                if i < escaped_path.len() - 1 {
33                    write!(result, "->'{segment}'").expect("write to String");
34                } else {
35                    write!(result, "->>'{segment}'").expect("write to String");
36                }
37            }
38            result
39        }
40    }
41
42    fn placeholder(&self, n: usize) -> String {
43        format!("${n}")
44    }
45
46    fn cast_type_name(&self, ty: ScalarFieldType) -> Option<&'static str> {
47        match ty {
48            ScalarFieldType::Text => None,
49            ScalarFieldType::Integer => Some("bigint"),
50            ScalarFieldType::Numeric => Some("numeric"),
51            ScalarFieldType::Boolean => Some("boolean"),
52            ScalarFieldType::DateTime => Some("timestamptz"),
53            ScalarFieldType::Date => Some("date"),
54            ScalarFieldType::Time => Some("time"),
55            // Deliberately uncast — see `ScalarFieldType::Uuid`. A `::uuid` cast
56            // on the left-hand side is evaluated per row and would raise 22P02
57            // on any row whose identity is not a UUID.
58            ScalarFieldType::Uuid => None,
59            // Also deliberately uncast — see `ScalarFieldType::Vector`. The
60            // variant tells the distance predicates that a native column exists
61            // to read (#1117); it is not a cast, and naming one here would
62            // change what every OTHER operator on a vector field compares.
63            ScalarFieldType::Vector => None,
64        }
65    }
66
67    fn cast_expr_as<'a>(&self, expr: &'a str, ty: ScalarFieldType) -> Cow<'a, str> {
68        match self.cast_type_name(ty) {
69            Some(name) => Cow::Owned(format!("({expr})::{name}")),
70            None => Cow::Borrowed(expr),
71        }
72    }
73
74    fn cast_param_as<'a>(&self, placeholder: &'a str, ty: ScalarFieldType) -> Cow<'a, str> {
75        match ty {
76            // A JSON boolean is bound as `QueryParam::Bool`, which already has
77            // the correct binary encoding. Routing it through `text` makes the
78            // server resolve the parameter as `text` and then reject the bool
79            // encoding with SQLSTATE 22P02.
80            ScalarFieldType::Boolean => Cow::Borrowed(placeholder),
81            _ => match self.cast_type_name(ty) {
82                // Two-step: force the server to resolve the bind as `text`
83                // before casting. Every other JSON scalar is bound as
84                // `QueryParam::Text`, so a direct `$1::timestamptz` would make
85                // the server expect that type's binary encoding.
86                Some(name) => Cow::Owned(format!("({placeholder}::text)::{name}")),
87                None => Cow::Borrowed(placeholder),
88            },
89        }
90    }
91
92    fn cast_native_param(&self, placeholder: &str, native_type: &str) -> String {
93        match native_type.to_lowercase().as_str() {
94            // bool uses QueryParam::Bool which encodes correctly in binary — no intermediate text.
95            "boolean" | "bool" => format!("{placeholder}::bool"),
96            // text/varchar/char(n) — no cast needed.
97            "text" | "varchar" | "character varying" | "char" | "bpchar" | "name" => {
98                placeholder.to_string()
99            },
100            // Everything else: two-step cast forces $N to be resolved as text by the
101            // server, avoiding binary-encoding mismatches for uuid, timestamps, ints, etc.
102            _ => format!("{placeholder}::text::{native_type}"),
103        }
104    }
105
106    fn ilike_sql(&self, lhs: &str, rhs: &str) -> String {
107        format!("{lhs} ILIKE {rhs}")
108    }
109
110    fn json_array_length(&self, expr: &str) -> String {
111        format!("jsonb_array_length(({expr})::jsonb)")
112    }
113
114    fn array_contains_sql(&self, lhs: &str, rhs: &str) -> Result<String, UnsupportedOperator> {
115        Ok(format!("({lhs})::jsonb @> {rhs}::jsonb"))
116    }
117
118    fn array_contained_by_sql(&self, lhs: &str, rhs: &str) -> Result<String, UnsupportedOperator> {
119        Ok(format!("({lhs})::jsonb <@ {rhs}::jsonb"))
120    }
121
122    fn array_overlaps_sql(&self, lhs: &str, rhs: &str) -> Result<String, UnsupportedOperator> {
123        // PostgreSQL has no `jsonb && jsonb` — `&&` is an array/range operator —
124        // so the emitted predicate could never run. `?|` would work but only
125        // matches STRING elements (`'[1,2]'::jsonb ?| ARRAY['1']` is false),
126        // which would silently under-match numeric arrays. Element-wise
127        // intersection is the general form.
128        Ok(format!(
129            "EXISTS (SELECT 1 FROM jsonb_array_elements(({lhs})::jsonb) AS _l(v) \
130             JOIN jsonb_array_elements({rhs}::jsonb) AS _r(v) ON _l.v = _r.v)"
131        ))
132    }
133
134    fn fts_matches_sql(&self, expr: &str, param: &str) -> Result<String, UnsupportedOperator> {
135        Ok(format!("to_tsvector({expr}) @@ to_tsquery({param})"))
136    }
137
138    fn fts_plain_query_sql(&self, expr: &str, param: &str) -> Result<String, UnsupportedOperator> {
139        Ok(format!("to_tsvector({expr}) @@ plainto_tsquery({param})"))
140    }
141
142    fn fts_phrase_query_sql(&self, expr: &str, param: &str) -> Result<String, UnsupportedOperator> {
143        Ok(format!("to_tsvector({expr}) @@ phraseto_tsquery({param})"))
144    }
145
146    fn fts_websearch_query_sql(
147        &self,
148        expr: &str,
149        param: &str,
150    ) -> Result<String, UnsupportedOperator> {
151        Ok(format!("to_tsvector({expr}) @@ websearch_to_tsquery({param})"))
152    }
153
154    fn fts_rank_sql(
155        &self,
156        document_expr: &str,
157        query_param: &str,
158    ) -> Result<String, UnsupportedOperator> {
159        Ok(format!(
160            "ts_rank(to_tsvector({document_expr}), websearch_to_tsquery({query_param}))"
161        ))
162    }
163
164    fn regex_sql(
165        &self,
166        lhs: &str,
167        rhs: &str,
168        case_insensitive: bool,
169        negate: bool,
170    ) -> Result<String, UnsupportedOperator> {
171        let op = match (case_insensitive, negate) {
172            (false, false) => "~",
173            (true, false) => "~*",
174            (false, true) => "!~",
175            (true, true) => "!~*",
176        };
177        Ok(format!("{lhs} {op} {rhs}"))
178    }
179
180    // ── PostgreSQL-only operators ──────────────────────────────────────────────
181    // Vector distance emission moved into `GenericWhereGenerator::vector_threshold_sql`
182    // (#386): the old `vector_distance_sql`/`jaccard_distance_sql` dialect seam emitted
183    // a non-boolean fragment over a mis-parenthesised cast and had no other caller.
184
185    fn inet_check_sql(&self, lhs: &str, check_name: &str) -> Result<String, UnsupportedOperator> {
186        match check_name {
187            "IsIPv4" => Ok(format!("family(({lhs})::inet) = 4")),
188            "IsNotIPv4" => Ok(format!("NOT (family(({lhs})::inet) = 4)")),
189            "IsIPv6" => Ok(format!("family(({lhs})::inet) = 6")),
190            "IsNotIPv6" => Ok(format!("NOT (family(({lhs})::inet) = 6)")),
191            "IsPrivate" => Ok(format!(
192                "(({lhs})::inet << '10.0.0.0/8'::inet OR ({lhs})::inet << '172.16.0.0/12'::inet OR ({lhs})::inet << '192.168.0.0/16'::inet OR ({lhs})::inet << 'fc00::/7'::inet)"
193            )),
194            "IsPublic" => Ok(format!(
195                "NOT (({lhs})::inet << '10.0.0.0/8'::inet OR ({lhs})::inet << '172.16.0.0/12'::inet OR ({lhs})::inet << '192.168.0.0/16'::inet OR ({lhs})::inet << 'fc00::/7'::inet)"
196            )),
197            "IsLoopback" => Ok(format!(
198                "(({lhs})::inet << '127.0.0.0/8'::inet OR ({lhs})::inet << '::1/128'::inet)"
199            )),
200            "IsNotLoopback" => Ok(format!(
201                "NOT (({lhs})::inet << '127.0.0.0/8'::inet OR ({lhs})::inet << '::1/128'::inet)"
202            )),
203            "IsMulticast" => Ok(format!(
204                "(({lhs})::inet << '224.0.0.0/4'::inet OR ({lhs})::inet << 'ff00::/8'::inet)"
205            )),
206            "IsNotMulticast" => Ok(format!(
207                "NOT (({lhs})::inet << '224.0.0.0/4'::inet OR ({lhs})::inet << 'ff00::/8'::inet)"
208            )),
209            "IsLinkLocal" => Ok(format!(
210                "(({lhs})::inet << '169.254.0.0/16'::inet OR ({lhs})::inet << 'fe80::/10'::inet)"
211            )),
212            "IsNotLinkLocal" => Ok(format!(
213                "NOT (({lhs})::inet << '169.254.0.0/16'::inet OR ({lhs})::inet << 'fe80::/10'::inet)"
214            )),
215            "IsDocumentation" => Ok(format!(
216                "(({lhs})::inet << '192.0.2.0/24'::inet OR ({lhs})::inet << '198.51.100.0/24'::inet OR ({lhs})::inet << '203.0.113.0/24'::inet OR ({lhs})::inet << '2001:db8::/32'::inet)"
217            )),
218            "IsNotDocumentation" => Ok(format!(
219                "NOT (({lhs})::inet << '192.0.2.0/24'::inet OR ({lhs})::inet << '198.51.100.0/24'::inet OR ({lhs})::inet << '203.0.113.0/24'::inet OR ({lhs})::inet << '2001:db8::/32'::inet)"
220            )),
221            "IsCarrierGrade" => Ok(format!("(({lhs})::inet << '100.64.0.0/10'::inet)")),
222            "IsNotCarrierGrade" => Ok(format!("NOT (({lhs})::inet << '100.64.0.0/10'::inet)")),
223            _ => Err(UnsupportedOperator {
224                dialect:  self.name(),
225                operator: "InetCheck",
226            }),
227        }
228    }
229
230    fn inet_binary_sql(
231        &self,
232        pg_op: &str,
233        lhs: &str,
234        rhs: &str,
235    ) -> Result<String, UnsupportedOperator> {
236        Ok(format!("({lhs})::inet {pg_op} {rhs}::text::inet"))
237    }
238
239    fn ltree_binary_sql(
240        &self,
241        pg_op: &str,
242        lhs: &str,
243        rhs: &str,
244        rhs_type: &str,
245    ) -> Result<String, UnsupportedOperator> {
246        Ok(format!("({lhs})::ltree {pg_op} {rhs}::{rhs_type}"))
247    }
248
249    fn ltree_any_lquery_sql(
250        &self,
251        lhs: &str,
252        placeholders: &[String],
253    ) -> Result<String, UnsupportedOperator> {
254        Ok(format!("({lhs})::ltree ? ARRAY[{}]", placeholders.join(", ")))
255    }
256
257    fn ltree_depth_sql(
258        &self,
259        op: &str,
260        lhs: &str,
261        rhs: &str,
262    ) -> Result<String, UnsupportedOperator> {
263        // `{rhs}::text::int`, not a bare `{rhs}`: `nlevel()` returns integer, so a
264        // bare placeholder is inferred as int4, and this crate's parameter
265        // convention sends JSON numbers as text (see `QueryParam::from`). The
266        // mismatch surfaces as the wire error "insufficient data left in
267        // message". The double cast pins the parameter as text and converts
268        // server-side, as `$n::text::vector` does for pgvector.
269        Ok(format!("nlevel(({lhs})::ltree) {op} {rhs}::text::int"))
270    }
271
272    fn ltree_lca_sql(
273        &self,
274        lhs: &str,
275        placeholders: &[String],
276    ) -> Result<String, UnsupportedOperator> {
277        Ok(format!("({lhs})::ltree = lca(ARRAY[{}])", placeholders.join(", ")))
278    }
279
280    fn ltree_id_subquery_sql(
281        &self,
282        pg_op: &str,
283        field_expr: &str,
284        table: &str,
285        path_column: &str,
286        fk_column: Option<&str>,
287        param: &str,
288    ) -> Result<String, UnsupportedOperator> {
289        // A configured table may be schema-qualified (`app.tb_node`); quoting it as one
290        // identifier named a relation that does not exist (#1396).
291        let qt = crate::quote_postgres_identifier(table);
292        let qp = self.quote_identifier(path_column);
293        let qi = self.quote_identifier("id");
294        let path_subquery = format!("SELECT {qp} FROM {qt} WHERE {qi} = {param}");
295
296        if let Some(fk) = fk_column {
297            // Cross-table: fk IN (SELECT id FROM t WHERE path <op> (subquery))
298            let qfk = self.quote_identifier(fk);
299            Ok(format!("{qfk} IN (SELECT {qi} FROM {qt} WHERE {qp} {pg_op} ({path_subquery}))"))
300        } else {
301            // Self-referencing: field_expr <op> (SELECT path FROM t WHERE id = $N)
302            Ok(format!("({field_expr})::ltree {pg_op} ({path_subquery})"))
303        }
304    }
305
306    fn row_view_column_expr(
307        &self,
308        json_column: &str,
309        field_name: &str,
310        col_type: &RowViewColumnType,
311    ) -> String {
312        let pg_type = match col_type {
313            RowViewColumnType::Text => "text",
314            RowViewColumnType::Int32 => "int",
315            RowViewColumnType::Int64 => "bigint",
316            RowViewColumnType::Float64 => "double precision",
317            RowViewColumnType::Boolean => "boolean",
318            RowViewColumnType::Uuid => "uuid",
319            RowViewColumnType::Timestamptz => "timestamptz",
320            RowViewColumnType::Date => "date",
321            RowViewColumnType::Json => "jsonb",
322        };
323        format!("({json_column}->>'{field_name}')::{pg_type}")
324    }
325}
fraiseql_db/dialect/postgres.rs

fraiseql_db/dialect/
postgres.rs
