Best index for column where values are mostly the same – Postgres

We have an integer column that currently consists only of 0 or 1 values. This column has now been used by a developer to store a unique 32-bit identifier on some occasions, and we need to be able to efficiently pull out rows containing any one of these identifiers. Given the value will be 0 or 1 say (I don’t have figures yet) 99% of the time, how might it best be indexed to query against the minority case? Am I even right in thinking the volume of common values will be an issue?

           Column           |  Type   |     Modifiers ----------------------------+---------+--------------------  event_value                | integer | not null 

There are currently no indexes on this column. And I don’t envisage the need to regularly select just the 0 or 1 values.

The table is of a reasonable size, currently 30 million rows and growing fast.

I appreciate this isn’t the best use of the column, but that can’t change in the short term.

Datetime conversion fails when filtered index is placed on partition column

I’ve got a mysterious error of char data conversion at the moment of insertion a new row into a table. Troubleshooting reveals that a filtered index is the source of the problem.

The index’s filter uses a DateTime column, which also is a partitioning column of the table. Some clients connect with Russian by default and get the error. I’ve managed to reproduce it on a test table.

Does anyone have any ideas on why this happens?

use YOUR_DATABASE ;  go  select @@VERSION ;  -- Microsoft SQL Server 2016 (SP2-CU1) ...  -----------------------------------------------------------------------------  -- CREATE DATETIME PARTITIONED TABLE  set language english ;  drop table if exists dbo.test_of_filtered_idx ;  drop partition scheme ps_test_of_filtered_idx ;  drop partition function pf_test_of_filtered_idx ;  go    set language english ;  go  create partition function pf_test_of_filtered_idx (datetime)     as range right      for values ('1999-11-01 00:00:00.000' , '1999-12-01 00:00:00.000' , '2000-01-01 00:00:00.000' , '2000-02-01 00:00:00.000')    create partition scheme ps_test_of_filtered_idx    as partition pf_test_of_filtered_idx    ALL to ([primary])    create table dbo.test_of_filtered_idx  (    id int not null identity (1,1) ,    dt datetime not null ,    payload char(127) not null default(replicate('A' , 127)) ,    constraint PK__test_of_filtered_idx primary key clustered (id , dt)  ) on [ps_test_of_filtered_idx] (dt) ;  go  -----------------------------------------------------------------------------  -- INSERT TEST ROW AND CREATE DATETIME FILTERED INDEX  set language russian ;  go  insert into dbo.test_of_filtered_idx (dt) output inserted.* values ('15.12.1999 00:00:00.000' /* russian datetime format DMY */) ;  go  set language english ;  go  create index IXF__test_of_filtered_idx__dt    on dbo.test_of_filtered_idx    (dt) include (id)    where dt >= '1999-12-10 00:00:00.000'       and dt < '2000-01-20 00:00:00.000' ;  go  -----------------------------------------------------------------------------  -- CHECK   set language english ;  go  insert into dbo.test_of_filtered_idx (dt) output inserted.* values ('12/15/1999 00:00:00.000' /* native american datetime format MDY */) ;  go  -- GET ERROR  set language russian ;  go  insert into dbo.test_of_filtered_idx (dt) output inserted.* values ('15.12.1999 00:00:00.000' /* russian datetime format DMY */) ;  -- Преобразование типа данных varchar в тип данных datetime привело к выходу значения за пределы диапазона.  -- The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.  go  -----------------------------------------------------------------------------  -- DROP INDEX AND GET ERRORFREE INSERTION  set language english ;  go  drop index IXF__test_of_filtered_idx__dt    on dbo.test_of_filtered_idx  ;  go  set language russian ;  go  insert into dbo.test_of_filtered_idx (dt) output inserted.* values ('15.12.1999 00:00:00.000' /* russian datetime format DMY */) ;  go  ----------------------------------------------------------------------------- 

SQL Outer Join while conditioning on an extra column

I am trying to pull up an outer join between two tables, on a specifc column “Identifier”, however, I would only like to carry out the outer joins on the rows where additional column “Date” matches between the tables. Example of what I am trying to achieve is, for the two tables below:

