// Licensed to the Apache Software Foundation (ASF) under one

// or more contributor license agreements. See the NOTICE file

// distributed with this work for additional information

// regarding copyright ownership. The ASF licenses this file

// to you under the Apache License, Version 2.0 (the

// "License"); you may not use this file except in compliance

// with the License. You may obtain a copy of the License at

//

// http://www.apache.org/licenses/LICENSE-2.0

//

// Unless required by applicable law or agreed to in writing,

// software distributed under the License is distributed on an

// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY

// KIND, either express or implied. See the License for the

// specific language governing permissions and limitations

// under the License.

#![warn(clippy::all)]

//! Test SQL syntax specific to PostgreSQL. The parser based on the

//! generic dialect is also tested (on the inputs it can handle).

#[macro_use]

mod test_utils;

use helpers::attached_token::AttachedToken;

use sqlparser::ast::*;

use sqlparser::dialect::{Dialect, GenericDialect, MySqlDialect, PostgreSqlDialect, SQLiteDialect};

use sqlparser::parser::ParserError;

use sqlparser::tokenizer::Span;

use test_utils::*;

#[test]

fn parse_create_table_generated_always_as_identity() {

//With primary key

let sql = "CREATE TABLE table2 (

column21 bigint primary key generated always as identity ,

column30 text );";

pg().one_statement_parses_to(

sql,

"CREATE TABLE table2 (\

column21 BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, \

column30 TEXT)",

);

let sql = "CREATE TABLE table2 (

column21 bigint primary key generated by default as identity ,

column30 text );";

pg().one_statement_parses_to(

sql,

"CREATE TABLE table2 (\

column21 BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, \

column30 TEXT)",

);

//With out primary key

let sql = "CREATE TABLE table2 (

column22 bigint generated always as identity ,

column30 text );";

pg().one_statement_parses_to(

sql,

"CREATE TABLE table2 (\

column22 BIGINT GENERATED ALWAYS AS IDENTITY, \

column30 TEXT)",

);

let sql = "CREATE TABLE table2 (

column22 bigint generated by default as identity ,

column30 text );";

pg().one_statement_parses_to(

sql,

"CREATE TABLE table2 (\

column22 BIGINT GENERATED BY DEFAULT AS IDENTITY, \

column30 TEXT)",

);

let sql = "CREATE TABLE table2 (

column23 bigint generated by default as identity ( INCREMENT 1 MINVALUE 1 MAXVALUE 20 START WITH 10 CACHE 2 NO CYCLE ),

column30 text );";

pg().one_statement_parses_to(

sql,

"CREATE TABLE table2 (\

column23 BIGINT GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 MINVALUE 1 MAXVALUE 20 START WITH 10 CACHE 2 NO CYCLE ), \

column30 TEXT)",

);

let sql = "CREATE TABLE table2 (

column24 bigint generated by default as identity ( INCREMENT 1 MINVALUE 1 MAXVALUE 20 START WITH 10 CACHE 2 CYCLE ),

column30 text );";

pg().one_statement_parses_to(

sql,

"CREATE TABLE table2 (\

column24 BIGINT GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 MINVALUE 1 MAXVALUE 20 START WITH 10 CACHE 2 CYCLE ), \

column30 TEXT)",

);

let sql = "CREATE TABLE table2 (

column25 bigint generated by default as identity ( INCREMENT 1 MINVALUE 1 MAXVALUE 20 START WITH 10 CACHE 2 ),

column30 text );";

pg().one_statement_parses_to(

sql,

"CREATE TABLE table2 (\

column25 BIGINT GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 MINVALUE 1 MAXVALUE 20 START WITH 10 CACHE 2 ), \

column30 TEXT)",

);

let sql = "CREATE TABLE table2 (

column26 bigint generated by default as identity ( INCREMENT 1 MINVALUE 1 MAXVALUE 20 START WITH 10 ),

column30 text );";

pg().one_statement_parses_to(

sql,

"CREATE TABLE table2 (\

column26 BIGINT GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 MINVALUE 1 MAXVALUE 20 START WITH 10 ), \

column30 TEXT)",

);

let sql = "CREATE TABLE table2 (

column27 bigint generated by default as identity ( INCREMENT 1 MINVALUE 1 MAXVALUE 20 ),

column30 text );";

pg().one_statement_parses_to(

sql,

"CREATE TABLE table2 (\

column27 BIGINT GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 MINVALUE 1 MAXVALUE 20 ), \

column30 TEXT)",

);

let sql = "CREATE TABLE table2 (

column28 bigint generated by default as identity ( INCREMENT 1 MINVALUE 1 ),

column30 text );";

pg().one_statement_parses_to(

sql,

"CREATE TABLE table2 (\

column28 BIGINT GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 MINVALUE 1 ), \

column30 TEXT)",

);

let sql = "CREATE TABLE table2 (

