<!--

$Header: /cvsroot/pgsql/doc/src/sgml/ref/cluster.sgml,v 1.11 2001/09/03 12:57:49 petere Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/ref/cluster.sgml,v 1.12 2001/10/31 04:49:43 momjian Exp $

Postgres documentation

-->

which uses the <productname>Postgres</productname> sorting code in

the ORDER BY clause to match the index, and which is much faster for

unordered data. You then drop the old table, use

<command>ALTER TABLE/RENAME</command>

<command>ALTER TABLE...RENAME</command>

to rename <replaceable class="parameter">temp</replaceable> to the old name, and

recreate any indexes. The only problem is that <acronym>OID</acronym>s

will not be preserved. From then on, <command>CLUSTER</command> should be

0,0,0);

if (!HeapTupleIsValid(indexTuple))

elog(ERROR,"ALTER TABLE/ADD CONSTRAINT: Index \"%u\" not found",

elog(ERROR,"ALTER TABLE /ADD CONSTRAINT: Index \"%u\" not found",

indexoid);

indexStruct= (Form_pg_index)GETSTRUCT(indexTuple);

NIL);

elog(NOTICE,"ALTER TABLE/ADD UNIQUE will create implicit index '%s' for table '%s'",

elog(NOTICE,"ALTER TABLE /ADD UNIQUE will create implicit index '%s' for table '%s'",

iname,relationName);

if (index_found)

elog(NOTICE,"Unique constraint supercedes existing index on relation \"%s\". Drop the existing index to remove redundancy.",relationName);

if (cxt->pkey!=NULL||

(OidIsValid(cxt->relOid)&&

relationHasPrimaryKey(cxt->relname)))

elog(ERROR,"%s/PRIMARY KEY multiple primary keys"

elog(ERROR,"%s /PRIMARY KEY multiple primary keys"

" for table '%s' are not allowed",

cxt->stmtType,cxt->relname);

cxt->pkey=index;

elog(ERROR,"%s: failed to make implicit index name",

cxt->stmtType);

elog(NOTICE,"%s/%s will create implicit index '%s' for table '%s'",

cxt->stmtType, (index->primary ?"PRIMARY KEY" :"UNIQUE"),

elog(NOTICE,"%s /%s will create implicit index '%s' for table '%s'",

cxt->stmtType, (index->primary ?"ADDPRIMARY KEY" :"ADDUNIQUE"),

index->idxname,cxt->relname);

}

}

n->constraints =$3;

if ($4 !=NULL)

elog(NOTICE,"CREATE TABLE/COLLATE %s not yet implemented"

elog(NOTICE,"CREATE TABLE /COLLATE %s not yet implemented"

"; clause ignored", $4);

$$ = (Node *)n;

SelectStmt *n = findLeftmostSelect((SelectStmt *)$7);

if (n->into !=NULL)

elog(ERROR,"CREATE TABLE/AS SELECT may not specify INTO");

elog(ERROR,"CREATE TABLE /AS SELECT may not specify INTO");

n->istemp =$2;

n->into =$4;

if ($5 != NIL)

if ($2 == RELATIVE)

{

if ($3 ==0)

elog(ERROR,"FETCH/RELATIVE at current position is not supported");

elog(ERROR,"FETCH /RELATIVE at current position is not supported");

$2 = FORWARD;

}

if ($3 <0)

|RELATIVE{$$ = RELATIVE; }

{

elog(NOTICE,"FETCH/ABSOLUTE not supported, using RELATIVE");

elog(NOTICE,"FETCH /ABSOLUTE not supported, using RELATIVE");

$$ = RELATIVE;

}

;

}

{

elog(ERROR,"CREATE FUNCTION/OUT parameters are not supported");

elog(ERROR,"CREATE FUNCTION /OUT parameters are not supported");

$$ =TRUE;

}

{

elog(ERROR,"CREATE FUNCTION/INOUT parameters are not supported");

elog(ERROR,"CREATE FUNCTION /INOUT parameters are not supported");

$$ =FALSE;

}

;

elog(ERROR,"COMMIT/CHAIN not yet supported");

elog(ERROR,"COMMIT /CHAIN not yet supported");

$$ =TRUE;

}

