Postgres 9.1数据库包含了public架构下的表yksus1……yksus9。pgAdmin会像下面的代码一样显示这些定义。 如何将这些表移动到firma1架构?
firma1架构中的其他表引用了这些表的主键,外键引用只来自于firma1架构下的表。 其中一些表包含数据。如果将表移动到firma1架构下,外键引用也应更新为firma1.yksusn表。表结构不能更改。
看起来,主键序列已经在firma1架构下,因此它们不应该被移动。 版本字符串:PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu,由gcc-4.4.real(Debian 4.4.5-8)4.4.5编译,64位。
CREATE TABLE yksus1
(
yksus character(10) NOT NULL DEFAULT ((nextval('firma1.yksus1_yksus_seq'::regclass))::text || '_'::text),
veebis ebool,
nimetus character(70),
"timestamp" character(14) DEFAULT to_char(now(), 'YYYYMMDDHH24MISS'::text),
username character(10) DEFAULT "current_user"(),
klient character(40),
superinden character(20),
telefon character(10),
aadress character(50),
tlnr character(15),
rus character(60),
CONSTRAINT yksus1_pkey PRIMARY KEY (yksus)
);
ALTER TABLE yksus1
OWNER TO mydb_owner;
CREATE TRIGGER yksus1_trig
BEFORE INSERT OR UPDATE OR DELETE
ON yksus1
FOR EACH STATEMENT
EXECUTE PROCEDURE setlastchange();
其他表类似:
CREATE TABLE yksus2
(
yksus character(10) NOT NULL DEFAULT ((nextval('firma1.yksus2_yksus_seq'::regclass))::text || '_'::text),
nimetus character(70),
"timestamp" character(14) DEFAULT to_char(now(), 'YYYYMMDDHH24MISS'::text),
osakond character(10),
username character(10) DEFAULT "current_user"(),
klient character(40),
superinden character(20),
telefon character(10),
aadress character(50),
tlnr character(15),
rus character(60),
CONSTRAINT yksus2_pkey PRIMARY KEY (yksus),
CONSTRAINT yksus2_osakond_fkey FOREIGN KEY (osakond)
REFERENCES yksus2 (yksus) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE
);
ALTER TABLE yksus2
OWNER TO mydb_owner;
CREATE TRIGGER yksus2_trig
BEFORE INSERT OR UPDATE OR DELETE
ON yksus2
FOR EACH STATEMENT
EXECUTE PROCEDURE setlastchange();
CREATE SCHEMA IF NOT EXISTS foo;
创建模式。 - Brian Burns