pg用法-地址预处理

-- 地址错误查询
select code, address  from cz_addr_info_1 cai where status ='30001'
update cz_addr_info_1 set status = null where status ='30001'

-- 查询地址重复
SELECT address  
FROM cz_addr_info_1 cai  
WHERE SUBSTRING(address FOR LENGTH(address)/2) = SUBSTRING(address FROM LENGTH(address)/2 + 1);


-- 更新地址重复
UPDATE cz_addr_info_1  
SET address = SUBSTRING(address FOR LENGTH(address)/2)  
WHERE SUBSTRING(address FOR LENGTH(address)/2) = SUBSTRING(address FROM LENGTH(address)/2 + 1);

-- 去重地址空格
select * from cz_addr_info_1 cai 
WHERE address  ~* '([^\s]+)\s+\1';
update cz_addr_info_1 
SET address  = regexp_replace(address , '\s+', '', 'g');

-- 去除地址中含有http的地址
update cz_addr_info_1 set address = NULL where  address like '%http%';

-- 查询向量相似度
SELECT 
    *
FROM 
    cz_addr_info_1 t1
WHERE     
    t1.vector notnull
    AND t1.vector <=> '[-0.01689033,0.022006862,0.011848766,0.0066328044,0.043823525,0.007602907,0.0030059656,-0.03462917,-0.023845553,-0.0007273506,0.04296969,-0.036111567,0.00013547778,0.046374563,-0.039800525,-0.011572115,0.029963521,-0.0046438146,0.0006545694,0.029120043,0.02213659,-0.0048455326,0.0017485274,0.004067191,-0.032381125,-0.0106571065,-0.021243393,-0.013074767,0.021421047,-0.06848616,-0.0052302238,0.021656673,-0.014762767,-0.02376629,0.01600802,0.016507402,-0.0019086283,-0.016490461,0.01710805,0.017015832,-0.01766601,0.0601803,0.011874036,-0.024509463,-0.021944888,-0.026009396,0.027687741,-0.07586523,-0.002169808,-0.027545324,-0.045107104,0.02727651,0.006629808,-0.0061331857,-0.032373793,-0.004104282,0.004394393,-0.03559659,0.001197056,-0.05069528,-0.036107842,0.04534259,-0.034752406,-0.045755524,-0.030453473,0.029119631,-0.03666846,-0.03139858,0.07750866,-0.04042384,0.013478069,0.0526306,-0.032874323,0.04102316,0.06639445,0.067840114,-0.027806152,-0.040430356,-0.02833902,0.048214804,0.011557126,0.041620728,-0.06302185,-0.028288657,-0.025006318,-0.07334587,0.0075878785,0.16360599,0.009458028,-0.009171505,-0.034793537,0.02977672,-0.03006293,0.01051873,0.0099222725,0.013932734,0.015550478,0.014977155,-0.022718364,0.0057778643,-0.009664982,-0.009147457,-0.078967735,0.03955615,0.0077341837,0.04334161,-0.052261125,0.018014485,0.018385103,-0.034090627,0.003952452,0.037047822,-0.022651052,0.043949407,0.03523291,0.018741045,0.035939842,0.048240352,0.023637682,-0.02019322,0.054099977,-0.018703481,0.011940293,0.013093318,-0.035556454,-0.037648898,0.028418647,-0.014793029,0.0305921,-0.03883141,0.039047245,0.04245463,0.0072577465,-0.0036623618,0.024353575,-0.067728974,0.03883288,-0.006587634,-0.004460078,-0.002660979,-0.031615168,0.022132846,0.0252181,0.05748121,0.012594486,0.03468778,0.028137757,0.015030868,-0.044583343,0.010322781,0.0016973976,0.006350314,0.0017942816,0.029159758,0.03561282,-0.046623062,-0.04577305,0.0061185868,-0.015257103,0.016563307,-0.001632294,0.014060508,-0.007186749,-0.013876945,-0.021889223,-0.015450457,-0.015584206,0.03489816,0.0500982,-0.070039056,-0.06657693,0.044823352,0.018872606,-0.0043664733,0.034682326,-0.042674713,0.008610627,0.05436466,0.0458371,0.04297147,-0.0034108534,0.04222667,-0.05405125,0.008419346,0.018142099,-0.03385108,-0.020209525,0.0011139103,-0.020478271,-0.044648856,0.0016867492,0.011619257,-0.018746031,-0.05245168,0.03277227,0.014083305,0.030510493,-0.0025079835,-0.04465965,0.03326305,0.03502711,-0.008543653,-0.03317485,-0.046435665,0.0048483894,-0.0037262365,-0.0048993006,0.006357497,-0.028425764,0.030792203,-0.002856103,-0.058538783,0.024003891,0.022658296,0.017771704,-0.01705498,0.009653136,-0.036131736,0.07245525,0.04937972,0.007769256,0.008153369,0.020890651,-0.017337365,-0.051437248,-0.0023013933,0.0029342177,0.013008652,-0.012676005,0.034956235,0.03566396,-0.010789811,-0.0033049344,-0.006907625,-0.041927002,-0.02324639,0.049153537,-0.0401011,-0.034372114,-0.055154584,-0.0021227943,-0.0036551775,-0.010552578,0.003688584,-0.020503424,-0.041374303,-0.0130954925,0.015004061,0.013278288,-0.016996702,0.013756047,-0.029121002,-0.024197556,0.007874907,-0.034264777,0.0046040765,0.037083518,0.012173465,-0.0010690987,0.0065838606,-0.0624014,0.009029022,0.0058806515,0.009400347,0.04236489,-0.0118327355,0.07753284,0.010622658,0.018770814,0.021105178,-0.011541148,-0.025556961,-0.03424614,-0.0017537785,-0.01416747,-0.005970928,0.015498747,-0.01551527,0.0018275661,0.033671983,-0.0013478018,0.038564943,0.004111546,0.0132098105,-0.029192386,0.023975315,-0.050259605,0.052551076,-0.006005598,-0.00294327,-0.015690515,-0.0051709046,-0.02061735,-0.012131884,-0.0038976937,-0.030949553,0.021294266,-0.025519436,0.023245852,-0.010797343,0.041317042,-0.018788956,-0.015408061,0.013579921,-0.039985824,0.00706872,-0.0181367,0.03144319,-0.0188059,0.027141681,-0.009090307,-0.0010559292,-0.02059351,0.009804009,-0.0050917906,-0.027007721,0.030004708,-0.014434641,0.01319469,-0.03834731,0.0123874545,0.040684633,-0.01370936,0.020044636,0.052073512,-0.0061297608,-0.010198027,-0.00411721,-0.03918202,0.017719738,-0.022308044,0.019996919,0.0027861756,-0.0039163562,0.01827093,0.004443403,0.05432931,-0.011445055,-0.02330246,0.030894507,0.01020509,0.02874066,-0.063519225,-0.00013661424,0.03250694,-0.0318238,0.055693496,-0.07312094,0.007223578,-0.01658925,0.039690483,0.039443713,0.034096353,0.03649014,-0.011204829,0.0019530797,0.023543874,0.0139525505,-0.011895744,-0.015254643,-0.005689682,-0.019379376,0.06632449,-0.0049052234,0.033716775,-0.0017796822,0.053118877,-0.019111108,-0.05861291,-0.02045191,0.01102281,-0.017250039,0.03918425,-0.018683666,-0.0025283343,-0.0050673247,0.07430236,0.019329589,-0.0024689545,0.03392516,-0.0121587245,0.0044429307,-0.014270869,0.00702049,0.023599502,0.020477405,0.012314061,-0.0014493312,-0.024146596,-0.011701171,-0.020271728,-0.010019086,-0.04984299,0.029704973,0.04319396,-0.011003531,0.015301516,-0.02679739,-0.03322004,0.07285727,0.0011820318,-0.026553832,-0.021805564,-0.03720395,0.016373591,-0.041449174,0.009460253,0.026444983,-0.0025497293,-0.019426437,0.038164623,-0.0050940844,0.00638582,0.0030165648,-0.017295329,0.039939325,0.012431884,0.017932778,-0.0001939525,0.036770575,0.024173839,-0.003715462,-0.027584363,-0.039191093,-0.069952175,0.0078021092,0.003912995,0.011633798,-0.05472462,-0.021123018,0.0057531926,-0.014121332,0.022793291,0.027912045,0.01129671,0.041288204,0.046548475,0.023800908,-0.021920491,-0.02414596,-0.037131656,0.020313982,-0.017980235,0.00051820395,0.02269614,-0.0014394085,0.023743644,0.042827096,-0.010801856,-0.023028359,0.005001413,-0.0007314174,0.046474107,-0.0048374753,0.013598564,0.009063265,0.02484535,-0.005342238,-0.017785892,-0.0054697497,0.010900898,-0.025455164,-0.025258299,0.0107211815,0.025686633,0.0017925858,-0.054698434,0.028079404,0.05201683,-0.020039484,-0.022280093,-0.0033575944,0.008823299,-0.00652738,0.004547885,-0.00015832506,-0.033966657,-0.0692137,0.059281297,-0.020325014,0.034490056,0.013589671,0.025249014,-0.009264367,0.01599788,-0.02013062,0.003260903,0.038515527,0.020812161,0.0076399827,0.029318292,0.0021725444,-0.0625444,0.0051154154,0.008436111,0.027702818,0.0041390597,0.011217349,-0.0003305985,-0.0263395,-0.013333525,-0.0077476217,0.03608784,0.0489542,-0.027577318,0.023963144,-0.020002391,-0.037252966,-0.026735144,0.041593134,0.042388115,0.0012979666,0.007805817,0.025026876,-0.02346305,0.025778491,0.021912398,-0.018585004,0.052776996,-0.021365687,-0.017544135,-0.012899915,-0.011113483,-0.008931395,0.0104335835,-0.020350983,0.008976462,0.03242885,-0.08014341,0.010835093,-0.004990555,0.02938031,0.01207683,0.05092745,0.0132073555,-0.024486238,0.005098464,-0.015373001,-0.005821741,0.024505446,-0.017784098,0.0018829615,-0.036253307,0.06698822,5.456173e-05,0.033111718,-0.016011555,-0.03585499,0.0005523839,0.025304835,0.0060723056,-0.019679189,-0.053734895,0.033330347,-0.035743672,-0.03948794,-0.039564677,0.010579681,0.009764184,-0.022094008,0.015222342,-0.04728908,0.009508901,-0.0051672673,0.01683868,-0.0031728498,0.06142933,0.05838838,-0.015950272,-0.07436846,0.075200826,-0.059005328,0.006233479,-0.0034872165,0.014074932,-0.0053268415,-0.0072125653,0.020724226,-0.0006388733,0.020877685,-0.0030711002,-0.0048855743,-0.0450417,-0.018286271,0.010027654,-0.061505668,-0.007952219,0.046833444,-0.0063337474,-0.014361198,-0.0041955006,-0.047222868,0.0069077937,-0.10399825,-0.010384259,-0.03137838,-0.051427405,-0.03592115,0.017506216,-0.038768772,-0.02962983,0.034534752,-0.06715486,0.03019686,-0.037248097,-0.0235071,0.07894974,-0.0052890163,-0.009527427,-0.014495988,-0.039734185,-0.0021677257,-0.017507482,-0.03203828,-0.019139998,-0.008456981,-0.040534638,-0.024231961,0.030934498,0.006631534,-0.073657684,0.044964533,-0.0032914276,-0.0022337832,0.0061564147,0.0007907864,-0.0054409816,0.014530919,0.005642158,-0.050597053,0.04498374,0.014887483,0.04383921,0.04196496,0.03453939,0.010368998,-0.009389505,0.05757533,-0.021542381,-0.010421094,0.011980843,-0.06488776,0.037114847,0.006683545,-0.03181035,-0.038438097,-0.0042996197,-0.018257441,0.039519563,0.016245635,-0.024424782,0.022453047,0.05682684,0.042006124,0.02625367,-0.013529313,0.06297533,0.029180592,0.016129192,0.0022805242,0.024533413,-0.024270661,0.008814077,0.0097064655,2.4975137e-05,-0.06019854,0.057314746,-0.022835132,0.048249185,0.021757457,0.018889444,-0.04103857,0.015256718,-0.01208421,-0.05683279,-0.01880134,-0.039009456,-0.018098928,-0.06604322,0.0135461055,0.032862794,-0.04723564,-0.0030781026,-0.0051421165,-0.0020644935,0.10241601,0.03463062,-0.02643411,-0.004140483,-0.018397637,0.0104925325,-0.03502181,0.011479652,-0.020497205,-0.0065987734,-0.00074169686,-0.0092927925,0.011931951,-0.04510227,0.0018319606,0.05901002,-0.031953573,-0.03193588,0.0067707645,-0.0071812645,-0.021565761,0.014960955,-0.02725783,-0.021784015,-0.0516325,-0.08702637,0.027554598,-0.040261306,0.008463519,-0.03186077,0.027499007,-0.024910884,-0.0026273855,-0.057041835,-0.008335649,0.021180088,-0.02839054,-0.063080944,0.0036362512,0.009278704,-0.0680094,0.0010849169,0.020785427,0.014143305,-0.0029694496,-0.0022735267,0.06023191,-0.053305905,0.022023764,0.0017593618,0.012255426,0.0045470325,0.051497426,-0.025419319,-0.009870835,-0.02783485,-0.0074938852,-0.0028205945,-0.009380706,-0.0017382263,0.023069764,-0.008275781,-0.017247276,-0.039770294,0.04607731,0.0060894904,0.0024347287,-0.0077941967,0.03632374,-0.010421967,-0.016531236,0.043927796,0.013018005,0.02100671,0.07552719,-0.03385515,0.025090475,-0.0083596,-0.062173635,-0.018629532,0.05035744,-0.024721442,0.010844403,-0.006257808,0.020451397,0.023577446,0.011705458,-0.04245324,0.02002473,-0.0018887906,-0.025375182,-0.007070055,-0.048271462,-0.019640319,-0.05023953,-0.023646666,0.020912023,0.00045857395,0.019590093,0.0055312477,-0.009727484,0.03787263,-0.03416573,0.023065478,-0.00798804,0.03864119,-0.008101033,-0.019624982,-0.016466701,-0.03394693,-0.024155894,0.048100688,0.020813625,-0.06614714,0.03296993,-0.07872042,0.010969689,0.02175693,-0.049506545,-0.019655231,0.009269423,-0.016091054,-0.012359859,-0.050146535,-0.00056123396,0.012745709,-0.015234109,0.021117665,-0.0007892282,0.036725994,-0.04498184,-0.032348353,-0.0030098998,-0.027111169,-0.063278675,-0.016989272,-0.043740533,-0.03772291,-0.0051296684,0.010905936,0.011292483,-0.011961463,0.015826961,-0.034679376,0.022124203,-0.017804157,-0.00766513,0.006892596,0.038674526,-0.024321651,-0.08656083,-0.039657436,-0.0713098,0.0038700125,-0.01734703,-0.003406149,0.025490442,0.0016965852,-0.0128045445,0.05297138,-0.0061184093,0.012108101,0.06657386,0.0048971134,0.025856653,-0.007476332,0.014998356,-0.0014299161,-0.013625855,0.0005766108,-0.039391696,-0.0041153855,-3.8937385e-05,-0.067038454,-0.019057035,0.016916052,-0.039700404,-0.029907854,-0.007463503,0.0013793566,0.018470459,-0.05332879,0.035997815,-0.025441587,-0.054922692,0.035388764,0.035122603,0.065347925,0.028593276,0.042255323,0.04504732,0.0350649,0.023074193,-0.009349079,0.024461329,-0.00031668387,0.012967024,-0.016786372,-0.0015757513,-0.023832843,-0.020186657,0.039842058,-0.016419256,0.046306007,0.0037272975,0.09776745,0.03901544,0.069340095,0.004510211,-0.022239774,0.033483632,-0.011395151,0.06401265,0.0061743897,0.005864293,-0.0044355937,0.035228074,-0.0020432652,-0.016012108,0.017773159,0.053921722,-0.047910582,0.04760365,-0.026627988,0.02507472,0.008070384,0.017372515,0.023873301,-0.047792118,0.020356532,-0.009887825,-0.016429178,-0.0042459485,0.033688642,-0.005495468,-0.026120313,0.009923735,-0.007289329,0.03110118,-0.0038940508,0.031547878,-0.0064709526,0.029496066,0.032661624,0.030912915,-0.044999305,-0.0037094338,-0.0152718425,0.008962612,-0.01684325,0.033799157,-0.005367254,-0.079655536,0.016094621,0.0073861913,0.045592546,-0.011411774,-0.069111735,-0.010709127,-0.021482682,-0.058381442,-0.024411429,0.037964895,0.0022137086,-0.0057428987,-0.022324683,0.008611255,-0.058748424,0.0047558052,-0.014133941,0.016787209,-0.022887157,-0.010499574,0.0058497884,0.0067521287,-0.01353274,0.04018077,0.0008848924,0.03089327,-0.0073747463,-0.020388769,0.023527445,0.035912886,-0.009882112,0.010190501,-0.05056548,-0.028984236,0.069328986,0.06907123,0.010985991,-0.04189555,-0.016359266,-0.009923242,-0.0005761628,-0.016020723,0.005629866,0.04274415,0.005255732,-0.022822866,0.017199144,0.04271213,0.020363059,0.0066328966,-0.007380457,0.0057744025,0.01688435,-0.07576448,-0.007582811,0.019595064,0.021389516,-0.05213661,0.00036652913,-0.03154172,-0.05731809,-0.009814325,0.019436717,-0.018512204,-0.02331903,0.033339724,0.01091999,0.021241657,-0.07340065,-0.00046028607,-0.05167827,0.003482004,0.026790027,-0.005030809,-0.018897029,-0.008340843,-0.029148418,0.042634077,-0.020456508,0.011653765,-0.04265074,-0.012604923,0.038489092,-0.0328003,0.0073386584,-0.034526903]'  < 0.152;