// 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::tokenizer::Span;

use test_utils::*;

use sqlparser::ast::*;

use sqlparser::dialect::{GenericDialect, PostgreSqlDialect};

use sqlparser::parser::ParserError;

#[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_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_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 { operations, .. } => match &operations[0] {

AlterTableOperation::AddConstraint(TableConstraint::Unique {

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_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 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 {

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 {

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 {

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 {

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 {

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 {

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 {

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 {

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 {

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 {

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

if_exists: true,

cascade_or_restrict: Some(ReferentialAction::Restrict),

}

);

}

#[test]

fn parse_alter_table_alter_column() {

pg().one_statement_parses_to(

"ALTER TABLE tab ALTER COLUMN is_active TYPE TEXT USING 'text'",

"ALTER TABLE tab ALTER COLUMN is_active SET DATA TYPE TEXT USING 'text'",

);

match alter_table_op(

pg().verified_stmt(

"ALTER TABLE tab ALTER COLUMN is_active SET DATA TYPE TEXT USING 'text'",

),

) {

AlterTableOperation::AlterColumn { column_name, op } => {

assert_eq!("is_active", column_name.to_string());

let using_expr =

Expr::Value(Value::SingleQuotedString("text".to_string()).with_empty_span());

assert_eq!(

op,

AlterColumnOperation::SetDataType {

data_type: DataType::Text,

using: Some(using_expr),

}

);

}

_ => unreachable!(),

}

}

#[test]

fn parse_alter_table_alter_column_add_generated() {

pg_and_generic()

.verified_stmt("ALTER TABLE t ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY");

pg_and_generic()

.verified_stmt("ALTER TABLE t ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY");

pg_and_generic().verified_stmt("ALTER TABLE t ALTER COLUMN id ADD GENERATED AS IDENTITY");

pg_and_generic().verified_stmt(

"ALTER TABLE t ALTER COLUMN id ADD GENERATED AS IDENTITY ( INCREMENT 1 MINVALUE 1 )",

);

pg_and_generic().verified_stmt("ALTER TABLE t ALTER COLUMN id ADD GENERATED AS IDENTITY ( )");

let res = pg().parse_sql_statements(

"ALTER TABLE t ALTER COLUMN id ADD GENERATED ( INCREMENT 1 MINVALUE 1 )",

);

assert_eq!(

ParserError::ParserError("Expected: AS, found: (".to_string()),

res.unwrap_err()

);

let res = pg().parse_sql_statements(

"ALTER TABLE t ALTER COLUMN id ADD GENERATED AS IDENTITY ( INCREMENT )",

);

assert_eq!(

ParserError::ParserError("Expected: a value, found: )".to_string()),

res.unwrap_err()

);

let res =

pg().parse_sql_statements("ALTER TABLE t ALTER COLUMN id ADD GENERATED AS IDENTITY (");

assert_eq!(

ParserError::ParserError("Expected: ), found: EOF".to_string()),

res.unwrap_err()

);

}

#[test]

fn parse_alter_table_add_columns() {

match pg().verified_stmt("ALTER TABLE IF EXISTS ONLY tab ADD COLUMN a TEXT, ADD COLUMN b INT") {

Statement::AlterTable {

name,

if_exists,

only,

operations,

location: _,

on_cluster: _,

} => {

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

assert!(if_exists);

assert!(only);

assert_eq!(

operations,

vec![

AlterTableOperation::AddColumn {

column_keyword: true,

if_not_exists: false,

column_def: ColumnDef {

name: "a".into(),

data_type: DataType::Text,

options: vec![],

},

column_position: None,

},

AlterTableOperation::AddColumn {

column_keyword: true,

if_not_exists: false,

column_def: ColumnDef {

name: "b".into(),

data_type: DataType::Int(None),

options: vec![],

},

column_position: None,

},

]

);

}

_ => unreachable!(),

}

}

#[test]

fn parse_alter_table_owner_to() {

struct TestCase {

sql: &'static str,

expected_owner: Owner,

}

let test_cases = vec![

TestCase {

sql: "ALTER TABLE tab OWNER TO new_owner",

expected_owner: Owner::Ident(Ident::new("new_owner".to_string())),

},

TestCase {

sql: "ALTER TABLE tab OWNER TO postgres",

expected_owner: Owner::Ident(Ident::new("postgres".to_string())),

},

TestCase {

sql: "ALTER TABLE tab OWNER TO CREATE", // treats CREATE as an identifier

expected_owner: Owner::Ident(Ident::new("CREATE".to_string())),

},

TestCase {

sql: "ALTER TABLE tab OWNER TO \"new_owner\"",

expected_owner: Owner::Ident(Ident::with_quote('\"', "new_owner".to_string())),

},

TestCase {

sql: "ALTER TABLE tab OWNER TO CURRENT_USER",

expected_owner: Owner::CurrentUser,

},

TestCase {

sql: "ALTER TABLE tab OWNER TO CURRENT_ROLE",

expected_owner: Owner::CurrentRole,

},

TestCase {

sql: "ALTER TABLE tab OWNER TO SESSION_USER",

expected_owner: Owner::SessionUser,

},

];

for case in test_cases {

match pg_and_generic().verified_stmt(case.sql) {

Statement::AlterTable {

name,

if_exists: _,

only: _,

operations,

location: _,

on_cluster: _,

} => {

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

assert_eq!(

operations,

vec![AlterTableOperation::OwnerTo {

new_owner: case.expected_owner.clone()

}]

);

}

_ => unreachable!("Expected an AlterTable statement"),

}

}

let res = pg().parse_sql_statements("ALTER TABLE tab OWNER TO CREATE FOO");

assert_eq!(

ParserError::ParserError("Expected: end of statement, found: FOO".to_string()),

res.unwrap_err()

);

let res = pg().parse_sql_statements("ALTER TABLE tab OWNER TO 4");

assert_eq!(

ParserError::ParserError("Expected: CURRENT_USER, CURRENT_ROLE, SESSION_USER or identifier after OWNER TO. sql parser error: Expected: identifier, found: 4".to_string()),

res.unwrap_err()

);

}

#[test]

fn parse_create_table_if_not_exists() {

let sql = "CREATE TABLE IF NOT EXISTS uk_cities ()";

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

match ast {

Statement::CreateTable(CreateTable {

name,

if_not_exists: true,

..

}) => {

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

}

_ => unreachable!(),

}

}

#[test]

fn parse_bad_if_not_exists() {

let res = pg().parse_sql_statements("CREATE TABLE NOT EXISTS uk_cities ()");

assert_eq!(

ParserError::ParserError("Expected: end of statement, found: EXISTS".to_string()),

res.unwrap_err()

);

let res = pg().parse_sql_statements("CREATE TABLE IF EXISTS uk_cities ()");

assert_eq!(

ParserError::ParserError("Expected: end of statement, found: EXISTS".to_string()),

res.unwrap_err()

);

let res = pg().parse_sql_statements("CREATE TABLE IF uk_cities ()");

assert_eq!(

ParserError::ParserError("Expected: end of statement, found: uk_cities".to_string()),

res.unwrap_err()

);

let res = pg().parse_sql_statements("CREATE TABLE IF NOT uk_cities ()");

assert_eq!(

ParserError::ParserError("Expected: end of statement, found: NOT".to_string()),

res.unwrap_err()

);

}

#[test]

fn parse_create_schema_if_not_exists() {

let sql = "CREATE SCHEMA IF NOT EXISTS schema_name";

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

match ast {

Statement::CreateSchema {

if_not_exists: true,

schema_name,

..

} => assert_eq!("schema_name", schema_name.to_string()),

_ => unreachable!(),

}