column29 bigint generated by default as identity ( INCREMENT 1 ),

column30 text );";

pg().one_statement_parses_to(

sql,

"CREATE TABLE table2 (\

column29 BIGINT GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 ), \

column30 TEXT)",

);

let sql = "CREATE TABLE table2 (

column22 bigint generated always as identity ,

column30 text );";

pg().one_statement_parses_to(

sql,

"CREATE TABLE table2 (\

column22 BIGINT GENERATED ALWAYS AS IDENTITY, \

column30 TEXT)",

);

let sql = "CREATE TABLE table2 (

column23 bigint generated always as identity ( INCREMENT 1 MINVALUE 1 MAXVALUE 20 START WITH 10 CACHE 2 NO CYCLE ),

column30 text );";

pg().one_statement_parses_to(

sql,

"CREATE TABLE table2 (\

column23 BIGINT GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 MINVALUE 1 MAXVALUE 20 START WITH 10 CACHE 2 NO CYCLE ), \

column30 TEXT)",

);

let sql = "CREATE TABLE table2 (

column24 bigint generated always as identity ( INCREMENT 1 MINVALUE 1 MAXVALUE 20 START WITH 10 CACHE 2 CYCLE ),

column30 text );";

pg().one_statement_parses_to(

sql,

"CREATE TABLE table2 (\

column24 BIGINT GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 MINVALUE 1 MAXVALUE 20 START WITH 10 CACHE 2 CYCLE ), \

column30 TEXT)",

);

let sql = "CREATE TABLE table2 (

column25 bigint generated always as identity ( INCREMENT 1 MINVALUE 1 MAXVALUE 20 START WITH 10 CACHE 2 ),

column30 text );";

pg().one_statement_parses_to(

sql,

"CREATE TABLE table2 (\

column25 BIGINT GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 MINVALUE 1 MAXVALUE 20 START WITH 10 CACHE 2 ), \

column30 TEXT)",

);

let sql = "CREATE TABLE table2 (

column26 bigint generated always as identity ( INCREMENT 1 MINVALUE 1 MAXVALUE 20 START WITH 10 ),

column30 text );";

pg().one_statement_parses_to(

sql,

"CREATE TABLE table2 (\

column26 BIGINT GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 MINVALUE 1 MAXVALUE 20 START WITH 10 ), \

column30 TEXT)",

);

let sql = "CREATE TABLE table2 (

column27 bigint generated always as identity ( INCREMENT 1 MINVALUE 1 MAXVALUE 20 ),

column30 text );";

pg().one_statement_parses_to(

sql,

"CREATE TABLE table2 (\

column27 BIGINT GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 MINVALUE 1 MAXVALUE 20 ), \

column30 TEXT)",

);

let sql = "CREATE TABLE table2 (

column28 bigint generated always as identity ( INCREMENT 1 MINVALUE 1 ),

column30 text );";

pg().one_statement_parses_to(

sql,

"CREATE TABLE table2 (\

column28 BIGINT GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 MINVALUE 1 ), \

column30 TEXT)",

);

let sql = "CREATE TABLE table2 (

column29 bigint generated always as identity ( INCREMENT 1 ),

column30 text );";

pg().one_statement_parses_to(

sql,

"CREATE TABLE table2 (\

column29 BIGINT GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 ), \

column30 TEXT)",

);

let sql = "CREATE TABLE table2 (

priceInDollar numeric,

princeInPound numeric GENERATED ALWAYS AS (priceInDollar * 0.22) STORED,

column30 text );";

pg().one_statement_parses_to(

sql,

"CREATE TABLE table2 (\

priceInDollar NUMERIC, \

princeInPound NUMERIC GENERATED ALWAYS AS (priceInDollar * 0.22) STORED, \

column30 TEXT)",

);

}

#[test]

