问题描述
亲爱的朋友们......
我使用的是postgres 7.4和SuSE 9.1。
我想在MySQL上使用auto_increment 。我查阅了
关于的文档和我找到了
" serial" 。
但是我不知道如何创建auto_increment。
这是我的尝试：
"
kv =＃alter table sales alter column salesid int4
serial;
错误：语法错误在或附近int4在角色40
"
请告诉我正确的命令。
非常感谢提前。
__________________________________
你是Yahoo！？
Yahoo!邮件 - 帮助您免受恶意病毒的侵害。
---------------------------（播出结束） ---------------------------
提示6：您是否搜索了我们的列表档案？
Dear my friends...
I am using postgres 7.4 and SuSE 9.1.
I want to use auto_increment as on MySQL. I look up
the documentation on www.postgres.com and I found
"serial" .
But I don''t know how to create auto_increment.
here is my try:
"
kv=# alter table sales alter column salesid int4
serial;
ERROR: syntax error at or near "int4" at character 40
"
Please tell me the correct command to that.
Thank you very much in advance.
__________________________________
Do you Yahoo!?
Yahoo! Mail - Helps protect you from nasty viruses.
http://promotions.yahoo.com/new_mail
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
推荐答案
Serial是一个宏这使得postgresql在
一次做了几件事。让我们通过运行一个带有串行关键字的
创建表来查看其中的重要部分，然后检查表格，我们应该这么做吗？
est => create table test（id serial primary key，info text）;
注意：CREATE TABLE将创建隐式序列test_id_seq for
" serial"列test.id
注意：CREATE TABLE / PRIMARY KEY将创建隐式索引
" test_pkey"用于表格测试
创建表格
test => \d测试
表" public.test"
列|输入|修饰符
-------- + --------- + ---------------------- --------------------------------
id |整数| not null default nextval（''public.test_id_seq'':: text）
info |文字|
索引：
" test_pkey"主键，btree（id）
test => \ds
关系清单
架构|名称|输入|所有者
-------- + ------------- + ---------- + ------- ---
公开| test_id_seq |序列| smarlowe
（1行）
现在，除了创建表和序列之外，postgresql还有，在
后台，为表上的序列创建了一个依赖项。这个
意味着如果我们删除表，创建
表语句创建的序列也将消失。
现在，你很接近，首先你需要添加一个正确类型的列，
创建一个序列并告诉表使用该序列作为
默认值。让我们假设我做了这样的表测试：
test =>创建表测试（信息文本）;
创建表
测试=>
现在我要添加一个自动递增列。我们不能只添加一个
序列，因为postgresql不支持在alter
表中设置默认值，所以我们只需添加一个int4，生成一个序列，并指定默认值：
test => alter table test add id int4 unique;
注意：ALTER TABLE / ADD UNIQUE将创建隐式索引
" test_id_key"对于表格测试
ALTER TABLE
test =>创建序列test_id_seq;
CREATE SEQUENCE
test => alter table test alter column id set default
nextval（''test_id_seq'':: text）;
ALTER TABLE
现在，如果你有一堆已经存在的行，如下所示：
test => select * from test;
info | id
------ + ----
abc |
def |
（2行）
然后你需要填充那些行id字段以放入一个序列，并且
这很简单，实际上：
est =>更新测试集id = DEFAULT;
更新2
test => select * from test;
info | id
------ + ----
abc | 1
def | 2
（2行）
test =>
你去吧！
---------------------------（播出结束）-------------- -------------
提示5：您查看了我们广泛的常见问题解答吗？
Serial is a "macro" that makes postgresql do a couple of things all at
once. Let''s take a look at the important parts of that by running a
create table with a serial keyword, and then examining the table, shall
we?
est=> create table test (id serial primary key, info text);
NOTICE: CREATE TABLE will create implicit sequence "test_id_seq" for
"serial" column "test.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"test_pkey" for table "test"
CREATE TABLE
test=> \d test
Table "public.test"
Column | Type | Modifiers
--------+---------+------------------------------------------------------
id | integer | not null default nextval(''public.test_id_seq''::text)
info | text |
Indexes:
"test_pkey" primary key, btree (id)
test=> \ds
List of relations
Schema | Name | Type | Owner
--------+-------------+----------+----------
public | test_id_seq | sequence | smarlowe
(1 row)
Now, as well as creating the table and sequence, postgresql has, in the
background, created a dependency for the sequence on the table. This
means that if we drop the table, the sequence created by the create
table statement will disappear as well.
Now, you were close, first you need to add a column of the proper type,
create a sequence and tell the table to use that sequence as the
default. Let''s assume I''d made the table test like this:
test=> create table test (info text);
CREATE TABLE
test=>
And now I want to add an auto incrementing column. We can''t just add a
serial because postgresql doesn''t support setting defaults in an alter
table, so we just add an int4, make a sequence, and assign the default:
test=> alter table test add id int4 unique;
NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index
"test_id_key" for table "test"
ALTER TABLE
test=> create sequence test_id_seq;
CREATE SEQUENCE
test=> alter table test alter column id set default
nextval(''test_id_seq''::text);
ALTER TABLE
Now, if you have a bunch of already existing rows, like this:
test=> select * from test;
info | id
------+----
abc |
def |
(2 rows)
then you need to populate those rows id field to put in a sequence, and
that''s pretty easy, actually:
est=> update test set id=DEFAULT;
UPDATE 2
test=> select * from test;
info | id
------+----
abc | 1
def | 2
(2 rows)
test=>
And there you go!
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
---------------------------（广播结束）------------------ ---------
提示9：如果您的
加入列的数据类型不匹配，规划人员将忽略您选择索引扫描的愿望
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column''s datatypes do not match
我会添加UNIQUE NOT NULL或PRIMARY KEY以防万一UPDATE尝试
将id字段修改为NULL，这将是一件非常糟糕的事情。
------------------ ---------（广播结束）---------------------------
提示4：不要杀死-9''邮政局长
I''d add UNIQUE NOT NULL or PRIMARY KEY just in case some UPDATE tries to
modify the id field to NULL which would be a Very Bad Thing to do.
---------------------------(end of broadcast)---------------------------
TIP 4: Don''t ''kill -9'' the postmaster
这篇关于改变表以设置串行功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！