sea_query/backend/postgres/
table.rs

1use super::*;
2use crate::write_int;
3
4impl TableBuilder for PostgresQueryBuilder {
5    fn prepare_column_def(&self, column_def: &ColumnDef, sql: &mut impl SqlWriter) {
6        fn f(this: &PostgresQueryBuilder, column_def: &ColumnDef, sql: &mut impl SqlWriter) {
7            this.prepare_column_type_check_auto_increment(column_def, sql);
8        }
9
10        self.prepare_column_def_common(column_def, sql, |column_def, sql| f(self, column_def, sql));
11    }
12
13    fn prepare_column_type(&self, column_type: &ColumnType, sql: &mut impl SqlWriter) {
14        match column_type {
15            ColumnType::Char(length) => match length {
16                Some(length) => {
17                    sql.write_str("char(").unwrap();
18                    write_int(sql, *length);
19                    sql.write_char(')')
20                }
21                None => sql.write_str("char"),
22            },
23            ColumnType::String(length) => match length {
24                StringLen::N(length) => {
25                    sql.write_str("varchar(").unwrap();
26                    write_int(sql, *length);
27                    sql.write_char(')')
28                }
29                _ => sql.write_str("varchar"),
30            },
31            ColumnType::Text => sql.write_str("text"),
32            ColumnType::TinyInteger | ColumnType::TinyUnsigned => sql.write_str("smallint"),
33            ColumnType::SmallInteger => sql.write_str("smallint"),
34            ColumnType::Integer | ColumnType::SmallUnsigned => sql.write_str("integer"),
35            ColumnType::BigInteger | ColumnType::Unsigned | ColumnType::BigUnsigned => {
36                sql.write_str("bigint")
37            }
38            ColumnType::Float => sql.write_str("real"),
39            ColumnType::Double => sql.write_str("double precision"),
40            ColumnType::Decimal(precision) => match precision {
41                Some((precision, scale)) => {
42                    sql.write_str("decimal(").unwrap();
43                    write_int(sql, *precision);
44                    sql.write_str(", ").unwrap();
45                    write_int(sql, *scale);
46                    sql.write_char(')')
47                }
48                None => sql.write_str("decimal"),
49            },
50            ColumnType::DateTime => sql.write_str("timestamp without time zone"),
51            ColumnType::Timestamp => sql.write_str("timestamp"),
52            ColumnType::TimestampWithTimeZone => sql.write_str("timestamp with time zone"),
53            ColumnType::Time => sql.write_str("time"),
54            ColumnType::Date => sql.write_str("date"),
55            ColumnType::Interval(fields, precision) => {
56                sql.write_str("interval").unwrap();
57
58                if let Some(fields) = fields {
59                    write!(sql, " {fields}").unwrap();
60                }
61
62                if let Some(precision) = precision {
63                    sql.write_char('(').unwrap();
64                    write_int(sql, *precision);
65                    sql.write_char(')').unwrap();
66                }
67                Ok(())
68            }
69            ColumnType::Binary(_) | ColumnType::VarBinary(_) | ColumnType::Blob => {
70                sql.write_str("bytea")
71            }
72            ColumnType::Bit(length) => match length {
73                Some(length) => {
74                    sql.write_str("bit(").unwrap();
75                    write_int(sql, *length);
76                    sql.write_char(')')
77                }
78                None => sql.write_str("bit"),
79            },
80            ColumnType::VarBit(length) => {
81                sql.write_str("varbit(").unwrap();
82                write_int(sql, *length);
83                sql.write_char(')')
84            }
85            ColumnType::Boolean => sql.write_str("bool"),
86            ColumnType::Money(precision) => match precision {
87                Some((precision, scale)) => {
88                    sql.write_str("money(").unwrap();
89                    write_int(sql, *precision);
90                    sql.write_str(", ").unwrap();
91                    write_int(sql, *scale);
92                    sql.write_char(')')
93                }
94                None => sql.write_str("money"),
95            },
96            ColumnType::Json => sql.write_str("json"),
97            ColumnType::JsonBinary => sql.write_str("jsonb"),
98            ColumnType::Uuid => sql.write_str("uuid"),
99            ColumnType::Array(elem_type) => {
100                self.prepare_column_type(elem_type, sql);
101                sql.write_str("[]")
102            }
103            ColumnType::Vector(size) => match size {
104                Some(size) => {
105                    sql.write_str("vector(").unwrap();
106                    write_int(sql, *size);
107                    sql.write_str(")")
108                }
109                None => sql.write_str("vector"),
110            },
111            ColumnType::Custom(iden) => sql.write_str(&iden.0),
112            ColumnType::Enum { name, .. } => sql.write_str(&name.0),
113            ColumnType::Cidr => sql.write_str("cidr"),
114            ColumnType::Inet => sql.write_str("inet"),
115            ColumnType::MacAddr => sql.write_str("macaddr"),
116            ColumnType::Year => unimplemented!("Year is not available in Postgres."),
117            ColumnType::LTree => sql.write_str("ltree"),
118        }
119        .unwrap()
120    }
121
122    fn column_spec_auto_increment_keyword(&self) -> &str {
123        ""
124    }
125
126    fn prepare_table_alter_statement(&self, alter: &TableAlterStatement, sql: &mut impl SqlWriter) {
127        if alter.options.is_empty() {
128            panic!("No alter option found")
129        };
130        sql.write_str("ALTER TABLE ").unwrap();
131        if let Some(table) = &alter.table {
132            self.prepare_table_ref_table_stmt(table, sql);
133            sql.write_str(" ").unwrap();
134        }
135
136        let mut opts = alter.options.iter();
137
138        join_io!(
139            opts,
140            opt,
141            join {
142                sql.write_str(", ").unwrap();
143            },
144            do {
145                match opt {
146                    TableAlterOption::AddColumn(AddColumnOption {
147                        column,
148                        if_not_exists,
149                    }) => {
150                        sql.write_str("ADD COLUMN ").unwrap();
151                        if *if_not_exists {
152                            sql.write_str("IF NOT EXISTS ").unwrap();
153                        }
154
155                        self.prepare_column_def_common(column, sql, |column_def, sql| {
156                            if let Some(column_type) = &column_def.types {
157                                write!(sql, " ").unwrap();
158                                if column_def.spec.auto_increment {
159                                    self.prepare_column_auto_increment(column_type, sql);
160                                } else {
161                                    self.prepare_column_type(column_type, sql);
162                                }
163                            }
164                        });
165                    }
166                    TableAlterOption::ModifyColumn(column_def) => {
167                        self.prepare_modify_column(sql, column_def);
168                    }
169                    TableAlterOption::RenameColumn(from_name, to_name) => {
170                        sql.write_str("RENAME COLUMN ").unwrap();
171                        self.prepare_iden(from_name, sql);
172                        sql.write_str(" TO ").unwrap();
173                        self.prepare_iden(to_name, sql);
174                    }
175                    TableAlterOption::DropColumn(DropColumnOption{
176                        column_name,
177                        if_exists
178                    }) => {
179                        sql.write_str("DROP COLUMN ").unwrap();
180                         if *if_exists {
181                            sql.write_str("IF EXISTS ").unwrap();
182                        }
183                        self.prepare_iden(column_name, sql);
184                    }
185                    TableAlterOption::DropForeignKey(name) => {
186                        let mut foreign_key = TableForeignKey::new();
187                        foreign_key.name(name.to_string());
188                        let drop = ForeignKeyDropStatement {
189                            foreign_key,
190                            table: None,
191                        };
192                        self.prepare_foreign_key_drop_statement_internal(
193                            &drop,
194                            sql,
195                            Mode::TableAlter,
196                        );
197                    }
198                    TableAlterOption::AddForeignKey(foreign_key) => {
199                        let create = ForeignKeyCreateStatement {
200                            foreign_key: foreign_key.to_owned(),
201                        };
202                        self.prepare_foreign_key_create_statement_internal(
203                            &create,
204                            sql,
205                            Mode::TableAlter,
206                        );
207                    }
208                    TableAlterOption::DropConstraint(name) => {
209                        sql.write_str("DROP CONSTRAINT ").unwrap();
210                        self.prepare_iden(name, sql);
211                    }
212                }
213            }
214        );
215    }
216
217    fn prepare_table_rename_statement(
218        &self,
219        rename: &TableRenameStatement,
220        sql: &mut impl SqlWriter,
221    ) {
222        sql.write_str("ALTER TABLE ").unwrap();
223        if let Some(from_name) = &rename.from_name {
224            self.prepare_table_ref_table_stmt(from_name, sql);
225        }
226        sql.write_str(" RENAME TO ").unwrap();
227        if let Some(to_name) = &rename.to_name {
228            self.prepare_table_ref_table_stmt(to_name, sql);
229        }
230    }
231
232    fn prepare_partition_by(&self, partition_by: &PartitionBy, sql: &mut impl SqlWriter) {
233        match partition_by {
234            PartitionBy::Range(cols) => {
235                sql.write_str("RANGE (").unwrap();
236                self.prepare_partition_cols(cols, sql);
237                sql.write_char(')').unwrap();
238            }
239            PartitionBy::List(cols) => {
240                sql.write_str("LIST (").unwrap();
241                self.prepare_partition_cols(cols, sql);
242                sql.write_char(')').unwrap();
243            }
244            PartitionBy::Hash(cols) => {
245                sql.write_str("HASH (").unwrap();
246                self.prepare_partition_cols(cols, sql);
247                sql.write_char(')').unwrap();
248            }
249            PartitionBy::Key(_) => panic!("Postgres does not support PARTITION BY KEY"),
250        }
251    }
252
253    fn prepare_partition_values(
254        &self,
255        partition_values: &PartitionValues,
256        sql: &mut impl SqlWriter,
257    ) {
258        sql.write_str("FOR VALUES ").unwrap();
259        match partition_values {
260            PartitionValues::In(values) => {
261                sql.write_str("IN (").unwrap();
262                self.prepare_partition_exprs(values, sql);
263                sql.write_char(')').unwrap();
264            }
265            PartitionValues::FromTo(from, to) => {
266                sql.write_str("FROM (").unwrap();
267                self.prepare_partition_exprs(from, sql);
268                sql.write_str(") TO (").unwrap();
269                self.prepare_partition_exprs(to, sql);
270                sql.write_char(')').unwrap();
271            }
272            PartitionValues::LessThan(_) => panic!("Postgres does not support VALUES LESS THAN"),
273            PartitionValues::With(modulus, remainder) => {
274                write!(sql, "WITH (MODULUS {modulus}, REMAINDER {remainder})").unwrap();
275            }
276        }
277    }
278}
279
280impl PostgresQueryBuilder {
281    fn prepare_partition_cols(&self, cols: &[DynIden], sql: &mut impl SqlWriter) {
282        let mut first = true;
283        for col in cols {
284            if !first {
285                sql.write_str(", ").unwrap();
286            }
287            self.prepare_iden(col, sql);
288            first = false;
289        }
290    }
291
292    fn prepare_partition_exprs(&self, exprs: &[Expr], sql: &mut impl SqlWriter) {
293        let mut first = true;
294        for expr in exprs {
295            if !first {
296                sql.write_str(", ").unwrap();
297            }
298            self.prepare_expr(expr, sql);
299            first = false;
300        }
301    }
302}
303
304impl PostgresQueryBuilder {
305    fn prepare_column_auto_increment(&self, column_type: &ColumnType, sql: &mut impl SqlWriter) {
306        let num_ty = if cfg!(feature = "option-postgres-use-serial") {
307            match column_type {
308                ColumnType::SmallInteger => "smallserial",
309                ColumnType::Integer => "serial",
310                ColumnType::BigInteger => "bigserial",
311                _ => unimplemented!("{:?} doesn't support auto increment", column_type),
312            }
313        } else {
314            match column_type {
315                ColumnType::SmallInteger => "smallint GENERATED BY DEFAULT AS IDENTITY",
316                ColumnType::Integer => "integer GENERATED BY DEFAULT AS IDENTITY",
317                ColumnType::BigInteger => "bigint GENERATED BY DEFAULT AS IDENTITY",
318                _ => unimplemented!("{:?} doesn't support auto increment", column_type),
319            }
320        };
321
322        sql.write_str(num_ty).unwrap();
323    }
324
325    fn prepare_column_type_check_auto_increment(
326        &self,
327        column_def: &ColumnDef,
328        sql: &mut impl SqlWriter,
329    ) {
330        if let Some(column_type) = &column_def.types {
331            let is_auto_increment = column_def.spec.auto_increment;
332
333            sql.write_str(" ").unwrap();
334
335            if is_auto_increment {
336                self.prepare_column_auto_increment(column_type, sql);
337            } else {
338                self.prepare_column_type(column_type, sql);
339            }
340        }
341    }
342
343    fn prepare_column_def_common<F, W>(&self, column_def: &ColumnDef, sql: &mut W, f: F)
344    where
345        F: Fn(&ColumnDef, &mut W),
346        W: SqlWriter,
347    {
348        self.prepare_iden(&column_def.name, sql);
349
350        f(column_def, sql);
351
352        self.prepare_column_spec(&column_def.spec, sql);
353    }
354
355    fn prepare_modify_column(&self, sql: &mut impl SqlWriter, column_def: &ColumnDef) {
356        let mut is_first = true;
357
358        macro_rules! write_comma_if_not_first {
359            () => {
360                if !is_first {
361                    write!(sql, ", ").unwrap();
362                } else {
363                    is_first = false
364                }
365            };
366        }
367
368        if let Some(column_type) = &column_def.types {
369            write!(sql, "ALTER COLUMN ").unwrap();
370            self.prepare_iden(&column_def.name, sql);
371            write!(sql, " TYPE ").unwrap();
372            self.prepare_column_type(column_type, sql);
373            is_first = false;
374        }
375
376        if column_def.spec.auto_increment {
377            //
378        }
379
380        if let Some(nullable) = column_def.spec.nullable {
381            write_comma_if_not_first!();
382            write!(sql, "ALTER COLUMN ").unwrap();
383            self.prepare_iden(&column_def.name, sql);
384            if nullable {
385                write!(sql, " DROP NOT NULL").unwrap();
386            } else {
387                write!(sql, " SET NOT NULL").unwrap();
388            }
389        }
390
391        if let Some(default) = &column_def.spec.default {
392            write_comma_if_not_first!();
393            write!(sql, "ALTER COLUMN ").unwrap();
394            self.prepare_iden(&column_def.name, sql);
395            write!(sql, " SET DEFAULT ").unwrap();
396            QueryBuilder::prepare_expr(self, default, sql);
397        }
398        if column_def.spec.unique {
399            write_comma_if_not_first!();
400            write!(sql, "ADD UNIQUE (").unwrap();
401            self.prepare_iden(&column_def.name, sql);
402            write!(sql, ")").unwrap();
403        }
404        if column_def.spec.primary_key {
405            write_comma_if_not_first!();
406            write!(sql, "ADD PRIMARY KEY (").unwrap();
407            self.prepare_iden(&column_def.name, sql);
408            write!(sql, ")").unwrap();
409        }
410        if let Some(check) = &column_def.spec.check {
411            write_comma_if_not_first!();
412            self.prepare_check_constraint(check, sql);
413        }
414
415        if let Some(x) = &column_def.spec.generated {
416            let _ = x;
417        }
418
419        if let Some(x) = &column_def.spec.comment {
420            let _ = x;
421        }
422
423        if let Some(expr) = &column_def.spec.using {
424            write!(sql, " USING ").unwrap();
425            QueryBuilder::prepare_expr(self, expr, sql);
426        }
427
428        if let Some(extra) = &column_def.spec.extra {
429            write!(sql, "{extra}").unwrap()
430        }
431
432        let _ = is_first;
433    }
434}
sea_query/backend/postgres/table.rs

sea_query/backend/postgres/
table.rs
