I am searching for an index to make the following quick: The query is a delete and reads: so it deletes all rows that do not match a few hundred criteria on t1 and t2. explain gives a sequential scan and rearranges the query: Can I create an index that will avoid that full scan? Thanks in advance! Answer Alth…
Tag: postgresql
How to iterate over PostgreSQL jsonb array of objects and modify elements?
Given jsonb array and PostgreSQL 12: Need to convert it to: Is it possible somehow to iterate over jsonb array and downcase only “type” values? I tried: but it separates data and type pairs into separateelements. Answer You need an intermediate level of nesting to rebuild the objects before you ag…
Is it possible to create a prepared statement and reuse it later with Java under postgres?
I am trying to test a few things out with postgres. I want to know if it’s possible to create define a PreparedStatement such as String statement = “Insert into table_one values (?)”; …
Postgres expand JSON with unkown keys
I want to expand a specific row in my table which has a json column with the followingstructure: id|column1 | —|————-| id1|{nested json}| id2|{nested json}| Structure of nested JSON …
2 dimensional table query with join and PostgreSQL [closed]
Trying to return a 2 dimensional table from SQL query (Postgres). Read about Pivot Table (but not sure if it is valid for join) and crosstab/tablefunc but no luck. Expected result App A | …
check if value is available in DB or not
I have a table which has the schema: i need a query for the report which contains whether the name i give exists in the DB . the name AD,BC,CA should be given by me and its not in any other table. Thanks in advance Answer Use an outer join against a values list:
How to use CASE WHEN in PostgreSQL without adding values to aggregation and GROUP BY?
I need to take one timestamp if my condition is right and other value, if not. I made such condition: CASE WHEN tr.type = ‘deposit’ THEN tr.timestamp::date ELSE tr.status_last_change_timestamp::date …
How to delete records of orders that is canceled within 5 minutes in database?
I have a record of users’ purchasing behavior. However, it is long and includes a lot of redundant data. I want to delete orders that purchased and deleted within 5 min My query so far: –TABLE 3 COD …
Why is my “CREATE INDEX CONCURRENTLY ..” command blocked by a “SELECT FROM” query? (Postgres 9.6)
I was running a migration to create an index, but the migration was blocked by another query. I resolved the problem after discovering that there was another query blocking my migrations; and after …
List the name of all the directors who have not directed a movie since a certain year
I’m trying to learn PostgreSQL with the imdb database and I can’t seem to figure out how to list the directors who have not directed a movie since a particular year. I have three tables to work with Table movie with mov_id, mov_title, mov_year Table director with dir_id, dir_name Table movie_direc…