fn parse_create_sequence() {

// SimpleLogger::new().init().unwrap();

let sql1 = "CREATE SEQUENCE name0";

pg().one_statement_parses_to(sql1, "CREATE SEQUENCE name0");

let sql2 = "CREATE SEQUENCE IF NOT EXISTS name0";

pg().one_statement_parses_to(sql2, "CREATE SEQUENCE IF NOT EXISTS name0");

let sql3 = "CREATE TEMPORARY SEQUENCE IF NOT EXISTS name0";

pg().one_statement_parses_to(sql3, "CREATE TEMPORARY SEQUENCE IF NOT EXISTS name0");

let sql4 = "CREATE TEMPORARY SEQUENCE name0";

pg().one_statement_parses_to(sql4, "CREATE TEMPORARY SEQUENCE name0");

let sql2 = "CREATE TEMPORARY SEQUENCE IF NOT EXISTS name1

AS BIGINT

INCREMENT BY 1

MINVALUE 1 MAXVALUE 20

START WITH 10";

pg().one_statement_parses_to(

sql2,

"CREATE TEMPORARY SEQUENCE IF NOT EXISTS name1 AS BIGINT INCREMENT BY 1 MINVALUE 1 MAXVALUE 20 START WITH 10", );

let sql3 = "CREATE SEQUENCE IF NOT EXISTS name2

AS BIGINT

INCREMENT 1

MINVALUE 1 MAXVALUE 20

START WITH 10 CACHE 2 NO CYCLE";

pg().one_statement_parses_to(

sql3,

"CREATE SEQUENCE IF NOT EXISTS name2 AS BIGINT INCREMENT 1 MINVALUE 1 MAXVALUE 20 START WITH 10 CACHE 2 NO CYCLE",

);

let sql4 = "CREATE TEMPORARY SEQUENCE IF NOT EXISTS name3

INCREMENT 1

NO MINVALUE MAXVALUE 20 CACHE 2 CYCLE";

pg().one_statement_parses_to(

sql4,

"CREATE TEMPORARY SEQUENCE IF NOT EXISTS name3 INCREMENT 1 NO MINVALUE MAXVALUE 20 CACHE 2 CYCLE",

);

let sql5 = "CREATE TEMPORARY SEQUENCE IF NOT EXISTS name3

INCREMENT 1

NO MINVALUE MAXVALUE 20 OWNED BY public.table01";

pg().one_statement_parses_to(

sql5,

"CREATE TEMPORARY SEQUENCE IF NOT EXISTS name3 INCREMENT 1 NO MINVALUE MAXVALUE 20 OWNED BY public.table01",

);

let sql6 = "CREATE TEMPORARY SEQUENCE IF NOT EXISTS name3

INCREMENT 1

NO MINVALUE MAXVALUE 20 OWNED BY NONE";

pg().one_statement_parses_to(

sql6,

"CREATE TEMPORARY SEQUENCE IF NOT EXISTS name3 INCREMENT 1 NO MINVALUE MAXVALUE 20 OWNED BY NONE",

);

let sql7 = "CREATE SEQUENCE name4

AS BIGINT

INCREMENT -15

MINVALUE - 2000 MAXVALUE -50

START WITH - 60";

pg().one_statement_parses_to(

sql7,

"CREATE SEQUENCE name4 AS BIGINT INCREMENT -15 MINVALUE -2000 MAXVALUE -50 START WITH -60",

);

let sql8 = "CREATE SEQUENCE name5

AS BIGINT

INCREMENT +10

MINVALUE + 30 MAXVALUE +5000

START WITH + 45";

pg().one_statement_parses_to(

sql8,

"CREATE SEQUENCE name5 AS BIGINT INCREMENT +10 MINVALUE +30 MAXVALUE +5000 START WITH +45",

);

assert!(matches!(

pg().parse_sql_statements("CREATE SEQUENCE foo INCREMENT 1 NO MINVALUE NO"),

Err(ParserError::ParserError(_))

));

}

#[test]

fn parse_drop_sequence() {

// SimpleLogger::new().init().unwrap();

let sql1 = "DROP SEQUENCE IF EXISTS name0 CASCADE";

pg().one_statement_parses_to(sql1, "DROP SEQUENCE IF EXISTS name0 CASCADE");

let sql2 = "DROP SEQUENCE IF EXISTS name1 RESTRICT";

pg().one_statement_parses_to(sql2, "DROP SEQUENCE IF EXISTS name1 RESTRICT");

let sql3 = "DROP SEQUENCE name2 CASCADE";

pg().one_statement_parses_to(sql3, "DROP SEQUENCE name2 CASCADE");

let sql4 = "DROP SEQUENCE name2";

pg().one_statement_parses_to(sql4, "DROP SEQUENCE name2");

let sql5 = "DROP SEQUENCE name0 CASCADE";

pg().one_statement_parses_to(sql5, "DROP SEQUENCE name0 CASCADE");

let sql6 = "DROP SEQUENCE name1 RESTRICT";

pg().one_statement_parses_to(sql6, "DROP SEQUENCE name1 RESTRICT");

let sql7 = "DROP SEQUENCE name1, name2, name3";

pg().one_statement_parses_to(sql7, "DROP SEQUENCE name1, name2, name3");

}

#[test]

fn parse_create_table_with_defaults() {

let sql = "CREATE TABLE public.customer (

customer_id integer DEFAULT nextval(public.customer_customer_id_seq),

store_id smallint NOT NULL,

first_name character varying(45) NOT NULL,

last_name character varying(45) COLLATE \"es_ES\" NOT NULL,

email character varying(50),

address_id smallint NOT NULL,

activebool boolean DEFAULT true NOT NULL,

create_date date DEFAULT now()::text NOT NULL,

last_update timestamp without time zone DEFAULT now() NOT NULL,

active int NOT NULL

) WITH (fillfactor = 20, user_catalog_table = true, autovacuum_vacuum_threshold = 100)";