+------------+---------------+-------------------+-----------------+ | Date       | Identifier    | TableA-Attribute1 |TableA-Attribute2| +------------+---------------+-------------------+-----------------+ | date1      |         Id1   | a                 | x               | | date1      |         Id2   | b                 | y               | | date1      |         Id3   | c                 | z               | | date2      |         Id1   | d                 | x               | | date2      |         Id2   | e                 | y               | +------------+---------------+-------------------+-----------------+   +------------+---------------+-------------------+-----------------+ | Date       | Identifier    | TableB-Attribute1 |TableB-Attribute2| +------------+---------------+-------------------+-----------------+ | date1      |         Id1   | aa                | xx              | | date1      |         Id2   | bb                | yy              | | date1      |         Id3   | cc                | zz              | | date1      |         Id4   | dd                | xx              | | date2      |         Id1   | ee                | yy              | +------------+---------------+-------------------+-----------------+ 

I need to end up with something that looks like this:

+------------+---------------+---------+-------+--------+-------+ | Date       | Identifier    | A-Attr1 |A-Attr2|B-Attr1 |B-Attr2| +------------+---------------+---------+-------+--------+-------+ | date1      |         Id1   | a       | x     |aa      |xx     | | date1      |         Id2   | b       | y     |bb      |yy     | | date1      |         Id3   | c       | z     |cc      |zz     | | date1      |         Id4   | -       | -     |dd      |xx     | | date2      |         Id1   | d       | x     |ee      |yy     | | date2      |         Id2   | e       | y     |-       |-      | +------------+---------------+---------+-------+--------+-------+ 

I was trying to do something like this

go select * from table1 full outer join (select * from table2 where table1.date=table2.date)

however, that unfortunately does not seem to be working.

How to do group by based on another column in SELECT?

I have a table on which I do a SELECT statement. It is joined to some other tables. With the SELECT one column is got but a sub query. like (select top 1 .. from..) for each row. Now I need to do an aggregation based on that column, so I have to group by id(which I also select) the amounts that I’ve got in that column got by the subquery. Is it possible and how?

Different column data in same row

select Distinct REGEXP_SUBSTR (Order_NO,'[^,:]+’, 1, 2) AS Order_NO1, –REGEXP_SUBSTR (Task_Name,'[^,:]+’, 1, 2) AS Task_Name, REGEXP_SUBSTR (Item_No,'[^,:]+’, 1, 2) AS Item_No, REGEXP_SUBSTR (Style_No,'[^,:]+’, 1, 2) AS Style_No, TASK1,TASK2,TASK3 from (select REGEXP_SUBSTR (task_details, ‘[^,]+’, 1, 1) AS Order_NO ,REGEXP_SUBSTR (task_details, ‘[^,]+’, 1, 2) AS Task_Name , REGEXP_SUBSTR (task_details, ‘[^,]+’, 1, 3) AS Item_No ,REGEXP_SUBSTR (task_details, ‘[^,]+’, 1, 4) AS Style_No ,initiated_date,due_date,update_time, case when task_name=’TASK1′ then update_details else null end as TASK1, case when task_name=’TASK2′ then update_details else null end as TASK2, case when task_name=’TASK3′ then update_details else null end as TASK3 from (select distinct a.task_id, b.task_category, b.task_name, b.initiated_date, b.due_date, b.task_details,a.update_time, a.update_details, a.username as initiator_or_updater, c.user_id AS assigned_user FROM Dashboard_Task_Completed b, dashboard_task_history A, dashboard_task_user c WHERE (a.update_time, A.task_id) in ( select MAX(UPDATE_TIME),task_id from dashboard_task_history dh where dh.task_id=a.task_id group by task_id ) and a.task_id=b.task_id and a.task_id=c.task_id and c.isescalation=’N’ and c.isreview=’N’ and b.task_category in (select Distinct(Task_Category) from Taskconfig where task_category=’TANDA’) ORDER BY a.task_id,a.update_time asc) order by task_id,update_time);

Current Output: enter image description here

Need Output Like below: enter image description here

