Postgres- update a value in a array on particular index

I have a column named ids that is of type bigint[]. ids cane be null or of any length

It has values based on certain hierarchy.

My use case is I want to update value that is at a particular index. For example ids[2] = 50.

How can I achieve this?

I looked at this https://www.postgresql.org/docs/current/functions-array.html#ARRAY-FUNCTIONS-TABLE but couldn’t find anything that could help.

Thanks in advance.

IndexError: list index out of range

I am pretty new in Python (or more precisely in programming).

I found on the Internet (link) this code:

import numpy as np import scipy.integrate as sp import sys g, l, k = 9.81, 10, float( sys.argv[1] ) PTS = 3000 def rhs( y, t, g, l, k ): return [ k*t/l * np.cos(y[1]) - g/l * np.sin(y[1]), y[0] ] y0 = [ 0, 0 ] t = np.linspace( 0, 30, PTS+1 ) y = sp.odeint( rhs , y0, t, args = (g, l, k) ) for i in range(PTS): print( t[i], y[i][1] * 180/np.pi, np.arctan(k*t[i]/g) * 180/np.pi ) 

I understood that I need to input a value of k to this code, so I got this code:

import numpy as np import scipy.integrate as sp import sys g, l, k = 9.81, 10, 0.4, float( sys.argv[1] ) PTS = 3000 def rhs( y, t, g, l, k ): return [ k*t/l * np.cos(y[1]) - g/l * np.sin(y[1]), y[0] ] y0 = [ 0, 0 ] t = np.linspace( 0, 30, PTS+1 ) y = sp.odeint( rhs , y0, t, args = (g, l, k) ) for i in range(PTS): print( t[i], y[i][1] * 180/np.pi, np.arctan(k*t[i]/g) * 180/np.pi ) 

But I got an error: IndexError: list index out of range.

I found on the Internet that this error means that I am providing an index for which a list element does not exist, but I do not know, how to fix it. Could somebody explain to me where is the problem and alternatively how to fix it, please?

Will Postgres use a multi-column index when setting multiple colums not null?

Typically, when I set a column not null, if it doesn’t have an index then I add it first, so that postgres can (hopefully) use the index while doing the table scan while locking the table, so that the table is locked for a shorter amount of time.

I want to set several columns not null, like so:

alter table foos   alter column bar1 set not null   alter column bar2 set not null   alter column bar3 set not null   alter column bar4 set not null; 

If I make a multicolumn index for these columns, will postgres use it when scanning the locked table before making this alteration?

CREATE INDEX CONCURRENTLY my_index on foos (bar1, bar2, bar3, bar4); 

What if I made a partial index on IS NULL (or, IS NOT NULL)

CREATE INDEX CONCURRENTLY my_index on foos (bar1, bar2, bar3, bar4) where bar1 is null and bar2 is null and bar3 is null and bar4 is null; 

Primary index vs clustered vs non clustered vs secondary index

I am new to ETL testing and dB environment.Some say PI is clustered index some say it is not. Even I found some where that secondary index is non clustered index. What is actually primary index and secondary index? When do we need primary index and when we need secondary index? If PI index and clustered index are different then how are they different?

error (Undefined index) and (Cannot modify header information – headers already)

Error:

Notice: Undefined index: link_type in /home/golchind/public_html/wp-content/themes/golchindl/functions.php on line 40

Notice: Trying to get property of non-object in /home/golchind/public_html/wp-content/themes/golchindl/functions.php on line 58

Warning: Cannot modify header information – headers already sent by (output started at /home/golchind/public_html/wp-content/themes/golchindl/functions.php:40) in /home/golchind/public_html/wp-admin/admin-header.php on line 9

help :

line 40

$ type = $ _POST[‘link_type’];

line 58

update_post_meta($ post->ID,”_oscar_data”,json_encode(array(“episodes”=>$ episodes,”links”=>$ links,”type”=>$ type), JSON_UNESCAPED_UNICODE));

