- View the data type of the current database
postgres=# \d pg_type ;
Table "pg_catalog.pg_type"
Column | Type | Modifiers
----------------+--------------+-----------
typname | name | not null
typnamespace | oid | not null
----
//Show all type types and corresponding storage types:
postgres=# select typname, typstorage from pg_type ;
typname | typstorage
---------------------------------------+------------
bool | p
bytea | x
char | p
name | p
int8 | p
int2 | p
int2vector | p
int4 | p
regproc | p
text | x
oid | p
tid | p
xid | p
cid | p
oidvector | p
pg_type | x
pg_attribute | x
pg_proc | x
pg_class | x
json | x
xml | x
_xml | x
//For the meaning of the storage type p x e m, search for it yourself, representing different storage methods.
Classification of data types on the left
- Common data types, numbers
For the serial type, the effect is actually the same as integer + next sequence.
When you create a series data type, it actually helps you create the series automatically
postgres=# create table t (id serial);
CREATE TABLE
postgres=# \d+ t
Table "public.t"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+------------------------------------------------+---------+--------------+-------------
id | integer | not null default nextval('t_id_seq'::regclass) | plain | |
//As you can see, the t_id_seq sequence is automatically created and added not null Constraints.
//Study this sequence:
postgres=# \d+ t_id_seq
Sequence "public.t_id_seq"
Column | Type | Value | Storage
---------------+---------+---------------------+---------
sequence_name | name | t_id_seq | plain
last_value | bigint | 1 | plain
start_value | bigint | 1 | plain
increment_by | bigint | 1 | plain
max_value | bigint | 9223372036854775807 | plain
min_value | bigint | 1 | plain
cache_value | bigint | 1 | plain
log_cnt | bigint | 0 | plain
is_cycled | boolean | f | plain
is_called | boolean | f | plain
Owned by: public.t.id
- Common Character Types
SQL defines two basic character types, varchar(n) and char(n), where n is a positive integer.Both types can store up to n character-length strings, and attempting to store longer strings into these types of fields will result in an error unless the characters exceeding the length are blank, in which case the string will be truncated to the maximum length.Without a length declaration, char equals char(1), while varchar can accept strings of any length.
char type, if not long enough to fill with spaces.
Note that regardless of the character set, the sum is in characters, not bytes, unlike ORACLE.
text: Surfaces are infinite in length and can actually support up to 1 GB (depending on version)
Below are all byte-related types, and above are character-related types
"char" single byte internal use type
Types used internally by name
postgres=# create table t2(c1 varchar(3));
//Field c1 allows up to three "characters", not bytes!!
CREATE TABLE
postgres=# insert into t2 values ('How do you do');
INSERT 0 1
postgres=# insert into t2 values ('abc');
INSERT 0 1
postgres=# insert into t2 values ('abcd');
//This is the wrong time because abcd is 4 characters.
ERROR: value too long for type character varying(3)
postgres=# select pg_column_size (c1),c1 from t2 ;
pg_column_size | c1
----------------+--------
10 | How do you do
4 | abc
(2 rows)
-Common event types
Interval: is a type of time interval.
Output format of time
postgres=# show datestyle;
DateStyle
-----------
ISO, MDY
(1 row)
postgres=# select now() ;
now
-------------------------------
2017-05-26 09:32:35.197556+08 Refers to Zone 8
(1 row)
interval type:
postgres=# select now()-current_date;
?column?
-----------------
09:35:29.864559
(1 row)
- Boolean type