I've spent several hours over the past few days trying to get PostgreSQL to play nice with RoR on my Mac. I've followed several tutorials using several different methods such as installing PostgreSQL manually and installing from various 1-click installers However the all the different methods I tried failed on the last step of installi...
Hi, I'm looking for the most efficient way to bulk-insert some millions of tuples into a database. I'm using Python, PostgreSQL and psycopg2. I have created a long list of tulpes that should be inserted to the database, sometimes with modifiers like geometric Simplify. The naïve way to do it would be string-formatting a list of INSERT...
I want to try to update a postgresql table with it's own values. This table contains an overview of the products sold by year and month. CREATE TABLE sales ( sector character(3), brand character(4), product character(16), syear integer, smonth integer, units_sold integer, units_sold_year integer, CONSTRAINT pk_sales_id ...
I have a log file containing search queries entered into my site's search engine. I'd like to "group" related search queries together for a report. I'm using Python for most of my webapp - so the solution can either be Python based or I can load the strings into Postgres if it is easier to do this with SQL. Example data: dog food goo...
I'm currently working on a report generation servlet that agglomerates information from several tables and generates a report. In addition to returning the resulting rows, I'm also storing them into a reports table so they won't need to be regenerated later, and will persist if the tables they're drawn from are wiped. To do the latter ...
What is the best way to list all of the tables within PostgreSQL's information_schema? To clarify: I am working with an empty DB (I have not added any of my own tables), but I want to see every table in the information_schema structure. ...
I've got some strange behaviour going on with my DB Seed. the piece of code in question is this: #seeding info about Question Types @question_types = [:name=> "Single Input", :name=> "Multiple Choice"] @question_types.each do |question_type| new_question_type = QuestionType.find_or_create_by_name(:name => question_type[:name]); end ...
I'm trying to call a PostgreSQL stored procedure from a Java app; the procedure has a DATE type parameter so I'm using a java.sql.Date type with CallableStatement.setDate(). However, executing the statement always results in an exception and the SQL logs show this: LOG: execute <unnamed>: select * from athlete.create_athlete($1,$2,$3,...
I am porting an Oracle function into Postgres PGPLSQL.. I have been using this guide: http://www.postgresql.org/docs/8.1/static/plpgsql.html CREATE OR REPLACE PROCEDURE DATA_UPDATE (mission NUMBER, task NUMBER) AS BEGIN IF mission IS NOT NULL THEN UPDATE MISSION_OBJECTIVE MO SET (MO.MO_TKR_TOTAL_OFF_SCHEDULED, ...
(This is similar to a question I asked earlier: http://stackoverflow.com/questions/2277731/porting-oracle-procedure-to-postgresql) I need to port: /* || The following private procedure will execute a dynamic pl/sql || statement passed to it. */ CREATE OR REPLACE FUNCTION DB_SHELL_UTIL_PKG.EXECUTE_STMT (stmt VARCHAR) IS v_num_...
Now that MySQL is in Oracle's hands, do you think it's a good idea to switch to using PostgreSQL for new applications instead? (Also what do you think about converting existing applications?) I've used both DB systems before and while PostgreSQL is great for it's licensing terms and standards compliance, MySQL is definitely easier to ge...
I've got a tsvector column in a table and I would like to be able to remove a lexeme from the tsvector. I've started playing around with ts_stat to rebuild a new tsvector, but it seems quite complicated. Is there a simple way to do it? ...
Hey, I have two tables: In one, I save all seats for a specific room. In the other table, data for the reservations of the seats is stored. Now, i would like to create a view, where I have a column which marks a seat as marked or unmarked. Or more abstract: I would like to give out true/false in a table column for a view, if a certain...
I am trying to add a foreign key constraint to a table in my PostgreSQL 8.4 database, but it is failing as the target field, though part of a multi-column primary key, is not in itself unique. The database has the following structure: Table 1 (names of primary IDs): PrimaryType, Name [Primary key = "PrimaryType"] Table 2 (names of c...
I have written a simple forum in PHP using PostgreSQL. The forum consists of a number of subforums (or categories, if you like) that contain topics. I have a table that stores when was the last time a user visited a topic. It's something like this: user_id, topic_id, timestamp. I can easily determine what topics should be marked as unre...
I have some tables in my postgres database, and a few of them inherit from each other. For example: CREATE TABLE parent_table(parent_table_id SERIAL PRIMARY KEY, my_col1 VARCHAR(16) NOT NULL, my_timestamp TIMESTAMP WITH TIME ZONE NOT NULL); CREATE TABLE child_table() INHERITS (parent_table); CREATE TABLE step_child_tab...
Several programs demand that the underlying DB is MySQL. Similarly, software like GridSQL demand that the underlying database is PostgreSQL. If they were integrated then, there would be a wealth of advantages including: Easier migration between the two. Possibly the ability to use MySQL replication with PostgreSQL Ability to use softw...
I have written a recursive function and depending on the output I need to select different fields. My question is now, how can I do this multiple times without having to call the function more then once? What I'm doing right now is just using the CASE WHEN... condition and checking every time what the functions return. (This is only a ps...
Hi, I have strange situation - when I'm trying to update model, that can contains attachment by Paperclip (I'm using postgres as db) with the following code I see errors: >> u.profile.avatar = nil => nil >> u.save! ActiveRecord::RecordInvalid: Avatar file size file size must be between 0 and 1048576 bytes. from /opt/local/lib/r...
hello, how do I create and manipulate information temporarily spacial? and codify so as not to overwrite and to be used in a web application. thanks for answers ...