5.3. 表

默认时间省略时区信息

			
CREATE TABLE message
(
  id serial NOT NULL,
  title character varying(250) NOT NULL,
  message character varying(250) NOT NULL,
  status boolean NOT NULL DEFAULT false,
  operator character varying(5) NOT NULL,
  ctime timestamp without time zone NOT NULL DEFAULT (now())::timestamp(0) without time zone,
  mtime timestamp without time zone NOT NULL DEFAULT (now())::timestamp(0) without time zone,
  CONSTRAINT message_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
			
			

			
PostgreSQL是支持“区域”，“字符集支持”的，允许你使用本区域的字符集做为字段名。但要注意，你的终端要支持该字符集支持。我这里使用UNICODE，EUC_CN也适用。

Create table "组"(
       "序号" Serial NOT NULL UNIQUE,
       "组名" Varchar(20) NOT NULL,
       "描述" Varchar(255),
       UNIQUE ("组名"),
      PRIMARY KEY ("序号")
);

创建表：
member=> Create table "组"(
member(> "序号" Serial NOT NULL UNIQUE,
member(> "组名" Varchar(20) NOT NULL,
member(> "描述" Varchar(255),
member(> UNIQUE ("组名"),
member(>  PRIMARY KEY ("序号")
member(> );
NOTICE:  CREATE TABLE will create implicit sequence '组_序号_seq' for SERIAL column '组.序号'
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index '组_pkey' for table '组'
NOTICE:  CREATE TABLE / UNIQUE will create implicit index '组_组名_key' for table '组'
CREATE TABLE
member=> \d
               List of relations
 Schema |        Name        |   Type   | Owner
--------+--------------------+----------+-------
 public | group              | table    | chen
 public | group_id_seq       | sequence | chen
 public | groupmember        | table    | chen
 public | groupmember_id_seq | sequence | chen
 public | role               | table    | chen
 public | role_id_seq        | sequence | chen
 public | rolemember         | table    | chen
 public | rolemember_id_seq  | sequence | chen
 public | system_log         | table    | chen
 public | system_log_id_seq  | sequence | chen
 public | trust              | table    | chen
 public | trust_id_seq       | sequence | chen
 public | user               | table    | chen
 public | user_id_seq        | sequence | chen
 public | user_log           | table    | chen
 public | user_log_id_seq    | sequence | chen
 public | userinfo           | table    | chen
 public | userinfo_id_seq    | sequence | chen
 public | vgroup             | view     | chen
 public | vgroupmember       | view     | chen
 public | vsystem_log        | view     | chen
 public | vuser              | view     | chen
 public | 组                | table    | chen
 public | 组_序号_seq     | sequence | chen
(24 rows)

查看表结构：

member=> \d 组
                                     Table "public.组"
 Column |          Type          |                         Modifiers
--------+------------------------+-----------------------------------------------------------
 序号 | integer                | not null default nextval('public."组_序号_seq"'::text)
 组名 | character varying(20)  | not null
 描述 | character varying(255) |
Indexes: 组_pkey primary key btree ("序号"),
         组_组名_key unique btree ("组名")

插入数据：
member=> insert into 组(组名,描述) values('域用户','9812.net域内用户');
INSERT 110971 1
member=> insert into "组"("组名","描述") values('域用户','9812.net域内用户');
ERROR:  Cannot insert a duplicate key into unique index 组_组名_key
member=> insert into "组"("组名","描述") values('计算机维护组','维护计算机的用户用户');
INSERT 110973 1

查看数据：
member=> select * from 组;
 序号 |       组名       |             描述
--------+--------------------+--------------------------------
      1 | 域用户          | 9812.net域内用户
      3 | 计算机维护组 | 维护计算机的用户用户
(2 rows)
member=> select * from "组";
 序号 |       组名       |             描述
--------+--------------------+--------------------------------
      1 | 域用户          | 9812.net域内用户
      3 | 计算机维护组 | 维护计算机的用户用户
(2 rows)

注：在操作非英文字段的表时。建议最好前，后加上“"”，“"”符号。并非所有API都支持非英文的编码。