Postgresql: sort by value position in array column, then by secondary order

I’m not quite sure what the best way to phrase this is…

So in my DB there is pillars text array which is basically an enum where providers ordered what values meant the most to their business, from most important to providing that value for their clients, to least important.

I’m using PostGIS to query providers in a specific area, and want to return providers ordered first by the pillar that a client selected they were looking for, then by closest location.

so if the pillars all have values ['a', 'b', 'c', 'd'], in any order depending on what providers selected, and the client selected pillar c

the results of the query would preferably return any/all providers that have pillar c at array index 0 first, ordered by distance to geopoint, then by providers that have pillar c at array index 1 second ordered by distance to client geopoint, then idx 2, then idx 3

I’m really only looking for the top 3 results in all cases, and providers with pillar c at idx 1 would only be needed if there were less than 3 results for index 0

Is this possible to pull off in a single query? or should I just run it with a where clause and check the results length until I have 3 results?

The pillars column is indexed with a gin index btw

How do I find out whenever a value changes in a certain column?

Need help if anyone can offer!

I currently have my select statement returning the TOP 1000 * results from a table. They are CC numbers that are currently active. I’d like to see how I can go about writing a query that could show me what has CC numbers have changed.

If something has changed, it will send a report to the listed people. If nothing, then it does nothing. It only actions once something has happened. We have an inhouse system that will do the automated part… I just need a select statement that will return what has changed. If I can get some help on this, it would be amazing!

Thank you!

Does the optimized column order for a PostgreSQL table always have variable length types at the end?

There’s a popular and seemingly authoritative blog post called On Rocks and Sand on how to optimize PostgreSQL tables for size to eliminate internal padding by re-ordering their column length. They explain how variable-length types incur some extra padding if they’re not at the end of the table:

This means we can chain variable length columns all day long without introducing padding except at the right boundary. Consequently, we can deduce that variable length columns introduce no bloat so long as they’re at the end of a column listing.

And at the end of the post, to summarize:

Sort the columns by their type length as defined in pg_type.

There’s a library that integrates with Ruby’s ActiveRecord to automatically re-order columns to reduce padding called pg_column_byte_packer. You can see the README in that repo cites the above blog post and in general does the same thing that the blog post describes.

However, the pg_column_byte_packer does not return results consistent with the blog post it cites. The blog post pulls from from PostgreSQL’s internal pg_type.typelen which puts variable-length columns always at the end via an alignment of -1. pg_column_byte_packer gives them an alignment of 3.

pg_column_byte_packer has an explanatory comment:

    # These types generally have an alignment of 4 (as designated by pg_type     # having a typalign value of 'i', but they're special in that small values     # have an optimized storage layout. Beyond the optimized storage layout, though,     # these small values also are not required to respect the alignment the type     # would otherwise have. Specifically, values with a size of at most 127 bytes     # aren't aligned. That 127 byte cap, however, includes an overhead byte to store     # the length, and so in reality the max is 126 bytes. Interestingly TOASTable     # values are also treated that way, but we don't have a good way of knowing which     # values those will be.     #     # See: `fill_val()` in src/backend/access/common/heaptuple.c (in the conditional     # `else if (att->attlen == -1)` branch.     #     # When no limit modifier has been applied we don't have a good heuristic for     # determining which columns are likely to be long or short, so we currently     # just slot them all after the columns we believe will always be long. 

The comment appears to be not wrong as text columns do have a pg_type.typalign of 4 but they’ve also got a pg_type.typlen of -1 which the blog post argues gets the most optimal packing when at the end of the table.

So in the case of a table that has a four byte alignment column, a text column, and a two byte alignment column, pg_column_byte_packer will put the text columns right in between the two. They’ve even got a unit test to assert that this always happens.

My question here is: what order of columns actually packs for minimal space? The comment from pg_column_byte_packer appears to be not wrong as text columns do have a pg_type.typalign of 4, but they’ve also got a pg_type.typlen of -1.

Better way of handling incorrect date format in a column with “char(10)” data type / TRY_CONVERT equivalent in PLSQL

I have a source table with below structure:

create table customer_info (customer_num    number,  birth_date      char(10)) 

