Having a difficult time putting what I am trying to do into words so searching is also difficult. Basically I am trying to look whether a certain value exists in a column, partitioned by group, and then propagate that value forward. In this example I want to check whether a user has completed the tutorial and…
Tag: postgresql
Split given string and prepare case statement
Table: table_name Insertion of records: Now I want to update set_name for some dates. For example: I want to update table like this: Note: The given_dates and set_name are pass a parameter because of they are dynamic. I may pass 2 sets as shown above s1,s2 or may pass 4 sets according to the requirement. So I…
Returning the row with the most recent timestamp from each group
I have a table (Postgres 9.3) defined as follows: The pertinent details here are the customer_id, the timestamp, and the licensekeys_checksum. There can be multiple entries with the same customer_id, some of those may have matching licensekey_checksum entries, and some may be different. There will never be ro…
How to get the column name of the result of a least function?
I have an employee table that looks like this: I needed to get the minimum value and maximum value of questions where the id is equal to 4. In this case, I needed 5 and 25 returned. I acheived that using the following query: But what this doesn’t return is the question id. How can I adjust my query to
How to use LIKE with ANY in Postgresql?
Is there a way to do a partial string match on a string array column in postgres? I’m trying the following syntax, but it is not working as I’d expect it to: Is there a correct way of doing this? Answer
PostgreSQL, SELECT CASE COALESCE
I need to ‘name’ categories: mycat is a text column with possible values ‘0’ to ‘4’. That works OK, but I have some an error in my program which very rarely writes null (or ” as I can see in pgAdmin). In such cases I have to treat that ” the same as ‘0R…
How to use variables in “EXECUTE format()” in plpgsql
I want to update a column in table stats with the specific column being a parameter, then return the updated value of that column [only has 1 row]: CREATE FUNCTION grow(col varchar) RETURNS integer …
Aggregate columns with additional (distinct) filters
This code works as expected, but I it’s long and creepy. As you can see, it consists of 3 repetitive parts for retrieving: player name and the amount of games they played player name and the amount of games they won player name and the amount of games they lost And each of those also consists of 2 parts…
Return 1 if number exists in table and 0 otherwise
I want to return 1 if some number already exists in table and 0 otherwise. I tried something but it doesn’t work: I want something similar to exists function that already exists in PostgreSQL, but instead of true and false I want 1 or 0. Answer EXISTS yields a boolean result. The simple way to achieve w…
Postgres: getting latest rows for an array of keys
I have a simple table for the events log: If I need the latest event for a given user, that’s obvious: However, suppose I need the latest events for each user id in an array. For example, for the table above and users {1, 4} I’d expect events {3, 5}. Is that possible in plain SQL without resorting…