PostgreSQL – Splitting Metadata Efficiently

postgresql

I have metadata in a postgresql table that I need to split. So far I have been attempting to use

Select col_name from "tbl_name" split_part('QGC|WGPLNW|IR|BBC|FCPC|WGPLNW|ARME|QPC', ", 1 | 2 | 3 | etc...);

Out of a table formatted like so:

QGC 10''
QGC 10''
QGC 10''
QGC 10''
QGC 12''
QGC 12''
QGC 12''
QGC 12''
QGC 12''
QGC 10''

I need only to extract the integers and would like to preserve the acronyms if possible. I have attempted to use the split substring with regex function but haven't found much success, although this is primarily due to my lack of understanding of how to pass substrings inside of queries when dealing with a table. If someone could explain the appropriate syntax in order to perform this task they would help me greatly. Thanks in advance.

Thanks Martin, You are right – I placed this in the wrong section, thanks for your input. Also, you are correct in assuming that the quotes are holdovers from the database and I have corrected them to more accurately represent the data. How would I go about having the topic moved as not to darken the door of gis.stackexchange?

Best Answer

After looking at split_part() from PostgreSQL String Functions, and assuming that column is the name of the "compound attribute" that is of the form "acronym codenum", then I'd say you should try something like this

SELECT 
  split_part (column, ' ', 1) AS acronym,
  split_part (column, ' ', 2) AS codenum
FROM table

Related Solutions

PostgreSQL – Date added row metadata

The only information you have about 'when' a row entered the table is the ID of the inserting transaction. The sad news is that this is in no way connected to any real timestamp.

So you either add the new column (where you can't figure out when the existing rows were inserted) - or probably you can create triggers on all tables which populate a transaction ID-timestamp table.

The number of rows approach can work only if you keep track of not only the inserts but deletes (and truncates) as well.

Alternatively, if this task is not needed too often, you can use PostgreSQL's point-in-time recovery mechanism to set up a database which mirrors the state of a given time in the past, then compare the data in the two versions.

PostgreSQL – Filtering on Array Length Inside JSON

Without knowing your data, I can only guess that the selectivity of your index is low (which happen if the length of the array does not vary much).

One trick to overcome this might be changing the query slightly and creating a covering index. For this, choose a NOT NULL column (for example, the primary key of the table) to count, and then include this column in the index:

CREATE INDEX n_items ON your_table (jsonb_array_length(data->'items'), id);

SELECT count(id) 
  FROM your_table
 WHERE JSONB_ARRAY_LENGTH(table.data->'items') = 2;

This will hopefully turn into an index-only scan (I tested this omitting the jsonb part, but you will be able to tell if it works).

Related Question