BEGIN WORK;

with shares as (

select

"cre_id"::uuid as id,

"cad" as cad,

round((split_part(share, '/', 1)::bigint)/(split_part(share, '/', 2)::bigint)::numeric, 5) as sh

from

t_dfgv98

where

share SIMILAR TO '[0-9]*/[0-9]*'

order by

"cad")

update

rtneo_contragent_real_estate cre

set

share_ = shares.sh

from

shares

where

cre.id = shares.id

COMMIT WORK;

-- not split_part(share, '/', 1) = ''

-- or not split_part(share, '/', 2) = ''

=============================================================================

CREATE TEMP TABLE t_dfgv98

(

"cre_id" varchar(50),

"cad" varchar(50),

"share" text,

"fio_data" text,

"fio_order" text,

"error" text

);

copy t_dfgv98 from '/home/rtneo/rtneo/data/creShare_set.csv' (delimiter ',', format "csv");

rollback work;

BEGIN WORK;

with shares as (

select t.cre_id::uuid as id, round((split_part(share, '/', 1)::bigint)/(split_part(share, '/', 2)::bigint)::numeric, 5) as sh from rtneo_contragent_real_estate cre join t_dfgv98 t on cre.id = t.cre_id::UUID where cre.share_ is null and (not t.share = '' and t.share is not null) and t.share SIMILAR TO '[0-9]*/[0-9]*'

)

update

rtneo_contragent_real_estate cre

set

share_ = shares.sh

from

shares

where

cre.id = shares.id;

COMMIT WORK;

//Доли для вставки

select t.cre_id::uuid as id, round((split_part(share, '/', 1)::bigint)/(split_part(share, '/', 2)::bigint)::numeric, 5) as sh from rtneo_contragent_real_estate cre join t_dfgv98 t on cre.id = t.cre_id::UUID where cre.share_ is null and (not t.share = '' and t.share is not null) and t.share SIMILAR TO '[0-9]*/[0-9]*'

//Только чистые доли

select cre.id, cre.share_, t.share, round((split_part(share, '/', 1)::bigint)/(split_part(share, '/', 2)::bigint)::numeric, 5) as total_share from rtneo_contragent_real_estate cre join t_dfgv98 t on cre.id = t.cre_id::UUID where cre.share_ is null and (not t.share = '' and t.share is not null) and t.share SIMILAR TO '[0-9]*/[0-9]*';

//Показывает все доли, вместе с грязными

select cre.id, cre.share_, t.share from rtneo_contragent_real_estate cre join t_dfgv98 t on cre.id = t.cre_id::UUID where cre.share_ is null and (not t.share = '' and t.share is not null);