20240910_在PostgreSQL中创建一个包含常见类型列的表脚本

SET search_path TO pgtarget;

CREATE TABLE tab_type(
   ID bigint NOT NULL primary key,
   name varchar(32) NOT NULL,
   description varchar(128) NOT NULL,
   cdate timestamp NOT NULL,
   type1 text,
   type2 char(10),
   type3 numeric(22,2),
   type4 date,
   type5 time,
   type6 bool
);

show search_path;
--> pgtarget

CREATE SEQUENCE tab_type_id_seq;
ALTER TABLE tab_type ALTER COLUMN id SET DEFAULT nextval('tab_type_id_seq');
ALTER SEQUENCE tab_type_id_seq START WITH 1;

-- drop  FUNCTION random_string_type;
CREATE OR REPLACE FUNCTION random_string_type(
  num INTEGER,
  chars TEXT default '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz~!@#$%^&*()_+=-[]`;.,/\|}{<>↖↑↗→↘↓↙←'
) RETURNS TEXT
LANGUAGE plpgsql
AS $$
DECLARE
  res_str TEXT := '';
BEGIN
  IF num < 1 THEN
      RAISE EXCEPTION 'Invalid length';
  END IF;
  FOR __ IN 1..num LOOP
    res_str := res_str || substr(chars, floor(random() * length(chars))::int + 1, 1);
  END LOOP;
  RETURN res_str;
END $$;

-- drop PROCEDURE all_insert_type;
CREATE PROCEDURE all_insert_type(count integer default 100)
LANGUAGE plpgsql AS
$$
DECLARE
	i integer := 0;
BEGIN
    for i in 1..count loop
	insert into tab_type values(nextval('tab_type_id_seq'),cast(random_string_type(20) as varchar(20)),cast(random_string_type(64) as varchar(64)) ,cast(concat(current_date - floor((random() * 25))::int,' ',make_time(floor((random() * 12))::int, floor((random() * 60))::int, floor((random() * 60))::int)) as timestamp),cast(random_string_type(60) as text),cast(random_string_type(10) as text),round(CAST((10 + random() * 10) as numeric),2),(current_date - floor((random() * 1000))::int),make_time(floor((random() * 12))::int,floor((random() * 60))::int, floor((random() * 60))::int),cast(random() < 0.5 AS bool)) ;
	end loop;
END;
$$;

call all_insert_type(10);

select round(CAST((10 + random() * 10) as numeric),2);
select * from tab_type;