Home | 簡體中文 | 繁體中文 | 雜文 | 打賞(Donations) | ITEYE 博客 | OSChina 博客 | Facebook | Linkedin | 知乎專欄 | Search | Email

4.3. 表

4.3.1. 預設時間與時區

預設時間省略時區信息

			
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
);
			
			

4.3.2. 漢字做欄位名

			
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都支持非英文的編碼。