// Licensed 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 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",

);

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,

with_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),

collation: 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),

collation: 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

}

)),

collation: 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

}

)),

collation: Some(ObjectName(vec![Ident::with_quote('"', "es_ES")])),

options: vec![ColumnOptionDef {

name: None,

option: ColumnOption::NotNull,

}],

},

ColumnDef {

name: "email".into(),

data_type: DataType::CharacterVarying(Some(

CharacterLength::IntegerLength {

length: 50,

unit: None

}

)),

collation: None,

options: vec![],

},

ColumnDef {

name: "address_id".into(),

data_type: DataType::SmallInt(None),

collation: None,

options: vec![ColumnOptionDef {

name: None,

option: ColumnOption::NotNull

}],

},

ColumnDef {

name: "activebool".into(),

data_type: DataType::Boolean,

collation: None,

options: vec![

ColumnOptionDef {

name: None,

option: ColumnOption::Default(Expr::Value(Value::Boolean(true))),

},

ColumnOptionDef {

name: None,

option: ColumnOption::NotNull,

}

],

},

ColumnDef {

name: "create_date".into(),

data_type: DataType::Date,

collation: None,

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),

collation: None,

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),

collation: None,

options: vec![ColumnOptionDef {

name: None,

option: ColumnOption::NotNull

}],

},

]

);

assert!(constraints.is_empty());

assert_eq!(

with_options,

vec![

SqlOption {

name: "fillfactor".into(),

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

},

SqlOption {

name: "user_catalog_table".into(),

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

},

SqlOption {

name: "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_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");

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_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_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()));

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,

collation: None,

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),

collation: 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!(),

}

}

#[test]

fn parse_drop_schema_if_exists() {

let sql = "DROP SCHEMA IF EXISTS schema_name";

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

match ast {

Statement::Drop {

object_type,

if_exists: true,

..

} => assert_eq!(object_type, ObjectType::Schema),

_ => unreachable!(),

}

}

#[test]

fn parse_copy_from_stdin() {

let sql = r#"COPY public.actor (actor_id, first_name, last_name, last_update, value) FROM stdin;

1 PENELOPE GUINESS 2006-02-15 09:34:33 0.11111

2 NICK WAHLBERG 2006-02-15 09:34:33 0.22222

3 ED CHASE 2006-02-15 09:34:33 0.312323

4 JENNIFER DAVIS 2006-02-15 09:34:33 0.3232

5 JOHNNY LOLLOBRIGIDA 2006-02-15 09:34:33 1.343

6 BETTE NICHOLSON 2006-02-15 09:34:33 5.0

7 GRACE MOSTEL 2006-02-15 09:34:33 6.0

8 MATTHEW JOHANSSON 2006-02-15 09:34:33 7.0

9 JOE SWANK 2006-02-15 09:34:33 8.0

10 CHRISTIAN GABLE 2006-02-15 09:34:33 9.1

11 ZERO CAGE 2006-02-15 09:34:33 10.001

12 KARL BERRY 2017-11-02 19:15:42.308637+08 11.001

A Fateful Reflection of a Waitress And a Boat who must Discover a Sumo Wrestler in Ancient China

Kwara & Kogi

{"Deleted Scenes","Behind the Scenes"}

'awe':5 'awe-inspir':4 'barbarella':1 'cat':13 'conquer':16 'dog':18 'feminist':10 'inspir':6 'monasteri':21 'must':15 'stori':7 'streetcar':2

PHP ₱ USD $

\N Some other value

\\."#;

pg_and_generic().one_statement_parses_to(sql, "");

}

#[test]

fn test_copy_from() {

let stmt = pg().verified_stmt("COPY users FROM 'data.csv'");

assert_eq!(

stmt,

Statement::Copy {

source: CopySource::Table {

table_name: ObjectName(vec!["users".into()]),

columns: vec![],

},

to: false,

target: CopyTarget::File {

filename: "data.csv".to_string(),

},

options: vec![],

legacy_options: vec![],

values: vec![],

}

);

let stmt = pg().verified_stmt("COPY users FROM 'data.csv' DELIMITER ','");

assert_eq!(

stmt,

Statement::Copy {

source: CopySource::Table {

table_name: ObjectName(vec!["users".into()]),

columns: vec![],

},

to: false,

target: CopyTarget::File {

filename: "data.csv".to_string(),

},

options: vec![],

legacy_options: vec![CopyLegacyOption::Delimiter(',')],

values: vec![],

}

);

let stmt = pg().verified_stmt("COPY users FROM 'data.csv' DELIMITER ',' CSV HEADER");

assert_eq!(

stmt,

Statement::Copy {

source: CopySource::Table {

table_name: ObjectName(vec!["users".into()]),

columns: vec![],

},

to: false,

target: CopyTarget::File {

filename: "data.csv".to_string(),

},

options: vec![],

legacy_options: vec![

CopyLegacyOption::Delimiter(','),

CopyLegacyOption::Csv(vec![CopyLegacyCsvOption::Header,])

],

values: vec![],

}

);

}

#[test]

fn test_copy_to() {

let stmt = pg().verified_stmt("COPY users TO 'data.csv'");

assert_eq!(

stmt,

Statement::Copy {

source: CopySource::Table {

table_name: ObjectName(vec!["users".into()]),

columns: vec![],

},

to: true,

target: CopyTarget::File {

filename: "data.csv".to_string(),

},

options: vec![],

legacy_options: vec![],

values: vec![],

}

);

let stmt = pg().verified_stmt("COPY users TO 'data.csv' DELIMITER ','");

assert_eq!(

stmt,

Statement::Copy {

source: CopySource::Table {

table_name: ObjectName(vec!["users".into()]),

columns: vec![],

},

to: true,

target: CopyTarget::File {

filename: "data.csv".to_string(),

},

options: vec![],

legacy_options: vec![CopyLegacyOption::Delimiter(',')],

values: vec![],

}

);

let stmt = pg().verified_stmt("COPY users TO 'data.csv' DELIMITER ',' CSV HEADER");

assert_eq!(

stmt,

Statement::Copy {

source: CopySource::Table {

table_name: ObjectName(vec!["users".into()]),

columns: vec![],

},

to: true,

target: CopyTarget::File {

filename: "data.csv".to_string(),

},

options: vec![],

legacy_options: vec![

CopyLegacyOption::Delimiter(','),

CopyLegacyOption::Csv(vec![CopyLegacyCsvOption::Header,])

],

values: vec![],