match pg_and_generic().one_statement_parses_to(sql, "") {

Statement::CreateTable(CreateTable {

name,

columns,

constraints,

table_options,

if_not_exists: false,

external: false,

file_format: None,

location: None,

..

}) => {

use pretty_assertions::assert_eq;

assert_eq!("public.customer", name.to_string());

assert_eq!(

columns,

vec![

ColumnDef {

name: "customer_id".into(),

data_type: DataType::Integer(None),

options: vec![ColumnOptionDef {

name: None,

option: ColumnOption::Default(

pg().verified_expr("nextval(public.customer_customer_id_seq)")

)

}],

},

ColumnDef {

name: "store_id".into(),

data_type: DataType::SmallInt(None),

options: vec![ColumnOptionDef {

name: None,

option: ColumnOption::NotNull,

}],

},

ColumnDef {

name: "first_name".into(),

data_type: DataType::CharacterVarying(Some(

CharacterLength::IntegerLength {

length: 45,

unit: None

}

)),

options: vec![ColumnOptionDef {

name: None,

option: ColumnOption::NotNull,

}],

},

ColumnDef {

name: "last_name".into(),

data_type: DataType::CharacterVarying(Some(

CharacterLength::IntegerLength {

length: 45,

unit: None

}

)),

options: vec![

ColumnOptionDef {

name: None,

option: ColumnOption::Collation(ObjectName::from(vec![

Ident::with_quote('"', "es_ES")

])),

},

ColumnOptionDef {

name: None,

option: ColumnOption::NotNull,

}

],

},

ColumnDef {

name: "email".into(),

data_type: DataType::CharacterVarying(Some(

CharacterLength::IntegerLength {

length: 50,

unit: None

}

)),

options: vec![],

},

ColumnDef {

name: "address_id".into(),

data_type: DataType::SmallInt(None),

options: vec![ColumnOptionDef {

name: None,

option: ColumnOption::NotNull

}],

},

ColumnDef {

name: "activebool".into(),

data_type: DataType::Boolean,

options: vec![

ColumnOptionDef {

name: None,

option: ColumnOption::Default(Expr::Value(

(Value::Boolean(true)).with_empty_span()

)),

},

ColumnOptionDef {

name: None,

option: ColumnOption::NotNull,

}

],

},

ColumnDef {

name: "create_date".into(),

data_type: DataType::Date,

options: vec![

ColumnOptionDef {

name: None,

option: ColumnOption::Default(pg().verified_expr("now()::TEXT"))

},

ColumnOptionDef {

name: None,

option: ColumnOption::NotNull,

}

],

},

ColumnDef {

name: "last_update".into(),

data_type: DataType::Timestamp(None, TimezoneInfo::WithoutTimeZone),

options: vec![

ColumnOptionDef {

name: None,

option: ColumnOption::Default(pg().verified_expr("now()")),

},

ColumnOptionDef {

name: None,

option: ColumnOption::NotNull,

}

],

},

ColumnDef {

name: "active".into(),

data_type: DataType::Int(None),

options: vec![ColumnOptionDef {

name: None,

option: ColumnOption::NotNull

}],

},

]

);

assert!(constraints.is_empty());

let with_options = match table_options {

CreateTableOptions::With(options) => options,

_ => unreachable!(),

};

assert_eq!(

with_options,

vec![

SqlOption::KeyValue {

key: "fillfactor".into(),

value: Expr::value(number("20"))

},

SqlOption::KeyValue {

key: "user_catalog_table".into(),

value: Expr::Value((Value::Boolean(true)).with_empty_span())

},

SqlOption::KeyValue {

key: "autovacuum_vacuum_threshold".into(),

value: Expr::value(number("100"))

},

]

);

}

_ => unreachable!(),

}

}

#[test]

fn parse_cast_in_default_expr() {

pg().verified_stmt("CREATE TABLE t (c TEXT DEFAULT (foo())::TEXT)");

pg().verified_stmt("CREATE TABLE t (c TEXT DEFAULT (foo())::INT::TEXT)");

pg().verified_stmt("CREATE TABLE t (c TEXT DEFAULT (foo())::TEXT NOT NULL)");

}

#[test]

fn parse_create_table_from_pg_dump() {

let sql = "CREATE TABLE public.customer (

customer_id integer DEFAULT nextval('public.customer_customer_id_seq'::regclass) NOT NULL,

store_id smallint NOT NULL,

first_name character varying(45) NOT NULL,

last_name character varying(45) NOT NULL,

info text[],

address_id smallint NOT NULL,

activebool boolean DEFAULT true NOT NULL,

create_date date DEFAULT now()::DATE NOT NULL,

create_date1 date DEFAULT 'now'::TEXT::date NOT NULL,

last_update timestamp without time zone DEFAULT now(),

release_year public.year,

active int

)";

