Open source RDBMS (relational database management system) when used in conjunction with the Ruby on Rails framework.
1
vote
2answers
37 views
Rails, ActiveRecord and SubQueries
I have a postgresql database with hourly gas consumption entries.
Now, I need to find the days with the highest consumption for every month.
In plain SQL I'd use subqueries like this:
SELECT
...
92
votes
15answers
76k views
PG::ConnectionBad - could not connect to server: Connection refused
Everytime I run my rails 4.0 server, I get this output.
Started GET "/" for 127.0.0.1 at 2013-11-06 23:56:36 -0500
PG::ConnectionBad - could not connect to server: Connection refused
Is the server ...
-1
votes
1answer
23 views
How to compare dates across two tables where one date is valid until updated?
I have a relatively complicated query to write; we use Postgres. Basically, I have data across two tables:
Table 1: Historical ticket prices
ticket_seller_id | show_id | low_price | created_at
...
2
votes
1answer
44 views
CSV Worker is locking dbase
Bit of a newbie and am completely baffled.
I have a worker that Imports CSV Contacts to save to the Contacts Table (prior to saving, it checks to see if it already exists based on phone or email ...
0
votes
2answers
664 views
Postgres server not starting after unexpected Yosemite shutdown
I'm trying to start a rails server but I'm getting this error...
/Users/kweihe/.rvm/gems/ruby-2.1.6/gems/activerecord-3.2.22/lib/active_record/connection_adapters/postgresql_adapter.rb:1222:in ...
1
vote
0answers
102 views
Querying nested arrays in Postgres JSON field with Rails
I am using Postgres 9.3 json data type. I want to do a query with data that is nested within the array.
For example, I want to query for users that have all of this interests = ["music", "movies", ...
2
votes
0answers
78 views
Postgres functions not being migrated in test environment (Rails) [duplicate]
I have the following migration:
class CreateNoAccentFunction < ActiveRecord::Migration
def up
execute <<-SQL
CREATE OR REPLACE FUNCTION no_accent(text) RETURNS text AS $$
...
399
votes
30answers
144k views
Can't find the 'libpq-fe.h header when trying to install pg gem
I am using rails 3.1 pre version. I like to use PostgreSQL but the problem is installing the pg gem. It gives me the following error:
demonchand@system-001:~/exercise/personal/pro$ gem install pg
...
9
votes
2answers
3k views
How to use ActiveRecord outside Rails?
I'm building a Rails application based on hexagonal architecture.
One of my adapters is storage adapter (maintained as a gem) that manages access to database and provides simple interface for rails ...
17
votes
3answers
27k views
How to set up Postgres database for local Rails project?
I recently got a new machine and would now like to work on my projects from Github. I'm curious as to how to properly set up the Postgres database on my local machine. I have postgresql, pgadmin3 and ...
0
votes
1answer
1k views
Querying a PostgreSQL multi-dimensional array data type in Rails 4
I am using a PostgreSQL multi-dimensional array to mimic an array of hashes, and I am looking for a way to locate a record by a key-value pair in that array like e.g ["key1","value1"]. An example ...
9
votes
4answers
8k views
Rails 4.0.1 on Heroku, can't create database
I can't get rake db:migrate to run on my rails 4.0.1 app on Heroku.
I'm guessing that I don't have postgres configured properly... but reading the docs on heroku hasn't really helped and I'm not sure ...
3
votes
1answer
645 views
Find values greater than using hstore and rails
I am storing product information, including the release year of the product, using hstore and postgresql in rails. Now I would like to be able to query for all products that was released before or ...
2
votes
0answers
76 views
is it possible to index html content in postgresql full text search?
I have a bunch of html pages. The idea is to allow users enter keywords to search through these html pages. only html pages that match the criteria will be store for later references. I knew that ...
1
vote
2answers
24 views
How to get unique value after removing space and group by some different column in postgres
I have a table like
id type note
1 A ab cd
1 B cdef
1 A abd
1 A abcd
I want to get all the unique notes after removing space so my result will be like this
id type note
1 A ab ...
0
votes
1answer
80 views
'pg' works locally, but not when deployed to Heroku
pg 0.18.4 works just fine in my development environment but not in Heroku. Locally, it first failed when I just ran bundler install but then succeeded when I manually ran gem install pg -v '0.18.4'. I ...
0
votes
0answers
25 views
Ruby jbuilder view inefficiently rendering jsonb postgres field
Jbuilder 2.3.2, in a Sinatra app with postgres 9.4
(I tagged Rails 4 because this is a Jbuilder question, and SO tag jbuilder is for questions about the Java IDE)
jsonb field in a Postgres table, ...
5
votes
2answers
373 views
Building and storing complex SQL query for later editing
I am using Rails 3.2.8 to build a "product set" builder that mirrors Google Analytics' Custom Profile builder. For example, a user may define a product set as follows:
(Category = 'Printers') and ...
14
votes
5answers
6k views
Postgres accent insensitive LIKE search in Rails 3.1 on Heroku
How can I modify a where/like condition on a search query in Rails:
find(:all, :conditions => ["lower(name) LIKE ?", "%#{search.downcase}%"])
so that the results are matched irrespective of ...
0
votes
1answer
18 views
What is the role of -s flag in creating user
I am trying to a create a user in postgres, I did the following.
sudo -u postgres createuser mystore
But I found out that I should use -s flag while creating the user, So my question is what is ...
1
vote
2answers
2k views
Retrieve the nextval from a sequence using activerecord in Ruby on Rails 3.2.14 / Ruby 2.0.0 / PostgreSQL 9.2.4
This should be SO simple. I want to retrieve the nextval of a sequence... it's not a default value... it's not a primary key... it's not a foreign key. In rare cases, I need a sequence number for a ...
3
votes
2answers
6k views
Rails :order by date in Postgres returning incorrect order
I have a model called Story that I'm trying to order by the created_at date. Since I've hosted my app on Heroku, which uses Postgresql, I have the following in my controller:
@stories = Story.find( ...
4
votes
1answer
2k views
Rails pg gem Incompatible library version
I'm getting the following 'incompatible library version' error when I try and access my Ruby on Rails website in development mode.
incompatible library version - ...
4
votes
1answer
408 views
pghero on PostgresApp pg_stat_statements must be loaded via shared_preload_libraries
I have this error
PG::ObjectNotInPrerequisiteState: ERROR: pg_stat_statements must be loaded via shared_preload_libraries
on localhost. Using osx (10.9.5), PostgresApp 9.3.1.0 and pghero gem
...
2
votes
2answers
50 views
Problems with heroku rake db:reset
I have a problem, I pushed my app to github and I made the deploy with heroku, everything was ok, but when I run heroku run --app myapp rake db:reset (to populate the database on heroku) I found the ...
0
votes
1answer
58 views
Rails 4.2 OR scope using includes/joins
Given a List model with relationships:
has_many :list_group_memberships, dependent: :destroy
has_many :groups, through: :list_group_memberships
has_many :users, -> { unscope(:order).uniq }, ...
0
votes
1answer
45 views
How to Delete all duplicate rows in postgres sql based on 3 duplicate columns and out of that keep max and second max date value rows
I have a table temp it has 118507658 records .
I need to clean up this table based on below condition.
If three columns that is (dp_content_definition_id,dp_order ,value_scope_id) are identical then ...
0
votes
1answer
41 views
Check that record doesn't exist/retrieve existing record active record
This question has two parts:
Consider an active record relation that has a Student - name:string, favorite_class:references and FavoriteClass name:string, abbrev:string.
First question: When ...
1
vote
2answers
52 views
Better to have a field “City” or a table “City”?
I'm making a website with Ruby on Rails and I'm thinking of the database structure.
The website is very simple, we make a list of many places, and tell from which city they are.
For example "Tour ...
1
vote
1answer
21 views
Pgagent and Postgis are not seen in pgAdmin?
I have a rail project and I tried to add checkin property to my app.
I have already install Postgis and pgAgent tool to my Postgres server from ApplicationStackBuilder but I can not find these tools ...
17
votes
3answers
10k views
Postgres JSON data type Rails query
I am using Postgres' json data type but want to do a query/ordering with data that is nested within the json.
I want to order or query with .where on the json data type. For example, I want to query ...
2
votes
1answer
205 views
How to find a set of missing dates in a table from date range query
There are some stackoverflow posts related to my question but not all that similar.
I would like an efficient and somewhat elegant(if possible) solution as to get an array of missing dates after ...
1
vote
0answers
143 views
postgres error role and db doesnt exist after downloading OSX Mavericks
I restarted my computer recently and after trying to start my db locally with rails s I got the following:
...
2
votes
2answers
3k views
Creating a PostgreSQL sequence to a field (which is not the ID of the record)
I am working on a Ruby on Rails app. We are using a PostgreSQL database.
There is a table named scores with the following columns:
Column | Type
--------------+-----------------------
id ...
22
votes
2answers
9k views
Error when creating unaccent extension on PostgreSQL
I am trying to configure PostgreSQL to use fulltext search in my rails app as mentioned in this Railscast.
I am using a fresh Ubuntu 12.04 server running PostgreSQL 9.1.5 installed using apt-get with ...
0
votes
0answers
134 views
Rails 4 Heroku Mysql to Postgres Dump Pending Migrations
I've converted my mysql db to postgres.
I'm used the following to import it to the db:
heroku pgbackups:restore database 'http://app.com' --app
This creates all the tables and imports the data.
...
29
votes
4answers
21k views
How to solve privileges issues when restore PostgreSQL Database
I have dumped a clean, no owner backup for Postgres Database with the command
pg_dump sample_database -O -c -U
Later, when I restore the database with
psql -d sample_database -U app_name
However, ...
7
votes
2answers
6k views
Set default value for Postgres JSON column in Rails < 4
So I'm starting to use the Postgres JSON datatype, now that there's a lot of fun stuff you can do with it.
In one of my Rails apps which is not yet Rails 4 (where support for Postgres JSON has been ...
7
votes
6answers
4k views
cannot install pg gem, checking for PQconnectdb() in -lpq… no
I'm trying to develop in Ruby on Rails using PostgreSQL locally, so I'm trying to install the pg gem. On Ubuntu 12.04. In terminal, I typed in:
gem install pg -v '0.12.2'
Which throws the ...
0
votes
1answer
132 views
Filtering datetime not working when using timezone
In my setup I want to filter Schedule by Activity, City, Date, Starting Time and End Time using select menus.
I've made it work with Ransack:
# db
create_table "schedules", force: :cascade do |t|
...
4
votes
1answer
203 views
Matching special characters (e.g. #, +) using pg_search
I'm using the pg_search gem in a Rails app to search against users - their bios and associated skill model. Users are developers, so their skills include things like "CSS", "C++", "C#", "Objective C", ...
0
votes
0answers
35 views
Weird results using distinct() and count() methods on apparently similar code
The following strange behavior has been spotted with Rails 4 (Rails 4.2.2/ruby 2.2.1) and PostgreSQL 9 (PostgreSQL 9.4.4).
Keeping in mind that grouping without aggregate expressions effectively ...
7
votes
2answers
707 views
Ransack, Postgres - sort on column from associated table with distinct: true
I have an app that uses the Ransack gem and I'm converting it from Mysql to Postgres.
In an instance where the sort column is from an associated table and the distinct option is set to true, Postgres ...
3
votes
3answers
2k views
Cannot remove an index from table in Rails 4 and PSQL 9.3
In my schema.rb I have the following line:
add_index "users", ["email"], name: "index_users_on_email", unique: true, using: :btree
When I run \di in psql I get:
Schema | ...
1
vote
2answers
543 views
RSpec Boolean Validation
Test:
it { should ensure_inclusion_of(:on_ride_photo).in_array([true, false]) }
Validation:
validates :on_ride_photo,
presence: true,
inclusion: { in: [true, false] }
Errors:
1) ...
0
votes
1answer
52 views
Getting PG duplicate table error on deploy
I want to deploy from a branch in which I have three new migration files.
However, on deploy, I get an error.
This is the log output:
DEBUG [50e68bc0] == CreateLimitGroups: migrating
...
2
votes
4answers
64 views
Rails: Optimize querying maximum values from associated table
I need to show a list of partners and the maximum value from the reservation_limit column from Klass table.
Partner has_many :klasses
Klass belongs_to :partner
# Partner controller
def index
...
0
votes
1answer
61 views
Why does the where() method run SQL queries after all nested relations are eager-loaded?
In my controller method for the the index view I have the following line.
@students_instance = Student.includes(:memo_tests => {:memo_target => :memo_level})
So for each Student I eager-load ...
1
vote
1answer
144 views
Give default value to nested jsonb on Rails 4 /postgresql 9.4
I wish to add a jsonb column (called per_deal_numbers) on my User table and preparing the migration
class AddInfoToUsers < ActiveRecord::Migration
def change
add_column :users, ...
1
vote
1answer
235 views
Unable to retreive excerpt from Postgres using pg_search gem
Update
First of all, there is no method "context". That was a word my brain made up at some point and stuck with. Obviosuly I should've been running .excerpt(). Second, I was running the command ...
