I have bunch of tables where several of them have hundreds of columns. I need to get a count of non-null values for each column and I’ve been doing it manually. I would like to figure out a way to get …
Tag: postgresql
Yii2 How to translate SUM SQL function to Query Builder?
I have this simple SQL query: SELECT product_name, SUM (product_amount) FROM orders GROUP BY product_name; It will show a list with product names and their amounts. Like this example: I want to …
Knex query returning bool logic
I am writing some knex queries to check if a supplied code otherwise known as a voucher is legitimate under 4 constraints. The voucher exists The voucher has not expired The voucher is for the …
Postgres – Multiple conditions on single column with the WHERE AND condition?
So I have a query as follows: SELECT AVG(ratings.rating) FROM ratings INNER JOIN movies ON movies.movieid = ratings.movieid INNER JOIN hasagenre ON hasagenre.movieid = movies.movieid INNER JOIN …
Postgresql – Return a column resulting from join as single json column
I have a many to many relationship between users and roles table (A typical usecase). They are related together using a user_role table. I execute the below query: select u.id, u.first_name, u….
How can I specify column name casing in TypeORM migrations
I’m using typeORM and I want to use migrations instead of syncing because I’m working in a team and it’s actually a lot of tedious work to get the db to a state in which the app actually functions. …
knex postgres join as nested JSON
I’m using knex with postgresql db, I have a row in table A which has one-to-one relation to rows in table B and one-to-many relation to rows in table C. I wanna join a row from A with B and C and get a json like Where stuff like aCol means column from table A, bCol – column from table
Explicitly distinguish similar queries in pg_stat_statements?
pg_stat_statements is (thankfully) pretty smart at normalizing queries to aggregate stats regardless of the arguments you pass. However, I’m facing a situation where a particular query is called from many locations across our application and I would like to get separate statistics based on these various…
Postgres compound index effectiveness with a given column list
Let’s say I have a compound index involving these 3 columns. (name, email, phone) Is this index still will be used for these queries? The first query involves just 2 of indexed fields and the second …
insert statement in postgresql
I have a table with 8 records. I have 4 records for Dec-17, same 4 records for Jan-18 (only yearmon changes) I am trying to have same records for the month of Feb-18 as well. All the column values …