function oscar_meta_box_js(){     wp_enqueue_style( 'oscar_box_style',get_bloginfo('template_url')."/css/admin_meta_box.css");     wp_enqueue_script( 'oscar_box', get_bloginfo('template_url'). '/js/meta_boxes.js', array('jquery'),'1.0',true); } add_action( 'admin_enqueue_scripts', 'oscar_meta_box_js');   function oscar_meta_save(){     global $  post,$  meta_args,$  meta_dls;     $  type = $  _POST['link_type'];     $  episodes = array();     $  links = array();     if($  type == "serial"){         for($  i=0;$  i<count($  _POST['ep_name']);$  i++){             $  episodes[] = array("name"=>$  _POST['ep_name'][$  i],"quality"=>$  _POST['ep_quality'][$  i],"medium_size"=>$  _POST['ep_med'][$  i]);         }         for($  i=0;$  i<count($  _POST['link']);$  i++){             $  links[] = array("name"=>$  _POST['name'][$  i],"link"=>$  _POST['link'][$  i],"subtitle"=>$  _POST['subtitle'][$  i],"screenshot"=>$  _POST['screenshot'][$  i],"episode"=>$  _POST['episode'][$  i]);         }     } else if($  type == "movie") {         for($  i=0;$  i<count($  _POST['movie_title']);$  i++){             $  episodes[] = array("name"=>$  _POST['movie_title'][$  i]);         }         for($  i=0;$  i<count($  _POST['mlink']);$  i++){             $  links[] = array("name"=>$  _POST['mname'][$  i],"link"=>$  _POST['mlink'][$  i],"subtitle"=>$  _POST['msubtitle'][$  i],"screenshot"=>$  _POST['mscreenshot'][$  i],"episode"=>$  _POST['mtitle'][$  i]);         }     }     update_post_meta($  post->ID,"_oscar_data",json_encode(array("episodes"=>$  episodes,"links"=>$  links,"type"=>$  type), JSON_UNESCAPED_UNICODE)); }        function oscar_download(){     global $  post;     ?> <script> var $   = jQuery; $  (function(){     var data = '<?=get_post_meta($  post->ID,"_oscar_data",true);?>';     if(data != ""){         data = JSON.parse(data);         var type = data['type'];         data['episodes'].forEach(function(e){             if(type == "serial")                 add_ep(e['name'],e['quality'],e['medium_size'])             else                 add_title(e['name']);         });         data['links'].forEach(function(e){             if(type == "serial")                 add_link(e['name'],e['link'],e['subtitle'],e['screenshot'],e['episode'])             else                 add_mlink(e['name'],e['link'],e['subtitle'],e['screenshot'],e['episode'])         });         if(data['type']){           $  ("#link_type").val(data['type']);     }     }     normalize_links()     normalize_mlinks() }); </script> <?php if ('series' == get_post_type()) { ?> <div id="serial" class="content">     <div class="side_inp_keeper">         <input id="title_serial" type="text" placeholder="عنوان فصل">         <input id="quality_serial" type="text" placeholder="کیفیت">         <input id="med_serial" type="text" placeholder="میانگین حجم هم قسمت">         <button id="add_ep" class="button">افزودن</button>     </div>     <div id="episodes">      </div>     <hr>     <div class="links" style="display:none;">             <div class="side_inp_keeper">                 <select id="ep"></select>                 <input type="text" id="name" placeholder="عنوان لینک">                 <input type="text" id="link" placeholder="لینک دانلود">                 <input type="text" id="subtitle" placeholder="زیر نویس">                 <input type="text" id="screenshot" placeholder="نمونه کیفیت">                 <button id="add_link" class="button">افزودن</button>             </div>             <div id="links">              </div>         </div> </div> <?php } else if ('movies' == get_post_type()) { ?> <div id="movie" class="content">     <div class="side_inp_keeper">         <input type="text" name="movie_title" id="movie_title" placeholder="عنوان (مثال : زبان اصلی)"><button id="add_link_title" class="button">افزودن</button>     </div>     <div id="titles">      </div>     <hr>     <div class="mlinks">         <div class="side_inp_keeper">                     <select id="titl"></select>                     <input type="text" id="mname" placeholder="عنوان لینک">                     <input type="text" id="mlink" placeholder="لینک دانلود">                     <input type="text" id="msubtitle" placeholder="زیر نویس">                     <input type="text" id="mscreenshot" placeholder="نمونه کیفیت">                     <button id="add_mlink" class="button">افزودن</button>         </div>         <div id="mlinks">          </div>     </div> </div> <?php } ?> <input type="hidden" name="link_type" id="link_type" value="serial">     <?php }    function oscar_meta_boxes() {     add_meta_box( 'oscar_download',"باکس دانلود", 'oscar_download', array('movies', 'series'), 'normal', 'high' ); } add_action("save_post","oscar_meta_save"); add_action( 'add_meta_boxes', 'oscar_meta_boxes' ); 

Web with two languages. The urls with the /es/ do not index

Good morning,

I have launched a website with English and Spanish languages. I’ve done it using WPML, but I’m having a lot of problems indexing and ranking the Spanish URLs, which appear in the URL the /es/.

