Dotaz: Postgresql 10: Fallback particia

[hmlovka]

Dobry den,

mam vytvorenu master tabulku:

CREATE TABLE measurement ( uuid varchar(16), logdate date not null, peaktemp int ) PARTITION BY LIST ( left(uuid,1) );

Particie budu vytvarene na zaklade prveho znaku stlpca uuid.

Nasledne vytvorim particie:

CREATE TABLE measurement_0 PARTITION OF measurement ( uuid varchar(16), logdate date not null, peaktemp int ) FOR VALUES IN ('0');

CREATE TABLE measurement_1 PARTITION OF measurement ( uuid varchar(16), logdate date not null, peaktemp int ) FOR VALUES IN ('1');

......

.....

tych particii bude celkom 10 (measurement_0 ..... measurement_9 )

Rad by som sa opytal ako mam vytvorit fallback particiu measurement_fallback, do ktorej budu ukadane vsetky ostatne riadky, teda riadky ktore nezacinaju cislicami ale pismenami.

Dakujem

Odpovědi

[EtDirloth]

Pre fallback particie si treba pockat na verziu 11. CREATE TABLE measurement_x PARTITION OF measurement DEFAULT;

Vo verzii 10 mozes pouzit VALUES IN (NULL):

CREATE TABLE measurement ( uuid varchar(16), logdate date not null, peaktemp int ) PARTITION BY LIST ( ( CASE WHEN uuid ~ '^[0-9].*' THEN left(uuid,1) ELSE NULL END) );
CREATE TABLE measurement_0 PARTITION OF measurement  FOR VALUES IN ('0');
CREATE TABLE measurement_x PARTITION OF measurement  FOR VALUES IN (NULL);

insert into measurement values ('0123456789012345', now()::date, 1);
insert into measurement values ('a123456789012345', now()::date, 1);

select * from only measurement_0;
       uuid       |  logdate   | peaktemp 
------------------+------------+----------
 0123456789012345 | 2018-01-05 |        1
(1 row)
select * from only measurement_x;
       uuid       |  logdate   | peaktemp 
------------------+------------+----------
 a123456789012345 | 2018-01-05 |        1
(1 row)

[hmlovka]

supeeer, funguje.... dakujem :)