PostgreSQL建表flink支持的字段集，insert插入数据

建表sql

CREATE TABLE all_flink_types (
    test_INT2	INT2
    ,test_SMALLSERIAL	SMALLSERIAL
    ,test_SERIAL2	SERIAL2
    ,test_INTEGER	INTEGER
    ,test_SERIAL	SERIAL
    ,test_BIGINT	BIGINT
    ,test_BIGSERIAL	BIGSERIAL
    ,test_REAL	REAL
    ,test_FLOAT4	FLOAT4
    ,test_FLOAT8	FLOAT8
    ,test_DOUBLE_PRECISION	DOUBLE PRECISION
    ,test_NUMERIC	NUMERIC(15,3)
    ,test_DECIMAL	DECIMAL(15,3)
    ,test_BOOLEAN	BOOLEAN
    ,test_DATE	DATE
    ,test_TIME	TIME(3)
    ,test_TIMESTAMP	TIMESTAMP(3)
    ,test_CHAR	CHAR(250)
    ,test_CHARACTER	CHARACTER(250)
    ,test_VARCHAR	VARCHAR(250)
    ,test_CHARACTER_VARYING	CHARACTER VARYING(250)
    ,test_TEXT	TEXT
    ,test_BYTEA	BYTEA
    ,test_ARRAY4	 integer ARRAY[4]
    ,test_ARRAY4_4	 text[4][4]
    ,primary key (test_INTEGER)
);

insert语句

INSERT INTO all_flink_types (
    test_INT2,
    test_SMALLSERIAL,
    test_SERIAL2,
    test_INTEGER,
    test_SERIAL,
    test_BIGINT,
    test_BIGSERIAL,
    test_REAL,
    test_FLOAT4,
    test_FLOAT8,
    test_DOUBLE_PRECISION,
    test_NUMERIC,
    test_DECIMAL,
    test_BOOLEAN,
    test_DATE,
    test_TIME,
    test_TIMESTAMP,
    test_CHAR,
    test_CHARACTER,
    test_VARCHAR,
    test_CHARACTER_VARYING,
    test_TEXT,
    test_BYTEA,
    test_ARRAY4,
    test_ARRAY4_4
) VALUES (
             123,  -- test_INT2
             1001, -- test_SMALLSERIAL (手动插入值，但通常不需要)
             1002, -- test_SERIAL2 (手动插入值，但通常不需要)
             1, -- test_INTEGER (这里设置为主键)
             1003, -- test_SERIAL (手动插入值，但通常不需要)
             9223372036854775807, -- test_BIGINT
             1004, -- test_BIGSERIAL (手动插入值，但通常不需要)
             3.14, -- test_REAL
             3.14, -- test_FLOAT4
             3.141592653589793, -- test_FLOAT8
             3.141592653589793, -- test_DOUBLE_PRECISION
             123.456, -- test_NUMERIC
             123.456, -- test_DECIMAL
             true, -- test_BOOLEAN
             '2023-10-23', -- test_DATE
             '12:34:56.789', -- test_TIME
             '2023-10-23 12:34:56.789', -- test_TIMESTAMP
             'Some Text', -- test_CHAR (会被截断到250字符)
             'Some Text', -- test_CHARACTER (会被截断到250字符)
             'Some Text', -- test_VARCHAR (最大250字符)
             'Some Text', -- test_CHARACTER_VARYING (最大250字符)
             'Some longer text that exceeds 250 characters but will be stored as is.', -- test_TEXT (无长度限制)
             '\xDEADBEEF', -- test_BYTEA (二进制数据)
             ARRAY[1,2,3,4], -- test_ARRAY4
             '{{"a","b","c","d"},{"e","f","g","h"},{"i","j","k","l"},{"m","n","o","p"}}'::text[][] -- test_ARRAY4_4 (二维数组)
         );

附加uuid  PostgreSQL中的如何生成 UUID/GUID_postgresql guid-CSDN博客

