--创建测试表create table tabnumber_test(col1 smallint,col2 integer,col3 bigint,col4 decimal,col5 numeric,col6 real,col7 double precision,col8 smallserial,col9 serial,col10 bigserial,col11 money);--查看表结构mytest=# \d tabnumber_test;Table "public.tabnumber_test"Column | Type | Collation | Nullable | Default--------+------------------+-----------+----------+-----------------------------------------------col1 | smallint | | |col2 | integer | | |col3 | bigint | | |col4 | numeric | | |col5 | numeric | | |col6 | real | | |col7 | double precision | | |col8 | smallint | | not null | nextval('tabnumber_test_col8_seq'::regclass)col9 | integer | | not null | nextval('tabnumber_test_col9_seq'::regclass)col10 | bigint | | not null | nextval('tabnumber_test_col10_seq'::regclass)col11 | money | | |mytest=#--插入测试数据insert into tabnumber_test(col1,col2,col3,col4,col5,col6,col7,col11) values(32767,32768,2147483648,32768.56,32768.56,35.78,39.12,100.30);insert into tabnumber_test(col1,col2,col3,col4,col5,col6,col7,col11) values(32766,32766,2147483646,32768.56,32768.31,35.78,40.12,110.31);--第一个数据超出长度暴多mytest=# insert into tabnumber_test(col1,col2,col3,col4,col5,col6,col7,col11) values(32768,32768,2147483648,32768.56,32768.56,35.78,39.12,100.30);ERROR: smallint out of rangemytest=# insert into tabnumber_test(col1,col2,col3,col4,col5,col6,col7,col11) values(32767,32768,2147483648,32768.56,32768.56,35.78,39.12,100.30);INSERT 0 1mytest=# select * from tabnumber_test;col1 | col2 | col3 | col4 | col5 | col6 | col7 | col8 | col9 | col10 | col11-------+-------+------------+----------+----------+-------+-------+------+------+-------+---------32767 | 32768 | 2147483648 | 32768.56 | 32768.56 | 35.78 | 39.12 | 1 | 1 | 1 | $100.30(1 row)mytest=# select * from tabnumber_test;col1 | col2 | col3 | col4 | col5 | col6 | col7 | col8 | col9 | col10 | col11-------+-------+------------+----------+----------+-------+-------+------+------+-------+---------32767 | 32768 | 2147483648 | 32768.56 | 32768.56 | 35.78 | 39.12 | 1 | 1 | 1 | $100.3032766 | 32766 | 2147483646 | 32768.56 | 32768.31 | 35.78 | 40.12 | 2 | 2 | 2 | $110.31(2 rows)--查看数据mytest=# select * from tabnumber_test;-[ RECORD 1 ]-----col1 | 32767col2 | 32768col3 | 2147483648col4 | 32768.56col5 | 32768.56col6 | 35.78col7 | 39.12col8 | 1col9 | 1col10 | 1col11 | $100.30
从上面可以看到，smallint范围比较小，serial类型其实就是自增序列。money类型是专门用来记录货币类型，数值部分与numeric一样，前者就比后者多了一个货币符号‘$’。
2、字符类型
--创建测试表create table tabchar_test(col1 varchar(20),col2 char(20),col3 bpchar,col4 bpchar(20),col5 text);--插入测试数据insert into tabchar_test values('haha','caca','dada ','papa ','mama');insert into tabchar_test values('gaga','nana','sasa','rara ','lala');--查看表结构mytest=# \d tabchar_test;Table "public.tabchar_test"Column | Type | Collation | Nullable | Default--------+-----------------------+-----------+----------+---------col1 | character varying(20) | | |col2 | character(20) | | |col3 | bpchar | | |col4 | character(20) | | |col5 | text | | |mytest=#--查看表数据mytest=# select * from tabchar_test;col1 | col2 | col3 | col4 | col5------+----------------------+---------+----------------------+------haha | caca | dada | papa | mama(1 row)--查看字段字符串长度mytest=#mytest=# select length(col1),length(col2),length(col3),length(col4),length(col5) from tabchar_test;length | length | length | length | length--------+--------+--------+--------+--------4 | 4 | 4 | 4 | 4(1 row)mytest=# insert into tabchar_test values('gaga','nana','sasa','rara ','lala');INSERT 0 1mytest=#mytest=# select * from tabchar_test;col1 | col2 | col3 | col4 | col5------+----------------------+---------+----------------------+------haha | caca | dada | papa | mamagaga | nana | sasa | rara | lala(2 rows)mytest=# select length(col1),length(col2),length(col3),length(col4),length(col5) from tabchar_test;length | length | length | length | length--------+--------+--------+--------+--------4 | 4 | 4 | 4 | 44 | 4 | 4 | 4 | 4(2 rows)
postgresql数据库字符类型，字符的长度，实际都还是计算非空字符的长度，bpchar(n)实际与char(n)是一样的。
--创建测试表create table tabtime_test(col1 timestamp,col2 timestamp with time zone,col3 date,col4 time,col5 time with time zone,col6 interval);--插入测试数据insert into tabtime_test values(now(),now(),now(),now(),now(),INTERVAL '1 month');insert into tabtime_test values(now(),now(),now(),now(),now(),now()+INTERVAL '1 month' - CURRENT_DATE);insert into tabtime_test values(now(),now(),now(),now(),now(),CURRENT_DATE+INTERVAL '1 month' - now());--查看表结构mytest=# \d tabtime_test;Table "public.tabtime_test"Column | Type | Collation | Nullable | Default--------+-----------------------------+-----------+----------+---------col1 | timestamp without time zone | | |col2 | timestamp with time zone | | |col3 | date | | |col4 | time without time zone | | |col5 | time with time zone | | |col6 | interval | | |mytest=#--查看表数据mytest=# select * from tabtime_test;col1 | col2 | col3 | col4 | col5 | col6----------------------------+-------------------------------+------------+-----------------+--------------------+-------2025-11-25 16:29:36.503391 | 2025-11-25 16:29:36.503391+08 | 2025-11-25 | 16:29:36.503391 | 16:29:36.503391+08 | 1 mon(1 row)mytest=# select * from tabtime_test;col1 | col2 | col3 | col4 | col5 | col6----------------------------+-------------------------------+------------+-----------------+--------------------+-------------------------2025-11-25 16:29:36.503391 | 2025-11-25 16:29:36.503391+08 | 2025-11-25 | 16:29:36.503391 | 16:29:36.503391+08 | 1 mon2025-11-25 16:38:05.764498 | 2025-11-25 16:38:05.764498+08 | 2025-11-25 | 16:38:05.764498 | 16:38:05.764498+08 | 30 days 16:38:05.764498(2 rows)mytest=# select * from tabtime_test;col1 | col2 | col3 | col4 | col5 | col6----------------------------+-------------------------------+------------+-----------------+--------------------+-------------------------2025-11-25 16:29:36.503391 | 2025-11-25 16:29:36.503391+08 | 2025-11-25 | 16:29:36.503391 | 16:29:36.503391+08 | 1 mon2025-11-25 16:38:05.764498 | 2025-11-25 16:38:05.764498+08 | 2025-11-25 | 16:38:05.764498 | 16:38:05.764498+08 | 30 days 16:38:05.7644982025-11-25 16:42:28.060743 | 2025-11-25 16:42:28.060743+08 | 2025-11-25 | 16:42:28.060743 | 16:42:28.060743+08 | 29 days 07:17:31.939257--查看倒计时（附加）mytest=# select to_char('2026-1-1 00:00:00'::timestamp - now(),'DD hh24:mi:ss')::INTERVAL;to_char------------------36 days 06:25:28(1 row)
文章转载自skylines，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