pg().one_statement_parses_to(sql, "CREATE TABLE public.customer (\

customer_id INTEGER DEFAULT nextval('public.customer_customer_id_seq'::REGCLASS) NOT NULL, \

store_id SMALLINT NOT NULL, \

first_name CHARACTER VARYING(45) NOT NULL, \

last_name CHARACTER VARYING(45) NOT NULL, \

info TEXT[], \

address_id SMALLINT NOT NULL, \

activebool BOOLEAN DEFAULT true NOT NULL, \

create_date DATE DEFAULT now()::DATE NOT NULL, \

create_date1 DATE DEFAULT 'now'::TEXT::DATE NOT NULL, \

last_update TIMESTAMP WITHOUT TIME ZONE DEFAULT now(), \

release_year public.year, \

active INT\

)");

}

#[test]

fn parse_create_table_with_inherit() {

let sql = "\

CREATE TABLE bazaar.settings (\

settings_id UUID PRIMARY KEY DEFAULT uuid_generate_v4() NOT NULL, \

user_id UUID UNIQUE, \

value TEXT[], \

use_metric BOOLEAN DEFAULT true\

)";

pg().verified_stmt(sql);

}

#[test]

fn parse_create_table_empty() {

// Zero-column tables are weird, but supported by at least PostgreSQL.

// <https://github.com/sqlparser-rs/sqlparser-rs/pull/94>

let _ = pg_and_generic().verified_stmt("CREATE TABLE t ()");

}

#[test]

fn parse_create_table_constraints_only() {

// Zero-column tables can also have constraints in PostgreSQL

let sql = "CREATE TABLE t (CONSTRAINT positive CHECK (2 > 1))";

let ast = pg_and_generic().verified_stmt(sql);

match ast {

Statement::CreateTable(CreateTable {

name,

columns,

constraints,

..

}) => {

assert_eq!("t", name.to_string());

assert!(columns.is_empty());

assert_eq!(

only(constraints).to_string(),

"CONSTRAINT positive CHECK (2 > 1)"

);

}

_ => unreachable!(),

};

}

#[test]

fn parse_exclude_constraint() {

let dialects = pg_and_generic();

let sql = "CREATE TABLE t (room INT, CONSTRAINT no_overlap EXCLUDE USING gist (room WITH =))";

match dialects.verified_stmt(sql) {

Statement::CreateTable(create_table) => match &create_table.constraints[..] {

[TableConstraint::Exclude(c)] => {

assert_eq!(c.name, Some(Ident::new("no_overlap")));

assert_eq!(c.index_method, Some(Ident::new("gist")));

assert_eq!(c.elements.len(), 1);

assert_eq!(

c.elements[0].column.column.expr,

Expr::Identifier(Ident::new("room"))

);

assert_eq!(c.elements[0].operator.to_string(), "=");

assert!(c.elements[0].column.operator_class.is_none());

assert!(c.include.is_empty());

assert!(c.where_clause.is_none());

assert!(c.characteristics.is_none());

}

other => panic!("expected single Exclude constraint, got {other:?}"),

},

other => panic!("expected CreateTable, got {other:?}"),

}

for sql in [

"CREATE TABLE t (col INT, EXCLUDE (col WITH =))",

"CREATE TABLE t (room INT, during INT, EXCLUDE USING gist (room WITH =, during WITH &&))",

"CREATE TABLE t (col INT, EXCLUDE USING gist (col WITH =) INCLUDE (col))",

"CREATE TABLE t (col INT, EXCLUDE USING gist (col WITH =) WHERE (col > 0))",

"CREATE TABLE t (col INT, EXCLUDE USING gist (col WITH =) DEFERRABLE INITIALLY DEFERRED)",

"CREATE TABLE t (col INT, EXCLUDE USING gist (col WITH =) NOT DEFERRABLE INITIALLY IMMEDIATE)",

"CREATE TABLE t (col INT, EXCLUDE USING btree (col ASC NULLS LAST WITH =))",

"CREATE TABLE t (col INT, EXCLUDE USING btree (col DESC NULLS FIRST WITH =))",

"CREATE TABLE t (col TEXT, EXCLUDE USING gist (col text_pattern_ops WITH =))",

"CREATE TABLE t (name TEXT, EXCLUDE USING gist ((lower(name)) text_pattern_ops WITH =))",

"CREATE TABLE t (name TEXT, EXCLUDE USING btree (name COLLATE \"C\" WITH =))",

"CREATE TABLE t (col INT, EXCLUDE USING gist (col WITH OPERATOR(pg_catalog.=)))",

"CREATE TABLE t (col INT, CONSTRAINT c EXCLUDE USING gist (col ASC WITH OPERATOR(pg_catalog.=)))",

"CREATE TABLE t (CONSTRAINT no_overlap EXCLUDE USING gist (room WITH =, during WITH &&) INCLUDE (id) WHERE (active = true))",

"ALTER TABLE t ADD CONSTRAINT no_overlap EXCLUDE USING gist (room WITH =)",

] {

dialects.verified_stmt(sql);

}

// Error cases: malformed EXCLUDE syntax must be rejected with a useful

// message rather than silently accepted.

for (sql, expected_message) in [

(

"CREATE TABLE t (CONSTRAINT c EXCLUDE USING gist (col))",

"Expected: WITH, found: )",

),

(

"CREATE TABLE t (CONSTRAINT c EXCLUDE USING gist ())",

"Expected: an expression, found: )",

),

(

"CREATE TABLE t (CONSTRAINT c EXCLUDE USING gist (col WITH))",

"Expected: ',' or ')' after column definition, found: EOF",

),

(

"CREATE TABLE t (CONSTRAINT c EXCLUDE foo)",

"Expected: (, found: foo",

),

] {

let result = dialects.parse_sql_statements(sql);

assert_eq!(

ParserError::ParserError(expected_message.to_string()),

result.unwrap_err()

);

}

// Dialects that do not opt in via `supports_exclude_constraint` must

// refuse to parse `EXCLUDE` constraints.

let unsupported = all_dialects_where(|d| !d.supports_exclude_constraint());

let sql = "CREATE TABLE t (col INT, EXCLUDE USING gist (col WITH =))";

for dialect in unsupported.dialects {

let parser = TestedDialects::new(vec![dialect]);

assert!(

parser.parse_sql_statements(sql).is_err(),

"dialect unexpectedly accepted EXCLUDE: {sql}"

);

}

}

