postgresql – rename a column without disrupting users

You can rename a column using the following statement:

ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name;

However, this brings the old column out of commission immediately. Any use sites that used the old column name will start having problems.

Is there a way to put the table into a transitory state in which both the old and new name can be used to refer to the column, so that I can move over my clients and then afterwards decommission the old name?

Pyspark Array Column – Replace Empty Elements with Default Value

I have a dataframe with a column which is an array of strings. Some of the elements of the array may be missing like so:

-------------|-------------------------------
ID           |array_list                      
---------------------------------------------
38292786     |(AAA,, JLT)                    |
38292787     |(DFG)                          |
38292788     |(SHJ, QKJ, AAA, YTR, CBM)      |
38292789     |(DUY, ANK, QJK, POI, CNM, ADD) |
38292790     |()                             |
38292791     |()                             |
38292792     |(,,, HKJ)                      |

I would like to replace the missing elements with a default value of “ZZZ”. Is there a way to do that? I tried the following code, which is using a transform function and a regular expression:

import pyspark.sql.functions as F
from pyspark.sql.dataframe import DataFrame

def transform(self, f):
    return f(self)

DataFrame.transform = transform  
  
df = df.withColumn("array_list2", F.expr("transform(array_list, x -> regexp_replace(x, '', 'ZZZ'))")) 

This doesn’t give an error but it is producing nonsense. I’m thinking I just don’t know the correct way to identify the missing elements of the array – can anyone help me out?

In production our data has around 10 million rows and I am trying to avoid using explode or a UDF (not sure if it’s possible to avoid using both though, just need the code run as efficiently as possible). I’m using Spark 2.4.4

This is what I would like my output to look like:

-------------|-------------------------------|-------------------------------
ID           |array_list                     | array_list2
---------------------------------------------|-------------------------------
38292786     |(AAA,, JLT)                    |(AAA, ZZZ, JLT)                     
38292787     |(DFG)                          |(DFG)                          
38292788     |(SHJ, QKJ, AAA, YTR, CBM)      |(SHJ, QKJ, AAA, YTR, CBM)      
38292789     |(DUY, ANK, QJK, POI, CNM, ADD) |(DUY, ANK, QJK, POI, CNM, ADD) 
38292790     |()                             |(ZZZ)                             
38292791     |()                             |(ZZZ)   
38292792     |(,,, HKJ)                      |(ZZZ, ZZZ, ZZZ, HKJ)           

cloud – Substring title column in Confluence’s page properties report macro

Is there a way to display a substring of the title column’s value in a page properties report?

I’m working with Confluence Cloud to create a documentation space for our apps. The space makes heavy use of a pattern where child pages are created using templates with a Label and Page Properties Macro, and the parent page uses the Page Properties Report Macro to list those child pages & their properties in a grid view.

i.e. I have 2 templates, with the below structure:

  • Template: Application
    • Label: PageType-Application
    • Button: Create from Template
      • Text: Add How To
      • Template Name: How To
    • Macro: Page Properties Report
      • Label: PageType-HowTo
      • Space(s): Current space
      • With Ancestor: Current page
  • Template: How To
    • Label: PageType-HowTo
    • Macro: Page Properties

From this, I have a site structure that looks like this:

  • Applications
    • Confluence
      • Confluence: How To: Change Password
      • Confluence: How To: Log in
    • StackExchange
      • StackExchange: How To: Change Password
      • StackExchange: How To: Log in

Were someone to go to my StackExchange app’s page, I’d like them to see this:

Stack Exchange

How Tos

(Add How To) | Title | Summary | | Change Password | Explains how a user can amend their own password | | Login | Describes how to access the system from a fresh browser session |

However, because each page in a space needs to have a unique name, and both apps would have a Change Password page, I need to include the app’s name to keep things unique; so the titles become Confluence: Change Password and StackExchange: Change Password. This additional text however is unhelpful when the user’s already on the application’s page, so knows the app from the context.

I accept that I may have an XY Problem here… My requirement is to create templates to reduce manual effort / decision making for the teams creating our documentation, by having each template contain buttons to create and link to the relevant child pages, whilst ensuring that the resulting documentation is easy to read; e.g. by not having lots of duplicate information displayed in each link.

i.e. If there’s a way to allow pages in a space to have the same name so long as they live in different parts of the tree structure, or a way to have the page properties report macro show a different column instead of the title column (though still linked to the child page), that would be an equally valid solution.

sharepoint online – AutoNumber Column Task ID restart every year

I am trying to create a column with increment number when adding a new task, but restart every year, for instance in year of 2017, i would like to Task ID looks like this: 2017-001, 2017-002, 2017-003 and so on.

Also it will restart back to 001 when the date is 2018, for instance in year of 2018, Task ID will look like this: 2018-001, 2018-002, 2018-003

How would i do this? Please help!

multi select – List filter with multiple selection for ID column

In SharePoint 2016 I have a list that uses the ID column in the list view.

For all the other columns I can filter by using the checkboxes for multiple choices, but with the ID column I can not.
Does anyone have a workaround?

