Tagged Questions
2
votes
1answer
554 views
Postgres' Composite Type on Rails 3
I've discovered a new thing about Postgres: Composite types. I really like this approach and it will be very useful for me.
The problem is that rails' ActiveRecord don't have native support for this.
...
6
votes
3answers
10k 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 ...
201
votes
17answers
81k 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
...
0
votes
1answer
50 views
How to compare if a record exist with json data type field?
I want to check if a record already exist on database, but I have one json data type field and I need to compare it too.
When I try check using exists? I got the following error:
SELECT 1 AS one ...
4
votes
2answers
3k views
Connection refused (PGError) (postgresql and rails)
I keep getting this error when i try to run my localhost using "$rails s":
(Mac OSX 10.8.3)
(ruby 2.0.0p195 (2013-05-14 revision 40734) [x86_64-darwin12.3.0])
(Rails 3.2.11)
(psql (PostgreSQL) ...
0
votes
0answers
36 views
Rails 4 - Desire To Set PostgreSQL Password & Access Database in Rails
I added the following question in April 2014 hoping to set up a password for PostgreSQL but never found a workable solution. I am using a Mac Mini Server running the latest version of Mavericks with ...
6
votes
2answers
7k views
Postgresql date() with timezone
I'm having an issue selecting dates properly from Postgres - they are being stored in UTC, but
not converting with the Date() function properly.
Converting the timestamp to a date gives me the wrong ...
6
votes
3answers
2k 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 ...
1
vote
1answer
625 views
Rails: rake db:create:all (could not connect to server) psql works fine
I've looked here and here but still can't solve my problem.
I have my setup working perfectly on my normal linux but when I tried to recreate it on my mac postgres won't work with rails. After ...
14
votes
6answers
14k views
Postgresql adapter (pg): could not connect to server
I get this error every this I run my Rails app (It cannot connect to my local Postgresql)
/Users/leonardo/.rvm/gems/ruby-1.9.3-p362/gems/activerecord-3.2.11/lib/
...
0
votes
2answers
57 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 ...
0
votes
0answers
35 views
Custom options for a custom database column in Rails
I'm creating a custom extension for the PostgreSQL database adapter to add geometry as a data type. I've been able to successfully add geometry as a column type but the options don't seem to stick.
...
6
votes
2answers
2k 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 ...
1
vote
1answer
441 views
Postgres operators for JSON data type using Rails 4
Got a record:
2.1.1 :202 > r.column_data
=> {"data1"=>[1, 2, 3], "data2"=>"data2-3", "array"=>[{"hello"=>1}, {"hi"=>2}], "nest"=>{"nest1"=>"yes"}}
Trying to query inside ...
15
votes
1answer
23k views
Resetting password of PostgreSQL on ubuntu [closed]
i am using Ubuntu.some days before i installed Postgresql database for ruby on rails.i created a superuser for database server and i forgot the password of postgresql superuser.can any one please help ...
-1
votes
1answer
102 views
Ruby on Rails: must appear in the GROUP BY clause or be used in an aggregate function [duplicate]
This is my query.
ShopifyOrderLineItem.select("shopify_order_line_items.*, sum(amount) as total_price, sum(quantity) as total_quantity").where(:vendor_id => ...
1
vote
1answer
48 views
Using UPCASE or Regexp in Array Column on Postgres
I am trying to query a Postgres Array Column disregarding case and perhaps even disregarding spaces as well.
SELECT "cats".* FROM "cats" WHERE ('CATS - PERSA' = ANY(UPCASE(cat_types))) ORDER BY ...
0
votes
1answer
43 views
Postgres server doesn't launch
I try to set up Postgres for rails in order to be able to deploy on Heroku. I am actually resuming rails tutorials and am a bit lost (am no programmer)
In PgadminIII I try to connect to the single ...
3
votes
2answers
1k views
Avoid PG::InvalidTextRepresentation error when using Postgres UUID in Rails
I started using Postgres UUID type for all my models' id fields. Works great and is supported (for the most part) in Rails 4:
create_table :users, id: :uuid do |t|
# ...
end
The problem is that ...
6
votes
2answers
1k views
Ruby on Rails+PostgreSQL: usage of custom sequences
Say I have a model called Transaction which has a :transaction_code attribute.
I want that attribute to be automatically filled with a sequence number which may differ from id (e.g. Transaction with ...
0
votes
2answers
77 views
Getting database ID of scaffold object in rails
I have a scaffold form Student with some fields like first_name, last_name, DOB, GPA, etc. and I want to be able to reference their database ID once they've been created and wondering what the best ...
16
votes
2answers
4k 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
59 views
Generate barcodes in PostgreSQL
I'd Like to ask if there are any lib to generate bar codes in PostrgeSQL, maybe in Java or pl/Ruby?
I have to insert such barcode inside HTML template generated in the server-side.
Thanks in advance.
...
1
vote
0answers
59 views
PostGIS performance issue with grabbing points by bounding box
I'm working on a google maps application along with Postgresql and PostGIS that fetches markers based on the current viewport of the map and the zoom level (each marker has a min and max zoom level ...
google-maps
0
votes
0answers
674 views
PG::UndefinedTable: ERROR: relation “column_name” does not exist
I have the following error inside Heroku logs after I run pgbackups:restore:
PG::UndefinedTable: ERROR: relation "column_name" does not exist
When I run command pgbackups:restore, it seems that ...
1
vote
2answers
45 views
Why are all of my tables in Rails 4/Postgres being created with “dimension” of 1?
I'm running into a LOT of problems just trying to do basic model generations and migrations in Rails 4 with Postgres. I have the pg gem installed, version 0.17.1.
In the beginning, I couldn't even ...
2
votes
1answer
278 views
How to query tables in Rails console
I have this application which I'm messing around with. (didn't create it, but trying fiddle with it and learn more about querying it, etc).
So, it has a lot of tables.
So, when I go into console, ...
1
vote
1answer
51 views
SELECT MIN and MAX across fields and aggregate by user
I have the following raw data saved in the db
id min_price, max_price, min_x, max_x, user_id
-------------------------------------------
1 50 200 5 null 1
2 0 ...
1
vote
2answers
75 views
Paginating joined results with calculated columns
We are calculating statistics for our client. Statistics are calculated for each SpecialtyLevel, and each statistic can have a number of error flags (not to be confused with validation errors). Here ...
0
votes
1answer
175 views
Case expression does not return the “else” value in Postgres query
I'm creating a SQL query with a lot of nested queries and I'm trying to use the CASE expression but it is behaving weirdly.
This is my query at the moment:
select t.fpl_id, t.team_name,
...
1
vote
0answers
91 views
Rails: Update a column with the value of a column in another table
I have basically the following code:
ModelA.join(:modelB).update_all('modelA.column = modelB.column')
That fails with:
ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR: missing ...
0
votes
1answer
42 views
Compare only the times of two timestamps in postgres
In PostgreSQL, I need a way to determine if the current time is within the hours of operation of a certain model with an opens_at and closes_at timestamp. What would be a valid Postgres way of doing ...
1
vote
1answer
71 views
PG:ConnectBad Rails 4 Postgresql
I know it seems like this question has been asked a hundred times but this time I think it is different.
When I restart my mac and run rails s, the site works fine.
➜ BankingApp git:(master) ✗ ...
10
votes
4answers
6k views
Millisecond resolution of DateTime in Ruby
I have a string like 2012-01-01T01:02:03.456 that I am storing in a Postgres database TIMESTAMP using ActiveRecord.
Unfortunately, Ruby seems to chop off the milliseconds:
ruby-1.9.3-rc1 :078 > ...
6
votes
2answers
15k views
Rails: rake db:create:all fails to connect to PostgreSQL database
I am trying to create a Rails app that uses PostgreSQL. Here is a description of what I did.
PostgreSQL setup:
I installed PostgreSQL 9.1.3 via the ppa:pitti/postgresql maintained by Martin Pitt. ...
1
vote
1answer
68 views
PostgreSQL: Need to perform Row operation
I'm new with PostgreSQL. I would like to perform a row operation on my table.
For example consider this table output :
Record | Accesories | Used
Joy | Laptop | 500
Joy | Android ...
0
votes
1answer
43 views
Grouping in Ruby on Rails using PostGres
I have the following line of code which works in sqlite, but in PostGres doesn't:
@income_by_category = SoldCategory.group(:category_name).having("branch_id=?", @branch_id).sum(:total).to_a
It's ...
2
votes
3answers
393 views
Using postgres arrays in Rails 4, how do I return an array of all the unique items in the array?
My migration looks like this:
create table :posts do |t|
t.string :tags, array: true, default: []
end
How can I get an array of all the unique tags stored under tags in all posts?
6
votes
2answers
693 views
Enabling Tiger Geocoder in postgis version 2.1
I have used homebrew to update to the most recent version of postgis. (2.1)
brew unlink postgis
brew install postgis
I then have created a migration to alter the postgis extension to 2.1
rails g ...
0
votes
1answer
41 views
Postgre SQL incorrect syntaxt near If [closed]
I'm trying to create function but it's having error at If statement don't know what's the problem with it.
CREATE FUNCTION uspgetcountrylistwithpagenumber(IN "PageNumber" integer, IN "PageSize" ...
1
vote
1answer
114 views
Postgres: average user response time from interaction with a bot
I have a table that stores all messages between users and a bot (basically a state machine), and I'm trying to find all pairs of message/response from this table, in order to calculate each user's ...
2
votes
1answer
132 views
Rails - Generate email address when one doesn't exist with Omniauth integration
I am working with omniauth with Rails and trying to get twitter, facebook and google hooked up for authentication but keep running into this error:
PG::Error: ERROR: duplicate key value violates ...
1
vote
4answers
453 views
Rails multitenant architecture, scoping access to multiple tenants
We have a single-tenant database architecture at the moment with MySQL running upward of 100 databases. We switch database connection on subdomain using the Apartment gem and all is dandy!
However, ...
0
votes
1answer
758 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 ...
2
votes
1answer
2k views
Invalid statement with Postgres hstore and rails
I am using the activerecord-postgres-hstore gem with rails 3.2. I have setup hstore and when I try and save any data I get the error:
ActiveRecord::StatementInvalid in ProductsController#create
...
2
votes
5answers
1k views
Postgres segmentation fault ruby gem
I am trying to run this project https://github.com/eLobato/cartodb-rb-client but apparently either my rvm is messed up or pg has a terrible bug.
This is the error trace
/usr/bin/ruby1.8 -S bundle ...
3
votes
1answer
223 views
What is a straightforward way to connect to Postgres.app with dbext (in Vim)?
When I try to connect to my Postgres.app db using dbext, I get the following error:
dbext:PostgreSQL requires a '$HOME/.pgpass' file in order to authenticate. This file is
missing. The binary ...
6
votes
5answers
3k 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
199 views
How do I .group and .sum in a rails model using Postgres?
I have a functioning method in my development environment (SQLite) that looks this:
def self.ytd
Production.find(
:all,
conditions: ["year == ?", Time.now.year],
select: "carrier_id, ...
9
votes
2answers
6k 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, ...
