I have a SQL table, and one column of the table has type text[]. I want to create write a query that will create a new table, which consists of all arrays flattened and concatenated. Ex: If there are 3 items in the table, and the array entry for each of those items is [1, 2, 3], NULL, [1, 4,
Tag: postgresql
Performing division with PostgreSQL / json
I have written a simple query against a table in a Postgres database that contains a column “manifest” of type json. Each cell contains a very long value and I am extracting the numerical value for “size”. I need to create a new column (perhaps call it “size in MB”), and perform division against size. Specifically, I need to take
PostgreSQL – I want to query out the latest Subscription status for every client with the date
The client can opt-in or opt-out for subscription from time to time. I need the latest Status of every client with the subscription date. If the client has opted-in at the first instance and never opted-out again, I need the first date when they opted-in. However, if the same client has opted-out after opting-in and then opted-in again, I need
find by name the id of a multi level categorie
my main problem is how can i search by name the id of a given category, let me explain , so ,in my database i have a table called product_category where is stored a 5 level categories. let’s take an example of it: id name parent_id 1 A 2 B 1 3 C 2 4 D 5 A 4 6
Hide duplicate column cells in view
First of all, this here doesn’t solve my problem at all. I tried this too. I want generate random unique fake data (first name and address) I used the following SQL request: The result apparently looked like this (the addresses repeat themselves for the next name too): How do I make the first names AND addresses unique? Please note this
PostgreSQL: Trigger INSERT INTO SELECT from other table
I’m trying to create a trigger that will add a new row processed entry to a destination table each time a new row is created in the source table. Step 1 Create destination table: Step 2 Create trigger function: ** The Select query inside function work normally when single running. Step 3 Create trigger: The problem is that Trigger does
How to make postgres not use a particular index?
I have the following query: As you can see, the cost of the last index scan on chaindata_tokentransfer_chain_id_block_number_tx_eeeac2a4_idx is very high. And the query is timing out. If I remove the filter on chain_id and block_number from the query, then the query is executing in a reasonable amount of time. Since this new less constrained query is working, I’d expect
Difference between CREATE CAST and CREATE CONVERSION
In postgres, it seems there are two customizable ways to convert one value type into another via SQL, CREATE CONVERSION and CREATE CAST. What is the difference between these two items, for example what might be an example of where one might be used over another? Answer A cast has nothing to do with a conversion. A cast converts data
Postgresql Get Maximum value per day with corresponding time
I have the following table: I want to get maximum value along with Date,ReceivedTime. Expected Result: Answer This answer assumes that, in the event of two or more records being tied on a given day for the same highest value, you want to retain the single record with the most recent ReceivedTime. We can use DISTINCT ON here:
Summation of 2 floating point values gives incorrect result with higher precision
The sum of 2 floating values in postgres gives the result with higher precision. Expected: Actual: The result is having higher precision than expected. The same operation with different set of inputs behaves differently. Here I have reduced the problem statement by finding the 2 numbers for which the issue exists. The actual problem is when I do this on