#[test]

fn parse_create_table_like_with_defaults() {

let sql = "CREATE TABLE new (LIKE old INCLUDING DEFAULTS)";

match pg().verified_stmt(sql) {

Statement::CreateTable(stmt) => {

assert_eq!(

stmt.name,

ObjectName::from(vec![Ident::new("new".to_string())])

);

assert_eq!(

stmt.like,

Some(CreateTableLikeKind::Parenthesized(CreateTableLike {

name: ObjectName::from(vec![Ident::new("old".to_string())]),

defaults: Some(CreateTableLikeDefaults::Including),

}))

)

}

_ => unreachable!(),

}

let sql = "CREATE TABLE new (LIKE old EXCLUDING DEFAULTS)";

match pg().verified_stmt(sql) {

Statement::CreateTable(stmt) => {

assert_eq!(

stmt.name,

ObjectName::from(vec![Ident::new("new".to_string())])

);

assert_eq!(

stmt.like,

Some(CreateTableLikeKind::Parenthesized(CreateTableLike {

name: ObjectName::from(vec![Ident::new("old".to_string())]),

defaults: Some(CreateTableLikeDefaults::Excluding),

}))

)

}

_ => unreachable!(),

}

}

#[test]

fn parse_alter_table_constraints_rename() {

match alter_table_op(

pg().verified_stmt("ALTER TABLE tab RENAME CONSTRAINT old_name TO new_name"),

) {

AlterTableOperation::RenameConstraint { old_name, new_name } => {

assert_eq!(old_name.to_string(), "old_name");

assert_eq!(new_name.to_string(), "new_name");

}

_ => unreachable!(),

}

}

#[test]

fn parse_alter_table_constraints_unique_nulls_distinct() {

match pg_and_generic()

.verified_stmt("ALTER TABLE t ADD CONSTRAINT b UNIQUE NULLS NOT DISTINCT (c)")

{

Statement::AlterTable(alter_table) => match &alter_table.operations[0] {

AlterTableOperation::AddConstraint {

constraint: TableConstraint::Unique(constraint),

..

} => {

let nulls_distinct = &constraint.nulls_distinct;

assert_eq!(nulls_distinct, &NullsDistinctOption::NotDistinct)

}

_ => unreachable!(),

},

_ => unreachable!(),

}

pg_and_generic().verified_stmt("ALTER TABLE t ADD CONSTRAINT b UNIQUE NULLS DISTINCT (c)");

pg_and_generic().verified_stmt("ALTER TABLE t ADD CONSTRAINT b UNIQUE (c)");

}

#[test]

