I need help optimizing a Postgres query which uses the BETWEEN clause with a timestamp field. I have 2 tables: containing about 3394 rows containing about 4000000 rows There are btree indexes on both PKs id_one and id_two, on the FK id_one and cut_time. I want to perform a query like: This query retrieves about 1.700.000 rows in about 7
Tag: postgresql
PostgreSQL – order by an array
I have 2 tables – course that contains id and name of the courses and tagCourse that contains tags for each course. I’d like to write a function that searches courses by given array of tags and returns them ordered by quantity of matching tags. However I don’t know how to write it correctly and in an efficient way. Please
Upgrading a varchar column to enum type in postgresql
We have a varchar column in a table, that we need to upgrade to enum type. All the values in the varchar column are valid values in the enumeration. There is no null values in the varchar column. …
Pivot on Multiple Columns using Tablefunc
Has anyone used tablefunc to pivot on multiple variables as opposed to only using row name? The documentation notes: The “extra” columns are expected to be the same for all rows with the same row_name value. I’m not sure how to do this without combining the columns that I want to pivot on (which I highly doubt will give me
Decode equivalent in postgres
There is no equivalent to the Oracle’s DECODE()’Function InPostgres`. Is there anyone who wrote decode as a Function?
how to execute a .sql script on heroku?
I have a .sql file with a bunch of insert commands that I want to execute on my postgres database on heroku. But I don’t know how to do it:- If I had access to postgres console I’d type the following: but it seems that heroku doesn’t support this command. I’ve tried with but that doesn’t let me input a
Regex remove all occurrences of multiple characters in a string
In my PostgreSQL I want to replace all characters (;) occurrences in a string. My query: update table_name set text = regexp_replace(text, ‘/[(;)]+/g’, ”); I think my regexp is …
Does Postgresql plpgsql/sql support short circuiting in the where clause?
If I have the following toy query Would the first condition in the WHERE clause short circuit the second condition which would have a complex run time? I’m working on some sql that is actually part of a FOR LOOP in plpgsql, and I could do iterations over all records that exist in the my_other_tables, and then test within the
Text to List in SQL
Is there any way on how to convert a comma separated text value to a list so that I can use it with ‘IN’ in SQL? I used PostgreSQL for this one. Ex.: This query: produces ‘SG’,’PH’ I wanted to produce this query: Nothing returned when I executed the first query. The table has been filled with location values ‘SG’
autoDocumentation of SQL queries [closed]
We use SQL queries to do most of analytical work at our company (PostgreSQL). Currently there are 250+ queries stored in fixed directory structure (there is also a lot of DDL/DML files). Is there …