﻿CREATE TABLE T1(c1 INT, c2 DOUBLE NOT NULL, c3 DOUBLE GENERATED ALWAYS AS (c1 + c2),
 c4 SMALLINT GENERATED ALWAYS AS (CASE WHEN c1 > c2 THEN 1 ELSE NULL END)
 ); 
 
/*u1*/INSERT INTO T1(c1,c2) VALUES(5,1);
/*u1*/INSERT INTO T1(c1,c2) VALUES(2,8);

/*r
 5,1,6,1
 2,8,10,NULL
*/SELECT * FROM T1;

CREATE TABLE T1x as (SELECT c1,c2,c3,c4 from T1) WITH DATA;

/*r
 5,1,6,1
 2,8,10,NULL
*/SELECT * FROM T1x;

/*u1*/INSERT INTO T1x(c1,c2) VALUES(20,10);

/*r
 5,1,6,1
 2,8,10,NULL
 20,10,NULL,NULL
*/SELECT * FROM T1x;

/*r
 5,1,6,1
 2,8,10,NULL
*/SELECT * FROM T1;

/*u1*/INSERT INTO T1x(c1,c2) VALUES(20,NULL);
/*u1*/INSERT INTO T1x(c1,c2) VALUES(NULL,10);

CREATE TABLE T1xx as (SELECT c1,c2,c3,c4 from T1) WITH NO DATA;

/*u1*/INSERT INTO T1xx(c1,c2) VALUES(20,10);

/*r
 20,10,NULL,NULL
*/SELECT * FROM T1xx;


CREATE TABLE T1y (id int,LIKE T1 EXCLUDING GENERATED);
/*u1*/INSERT INTO T1y(id,c1,c2) VALUES(100,20,10);

/*r
 100,20,10,NULL,NULL
*/SELECT * FROM T1y;

CREATE TABLE T1yy (id int,LIKE T1 INCLUDING GENERATED);
/*u1*/INSERT INTO T1yy(id,c1,c2) VALUES(100,20,10);

/*r
 100,20,10,30,1
*/SELECT * FROM T1yy;
/*u1*/INSERT INTO T1yy(id,c1,c2) VALUES(101,NULL,15);
/*e*/INSERT INTO T1yy(id,c1,c2) VALUES(50,21,NULL);

CREATE TABLE T2(c1 INT, c2 DOUBLE NOT NULL, c3 DOUBLE GENERATED ALWAYS AS (c1 + c2),
 c4 SMALLINT GENERATED ALWAYS AS (CASE WHEN c1 > c2 THEN 1 ELSE NULL END)
 ); 
 
ALTER TABLE T2 ALTER COLUMN c4 DROP GENERATED;

/*u1*/INSERT INTO T2(c1,c2) VALUES(5,1);
/*u1*/INSERT INTO T2(c1,c2) VALUES(2,8);

/*r
 5,1,6,NULL
 2,8,10,NULL
*/SELECT c1,c2,c3,c4 FROM T2;

create table greetings
 (i int generated always as identity, ch varchar(50) default 'horra');
/*u1*/insert into greetings values (DEFAULT, 'hello');
/*u1*/insert into greetings(ch) values ('bonjour');

/*r
 0,hello
 1,bonjour
*/SELECT * FROM greetings;

CREATE TABLE greetingsY (id int,LIKE greetings EXCLUDING IDENTITY EXCLUDING DEFAULTS);

/*u1*/insert into greetingsY(id) values (10);
/*u1*/insert into greetingsY(id,i,ch) values (11,100,'ayubo');

/*r
 10,NULL,NULL
 11,100,ayubo
*/SELECT * FROM greetingsY;

CREATE TABLE greetingsYY (id int,LIKE greetings INCLUDING IDENTITY INCLUDING DEFAULTS);

/*u1*/insert into greetingsYY(id) values (10);

/*r
 10,2,horra
*/SELECT * FROM greetingsYY;

CREATE TABLE ZZZZ (id int,LIKE T1 INCLUDING GENERATED,LIKE greetings EXCLUDING IDENTITY EXCLUDING DEFAULTS);

drop table greetings;

create table greetings(i int generated by default as identity, ch varchar(50));
-- specify value "1":
/*u1*/insert into greetings values (10, 'hi');
-- use generated default
/*u1*/insert into greetings values (DEFAULT, 'salut');
-- use generated default
/*u1*/insert into greetings(ch) values ('bonjour');

/*r
 10,hi
 11,salut
 12,bonjour
*/SELECT * FROM greetings;

drop table greetings;

create table greetings
 (i int generated by default as identity (START WITH 2 INCREMENT BY 2), ch varchar(50) default 'ayubo');
-- specify value "1":
/*u1*/insert into greetings values (1, 'hi');
-- use generated default
/*u1*/insert into greetings values (DEFAULT, 'salut');
-- use generated default
/*u1*/insert into greetings(ch) values ('bonjour'); 

/*r
 1,hi
 2,salut
 4,bonjour
*/SELECT * FROM greetings;

TRUNCATE TABLE greetings CONTINUE IDENTITY;

/*u1*/insert into greetings values (DEFAULT, 'salut');
/*u1*/insert into greetings values (DEFAULT, 'bonjour');

/*r
 6,salut
 8,bonjour
*/SELECT * FROM greetings;

TRUNCATE TABLE greetings RESTART IDENTITY;

/*u1*/insert into greetings values (DEFAULT, 'salut');
/*u1*/insert into greetings values (DEFAULT, 'bonjour');

/*r
 2,salut
 4,bonjour
*/SELECT * FROM greetings;

CREATE VIEW greetingsV(cv1,cv2) as SELECT * FROM greetings;

/*r
 2,salut
 4,bonjour
*/SELECT * FROM greetingsV;

DELETE FROM greetingsV WHERE cv1=2;

/*c1*/SELECT * FROM greetings;

/*u1*/insert into greetings DEFAULT VALUES;

/*r 
 4,bonjour
 6,ayubo
*/SELECT * FROM greetings;

create table greetings2
 (i int generated by default as identity (START WITH 2 INCREMENT BY 1), ch varchar(50));

/*u2*/INSERT INTO greetings2 (SELECT * FROM greetings);

/*r 
 4,bonjour
 6,ayubo
*/SELECT * FROM greetings2;

delete from greetings2;

create table greetings3
 ( ch varchar(50),i int generated by default as identity (START WITH 2 INCREMENT BY 1));

/*u2*/INSERT INTO greetings3(i,ch) (SELECT * FROM greetings);

/*r 
 4,bonjour
 6,ayubo
*/SELECT i,ch FROM greetings3;


CREATE VIEW greetings2V as SELECT * FROM greetings2;
/*u2*/INSERT INTO greetings2V (SELECT * FROM greetings);

/*r 
 4,bonjour
 6,ayubo
*/SELECT * FROM greetings2;

delete from greetings2;

/*u1*/INSERT INTO greetings2V VALUES(4,'bonjour');

/*r 
 4,bonjour
*/SELECT * FROM greetings2;



CREATE TABLE T91(c1 INT default 7, c2 bigint generated by default as identity (START WITH 2 INCREMENT BY 1));
 
/*u1*/INSERT INTO T91(c1,c2) VALUES(5,1);
/*u1*/INSERT INTO T91(c1,c2) VALUES(2,8);

/*r
 5,1
 2,8
*/SELECT * FROM T91;

/*u1*/update T91 set c1=default,c2=default where c1=5;

/*r
 2,8
 7,1
*/SELECT * FROM T91;


 