Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

select regexp_substr('Main fabric: 50% Organic Cotton, 49% Cotton, 1% Elastane', 
                    '\y(\d+)%',1,2,'',1)

regexp_substr

49

SELECT 1

select regexp_substr('Main fabric: 50% Organic Cotton, 49% Cotton, 1% Elastane', 
                    '% (.+),?',1,2,'',1)

regexp_substr

null

SELECT 1

select regexp_substr('Main fabric: 50% Organic Cotton, 49% Cotton, 1% Elastane', 
                    '% ([^,]+),?',1,2,'',1)

regexp_substr

Cotton

SELECT 1

create table import_ (
articleNumber text primary key, 
material text );
insert into import_ values 
('TEST', 'Main fabric: 50% Organic Cotton, 49% Cotton, 1% Elastane' )

CREATE TABLE

INSERT 0 1

create table toode (
originart char(20) primary key,
mprots1 Numeric( 9,3),
mprots2 Numeric( 9,3),
mprots3 Numeric( 9,3),
materjal1 text,
materjal2 text,
materjal3 text,
materjal4 text,
materjal5 text );

insert into toode
select articleNumber
      ,regexp_substr(material,'\y(\d+)%',1,1,'',1)::numeric(9,3)
      ,regexp_substr(material,'\y(\d+)%',1,2,'',1)::numeric(9,3)
      ,regexp_substr(material,'\y(\d+)%',1,3,'',1)::numeric(9,3)
      ,regexp_substr(material,'% ([^,]+),?',1,1,'',1)
      ,regexp_substr(material,'% ([^,]+),?',1,2,'',1)
      ,regexp_substr(material,'% ([^,]+),?',1,3,'',1)
from import_
returning *;

CREATE TABLE

originart
mprots1
mprots2
mprots3
materjal1
materjal2
materjal3
materjal4
materjal5

TEST
50.000
49.000
1.000
Organic Cotton
Cotton
Elastane
null
null

INSERT 0 1