;

ResTarget *d;

if (length(src) !=length(dst))

elog(ERROR,"CREATE TABLE/AS SELECT has mismatched column count");

elog(ERROR,"CREATE TABLE /AS SELECT has mismatched column count");

while ((src != NIL) && (dst != NIL))

{

|LOCALTEMPORARY{$$ = make_str("local temporary"); }

|LOCALTEMP{$$ = make_str("local temp"); }

|GLOBALTEMPORARY{

mmerror(ET_NOTICE,"Currently unsupported CREATE TABLE/GLOBAL TEMPORARY will be passed to backend");

mmerror(ET_NOTICE,"Currently unsupported CREATE TABLE /GLOBAL TEMPORARY will be passed to backend");

$$ = make_str("global temporary");

}

|GLOBALTEMP{

mmerror(ET_NOTICE,"Currently unsupported CREATE TABLE/GLOBAL TEMP will be passed to backend");

mmerror(ET_NOTICE,"Currently unsupported CREATE TABLE /GLOBAL TEMP will be passed to backend");

$$ = make_str("global temp");

}

|/*EMPTY*/{$$ = EMPTY; }

{

if (strlen($4) >0)

{

sprintf(errortext,"Currently unsupported CREATE TABLE/COLLATE %s will be passed to backend", $4);

sprintf(errortext,"Currently unsupported CREATE TABLE /COLLATE %s will be passed to backend", $4);

mmerror(ET_NOTICE, errortext);

}

$$ = cat_str(4,$1,$2,$3,$4);

{ FoundInto =0; }SelectStmt

{

if (FoundInto ==1)

mmerror(ET_ERROR,"CREATE TABLE/AS SELECT may not specify INTO");

mmerror(ET_ERROR,"CREATE TABLE /AS SELECT may not specify INTO");

$$ = cat_str(7, make_str("create"),$2, make_str("table"),$4,$5, make_str("as"),$8);

}

}

{

mmerror(ET_NOTICE,"Currently unsupported CREATE TABLE/GLOBAL TEMPORARY will be passed to backend");

mmerror(ET_NOTICE,"Currently unsupported CREATE TABLE /GLOBAL TEMPORARY will be passed to backend");

$$ = cat_str(3, make_str("global temporary"),$3,$4);

}

{

mmerror(ET_NOTICE,"Currently unsupported CREATE TABLE/GLOBAL TEMP will be passed to backend");

mmerror(ET_NOTICE,"Currently unsupported CREATE TABLE /GLOBAL TEMP will be passed to backend");

$$ = cat_str(3, make_str("global temp"),$3,$4);

}

-- FOREIGN KEY CONSTRAINT adding TEST

CREATE TABLE tmp2 (a int primary key);

NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'tmp2_pkey' for table 'tmp2'

NOTICE: CREATE TABLE /PRIMARY KEY will create implicit index 'tmp2_pkey' for table 'tmp2'

CREATE TABLE tmp3 (a int, b int);

CREATE TABLE tmp4 (a int, b int, unique(a,b));

NOTICE: CREATE TABLE/UNIQUE will create implicit index 'tmp4_a_key' for table 'tmp4'

NOTICE: CREATE TABLE / ADDUNIQUE will create implicit index 'tmp4_a_key' for table 'tmp4'

CREATE TABLE tmp5 (a int, b int);

-- Insert rows into tmp2 (pktable)

INSERT INTO tmp2 values (1);

-- Note: these tables are TEMP to avoid name conflicts when this test

-- is run in parallel with foreign_key.sql.

CREATE TEMP TABLE PKTABLE (ptest1 int PRIMARY KEY);

NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable'

NOTICE: CREATE TABLE /PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable'

CREATE TEMP TABLE FKTABLE (ftest1 text);

-- This next should fail, because text=int does not exist

ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;

DROP TABLE fktable;

CREATE TEMP TABLE PKTABLE (ptest1 int, ptest2 text,

PRIMARY KEY(ptest1, ptest2));

NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable'

NOTICE: CREATE TABLE /PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable'

-- This should fail, because we just chose really odd types

CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 datetime);

ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable;

