Let's assume three models, standard joins: class Mailbox < ActiveRecord::Base has_many :addresses has_many :domains, :through => :addresses end class Address < ActiveRecord::Base belongs_to :mailbox belongs_to :domain end class Domain < ActiveRecord::Base has_many :addresses has_many :mailboxes, :through => :addresses end ...
The validation seems to fail for some reason. By the validation, I mean the "if ($result)" -part. How can I correctly validate SQL-query? $dbconn = pg_connect("host=localhost port=5432 dbname=heoa user=heoa password=123"); $email = $_POST['login']['email']; $result = pg_query_params( $dbconn, 'SELECT user_id ...
Is there any way to find out particular datatype [say for example chkpass] is available or not through a query ? ...
anyone know how I would write that query with AR? select *, (m.user_id=1) as member from band b join memberships m on m.band_id = g.id; Thanks in advance. ...
I would like to connect to my Postgres 8.3 database using SSL from my XP client using OpenSSL. This works fine without SSL. When I try it with SSL (no client certificate), I get the error: error:140790E5:SSL routines:SSL23_WRITE:ssl handshake failure I suspect that I need to change something with the Postgres configuration but I don't...
We have this PHP application which selects a row from the database, works on it (calls an external API which uses a webservice), and then inserts a new register based on the work done. There's an AJAX display which informs the user of how many registers have been processed. The data is mostly text, so it's rather heavy data. The proce...
Hello! Periodically, Postgres "forgets" about its index and starts to slow down. I do analyze and it "recalls" back its index. Everything would be ok, but last time this happened only after 2 hours I did analyze. There are no deletes, inserts are quite slow, around 10000 per hour. Might be this a bug in Postgresql itself? Version 8.3.7...
I got this error: ERROR: could not serialize access due to concurrent update But I'm not using serializable transaction isolation. Is that possible without setting the default isolation level to serializable? The postgres docs only mention it for serializable transactions. ...
I'm running some bizarre Postgres migration code from OpenCongress and I'm getting this error: RuntimeError: ERROR C25001 MVACUUM cannot run inside a transaction block Fxact.c L2649 RPreventTransactionChain: VACUUM FULL ANALYZE; So I'd like to try running it without getting wrapped by a transaction. ...
I have a table structured as: create table a ( a bigint primary key, csv varchar(255) ) I would like to be able to query a view ("b") such that: select * from b; Yields something like: a | b ------ 1 | A 1 | B 1 | C For the case where the initial table has one row of data (1, 'A,B,C'). Is this possible with a postgres vie...
ATM I am trying to learn how to efficiently use database inidices and would appreciate to get some expert input. I do not have any performance issues currently. I would just like to know, how you would handle your indices with this query: SELECT B.event, COALESCE(B.system, C.surname || ' ' || C.forename) AS name, C.label...
Hi! I have to make installer that installs Postgresql before our application, makes database and execute some sql scripts against the database. Now I'm using the Bootstrapper to install Postgresql and then our application and using Wix install sequence I execute SQL script that takes care of the rest. I'm wondering if there is any wa...
i am using this function in a trigger: CREATE OR REPLACE FUNCTION xx() RETURNS trigger AS $xx$ BEGIN INSERT INTO my_log (x, y, z) VALUES (NEW.x, NEW.y, current_setting('myvar.user')); RETURN NULL; END; $xx$ LANGUAGE plpgsql; now i would like to check, if 'myvar.user' is a valid integer, and if not, do anothe...
Ffor testing purposes I need to write a SQL query which contains the actual record number as a column in the result set. If my SELECT gets back to me with 10 records as the result, I need to have one column which contains the values 1-10. Is there a way to achieve this without a stored procedure cursoring through my data? EDIT: I nee...
Hi all, since my approach for a test query which I worked on in this question did not work out I'm trying something else now. Is there a wayto tell pg's random() function to getme only numbers between 1 and 10? thx for your time K ...
I have a Django view which creates 500-5000 new database INSERTS in a loop. Problem is, it is really slow! I'm getting about 100 inserts per minute on Postgres 8.3. We used to use MySQL on lesser hardware (smaller EC2 instance) and never had these types of speed issues. Details: Postgres 8.3 on Ubuntu Server 9.04. Server is a "large"...
Consider a dimensional model with fact tables like (fk_dim1value, fk_dim2value, ..., value) where the fk_X columns are foreign keys into corresponding trivial dimension tables dim1value (id, value), dim2value (id, value), etc. These fact-and-dimension tables are collected automatically from disparate sources, so there's a lot of them .....
I have two tables: Companies: (id, name, city) Workers: (id, name) I would like to get all companies and sort them by numbers of employes. The result should give: count | company id | company name | city ------------------------------------------ 90 6 foo corp NY 45 9 bar corp LA 0 ...
I have two tables: Companies: (id, name) Workers: (id, name, country) I would like to get all companies and sort them by numbers of employees from a given country. For a query looking for companies that have more workers from the USA, the result should give: #workers from USA | company id | company name ----------------------------...
Hi there, I need to add a foreign key field to an existing django model/postgres table. As per the django documentation, I ran the 'sqlall myapp' command to 'work out the difference'. The obvious difference is that the table in question now has an extra column with a new contraint, which looks like this: ALTER TABLE "myapp_mytable" A...