I have a table that contains a jsonb field called data. This field can contain arbitrary data. One of them could be emails. Like; So, email field is Array. Normally if it would a string i could easily use ILIKE or ~* on this data, however, being an array makes me think twice. Because it still works. The query…
Tag: postgresql
UNION ALL not combining rows
I have this table called “valores_indices” where it imports stocks values every 1 hour, I need to get the values from each monitored stock at 8am and 6pm, those being respectively my “vlrAberto” and “vlrFechado” selects I don’t get why these are not merging, I’v…
Select all the years from a range without having a years table
I have selected the current year with I want to print a list of all the years from 1990 to the current year without having to create a table of the list of years, is there a way to do it or I must create a table to do this? Answer or
How to replace IN in an SQL query containing a lot of parameters with Postgresql?
I am trying to retrieve information from a database using a Python tuple containing a set of ids (between 1000 and 10000 ids), but my query uses the IN statement and is subsequently very slow. and then I query the database using PostgreSQL to charge the result in a Pandas dataframe: I know that IN is not very…
How to combine two records while keeping all referencing records?
PostgreSQL 11.1 I have struggled with this problem for a long time. (I have tried to improve the question from before). Problem: A person has two different names in table tempA. Each name has its associated records in table tempB. How can I move all the records associated with one name to the other name, then…
SQL – Update multiple tables, the same column across one query
I have a postgres database There are 3 tables, I’m trying to make a sql UPDATE statement that updates an active column in each table. Table1 has a field record that looks like this: Table2 Table3 is the same as table 2 I need a query that looks for the name “Digital Only” and it’s ID o…
Cannot use SQL Lead function correctly
I have a table with 4 columns: date, event_name, video_id and user_id. I need to write a query that will return the number of users who viewed at least one video in a given day and the number of those users who returned the next day to view at least one video. The video_play event signifies that a video was
SQL (MYSQL, Postgres) Lookup/report table
I’m basically making a lookup table for my three tables but its not doing what I want. Those 3 tables were created by loading 3 different csv files. What I’m trying to do is inserting the ID’s from those tables into the lookup one. This is what I keep getting: What I need is: I kind of get w…
Atomic optimistic locking on a PostgreSQL table doesn’t fail if clause not satifisfied
Let’s say I have the table definition below: CREATE TABLE dummy_table ( id SERIAL PRIMARY KEY , version INT NOT NULL, data TEXT NOT NULL); INSERT INTO dummy_table(version, …
How can I count rows into more than one ‘then’ of a Postgres searched case expression? (reporting)
Background I have a Postgres 11 database running on RDS. I need to produce a report which shows counts of events over a certain period, grouping, renaming, and counting them based on description …