drop schema if exists test_ddl_and_dml cascade;
create schema test_ddl_and_dml;
set current_schema = test_ddl_and_dml;
DROP TABLE IF EXISTS Products;
CREATE TABLE Products(
QtyAvailable smallint,
UnitPrice money,
InventoryValue AS (QtyAvailable * UnitPrice)
);
ALTER TABLE Products ADD RetailValue AS (QtyAvailable * UnitPrice * 1.5) PERSISTED;
INSERT INTO Products (QtyAvailable, UnitPrice) VALUES (25, 2.00), (10, 1.5);
select * from Products;
\d+ Products
ALTER TABLE Products DROP COLUMN RetailValue;
select * from Products;
ALTER TABLE Products ADD RetailValue2 AS (QtyAvailable * RetailValue * 1.5) PERSISTED;
ALTER TABLE Products DROP COLUMN UnitPrice;
DROP TABLE IF EXISTS Products;
drop table if exists ttb16;
create table ttb16(str1 varchar(20),str2 varchar(20),str3 as (str1 || str2));
insert into ttb16 values('hello ','world');
insert into ttb16 values('runoob');
select * from ttb16;
alter table ttb16 add column str4 as (str1 || str2);
select * from ttb16;
drop table ttb16;
DROP TABLE IF EXISTS Products;
CREATE TABLE Products(QtyAvailable smallint, UnitPrice money, InventoryValue AS (QtyAvailable * UnitPrice));
INSERT INTO Products(QtyAvailable, UnitPrice) VALUES (25, 2.00), (10, 1.5), (25, 2.00), (10, 1.5), (10, 1.5);
select DISTINCT TOP 1 * from Products;
select TOP 2 PERCENT WITH TIES * from Products ORDER BY InventoryValue;
select TOP 2 PERCENT * from Products;
select TOP 1 * from Products limit 10;
select TOP 200 PERCENT * from Products;
select TOP (select 10) WITH TIES * from Products;
select * from Products ORDER BY qtyavailable;
select TOP 1 * from Products ORDER BY qtyavailable;
select * from Products ORDER BY qtyavailable offset 2 rows;
select * from Products ORDER BY qtyavailable fetch first 1 rows only;
select * from Products ORDER BY qtyavailable offset 3 rows fetch first 1 rows only;
select * from Products ORDER BY qtyavailable fetch first 1 rows only offset 3 rows;
select TOP 1 * from Products ORDER BY qtyavailable offset 2 rows;
select TOP 1 * from Products ORDER BY qtyavailable fetch first 1 rows only;
select TOP 1 * from Products ORDER BY qtyavailable offset 3 rows fetch first 1 rows only;
select TOP 1 * from Products ORDER BY qtyavailable fetch first 1 rows only offset 3 rows;
DROP TABLE IF EXISTS Products;
select 5!;
SELECT 1 ABORT;
SELECT 1 ABSOLUTE;
SELECT 1 ACCESS;
SELECT 1 ACCOUNT;
SELECT 1 ACTION;
SELECT 1 ADD;
SELECT 1 ADMIN;
SELECT 1 AFTER;
SELECT 1 AGGREGATE;
SELECT 1 ALGORITHM;
SELECT 1 ALL;
SELECT 1 ALSO;
SELECT 1 ALTER;
SELECT 1 ALWAYS;
SELECT 1 ANALYSE;
SELECT 1 ANALYZE;
SELECT 1 AND;
SELECT 1 ANY;
SELECT 1 APP;
SELECT 1 APPEND;
SELECT 1 APPLY;
SELECT 1 ARCHIVE;
SELECT 1 ASC;
SELECT 1 ASOF;
SELECT 1 ASSERTION;
SELECT 1 ASSIGNMENT;
SELECT 1 ASYMMETRIC;
SELECT 1 AT;
SELECT 1 ATTRIBUTE;
SELECT 1 AUDIT;
SELECT 1 AUTHID;
SELECT 1 AUTHORIZATION;
SELECT 1 AUTO_INCREMENT;
SELECT 1 AUTOEXTEND;
SELECT 1 AUTOMAPPED;
SELECT 1 BACKWARD;
SELECT 1 BARRIER;
SELECT 1 BEFORE;
SELECT 1 BEGIN;
SELECT 1 BEGIN_NON_ANOYBLOCK;
SELECT 1 BIGINT;
SELECT 1 BINARY;
SELECT 1 BINARY_DOUBLE;
SELECT 1 BINARY_DOUBLE_INF;
SELECT 1 BINARY_DOUBLE_NAN;
SELECT 1 BINARY_INTEGER;
SELECT 1 BIT;
SELECT 1 BLANKS;
SELECT 1 BLOB;
SELECT 1 BLOCKCHAIN;
SELECT 1 BODY;
SELECT 1 BOOLEAN;
SELECT 1 BOTH;
SELECT 1 BUCKETCNT;
SELECT 1 BUCKETS;
SELECT 1 BUILD;
SELECT 1 BYTE;
SELECT 1 BYTEAWITHOUTORDER;
SELECT 1 BYTEAWITHOUTORDERWITHEQUAL;
SELECT 1 CACHE;
SELECT 1 CALL;
SELECT 1 CALLED;
SELECT 1 CANCELABLE;
SELECT 1 CASCADE;
SELECT 1 CASCADED;
SELECT 1 CASE;
SELECT 1 CAST;
SELECT 1 CATALOG;
SELECT 1 CATALOG_NAME;
SELECT 1 CHAIN;
SELECT 1 CHANGE;
SELECT 1 CHARACTERISTICS;
SELECT 1 CHARACTERSET;
SELECT 1 CHARSET;
SELECT 1 CHECK;
SELECT 1 CHECKPOINT;
SELECT 1 CLASS;
SELECT 1 CLASS_ORIGIN;
SELECT 1 CLEAN;
SELECT 1 CLIENT;
SELECT 1 CLIENT_MASTER_KEY;
SELECT 1 CLIENT_MASTER_KEYS;
SELECT 1 CLOB;
SELECT 1 CLOSE;
SELECT 1 CLUSTER;
SELECT 1 TSQL_CLUSTERED;
SELECT 1 COALESCE;
SELECT 1 COLLATE;
SELECT 1 COLLATION;
SELECT 1 COLUMN;
SELECT 1 COLUMN_ENCRYPTION_KEY;
SELECT 1 COLUMN_ENCRYPTION_KEYS;
SELECT 1 COLUMN_NAME;
SELECT 1 COLUMNS;
SELECT 1 TSQL_COLUMNSTORE;
SELECT 1 COMMENT;
SELECT 1 COMMENTS;
SELECT 1 COMMIT;
SELECT 1 COMMITTED;
SELECT 1 COMPACT;
SELECT 1 COMPATIBLE_ILLEGAL_CHARS;
SELECT 1 COMPILE;
SELECT 1 COMPLETE;
SELECT 1 COMPLETION;
SELECT 1 COMPRESS;
SELECT 1 CONCURRENTLY;
SELECT 1 CONDITION;
SELECT 1 CONFIGURATION;
SELECT 1 CONNECT;
SELECT 1 CONNECTION;
SELECT 1 CONSISTENT;
SELECT 1 CONSTANT;
SELECT 1 CONSTRAINT;
SELECT 1 CONSTRAINT_CATALOG;
SELECT 1 CONSTRAINT_NAME;
SELECT 1 CONSTRAINT_SCHEMA;
SELECT 1 CONSTRAINTS;
SELECT 1 CONSTRUCTOR;
SELECT 1 CONTENT;
SELECT 1 CONTINUE;
SELECT 1 CONTVIEW;
SELECT 1 CONVERSION;
SELECT 1 CONVERT;
SELECT 1 COORDINATOR;
SELECT 1 COORDINATORS;
SELECT 1 COPY;
SELECT 1 COST;
SELECT 1 CROSS;
SELECT 1 CSN;
SELECT 1 CSV;
SELECT 1 CUBE;
SELECT 1 CURRENT;
SELECT 1 CURRENT_CATALOG;
SELECT 1 CURRENT_DATE;
SELECT 1 CURRENT_ROLE;
SELECT 1 CURRENT_SCHEMA;
SELECT 1 CURRENT_TIME;
SELECT 1 CURRENT_TIMESTAMP;
SELECT 1 CURRENT_USER;
SELECT 1 CURSOR;
SELECT 1 CURSOR_NAME;
SELECT 1 CYCLE;
SELECT 1 DATA;
SELECT 1 DATABASE;
SELECT 1 DATAFILE;
SELECT 1 DATANODE;
SELECT 1 DATANODES;
SELECT 1 DATATYPE_CL;
SELECT 1 DATE;
SELECT 1 DATE_FORMAT;
SELECT 1 DAY_HOUR;
SELECT 1 DAY_MINUTE;
SELECT 1 DAY_SECOND;
SELECT 1 DBCOMPATIBILITY;
SELECT 1 DEALLOCATE;
SELECT 1 DEC;
SELECT 1 DECIMAL;
SELECT 1 DECLARE;
SELECT 1 DECODE;
SELECT 1 DEFAULT;
SELECT 1 DEFAULTS;
SELECT 1 DEFERRABLE;
SELECT 1 DEFERRED;
SELECT 1 DEFINER;
SELECT 1 DELETE;
SELECT 1 DELIMITER;
SELECT 1 DELIMITERS;
SELECT 1 DELTA;
SELECT 1 DELTAMERGE;
SELECT 1 DENSE_RANK;
SELECT 1 DESC;
SELECT 1 DETERMINISTIC;
SELECT 1 DIAGNOSTICS;
SELECT 1 DICTIONARY;
SELECT 1 DIRECT;
SELECT 1 DIRECTORY;
SELECT 1 DISABLE;
SELECT 1 DISCARD;
SELECT 1 DISCONNECT;
SELECT 1 DISTINCT;
SELECT 1 DISTRIBUTE;
SELECT 1 DISTRIBUTION;
SELECT 1 DO;
SELECT 1 DOCUMENT;
SELECT 1 DOMAIN;
SELECT 1 DOUBLE;
SELECT 1 DROP;
SELECT 1 DUMPFILE;
SELECT 1 DUPLICATE;
SELECT 1 EACH;
SELECT 1 ELASTIC;
SELECT 1 ELSE;
SELECT 1 ENABLE;
SELECT 1 ENCLOSED;
SELECT 1 ENCODING;
SELECT 1 ENCRYPTED;
SELECT 1 ENCRYPTED_VALUE;
SELECT 1 ENCRYPTION;
SELECT 1 ENCRYPTION_TYPE;
SELECT 1 END;
SELECT 1 ENDS;
SELECT 1 ENFORCED;
SELECT 1 ENUM;
SELECT 1 EOL;
SELECT 1 ERROR;
SELECT 1 ERRORS;
SELECT 1 ESCAPE;
SELECT 1 ESCAPED;
SELECT 1 ESCAPING;
SELECT 1 EVENT;
SELECT 1 EVENTS;
SELECT 1 EVERY;
SELECT 1 EXCHANGE;
SELECT 1 EXCLUDE;
SELECT 1 EXCLUDED;
SELECT 1 EXCLUDING;
SELECT 1 EXCLUSIVE;
SELECT 1 EXECUTE;
SELECT 1 EXISTS;
SELECT 1 EXPIRED;
SELECT 1 EXPLAIN;
SELECT 1 EXTENSION;
SELECT 1 EXTERNAL;
SELECT 1 EXTRACT;
SELECT 1 FALSE;
SELECT 1 FAMILY;
SELECT 1 FAST;
SELECT 1 FEATURES;
SELECT 1 FENCED;
SELECT 1 FIELDS;
SELECT 1 FILEHEADER;
SELECT 1 FILL_MISSING_FIELDS;
SELECT 1 FILLER;
SELECT 1 FINAL;
SELECT 1 FIRST;
SELECT 1 FIXED;
SELECT 1 FLOAT;
SELECT 1 FOLLOWING;
SELECT 1 FOLLOWS;
SELECT 1 FORCE;
SELECT 1 FOREIGN;
SELECT 1 FORMATTER;
SELECT 1 FORWARD;
SELECT 1 FREEZE;
SELECT 1 FULL;
SELECT 1 FUNCTION;
SELECT 1 FUNCTIONS;
SELECT 1 GENERATED;
SELECT 1 GET;
SELECT 1 GLOBAL;
SELECT 1 GRANTED;
SELECT 1 GREATEST;
SELECT 1 GROUPING;
SELECT 1 GROUPPARENT;
SELECT 1 HANDLER;
SELECT 1 HDFSDIRECTORY;
SELECT 1 HEADER;
SELECT 1 HOLD;
SELECT 1 HOUR_MINUTE;
SELECT 1 HOUR_SECOND;
SELECT 1 IDENTIFIED;
SELECT 1 IDENTITY;
SELECT 1 IF;
SELECT 1 IGNORE;
SELECT 1 IGNORE_EXTRA_DATA;
SELECT 1 ILIKE;
SELECT 1 IMCSTORED;
SELECT 1 IMMEDIATE;
SELECT 1 IMMUTABLE;
SELECT 1 IMPLICIT;
SELECT 1 IN;
SELECT 1 INCLUDE;
SELECT 1 INCLUDING;
SELECT 1 INCREMENT;
SELECT 1 INCREMENTAL;
SELECT 1 INDEX;
SELECT 1 INDEXES;
SELECT 1 INFILE;
SELECT 1 INFINITE;
SELECT 1 INHERIT;
SELECT 1 INHERITS;
SELECT 1 INITIAL;
SELECT 1 INITIALLY;
SELECT 1 INITRANS;
SELECT 1 INLINE;
SELECT 1 INNER;
SELECT 1 INOUT;
SELECT 1 INPUT;
SELECT 1 INSENSITIVE;
SELECT 1 INSERT;
SELECT 1 INSTEAD;
SELECT 1 INT;
SELECT 1 INTEGER;
SELECT 1 INTERNAL;
SELECT 1 INTERVAL;
SELECT 1 INVISIBLE;
SELECT 1 INVOKER;
SELECT 1 IP;
SELECT 1 CHECKIDENT;
SELECT 1 ISOLATION;
SELECT 1 JOIN;
SELECT 1 JSON_EXISTS;
SELECT 1 KEY;
SELECT 1 KEYATH;
SELECT 1 KEY_STORE;
SELECT 1 KILL;
SELECT 1 LABEL;
SELECT 1 LANGUAGE;
SELECT 1 LARGE;
SELECT 1 LAST;
SELECT 1 LATERAL;
SELECT 1 LC_COLLATE;
SELECT 1 LC_CTYPE;
SELECT 1 LEADING;
SELECT 1 LEAKPROOF;
SELECT 1 LEAST;
SELECT 1 LEFT;
SELECT 1 LESS;
SELECT 1 LEVEL;
SELECT 1 LIKE;
SELECT 1 LINES;
SELECT 1 LIST;
SELECT 1 LISTEN;
SELECT 1 LOAD;
SELECT 1 LOCAL;
SELECT 1 LOCALTIME;
SELECT 1 LOCALTIMESTAMP;
SELECT 1 LOCATION;
SELECT 1 LOCK;
SELECT 1 LOCKED;
SELECT 1 LOG;
SELECT 1 LOGGING;
SELECT 1 LOGIN_ANY;
SELECT 1 LOGIN_FAILURE;
SELECT 1 LOGIN_SUCCESS;
SELECT 1 LOGOUT;
SELECT 1 LOOP;
SELECT 1 MAP;
SELECT 1 MAPPING;
SELECT 1 MASKING;
SELECT 1 MASTER;
SELECT 1 MATCH;
SELECT 1 MATCHED;
SELECT 1 MATERIALIZED;
SELECT 1 MAXEXTENTS;
SELECT 1 MAXSIZE;
SELECT 1 MAXTRANS;
SELECT 1 MAXVALUE;
SELECT 1 MEMBER;
SELECT 1 MERGE;
SELECT 1 MESSAGE_TEXT;
SELECT 1 METHOD;
SELECT 1 MINEXTENTS;
SELECT 1 MINUTE_SECOND;
SELECT 1 MINVALUE;
SELECT 1 MODE;
SELECT 1 MODEL;
SELECT 1 MODIFY;
SELECT 1 MOVE;
SELECT 1 MOVEMENT;
SELECT 1 MYSQL_ERRNO;
SELECT 1 NAMES;
SELECT 1 NAN;
SELECT 1 NATIONAL;
SELECT 1 NATURAL;
SELECT 1 NCHAR;
SELECT 1 NEXT;
SELECT 1 NO;
SELECT 1 NOCOMPRESS;
SELECT 1 NOCYCLE;
SELECT 1 NODE;
SELECT 1 NOLOGGING;
SELECT 1 NOMAXVALUE;
SELECT 1 NOMINVALUE;
SELECT 1 TSQL_NONCLUSTERED;
SELECT 1 NONE;
SELECT 1 NOTHING;
SELECT 1 NOTIFY;
SELECT 1 NOVALIDATE;
SELECT 1 NOWAIT;
SELECT 1 NTH_VALUE;
SELECT 1 NULL;
SELECT 1 NULLCOLS;
SELECT 1 NULLIF;
SELECT 1 NULLS;
SELECT 1 NUMBER;
SELECT 1 NUMERIC;
SELECT 1 NUMSTR;
SELECT 1 NVARCHAR;
SELECT 1 NVARCHAR2;
SELECT 1 NVL;
SELECT 1 OBJECT;
SELECT 1 OF;
SELECT 1 OFF;
SELECT 1 OIDS;
SELECT 1 ONLY;
SELECT 1 OPERATOR;
SELECT 1 OPTIMIZATION;
SELECT 1 OPTION;
SELECT 1 OPTIONALLY;
SELECT 1 OPTIONS;
SELECT 1 OR;
SELECT 1 OUT;
SELECT 1 OUTER;
SELECT 1 OUTFILE;
SELECT 1 OVERLAY;
SELECT 1 OWNED;
SELECT 1 OWNER;
SELECT 1 PACKAGE;
SELECT 1 PACKAGES;
SELECT 1 PARALLEL_ENABLE;
SELECT 1 PARSER;
SELECT 1 PARTIAL;
SELECT 1 PARTITION;
SELECT 1 PARTITIONS;
SELECT 1 PASSING;
SELECT 1 PASSWORD;
SELECT 1 PCTFREE;
SELECT 1 PER;
SELECT 1 TSQLERCENT;
SELECT 1 PERFORMANCE;
SELECT 1 PERM;
SELECT 1 TSQLERSISTED;
SELECT 1 PIPELINED;
SELECT 1 PLACING;
SELECT 1 PLAN;
SELECT 1 PLANS;
SELECT 1 POLICY;
SELECT 1 POOL;
SELECT 1 POSITION;
SELECT 1 PRECEDES;
SELECT 1 PRECEDING;
SELECT 1 PREDICT;
SELECT 1 PREFERRED;
SELECT 1 PREFIX;
SELECT 1 PREPARE;
SELECT 1 PREPARED;
SELECT 1 PRESERVE;
SELECT 1 PRIMARY;
SELECT 1 PRIOR;
SELECT 1 PRIORER;
SELECT 1 PRIVATE;
SELECT 1 PRIVILEGE;
SELECT 1 PRIVILEGES;
SELECT 1 PROCEDURAL;
SELECT 1 PROCEDURE;
SELECT 1 PROFILE;
SELECT 1 PUBLICATION;
SELECT 1 PUBLISH;
SELECT 1 PURGE;
SELECT 1 QUERY;
SELECT 1 QUOTE;
SELECT 1 RANDOMIZED;
SELECT 1 RANGE;
SELECT 1 RATIO;
SELECT 1 RAW;
SELECT 1 READ;
SELECT 1 REAL;
SELECT 1 REASSIGN;
SELECT 1 REBUILD;
SELECT 1 RECHECK;
SELECT 1 RECURSIVE;
SELECT 1 RECYCLEBIN;
SELECT 1 REDISANYVALUE;
SELECT 1 REF;
SELECT 1 REFERENCES;
SELECT 1 REFRESH;
SELECT 1 REINDEX;
SELECT 1 REJECT;
SELECT 1 RELATIVE;
SELECT 1 RELEASE;
SELECT 1 RELOPTIONS;
SELECT 1 REMOTE;
SELECT 1 REMOVE;
SELECT 1 RENAME;
SELECT 1 REPEAT;
SELECT 1 REPEATABLE;
SELECT 1 REPLACE;
SELECT 1 REPLICA;
SELECT 1 RESET;
SELECT 1 RESIZE;
SELECT 1 RESOURCE;
SELECT 1 RESPECT;
SELECT 1 RESTART;
SELECT 1 RESTRICT;
SELECT 1 RESULT;
SELECT 1 RETURN;
SELECT 1 RETURNED_SQLSTATE;
SELECT 1 RETURNS;
SELECT 1 REUSE;
SELECT 1 REVOKE;
SELECT 1 RIGHT;
SELECT 1 ROLE;
SELECT 1 ROLES;
SELECT 1 ROLLBACK;
SELECT 1 ROLLUP;
SELECT 1 ROTATE;
SELECT 1 ROTATION;
SELECT 1 ROW;
SELECT 1 ROW_COUNT;
SELECT 1 ROWNUM;
SELECT 1 ROWS;
SELECT 1 ROWTYPE;
SELECT 1 RULE;
SELECT 1 SAMPLE;
SELECT 1 SAVEPOINT;
SELECT 1 SCHEDULE;
SELECT 1 SCHEMA;
SELECT 1 SCHEMA_NAME;
SELECT 1 SCROLL;
SELECT 1 SEARCH;
SELECT 1 SECURITY;
SELECT 1 SELF;
SELECT 1 SEPARATOR;
SELECT 1 SEQUENCE;
SELECT 1 SEQUENCES;
SELECT 1 SERIALIZABLE;
SELECT 1 SERVER;
SELECT 1 SESSION;
SELECT 1 SESSION_USER;
SELECT 1 SET;
SELECT 1 SETOF;
SELECT 1 SETS;
SELECT 1 SHARE;
SELECT 1 SHIPPABLE;
SELECT 1 SHOW;
SELECT 1 SHRINK;
SELECT 1 SHUTDOWN;
SELECT 1 SIBLINGS;
SELECT 1 SIMILAR;
SELECT 1 SIMPLE;
SELECT 1 SIZE;
SELECT 1 SKIP;
SELECT 1 SLAVE;
SELECT 1 SLICE;
SELECT 1 SMALLDATETIME;
SELECT 1 SMALLDATETIME_FORMAT;
SELECT 1 SMALLINT;
SELECT 1 SNAPSHOT;
SELECT 1 SOME;
SELECT 1 SOURCE;
SELECT 1 SPACE;
SELECT 1 SPECIFICATION;
SELECT 1 SPILL;
SELECT 1 SPLIT;
SELECT 1 SQL;
SELECT 1 STABLE;
SELECT 1 STACKED;
SELECT 1 STANDALONE;
SELECT 1 START;
SELECT 1 STARTING;
SELECT 1 STARTS;
SELECT 1 STATEMENT;
SELECT 1 STATEMENT_ID;
SELECT 1 STATIC;
SELECT 1 STATISTICS;
SELECT 1 STDIN;
SELECT 1 STDOUT;
SELECT 1 STORAGE;
SELECT 1 STORE;
SELECT 1 STORED;
SELECT 1 STRATIFY;
SELECT 1 STREAM;
SELECT 1 STRICT;
SELECT 1 STRIP;
SELECT 1 SUBCLASS_ORIGIN;
SELECT 1 SUBPARTITION;
SELECT 1 SUBPARTITIONS;
SELECT 1 SUBSCRIPTION;
SELECT 1 SUBSTRING;
SELECT 1 SYMMETRIC;
SELECT 1 SYNONYM;
SELECT 1 SYS_REFCURSOR;
SELECT 1 SYSDATE;
SELECT 1 SYSID;
SELECT 1 SYSTEM;
SELECT 1 TABLE;
SELECT 1 TABLE_NAME;
SELECT 1 TABLES;
SELECT 1 TABLESAMPLE;
SELECT 1 TABLESPACE;
SELECT 1 TEMP;
SELECT 1 TEMPLATE;
SELECT 1 TEMPORARY;
SELECT 1 TERMINATED;
SELECT 1 TEXT;
SELECT 1 THAN;
SELECT 1 THEN;
SELECT 1 TIES;
SELECT 1 TIME;
SELECT 1 TIME_FORMAT;
SELECT 1 TIMECAPSULE;
SELECT 1 TIMESTAMP;
SELECT 1 TIMESTAMP_FORMAT;
SELECT 1 TIMESTAMPDIFF;
SELECT 1 TIMEZONE_HOUR;
SELECT 1 TIMEZONE_MINUTE;
SELECT 1 TINYINT;
SELECT 1 TSQL_TOP;
SELECT 1 TRAILING;
SELECT 1 TRANSACTION;
SELECT 1 TRANSFORM;
SELECT 1 TREAT;
SELECT 1 TRIGGER;
SELECT 1 TRIM;
SELECT 1 TRUE;
SELECT 1 TRUNCATE;
SELECT 1 TRUSTED;
SELECT 1 TSFIELD;
SELECT 1 TSTAG;
SELECT 1 TSTIME;
SELECT 1 TYPES;
SELECT 1 UNBOUNDED;
SELECT 1 UNCOMMITTED;
SELECT 1 UNDER;
SELECT 1 UNENCRYPTED;
SELECT 1 UNIMCSTORED;
SELECT 1 UNIQUE;
SELECT 1 UNKNOWN;
SELECT 1 UNLIMITED;
SELECT 1 UNLISTEN;
SELECT 1 UNLOCK;
SELECT 1 UNLOGGED;
SELECT 1 UNTIL;
SELECT 1 UNUSABLE;
SELECT 1 UPDATE;
SELECT 1 USE;
SELECT 1 USEEOF;
SELECT 1 USER;
SELECT 1 USING;
SELECT 1 VACUUM;
SELECT 1 VALID;
SELECT 1 VALIDATE;
SELECT 1 VALIDATION;
SELECT 1 VALIDATOR;
SELECT 1 VALUES;
SELECT 1 VARCHAR;
SELECT 1 VARCHAR2;
SELECT 1 VARIABLES;
SELECT 1 VARIADIC;
SELECT 1 VARRAY;
SELECT 1 VCGROUP;
SELECT 1 VERBOSE;
SELECT 1 VERIFY;
SELECT 1 VERSION;
SELECT 1 VIEW;
SELECT 1 VISIBLE;
SELECT 1 VOLATILE;
SELECT 1 WAIT;
SELECT 1 WARNINGS;
SELECT 1 WEAK;
SELECT 1 WHEN;
SELECT 1 WHILE;
SELECT 1 WHITESPACE;
SELECT 1 WORK;
SELECT 1 WORKLOAD;
SELECT 1 WRAPPER;
SELECT 1 WRITE;
SELECT 1 XMLATTRIBUTES;
SELECT 1 XMLCONCAT;
SELECT 1 XMLELEMENT;
SELECT 1 XMLEXISTS;
SELECT 1 XMLFOREST;
SELECT 1 XMLPARSE;
SELECT 1 XMLPI;
SELECT 1 XMLROOT;
SELECT 1 XMLSERIALIZE;
SELECT 1 YEAR_MONTH;
SELECT 1 YES;
SELECT 1 ZONE;
drop table if exists CONSTRAINT_ASC;
create table CONSTRAINT_ASC(id int not null, v1 varchar(30), constraint PK_CONSTRAINT_ASC primary key(id ASC));
\d+ CONSTRAINT_ASC
drop table CONSTRAINT_ASC;
drop table if exists CONSTRAINT_DESC;
create table CONSTRAINT_DESC(id int not null, v1 varchar(30), constraint PK_CONSTRAINT_DESC primary key(id DESC));
\d+ CONSTRAINT_DESC
drop table CONSTRAINT_DESC;
CREATE TABLE UNIQUE_CONSTRAINT_TEST(v1 int, v2 int, v3 varchar(255), constraint UNIQ_V2 UNIQUE(v2 desc));
\d+ UNIQUE_CONSTRAINT_TEST
drop table UNIQUE_CONSTRAINT_TEST;
drop table if exists ADD_DEFAULT;
create table ADD_DEFAULT(id int, v1 varchar(20), v2 float);
\d+ ADD_DEFAULT
alter table ADD_DEFAULT add default (mod(4, 3)) for id;
\d+ ADD_DEFAULT
insert into ADD_DEFAULT(v1, v2) values('bac', 3.1);
select * from ADD_DEFAULT;
alter table ADD_DEFAULT add default (2.45) for v2;
\d+ ADD_DEFAULT
insert into ADD_DEFAULT(v1) VALUES('def');
select * from ADD_DEFAULT;
ALTER TABLE ADD_DEFAULT ALTER COLUMN id DROP DEFAULT;
\d+ ADD_DEFAULT
ALTER TABLE ADD_DEFAULT ALTER COLUMN v2 DROP DEFAULT;
\d+ ADD_DEFAULT
drop table if exists ADD_DEFAULT;
drop table if exists ADD_CONSTRAINT_DEFAULT;
create table ADD_CONSTRAINT_DEFAULT(id int, v1 varchar(20), v2 timestamptz);
\d+ ADD_CONSTRAINT_DEFAULT
alter table ADD_CONSTRAINT_DEFAULT add constraint ADD_SYSTEIME_DEFAULT default (pg_systimestamp()) for v2;
\d+ ADD_CONSTRAINT_DEFAULT
insert into ADD_CONSTRAINT_DEFAULT(id, v1) values(1, 'abc');
alter table ADD_CONSTRAINT_DEFAULT add constraint ADD_INT_DEFAULT default ((9)) for id;
\d+ ADD_CONSTRAINT_DEFAULT
insert into ADD_CONSTRAINT_DEFAULT(v1) values('abc');
select * from ADD_CONSTRAINT_DEFAULT;
drop table if exists ADD_CONSTRAINT_DEFAULT;
set d_format_behavior_compat_options = 'enable_sbr_identifier';
CREATE TABLE t1(v0 [int], v1 [int2], v2 [int4], v3 [int8], v4 [tinyint], v5 [smallint], v6 [bigint], v7 [float],
v8 [float4], v9 [real], v10 [float8], v11 [double precision], v12 [binary_integer], v13 [binary_double], v14 [numeric], v15 [decimal],
v16 [dec], v17 [bpchar], v18 [char], v19 [char](30), v20 [varchar], v21 [varchar](30), v22 [nvarchar2], v23 [nvarchar2](30), v24 [nvarchar],
v25 [nvarchar](30), v26 [abstime], v27 [reltime], v28 [date], v29 [time], v30 [timestamptz], v31 [timestamp], v32 [timetz], v33 [bit], v34 [interval],
v35 [uuid], v36 [bool], v37 [json], v38 [unknown], v39 [jsonb], v40 [clob], v41 [blob], v42 [money], v43 [name], v44 [oid], v45 [bytea],
v46 [varbinary], v47 [sql_variant]);
\d+ t1
drop table t1;
CREATE OR REPLACE FUNCTION show_value(input [double precision])
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
RAISE NOTICE '%', input;
END;
$$;
select show_value(1.2);
drop function if exists show_value([double precision]);
CREATE OR REPLACE FUNCTION show_value(input [smallint])
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
RAISE NOTICE '%', input;
END;
$$;
select show_value(25);
drop function if exists show_value([smallint]);
create or replace function show_value(input [int4])
returns void
language plpgsql
as $$
begin
RAISE NOTICE '%', input;
end;
$$;
select show_value(10);
drop function if exists show_value([int4]);
create or replace function show_value(input [char])
returns void
language plpgsql
as $$
begin
RAISE NOTICE '%', input;
end;
$$;
select show_value('o');
drop function if exists show_value([char]);
create or replace function show_value(input [char](30))
returns void
language plpgsql
as $$
begin
RAISE NOTICE '%', input;
end;
$$;
select show_value('oiiaeooiiai');
drop function if exists show_value([char](30));
declare
v1 [bigint];
begin
raise info 'INFO:执行中';
end;
/
declare
v1 [double precision];
begin
raise info 'INFO:执行中';
end;
/
declare
v1 [binary_double];
begin
raise info 'INFO:执行中';
end;
/
select '1'::[int] AS result;
select '1'::[smallint] AS result;
select '1'::[binary_integer] AS result;
select '1'::[float] AS result;
select '1'::[double precision] AS result;
select '1'::[binary_double] AS result;
select 1::[char] AS result;
select 1::[char](30) AS result;
select 1::[nvarchar] AS result;
select 1::[nvarchar](30) AS result;
select CAST('1' AS [int]) as result;
select CAST('1' AS [smallint]) as result;
select CAST('1' AS [binary_integer]) as result;
select CAST('1' AS [float]) as result;
select CAST('1' AS [double precision]) as result;
select CAST('1' AS [binary_double]) as result;
select CAST(1 AS [char]) as result;
select CAST(1 AS [char](30)) as result;
select CAST(1 AS [nvarchar]) as result;
select CAST(1 AS [nvarchar](30)) as result;
create type person AS (name [text], age [int]);
drop type person;
create type [person] AS (name [text], age [int]);
drop type person;
create table t1(id [int], v1 [varchar]);
alter table t1 modify v1 [char];
\d+ t1
drop table t1;
set d_format_behavior_compat_options = '';
reset current_schema;
drop schema if exists test_ddl_and_dml cascade;
create schema if not exists abc;
CREATE TABLE if not exists abc.Students ( StudentID char(10) NOT NULL , StudentName nvarchar(50) NOT NULL , Gender char(1) , IDCardNumber char(18) NOT NULL , Email nvarchar(100) , PhoneNumber varchar(20) , Address nvarchar(200) , AdmissionDate date NOT NULL , ClassID integer NOT NULL , MajorID integer NOT NULL ) ;
select nspcollation from pg_namespace where nspname='abc';
drop schema abc cascade;
create schema if not exists sch_ddl_001 character set = utf8 collate utf8_unicode_ci;
select nspcollation from pg_namespace where nspname='sch_ddl_001';
drop schema sch_ddl_001;
