How can I write the following query in Rails?

I need to write a query that only counts individuals that have not attended an event previously. Here are the models class Individual < ActiveRecord::Base has_many :attendances, dependent: :destroy has_many :events, through: :attendances end class At

ResultSet returns an empty column in CSV

I'm using JOOQ and Postgres. In Postgres I have a column gender: 'gender' AS gender, (the table itself is a view and the gender column is a placeholder for a value that gets calculated in Java) In Java when I .fetch() the view, I do some calculations

SQL Alternative to Using WHERE as a Subquery

I'm learning SQL using PostgreSQL, and I'm trying to modify one of the queries from the PostgreSQL Tutorial website. The original query is this: SELECT film_id, title FROM film WHERE film_id IN ( SELECT inventory.film_id FROM rental INNER JOIN invent

How to insert lines in a loop

I have the following plpgsql function in PostgreSQL: CREATE OR REPLACE FUNCTION func1() RETURNS SETOF type_a AS $BODY$ declare param text; sqls varchar; row type_a; begin code..... sqls='select * from func3(' || param || ') '; for row in execute sqls

Postgresql creates pg_dump for schema upgrade

i have two postgres databases on different servers. Both have a 'public schema' with the same tables inside. One of them is used for developing and I am creating new tables of new columns inside some tables. How can i pg_dump the developing DB struct

Transactions - Oracle Vs PostgreSQL

PostgreSQL do not have feature to insert Save Points in the trigger functions and when there is an exception ( Like exception NO_DATA_FOUND) the entire transaction is rolled back. But instead of rolling back the entire transaction , I just wanted to

How to join a single line in the table attached with postgres?

I have the following schema: CREATE TABLE author ( id integer , name varchar(255) ); CREATE TABLE book ( id integer , author_id integer , title varchar(255) , rating integer ); And I want each author with its last book: SELECT,, aut

Find sentences with two adjacent words in Pg

I need help crafting an advanced Postgres query. I am trying to find sentences with two words adjacent to each other, using Postgres directly, not some command language extension. My tables are: TABLE word (spelling text, wordid serial) TABLE sentenc

How to implement java code as a service in grails

I have written a code in java that help me to generate a pdf . I have used Itext libraries to generate it. This code also consists of database connectivity with postgres. I have used two jar files itextpdf-5.4.5.jar and postgresql-9.0-901.jar. But as

Paging problem when sorting based on the last modified property

I need to show some records sorted based on modified column (latest activity on top) (Post with new edit or comments at the top) App UI has twitter like 'more' post button for infinite scroll. each 'more' will add next 10 records to UI. Issue is that

aggregation and statistics functions on NOSQL databases

Using SQL databases, it is easy to do statistical / aggregate functions like covariance, standard deviation, kurtosis, skewness, deviations, means and medians, summation and product etc, without taking the data out to an application server. http://ww

JDBC PSD Transactions Cause Blocking

Question: Updated: Why does inserting a row into table A with a foreign key constraint to table B and then updating the row in table B that the inserted row in table A references in a transaction cause a deadlock? Scenario: reservation.time_slot_id h

Django: syncdb does not create a new table after loading dump

tl;dr: We want to add a new table to the database, without losing previous data. First we took a dump of the database using: $ psql -h localhost dbname > dump_file Then we loaded it to our local db. This is working fine. Problem starts: On adding a n

How to return multiple lines of the PL / pgSQL function?

I have spent good amount of time trying to figure it out and I haven't been able to resolve it. So, I need your help please. I am trying to write a PL/pgSQL function that returns multiple rows. The function I wrote is shown below. But it is not worki

Summation and order at once

I have a table of orders. There I need to find out which 3 partner_id's have made the largest sum of amount_totals, and sort those 3 from biggest to smallest. testdb=# SELECT amount_total, partner_id FROM sale_order; amount_total | partner_id -------

Postgres performance issues

We are running Postgres 9.1.3 and we have recently started to run into major performance problems on one of our servers. Our queries ran fine for a while, but as of August 1st, they have slowed down dramatically. It would appear that most of the prob

MSSQL - & gt; MySQL or PostgreSQL database replication

I find myself in a situation that someone else must have got stuck in at some point. Our company runs an in house MSSQL based database, our website then sits on an external server and currently runs MySQL. The problem comes that both databases need t

How to modify Postgres table data according to its content?

This is probably a super simple question, but I'm struggling to come up with the right keywords to find it on Google. I have a Postgres table that has among its contents a column of type text named content_type. That stores what type of entry is stor

How to fix Postgres to start after a crash?

Due to a sudden power outage, the PostGres server running on my local machine shut down abruptly. After rebooting, I tried to restart postgres and I get this error: $ pg_ctl -D /usr/local/pgsql/data restart pg_ctl: PID file "/usr/local/pgsql/data/pos