How to “group” based on consecutive occurrences of an element in a column in postgres

I have a table that, when ordered, describes a path by point descriptions in each row. A column tells whether a row is a special (stop) point within that path and is empty otherwise. The path starts and ends with such a row and has many in between. What I want to do is create line segments from the point rows that start at such a row and also end at such a row.

point  stop   1     yes   2         3     yes   4   5   6     yes 

I want to call ST_Makeline from PostGIS to turn the ordered points into these line segments. How can I enforce that ST_Makeline, being a “summary” function is only called for the inclusive point groups 1 to 3 and 3 to 6?

User or group column is populated with incorrect values when adding data from CSOM

I am working on a console application that updates list items using managed CSOM. I have a user or group column in this list that can contain multiple values. Now, I am using below code to update the value of this column:

// Get the user   User newUser = web.EnsureUser("LoginName");   context.Load(newUser);   context.ExecuteQuery();   FieldUserValue userValue = new FieldUserValue();   userValue.LookupId = newUser.Id;  // Update the list item   listItem["User"] = userValue; 

The issue is if I assign the value of user1 to this column, the value that I see after the column is updated is another user; like user2. I don’t know why it is populating this code with some other user?

Any idea on this issue?

How to have a value different from 0 in netstat’s Send-Q column for a socket in the LISTEN state?

I am currently trying to solve a Capture The Flag challenge that involves trying to escalate privileges by taking advantage of an exploit in a bash script.

The script first does the following to get all sockets with TCP protocol in the LISTEN state:

output=$  ($  _netstat -ntpl 2> /dev/null | $  _egrep '^t') 

and then it parses the output line by line. One of the things it does for each line is this:

if [[ "$  cur_syn" == "0" ||  "$  max_syn" != "$  cur_syn" ]]     then    continue fi 

$ cur_syn is the value of the Recv-Q column as returned by netstat, and $ max_syn is the value of the Send-Q column.

So, only a socket that is in the LISTEN state and with Recv-Q != 0 and Recv-Q==Send-Q will pass these checks.

netstat‘s man states that:

Recv-Q Established: The count of bytes not copied by the user program connected to this socket. Listening: Since Kernel 2.6.18 this column contains the current syn backlog.

and

Send-Q Established: The count of bytes not acknowledged by the remote host. Listening: Since Kernel 2.6.18 this column contains the maximum size of the syn backlog.

The thing is, I seem not to be able to create a socket that has a Send-Q different from 0.

If my interpretation is correct, the Send-Q value for a socket that is listening is the max size of the backlog, which is the backlog param in C’s listen(2) function. But even when I create a listening server socket with a backlog of size 3, netstat still reports the Send-Q as being 0! What am I doing wrong?

FYI, I have managed to make the Recv-Q change by having multiple clients connect to a server socket that has received a SIGSTOP. Recv-Q goes up all the way to maximum size of the syn backlog + 1, and then all connections are refused. But alas,Send-Q remains unchanged.

Rank of certain ‘Kronecker Column Hadamard Row Sum’ of matrices

Define Kronecker Column Hadamard Row Sum of two matrices $ M_1$ and $ M_2$ of size $ n_1\times m$ and $ n_2\times m$ respectively to be the $ n_1n_2\times m$ matrix whose $ ((i-1)n_1+(j-1))$ th row is sum of $ i$ th row of $ M_1$ and $ j$ th row of $ M_2$ .

Take $ n$ different matrices $ M_1,\dots,M_n$ with ranks $ r_1$ through $ r_n$ respectively and sizes $ m_1\times m$ through $ m_n\times m$ respectively and with $ \prod_{i=1}^n m_i<m$ .

  1. How large can the rank of Kronecker Column Hadamard Row Sum of $ M_1,\dots,M_n$ be?

  2. If each of $ M_1,\dots,M_n$ is picked uniformly from matrices with ranks $ r_1$ through $ r_n$ respectively and sizes $ m_1\times m$ through $ m_n\times m$ respectively then what is the expected rank of Kronecker Row Direct Sum of $ M_1,\dots,M_n$ and would an inequality like Chebyshev’s expected to hold?