Unfortunately the birth_date column data type is char(10) instead of date. Some example data for this table would be like below:

customer_num    |  birth_date         --------------------------------   1             |  2001/01/01               1             |  2010/01/01               1             |  2021/01/01                1             |  12hhhhuu6   --> Incorrect date             1             |  2001/01/01               1             |  2001/23/01  --> Incorrect date 

what I’ve done is writing a function to evaluate every single record and return it’s correct format but as you know , using a function for every single record is nod a good idea and it somehow kills performance. I was wondering if you could suggest a better way for this.

create or replace function new_to_date_en(d varchar2) return DATE is   v_date date; begin   select to_date(d,'yyyy/mm/dd' ) into v_date from dual;   return to_date(d,'yyyy/mm/dd');   exception when others then return to_dateto_date('2021/03/07', 'yyyy/mm/dd'); end;   

Using the function:

 select customer_num,         new_to_date_en(birth_date)  from customer_info; 

There is a way in T-SQL COALESCE(TRY_CONVERT(date, @date, 111), '2012-01-01'). Is there a similar way in oracle plsql?

Thanks in advance

Select all rows that have all of the following column values

I have the following tables Genres, Films, Directors. They have the following schema:


I want to write a query that will allow me to select all of Director information for every director that has made atleast one movie in the same genre(s) as another director. For example if Stanley Kubrick has made films in genres ‘Sci-Fi’, ‘Thriller’, and ‘Crime’, I want to select all the directors who have made at least 1 sci-fi AND 1 thriller AND 1 crime film.

I’ve tried the query seen below but this will give me directors who have made atleast 1 sci-fi OR 1 thriller OR 1 crime film.

SELECT DISTINCT D.DID, D.First_Name, D.Last_Name FROM Directors D LEFT JOIN Films F ON F.DID = D.DID LEFT JOIN Genres G ON G.GID = B.GID WHERE G.Genre IN (   SELECT DISTINCT G1.Genre   FROM Generes G1   LEFT JOIN Films F1   ON F1.GID = G1.GID   LEFT JOIN Directors D1   ON D1.DID = D1.DID   WHERE D1.First_Name = 'Stanley'   AND D1.Last_Name = 'Kubrick' );  Additionally, I am not able to check before hand which Genres the director in question has been involved with. The query should work with the only given information being the Directors First and Last name. 

Referencing a Column in the same table

Keeps telling me that I’m referencing SubTotal and I’m not sure how to fix that.

The error message I’m getting is "Column CHECK constraint for column ‘Total’ references another column, table ‘Job’."

Create Table Job(     JobNumber int not null         Identity (1,1)         Constraint PK_Job primary key clustered,     Date datetime not null,     Address varchar(100) not null,     City varchar(50) not null,     Province char(2) not null         Constraint CK_Province Check (Province like '[A-Z][A-Z]'),     PostalCode char(7) not null         Constraint CK_PostalCode Check (PostalCode like '[A-Z][0-9][A-Z][0-9][A-Z][0-9]'),     SubTotal money not null,     GST money not null,     Total money not null         Constraint CK_Total Check (Total>SubTotal),               /*COME BACK TO THIS */     ClientID int not null         Constraint FK_JobToClient references Client(ClientID),     StaffID int not null         Constraint FK_JobToStaff references Staff(StaffID), ) 

Do I need to create index for the column when using it in update statement?

I have a table in postgresql11 which has a userId as a column. And it is not primary key in the table. I need to perform below update statement on the table.

update entity set name='xxxx' where userId = 'yyy.

The userId is in the where condition but I don’t know whether I should create an index for userId. Will it improve performance? If yes, how can I specify index in the update statement?

Are creature environmental effects a bubble or column?

I am planning out my campaigns first BBE and am going with an aboleth.

In the rules an aboleth has a number of regional effects that reach up to a mile. This aboleth is currently in an underground pool in a mine about a mile underground. So would the characters see any evidence of the aboleth effects at ground level away from the mine, or would the effects only exist in a bubble the top of which is level with the top of the mine?

To my mind the effects will be a bubble, so widening out from the aboleth, if the approached underground they would meet them far sooner?