CREATE EXTENSION pgcrypto;
CREATE EXTENSION "uuid-ossp";

SELECT gen_random_uuid();

CREATE TABLE auuid (
    test_INT2	INT2
    ,test_SMALLSERIAL	SMALLSERIAL
    ,test_SERIAL2	SERIAL2
    ,test_INTEGER	INTEGER
    ,test_SERIAL	SERIAL
    ,test_BIGINT	BIGINT
    ,test_BIGSERIAL	BIGSERIAL
    ,test_REAL	REAL
    ,test_FLOAT4	FLOAT4
    ,test_FLOAT8	FLOAT8
    ,test_DOUBLE_PRECISION	DOUBLE PRECISION
    ,test_NUMERIC	NUMERIC(15,3)
    ,test_DECIMAL	DECIMAL(15,3)
    ,test_BOOLEAN	BOOLEAN
    ,test_DATE	DATE
    ,test_TIME	TIME(3)
    ,test_TIMESTAMP	TIMESTAMP(3)
    ,test_CHAR	CHAR(250)
    ,test_CHARACTER	CHARACTER(250)
    ,test_VARCHAR	VARCHAR(250)
    ,test_CHARACTER_VARYING	CHARACTER VARYING(250)
    ,test_TEXT	TEXT
    ,test_BYTEA	BYTEA
    ,test_ARRAY4	 integer ARRAY[4]
    ,test_ARRAY4_4	 text[4][4]
    ,uuid UUID DEFAULT gen_random_uuid()
    ,primary key (test_INTEGER)
);
INSERT INTO auuid (
    test_INT2,
    test_SMALLSERIAL,
    test_SERIAL2,
    test_INTEGER,
    test_SERIAL,
    test_BIGINT,
    test_BIGSERIAL,
    test_REAL,
    test_FLOAT4,
    test_FLOAT8,
    test_DOUBLE_PRECISION,
    test_NUMERIC,
    test_DECIMAL,
    test_BOOLEAN,
    test_DATE,
    test_TIME,
    test_TIMESTAMP,
    test_CHAR,
    test_CHARACTER,
    test_VARCHAR,
    test_CHARACTER_VARYING,
    test_TEXT,
    test_BYTEA,
    test_ARRAY4,
    test_ARRAY4_4
) VALUES (
             123,  -- test_INT2
             1001, -- test_SMALLSERIAL (手动插入值，但通常不需要)
             1002, -- test_SERIAL2 (手动插入值，但通常不需要)
             1, -- test_INTEGER (这里设置为主键)
             1003, -- test_SERIAL (手动插入值，但通常不需要)
             9223372036854775807, -- test_BIGINT
             1004, -- test_BIGSERIAL (手动插入值，但通常不需要)
             3.14, -- test_REAL
             3.14, -- test_FLOAT4
             3.141592653589793, -- test_FLOAT8
             3.141592653589793, -- test_DOUBLE_PRECISION
             123.456, -- test_NUMERIC
             123.456, -- test_DECIMAL
             true, -- test_BOOLEAN
             '2023-10-23', -- test_DATE
             '12:34:56.789', -- test_TIME
             '2023-10-23 12:34:56.789', -- test_TIMESTAMP
             'Some Text', -- test_CHAR (会被截断到250字符)
             'Some Text', -- test_CHARACTER (会被截断到250字符)
             'Some Text', -- test_VARCHAR (最大250字符)
             'Some Text', -- test_CHARACTER_VARYING (最大250字符)
             'Some longer text that exceeds 250 characters but will be stored as is.', -- test_TEXT (无长度限制)
             '\xDEADBEEF', -- test_BYTEA (二进制数据)
             ARRAY[1,2,3,4], -- test_ARRAY4
             '{{"a","b","c","d"},{"e","f","g","h"},{"i","j","k","l"},{"m","n","o","p"}}'::text[][] -- test_ARRAY4_4 (二维数组)
         );