fn parse_alter_table_constraint_using_index() {

// PRIMARY KEY USING INDEX

// https://www.postgresql.org/docs/current/sql-altertable.html

let sql = "ALTER TABLE tab ADD CONSTRAINT c PRIMARY KEY USING INDEX my_index";

match pg_and_generic().verified_stmt(sql) {

Statement::AlterTable(alter_table) => match &alter_table.operations[0] {

AlterTableOperation::AddConstraint {

constraint: TableConstraint::PrimaryKeyUsingIndex(c),

..

} => {

assert_eq!(c.name.as_ref().unwrap().to_string(), "c");

assert_eq!(c.index_name.to_string(), "my_index");

assert!(c.characteristics.is_none());

}

_ => unreachable!(),

},

_ => unreachable!(),

}

// UNIQUE USING INDEX

pg_and_generic().verified_stmt("ALTER TABLE tab ADD CONSTRAINT c UNIQUE USING INDEX my_index");

// Without constraint name

pg_and_generic().verified_stmt("ALTER TABLE tab ADD PRIMARY KEY USING INDEX my_index");

pg_and_generic().verified_stmt("ALTER TABLE tab ADD UNIQUE USING INDEX my_index");

// With DEFERRABLE

pg_and_generic().verified_stmt(

"ALTER TABLE tab ADD CONSTRAINT c PRIMARY KEY USING INDEX my_index DEFERRABLE",

);

pg_and_generic().verified_stmt(

"ALTER TABLE tab ADD CONSTRAINT c UNIQUE USING INDEX my_index NOT DEFERRABLE INITIALLY IMMEDIATE",

);

pg_and_generic().verified_stmt(

"ALTER TABLE tab ADD CONSTRAINT c PRIMARY KEY USING INDEX my_index DEFERRABLE INITIALLY DEFERRED",

);

}

#[test]

fn parse_constraint_include_columns() {

// INCLUDE covering columns on PRIMARY KEY / UNIQUE table constraints.

// https://www.postgresql.org/docs/current/sql-createtable.html

pg_and_generic().verified_stmt(

"CREATE TABLE t (id INT, payload TEXT, CONSTRAINT t_pk PRIMARY KEY (id) INCLUDE (payload))",

);

pg_and_generic().verified_stmt(

"CREATE TABLE t (id INT, email TEXT, payload TEXT, CONSTRAINT t_uk UNIQUE (email) INCLUDE (payload))",

);

pg_and_generic().verified_stmt(

"CREATE TABLE t (a INT, b INT, c INT, d INT, CONSTRAINT t_pk PRIMARY KEY (a, b) INCLUDE (c, d))",

);

pg_and_generic()

.verified_stmt("ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY (id) INCLUDE (payload)");

pg_and_generic()

.verified_stmt("ALTER TABLE t ADD CONSTRAINT t_uk UNIQUE (email) INCLUDE (payload)");

pg_and_generic().verified_stmt(

"ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY (id) INCLUDE (payload) DEFERRABLE INITIALLY DEFERRED",

);

match pg_and_generic().verified_stmt(

"ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY (id) INCLUDE (payload, extra)",

) {

Statement::AlterTable(alter_table) => match &alter_table.operations[0] {

AlterTableOperation::AddConstraint {

constraint: TableConstraint::PrimaryKey(pk),

..

} => {

assert_eq!(pk.name.as_ref().unwrap().to_string(), "t_pk");

assert_eq!(

pk.include

.iter()

.map(|i| i.value.clone())

.collect::<Vec<_>>(),

vec!["payload".to_string(), "extra".to_string()]

);

}

_ => unreachable!(),

},

_ => unreachable!(),

}

}

#[test]

fn parse_alter_table_disable() {

pg_and_generic().verified_stmt("ALTER TABLE tab DISABLE ROW LEVEL SECURITY");

pg_and_generic().verified_stmt("ALTER TABLE tab DISABLE RULE rule_name");

}

#[test]

fn parse_alter_table_disable_trigger() {

pg_and_generic().verified_stmt("ALTER TABLE tab DISABLE TRIGGER ALL");

pg_and_generic().verified_stmt("ALTER TABLE tab DISABLE TRIGGER USER");

pg_and_generic().verified_stmt("ALTER TABLE tab DISABLE TRIGGER trigger_name");

}

#[test]

fn parse_alter_table_enable() {

pg_and_generic().verified_stmt("ALTER TABLE tab ENABLE ALWAYS RULE rule_name");

pg_and_generic().verified_stmt("ALTER TABLE tab ENABLE ALWAYS TRIGGER trigger_name");

pg_and_generic().verified_stmt("ALTER TABLE tab ENABLE REPLICA TRIGGER trigger_name");

pg_and_generic().verified_stmt("ALTER TABLE tab ENABLE REPLICA RULE rule_name");

pg_and_generic().verified_stmt("ALTER TABLE tab ENABLE ROW LEVEL SECURITY");

pg_and_generic().verified_stmt("ALTER TABLE tab FORCE ROW LEVEL SECURITY");

pg_and_generic().verified_stmt("ALTER TABLE tab NO FORCE ROW LEVEL SECURITY");

pg_and_generic().verified_stmt("ALTER TABLE tab ENABLE RULE rule_name");

pg_and_generic().verified_stmt("ALTER TABLE tab ENABLE TRIGGER ALL");

pg_and_generic().verified_stmt("ALTER TABLE tab ENABLE TRIGGER USER");

pg_and_generic().verified_stmt("ALTER TABLE tab ENABLE TRIGGER trigger_name");

}

#[test]

fn parse_truncate_table() {

pg_and_generic()

.verified_stmt("TRUNCATE TABLE \"users\", \"orders\" RESTART IDENTITY RESTRICT");

pg_and_generic().verified_stmt("TRUNCATE users, orders RESTART IDENTITY");

}