drop table atacc3;

drop table atacc2;

drop table atacc1;

-- test unique constraint adding

create table atacc1 ( test int );

-- add a unique constraint

alter table atacc1 add constraint atacc_test1 unique (test);

NOTICE: ALTER TABLE/UNIQUE will create implicit index 'atacc_test1' for table 'atacc1'

-- insert first value

insert into atacc1 (test) values (2);

-- should fail

insert into atacc1 (test) values (2);

ERROR: Cannot insert a duplicate key into unique index atacc_test1

-- should succeed

insert into atacc1 (test) values (4);

-- try adding a unique oid constraint

alter table atacc1 add constraint atacc_oid1 unique(oid);

NOTICE: ALTER TABLE/UNIQUE will create implicit index 'atacc_oid1' for table 'atacc1'

drop table atacc1;

-- let's do one where the unique constraint fails when added

create table atacc1 ( test int );

-- insert soon to be failing rows

insert into atacc1 (test) values (2);

insert into atacc1 (test) values (2);

-- add a unique constraint (fails)

alter table atacc1 add constraint atacc_test1 unique (test);

NOTICE: ALTER TABLE/UNIQUE will create implicit index 'atacc_test1' for table 'atacc1'

ERROR: Cannot create unique index. Table contains non-unique values

insert into atacc1 (test) values (3);

drop table atacc1;

-- let's do one where the unique contsraint fails

-- because the column doesn't exist

create table atacc1 ( test int );

-- add a unique constraint (fails)

alter table atacc1 add constraint atacc_test1 unique (test1);

ERROR: ALTER TABLE: column "test1" named in key does not exist

drop table atacc1;

-- something a little more complicated

create table atacc1 ( test int, test2 int);

-- add a unique constraint

alter table atacc1 add constraint atacc_test1 unique (test, test2);

NOTICE: ALTER TABLE/UNIQUE will create implicit index 'atacc_test1' for table 'atacc1'

-- insert initial value

insert into atacc1 (test,test2) values (4,4);

-- should fail

insert into atacc1 (test,test2) values (4,4);

ERROR: Cannot insert a duplicate key into unique index atacc_test1

-- should all succeed

insert into atacc1 (test,test2) values (4,5);

insert into atacc1 (test,test2) values (5,4);

insert into atacc1 (test,test2) values (5,5);

drop table atacc1;

-- lets do some naming tests

create table atacc1 (test int, test2 int, unique(test));

NOTICE: CREATE TABLE/UNIQUE will create implicit index 'atacc1_test_key' for table 'atacc1'

alter table atacc1 add unique (test2);

NOTICE: ALTER TABLE/UNIQUE will create implicit index 'atacc1_test2_key' for table 'atacc1'

-- should fail for @@ second one @@

insert into atacc1 (test2, test) values (3, 3);

insert into atacc1 (test2, test) values (2, 3);

ERROR: Cannot insert a duplicate key into unique index atacc1_test_key

drop table atacc1;

---

CREATE TABLE serialTest (f1 text, f2 serial);

NOTICE: CREATE TABLE will create implicit sequence 'serialtest_f2_seq' for SERIAL column 'serialtest.f2'

NOTICE: CREATE TABLE/UNIQUE will create implicit index 'serialtest_f2_key' for table 'serialtest'

NOTICE: CREATE TABLE / ADDUNIQUE will create implicit index 'serialtest_f2_key' for table 'serialtest'

INSERT INTO serialTest VALUES ('foo');

INSERT INTO serialTest VALUES ('bar');

INSERT INTO serialTest VALUES ('force', 100);