CODEWITHSUNDEEP
partitioningmonetdb
slamarca
slamarca

Reputation: 1

Why MonetDB does not add this table to the merge table?

I am struggling with merge tables and need community help!

  1. when I add a table to a newly created merge table, does the "added" table need to be empty?

  2. if the "added" table has an identity column (defined as int next value for "monetdb"."seq_nnnn") does the merge table need a corresponding column (and what about the sequence? is the same or must I create a new one?)

I try to be more specific. I create the "normal" table

  1. what about indexes and constraints in the "added" table? do they need to be replicated in the merge table?

I tried many thing but always ended up with

SQL Error [3F000]: ALTER MERGE TABLE: to be added table index type doesn't match MERGE TABLE definition

"normal" table is:

CREATE TABLE monetdb234234x.movcassa_m1 (
    idmov int NOT NULL,
    tipmov char(3) NOT NULL,
    nummov int NOT NULL,
    numsco int NOT NULL,
    datmov date NOT NULL,
    dataora timestamp NOT NULL,
    codmag char(2) NOT NULL,
    codcli char(7) NOT NULL,
    prezzosco decimal(12,5) DEFAULT '0' NOT NULL,
    CONSTRAINT movcassa_m1_idmov_pkey PRIMARY KEY (idmov)
);
CREATE INDEX movcassa_m1_datmov ON monetdb234234x.movcassa_m1 (datmov);
CREATE INDEX movcassa_m1_tipmov ON monetdb234234x.movcassa_m1 (tipmov);

then I create the "merge" table:

CREATE merge TABLE monetdb234234x.movcassa_merge (
    idmov int NOT NULL,
    tipmov char(3) NOT NULL,
    nummov int NOT NULL,
    numsco int NOT NULL,
    datmov date NOT NULL,
    dataora timestamp NOT NULL,
    codmag char(2) NOT NULL,
    codcli char(7) NOT NULL,
    prezzosco decimal(12,5) DEFAULT '0' NOT NULL
);

then I try to add the table:

alter TABLE monetdb234234x.movcassa_merge add table movcassa_m1

and get the error:

SQL Error [3F000]: ALTER MERGE TABLE: to be added table key doesn't match MERGE TABLE definition

Edited:

if I remove the primary index clause from the first table adding to the merge table succeeds. But obviously I need a primary key in the table, because I must insert data in that table.

Upvotes: 0

Views: 105

Answers (1)

stefanos
stefanos

Reputation: 1

  1. tables which are added to a merge table (aka child tables) do not have to be empty

  2. I'm not sure what you want to achieve here but the only constrain for the merge table would be to have matching column types (e.g. the actual column constrains are not compared between merge table and child table). so you could do something like

create table bar (n int, x text, idcol int default next value for barseq);
create table foo (m int, y text, idcol int default next value for fooseq);

create merge table foobar (l int, z text, idcol int);
alter table foobar add table bar;
alter table foobar add table foo;

of course you will not have any constrains (like uniqueness) in the idcol column of the merge table

  1. they do not need to be replicated and in most of the cases you will not be able to replicate them. You should think of merge tables as a UNION ALL views between the child tables.

For more info on merge (and merge partitioned) table usage this blogpost might be useful: https://www.monetdb.org/blogs/update-mergetables/

Edited:

what I described about matching attributes holds true even in the case of primary keys. the merge table must have the same column indicated as primary key as the to-be-added table (child) e.g.

create table foo (n int, m text, constraint foo_n_pkey primary key (n));

create merge table foo_merge (n int, m text, constraint foo_merge_n_pkey primary key (n));
alter table foo_merge add table foo;

Upvotes: 0

Related Questions