Please see the behavior of the ID column and another column.

ID Column doesn't show the check boxes

All other columns do show the check boxes for multiple filtering choice

Thanks,
Effy L.

sharepoint online – How to format calculated column number in Flow

Use below formula:

formatNumber(int(outputs('<Get SharePoint Data Action Name>')?('body/<calculated column name>')), '0,0.00', 'en-us')

This will convert your calculated column to #,#.00 format (with 2 decimal points).

If you want the number without decimal points then you can use below formula:

formatNumber(int(outputs('<Get SharePoint Data Action Name>')?('body/<calculated column name>')), '0,0', 'en-us')

Replace Get SharePoint Data Action Name and calculated column name with your action name and calculate column name respectively.

You can convert the string to number in Microsoft flow using int function.

And You can format the number to desired format in Microsoft flow using formatNumber function.

Flow:

enter image description here

Output:

enter image description here

mysql – Bring data from two columns under same column and two rows if both of them contains non null value in Spark SQL

I have a table emp and I have two bring data from two columns(“Office add”, “Home add”) under the column “address” if both the columns “off_Add” and “home_add” contains some values but in different rows.
If any of the two columns contains null then non null address should come under address tab.

Emp
id, name, office_Add, home_Address
1 avnish 234 567
2 Neha 567 null
3 Pintu null 8766
4 Priya 456 456

emp1
Output should be

id, name, address
1 avnish 234
1 avnish 567
2 Neha 567
3 Pintu 8766
4 Priya 456
5 Priya 456

Google sheets array formula for taking average every Sunday and output is in same row corresponding column

I would like to take the average using an array of every 7 days on a Sunday and place the output on the same row but corresponding column of that Sunday.

If a cell in B is left blank then it should not be considered for an average. Meaning if on all numbers were filled for 6 days and the 7th was blank then the average should be the sum of the 6 days divided by 6 days.

Without an arrayformula() i was using:

=if(weekday(A8, 1)=1, average(B2:B8),””)

Any help would greatly be appreciated!

For example A8 is a Sunday so C8 should show the average of the range B2:B8

enter image description here

google sheets – Duplicate row values to next row, based off specific column

I’m trying to set up a button that will copy information from the last row and paste it into the row right underneath (based on where column D last has a value). Almost like a “duplicate” button, because I am inputting information that will repeat.

I had a script that was doing the copy perfectly. However, I realized an issue — because I have a unique ID pre-loaded to fill up all rows in the first column (causing the added row to come after this), I wanted this script to seek the last row based on the FOURTH column, not the FIRST.

On a similar pattern, I am looking to ONLY copy the information from column 4, 5, 6, and 7, into the row underneath — not any other info.

My idea was that I could basically combine these two concepts — copying and pasting the last row into the row underneath, and using GetColumnHeight so that I can control which columns should be selected.

function addRow() {
  var sh = ss.getActiveSheet(), lRow = sh.getLastRow();
  var lCol = sh.getLastColumn(), range = sh.getRange(lRow,1,4,lCol);
  sh.insertRowsAfter(lRow, 1);
  range.copyTo(sh.getRange(lRow+1, 1, 1, lCol), {contentsOnly:false});

  var vs=sh.getRange(e.range.rowStart,4,1,3).getValues()
  tsh.getRange(getColumnHeight(3,tsh,e.source)+1,4,1,3).setValues(vs);
    }
  }
function getColumnHeight(col,sh,ss){
  var ss=ss||SpreadsheetApp.getActive();
  var sh=sh||ss.getActiveSheet();
  var col=col||sh.getActiveCell().getColumn();
  var v=sh.getRange(1,col,sh.getLastRow(),1).getValues().map(function(r){return r(0);});
  var s=0;
  var h=0;
  v.forEach(function(e,i){if(e==''){s++;}else{s=0;}h++;});
  return (h-s);
}

I’m really all over the place right now, and struggling to piece something together that will copy and paste the values in column 4,5,6, and 7 into the row below, ignoring the filled values in column A. Any suggestions?

Here is a visual to further explain my goal:

https://docs.google.com/spreadsheets/d/1aUQJr8mdizNR9LHavHHt_K34Yf0ZVI4nl38FD9o-LHo/edit?usp=sharing

magento2 – How to add new column to order

I want to know how can I add new column and make use of it in order details view in order view / my custom tab. I’m running magento 2.4.1 version. Lets call this field my_custom_field.

I have tried adding db_schema.xml file with following content:

<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
<table name="sales_order" resource="sales" engine="innodb" comment="Sales Order">
    <column xsi:type="varchar" length="255" name="my_custom_field" nullable="true" comment="parcel number"/>

</table>

Then I upgraded setup.
Then I tried to set the value of that field for my order in my CustomTab -> execute method:

$order = $this->orderRepository->get($this->_coreRegistry->registry('current_order')->getId());
$order->setCustomAttribute('my_custom_field', 'test');
$this->orderRepository->save($order);

var_dump($order->getCustomAttribute('my_custom_field'));exit; # returns null

Could anyone point what am I doing wrong?