I've been using the acts-as-taggable-on for tagging in my development app, but when I push it to Heroku, I get a nasty error: ActionView::TemplateError (PGError: ERROR: relation "tags" does not exist : SELECT tags.*, taggings.tags_count AS count FROM "tags" JOIN (SELECT taggings.tag_id, COUNT(taggings.tag_id) AS tags_count FROM "taggi...
Hi all, new Stacker here! From a client I receive SQL queries that contain all sorts of redundant sub-SELECTs, generated by a builder in MS Access. I convert the SQL to Postgres, and it runs - but I know it's hopelessly inefficient. So, I set about optimising the query, and produce iterative improvements - and to determine whether each ...
I have the following problem, I need to put in a script that is going to run before the new version is rolled the SQL code that enables the pgAgent in PostgreSQL. However, this code should be run on the maintenance database (postgres) and the database where we run the script file is another one. I remember that in SQL Server there is a ...
I have installed following gems: actionmailer (2.3.8) actionpack (2.3.8) activerecord (2.3.8) activeresource (2.3.8) activesupport (2.3.8) open4 (1.0.1) pg (0.9.0) rack (1.1.0) rails (2.3.8) rake (0.8.7) My application uses postgres as db and pg gem and it had been working fine by the time I done rake rails:freeze:gems rake gems:un...
Hi I'm sure there's query for this kind of operation, but I just can't seem to get it. I have a table like this: product_id | saved_by | deleted_by | accepted_by | published_by -----------+----------+------------+-------------+------------- 1 | user1 | | user1 | -----------+----------+------------+---------...
Basically it doesn't work ... I can install the jdbc driver for pg 90 in netbeans and browse the database, issue sql commands etc. But creating entity beans from the tables via the same connection does not work, Netbeans can't find any tables in the database. Do I have to downgrade to pg 84, or are there some tricks to make it work? ...
Hey everyone, I am currently working with some of my teacher's old Postgres SQL code and modifying it to match what I need. However, there is one piece of code that I can't seem to find an explanation of anywhere online. In the code he had a case statement with a ~~* used as what looked like a comparison. I am unfamiliar with this synta...
I am developing a (currently) Rails 2.3.x application with a PostgreSQL 8.4 database backend. In my Rails application, I have a model corresponding to a database table that has two columns of datatype SERIAL and set as NOT NULL. I have one of these columns set as the primary key in both Rails and as a PostgreSQL constraint. Table defin...
I was installing some software completely unrelated to Postgres. The server locked up and I had to do a hard reset. After the server came back up the Postgres service will not start. Has anyone run into this? I have no idea where to even start to troubleshoot it. The postgres service was definately running prior to the lock up. ...
Does anybody know how psql accesses PostgreSQL database? What it uses odbc, jdbc or some native database library? ...
Hey, I am trying to create a system to match wildcard domain names. Although the query above will work for me is it possible to index it at all? I don't have access to the DB to do any clever stuff as I am using Heroku to host my app but I can add indexes easy enough? Thanks! Edit: I would like to match wildcard domain names in my da...
Hello All, Was wondering if someone could assist with some Postgres. I have a table which has a column called mydate which is a postgres date type. I want to do something like: SELECT * FROM MyTable WHERE mydate > [Today-1year] I've never used Postgres before and I'm sure I just need to know the name of some functions- I'll gladly l...
So, it seems to me like a query on a table with 10k records and a query on a table with 10mil records are almost equally fast if they are both fetching roughly the same number of records and making good use of simple indexes(auto increment, record id type indexed field). My question is, will this extend to a table with close to 4 billio...
Hello, I'm working on my first assignment using SQL on our class' PostgreSQL server. A sample database has the (partial here) schema: CREATE TABLE users ( id int PRIMARY KEY, userStatus varchar(100), userType varchar(100), userName varchar(100), email varchar(100), age int, street varchar(100), city varchar(100), sta...
Well, the question pretty much summarises it. My db activity is very update intensive, and I want to programmatically issue a Vacuum Analyze. However I get an error that says that the query cannot be executed within a transaction. Is there some other way to do it? ...
Here, I am unable to convert boolean value into character in postgre sql query. SELECT *FROM ltl_class_nmfc_aliases WHERE ltl_class_nmfc_aliases.id NOT IN(SELECT ltl_class_nmfc_aliases_id FROM commodities_shippeds WHERE commodities_shipped_obj_type LIKE 'ClientOffice') OR ltl_class_id IS NULL AND lower(commodity_description_alias) ...
I am looking for something similar to MySQL Proxy. The purpose is to modify incoming queries on the server. I am not looking for alternative ways to achieve the same. My best guess at the moment is to modify GridSQL, but this adds complexity and it takes time. I have asked this question before in a vastly different way and got no relevan...
I have a trigger that executes a function on table insert or update. It looks like this: CREATE OR REPLACE FUNCTION func_fk_location_area() RETURNS "trigger" AS $$ BEGIN IF EXISTS ( -- there was a row valid in area when location started SELECT * FROM location WHERE NOT EXISTS ( SELECT * FROM area...
Hello All, First of all this is a homework assignment so I'm looking for assistance, not solutions. Let me try to explain my schema. I have three tables we'll call users (with columns id and name), parties (with columns id, partydate, and user_id) and questions (with columns id, createdate, and user_id). My requirement is to show for e...
Hi fellas, I'm developing a simple articles website in brazilian portuguese language. The search feature is based on a full-text search, but it isn't returning expected results. I made this on postgresql. Here is the simplified table: Artigos -id -title -- article title -intro -- article introduction -content -- article body -publishd...