I’m using hibernate 4.2.6 and PostgreSQL 9.1 I’ve been trying to execute sql query with hibernate. I’ve written: Session session = Hibernate.util.HibernateUtil.getSessionFactory().openSession(); …
Tag: postgresql
Get count of foreign key from multiple tables
I have 3 tables, with Table B & C referencing Table A via Foreign Key. I want to write a query in PostgreSQL to get all ids from A and also their total occurrences from B & C. Output desired (just the id from A & total count in B & C) : SQL so far SQL Fiddle : The
How to GROUP BY and CONCATENATE fields in redshift
How to GROUP BY and CONCATENATE fields in Redshift e.g. If I have table How can I get result like this There are some solutions for PostgreSQL, but none of functions mentioned in those answers are available in Redshift rightnow. Answer Well, I am a little late but the announcement about this feature happened …
Rails Activerecord Relation: using subquery as a table for a SQL select statement
Can somebody help me figure out how to write the following SQL using Rails (I’m using Rails 4) Activerecord methods? I know you can do this with find_by_sql but I’d like to preserve the active record relation. Here’s the sql for a postGreSQL db that I’m trying to create: For my subquer…
Best way to check for “empty or null value”
What is best way to check if value is null or empty string in Postgres sql statements? Value can be long expression so it is preferable that it is written only once in check. Currently I’m using: But it looks a bit ugly. stringexpression may be char(n) column or expression containing char(n) columns wit…
Aggregate functions across multiple columns in postgres
I have a postgresql table with multiple fields containing integers (a1,a2,a3 etc). I want to run aggregate functions(mean, standard deviation etc) across more than one of the columns at once. (Some of them may have a reasonable number of nulls, so I don’t want to just generate column averages and then a…
printing a value of a variable in postgresql
I have a postgresql function How can I print the value of the deletedContactId to the console? Answer You can raise a notice in Postgres as follows: Read here
PostgreSQL – Query Optimization
I have this below query which takes about 15-20 secs to run. with cte0 as ( SELECT label, date, CASE WHEN Lead(label || date || “number”) OVER (PARTITION BY label || date || “number” ORDER BY “label”, “date”, “number”, “time”) IS NULL THE…
Postgresql query between date ranges
I am trying to query my postgresql db to return results where a date is in certain month and year. In other words I would like all the values for a month-year. The only way i’ve been able to do it so far is like this: Problem with this is that I have to calculate the first date and last
Stored procedure to return count
I am trying to make a stored procedure for the query I have: or I have written this stored procedure but it returns count of all the records in column not the result of query plus I need to write stored procedure in plpgsql not in SQL. Help me write this type of stored procedure in plpgsql which returns retur…