#[test]

fn parse_create_extension() {

pg_and_generic().verified_stmt("CREATE EXTENSION extension_name");

pg_and_generic().verified_stmt("CREATE EXTENSION extension_name WITH SCHEMA schema_name");

pg_and_generic().verified_stmt("CREATE EXTENSION extension_name WITH VERSION version");

pg_and_generic().verified_stmt("CREATE EXTENSION extension_name WITH CASCADE");

pg_and_generic().verified_stmt(

"CREATE EXTENSION extension_name WITH SCHEMA schema_name VERSION version CASCADE",

);

pg_and_generic()

.verified_stmt("CREATE EXTENSION extension_name WITH SCHEMA schema_name CASCADE");

pg_and_generic().verified_stmt("CREATE EXTENSION extension_name WITH VERSION version CASCADE");

pg_and_generic()

.verified_stmt("CREATE EXTENSION extension_name WITH SCHEMA schema_name VERSION version");

}

#[test]

fn parse_drop_extension() {

assert_eq!(

pg_and_generic().verified_stmt("DROP EXTENSION extension_name"),

Statement::DropExtension(DropExtension {

names: vec!["extension_name".into()],

if_exists: false,

cascade_or_restrict: None,

})

);

assert_eq!(

pg_and_generic().verified_stmt("DROP EXTENSION extension_name CASCADE"),

Statement::DropExtension(DropExtension {

names: vec!["extension_name".into()],

if_exists: false,

cascade_or_restrict: Some(ReferentialAction::Cascade),

})

);

assert_eq!(

pg_and_generic().verified_stmt("DROP EXTENSION extension_name RESTRICT"),

Statement::DropExtension(DropExtension {

names: vec!["extension_name".into()],

if_exists: false,

cascade_or_restrict: Some(ReferentialAction::Restrict),

})

);

assert_eq!(

pg_and_generic().verified_stmt("DROP EXTENSION extension_name, extension_name2 CASCADE"),

Statement::DropExtension(DropExtension {

names: vec!["extension_name".into(), "extension_name2".into()],

if_exists: false,

cascade_or_restrict: Some(ReferentialAction::Cascade),

})

);

assert_eq!(

pg_and_generic().verified_stmt("DROP EXTENSION extension_name, extension_name2 RESTRICT"),

Statement::DropExtension(DropExtension {

names: vec!["extension_name".into(), "extension_name2".into()],

if_exists: false,

cascade_or_restrict: Some(ReferentialAction::Restrict),

})

);

assert_eq!(

pg_and_generic().verified_stmt("DROP EXTENSION IF EXISTS extension_name"),

Statement::DropExtension(DropExtension {

names: vec!["extension_name".into()],

if_exists: true,

cascade_or_restrict: None,

})

);

assert_eq!(

pg_and_generic().verified_stmt("DROP EXTENSION IF EXISTS extension_name CASCADE"),

Statement::DropExtension(DropExtension {

names: vec!["extension_name".into()],

if_exists: true,

cascade_or_restrict: Some(ReferentialAction::Cascade),

})

);

assert_eq!(

pg_and_generic().verified_stmt("DROP EXTENSION IF EXISTS extension_name RESTRICT"),

Statement::DropExtension(DropExtension {

names: vec!["extension_name".into()],

if_exists: true,

cascade_or_restrict: Some(ReferentialAction::Restrict),

})

);

assert_eq!(

pg_and_generic()

.verified_stmt("DROP EXTENSION IF EXISTS extension_name1, extension_name2 CASCADE"),

Statement::DropExtension(DropExtension {

names: vec!["extension_name1".into(), "extension_name2".into()],

if_exists: true,

cascade_or_restrict: Some(ReferentialAction::Cascade),

})

);

assert_eq!(

pg_and_generic()

.verified_stmt("DROP EXTENSION IF EXISTS extension_name1, extension_name2 RESTRICT"),

Statement::DropExtension(DropExtension {

names: vec!["extension_name1".into(), "extension_name2".into()],

if_exists: true,

cascade_or_restrict: Some(ReferentialAction::Restrict),

})

);

}

#[test]

fn parse_create_collation() {

assert_eq!(

pg_and_generic()

.verified_stmt("CREATE COLLATION test3 (provider = icu, lc_collate = 'en_US.utf8')",),

Statement::CreateCollation(CreateCollation {

if_not_exists: false,

name: ObjectName::from(vec![Ident::new("test3")]),

definition: CreateCollationDefinition::Options(vec![

SqlOption::KeyValue {

key: Ident::new("provider"),

value: Expr::Identifier(Ident::new("icu")),

},

SqlOption::KeyValue {

key: Ident::new("lc_collate"),

value: Expr::Value(

Value::SingleQuotedString("en_US.utf8".to_string()).with_empty_span(),

),