Google Search Console is declaring all URLS like domain.com/en/whatever as “Duplicate, submitted URL not selected as canonical”

I really need these pages, despite being translations of the English version, to also index and rank keywords.

Any solutions? Thank you!

Does interrupting ALTER INDEX COALESCE CLEANUP loose the work so far?

In Oracle 12c (12.1) we have a huge partitioned table with interval partitioning. Beside local indexes, there are two global indexes on it. Dropping old partitions with UPDATE INDEXES clause helps the global indexes keeping valid.

Next night, the feature called Asynchronous Global Index Maintenance background job has started an ALTER INDEX xxx COALESCE CLEANUP command, which runs since several days and cleans up the global indexes from orphan entries of dropped partitions.

Unfortunately, we need to recreate the affected index with different parameters, but it’s not possible due to resource busy error because of a shared DML lock (Row-X (SX)) caused by the background job. Since we need to deploy a new release, we need definitely the DDL on the index.

Question: If we kill the session, will the work of past 2 days be lost and job need to restart again from beginning?

Create index for WHERE COALESCE() condition

I’m using PostgreSQL V9.6.11

Table DDL:

CREATE TABLE test_c (            insrt_prcs_id bigint NOT NULL,            updt_prcs_id bigint, src_sys_id integer NOT NULL,           load_dttm timestamp(6) with time zone NOT NULL,            updt_dttm timestamp(6) without time zone);  

I was trying to create index for the below query:

SELECT *  FROM test_c              WHERE COALESCE(u_dttm,l_dttm) > '2020-04-10 15:29:44.596311-07' AND   COALESCE(u_dttm,l_dttm) <= '2020-04-11 15:29:44.596311-07'  

Create index as:

create index idx_test_c  on test_c(COALESCE((updt_dttm, load_dttm))) 

But query planner is not scanning index:

EXPLAIN ANALYZE SELECT *             FROM test_c              WHERE COALESCE(u_dttm,l_dttm) > '2020-04-10 15:29:44.596311-07' AND   COALESCE(u_dttm,l_dttm) <= '2020-04-11 15:29:44.596311-07'  
Seq Scan on test_c as test_c (cost=0..1857.08 rows=207 width=496) (actual=5.203..5.203 rows=0 loops=1) Filter: ((COALESCE((test_c.updt_dttm)::timestamp with time zone, test_c.load_dttm) > '2020-04-10 15:29:44.596311-07'::timestamp with time zone) AND (COALESCE((test_c.updt_dttm)::timestamp with time zone, test_c.load_dttm) <= '2020-04-11 15:29:44.596311-07'::timestamp with time zone)) Rows Removed by Filter: 41304 

Why index scan is not happening?

Querying a table with 200+ million rows by filtering on the clustered index AND additional column is super slow

We have one table that has clustered index on two columns: first one is NVARCHAR(50) and second one is on DATETIME2(3) type.

There are few other columns. When we query the table with WHERE clause on the clustered index columns, the result is received immediately, even when the result set is tens of thousands of rows.

However, if we add one more predicate on the where clause, the query is super slow. After ten seconds I just stop it, because that is not useful to us.

The table has 200+ million rows.

This is the query that is super fast:

SELECT * FROM Table WHERE Col1 = 'blah' AND Col2 BETWEEN 'date1' AND 'date2' 

This is the query that is super slow:

SELECT * FROM Table WHERE Col1 = 'blah' AND Col2 BETWEEN 'date1' AND 'date2' AND BooleanColumn = 1 

In my mind, the second query should use the clustered index to search for rows and then simply scan the result set to filter out what’s needed.

Is it possible to (somehow) make the second query work fast without creating additional non clustered index that would include other columns that we need to filter on?

PostgreSQL: difference between index on materialized view and index on tables used in a non-materialized view

I have tables a(id TEXT, url TEXT) and b(id TEXT, url TEXT) containing same or similar set of columns.

Would the query plans created for simple SELECT * FROM m WHERE id = ? be identical (or at least within same order of magnitude) for the following two views:

  1. Materialized view combining columns from both tables with an index on the shared id field
CREATE MATERIALIZED VIEW m AS (   SELECT id, url FROM a   UNION   SELECT id, url FROM b ); CREATE INDEX idx_m_id ON m(id);  
  1. View combining the tables, with each table having its own index on the column
CREATE INDEX idx_a_id ON a(id);  CREATE INDEX idx_b_id ON b(id);  CREATE VIEW m AS (   SELECT id, url FROM a   UNION   SELECT id, url FROM b );