postgresql

function ts_rank_cd(text, tsquery) does not exist

i'm using full text search supported by postgres, i installed acts_as_tsearch plugin and it works successfully, but when i tried it later i found an error ntimeError: ERROR C42883 Mfunction ts_rank_cd(text, tsquery) does not exist HNo function matches the given name and argument types. You might need to add explicit type any ideas ?? ...

Ruby on Rails: How to set a database timeout in application configuration?

I'd like to set my database to timeout requests that do not complete within a set amount of time, in order to prevent outlier requests from monopolizing the entire application. Is there anything I can add to my Rails configuration files in order to set this? I tried to add a line I saw often online of timeout: 5000 to my database.yml, ...

Find rows that have a field which is a substring of the search string

Given a table like so: id | value ------------------- 1 | food 2 | foot 3 | barfoo 4 | bar 5 | baz Using postgres I want to find all of the rows where the value field matches from the start of the search field. Sort of like SELECT * FROM table where 'foo' ilike value% Searchi...

Inserting Encrypted Data in Postgres via SQLALchemy

Hi, I want to encrypt a string using RSA algorithm and then store that string into postgres database using SQLAlchemy in python. Then Retrieve the encrypted string and decrypt it using the same key. My problem is that the value gets stored in the database is not same as the actual encrypted string. The datatype of column which is storin...

Stop (long) running SQL query in PostgreSQL when session or requests no longer exist?

Hi, I'm not sure where to start on addressing this issue but if I have a AJAX web application that sends requests to the server and runs long queries on the database (postgresql in my case), is there a way to stop or kill the queries if while still running, the user refreshes the page or closes the session...etc? ...

PostgreSQL throws error: "ERROR: permission denied for relation table_name"

Hey, I'm trying to import a schema-dump into PostgreSQL with 'psql -U username -W dbname < migrations/schema.psql', the Schema is imported partly, but the console throws errors like "ERROR: permission denied for relation table_name" and "ERROR: relation table_name does not exist". I've created the database like this: "createdb -O usern...

Elegant PostgreSQL Group by for Ruby on Rails / ActiveRecord

Trying to retrieve an array of ActiveRecord Objects grouped by date with PostgreSQL. More specifically I'm trying to translate the following MySQL querry: @posts = Post.all(:group => "date(date)", :conditions => ["location_id = ? and published = ?", @location.id, true], :order => "created_at DESC") I am aware that PostgreSQL ...

Performance of a SETOF returning function which returns results of another SETOF function?

I have written a function that takes two arguments and returns a SETOF result. CREATE FUNCTION foo(parentId bigint, childId bigint) RETURNS SETOF bar AS ... I would like to write two "wrappers" for this function that make it simpler to call: CREATE FUNCTION foo_parent(parentId bigint) RETURNS SETOF bar AS ... BEGIN RETURN ...

Don't more than a few dozen partitions make sense?

I store time-series simulation results in PostgreSQL. The db schema is like this. table SimulationInfo ( simulation_id integer primary key, simulation_property1, simulation_property2, .... ) table SimulationResult ( // The size of one row would be around 100 bytes simulation_id integer, res_date Date, res_...

Database SQL Compatibility

I am deploying a Ruby on Rails application that I developed with Sqlite3 to a server with either MySQL or PostgreSQL. I quickly discovered that the "group by" and "strftime" functions that I am using heavily to produce by-month rollup reports are working differently or not compatible between the various databases. I can refactor my co...

Moving a database with pg_dump and psql -U postgres db_name < ... results in "ERROR: relation "table_name" does not exist"

I moved my PostgresQL database from one hard drive to another using pg_dump -U postgres db_name > db_name.dump and then psql -U postgres db_name < db_name.dump I created the database db_name the same way in both instances. In the new database when I run my Java program with a JPA query (or a JDBC query) I get this error: "ERROR:...

How do I set the transaction isolation level in SQLAlchemy for PostgreSQL?

We're using SQLAlchemy declarative base and I have a method that I want isolate the transaction level for. To explain, there are two processes concurrently writing to the database and I must have them execute their logic in a transaction. The default transaction isolation level is READ COMMITTED, but I need to be able to execute a piece ...

what DB for high amount of inserts/sec

We're deploy an Instant messenger (AJAX), for it we write a comet server who will handle the communication now we want to store the sent messages to a DB, which DB engine one give the best performance? We talk about 5000 Insert/sec, i think MySQL and PostgreSQL was out of the game, any proposals? HamsterDB, SQLite, MongoDB ...? We don'...

Find similar strings in postgresql hosted on Heroku

Given an article's title, I would like to find all the similar articles in my blog based on the title only. How would you proceed? (I use postgresql and heroku) ...

finding most common value across multiple tables

Hello, given the following tables, how would I go about finding the most common ip address across all tables, and ideally, the number of times that ip occurs across all tables. bad_guys_1 bad_guys_2 | id | ip | | id | ip | +----+---------+ +----+---------+ | 1 | 1.2.3.4 | | 1 | 1.2.3.4 | | 2 | 2.3.4.5 | | 2...

Postgres: delete all tables or cascade a delete?

From Googling, I'm pretty sure the answer to this is "no", but anyway. Question: is it possible either to delete all rows from all tables with a single command in postgres (without destroying the database), or to cascade a delete in postgres? If not - wow. ...

Postgres: How to fire multiple queries in same time?

I have one procedure which updates record values, and i want to fire it up against all records in table (over 30k records), procedure execution time is from 2 up to 10 seconds, because it depends on network load. Now i'm doing UPDATE table SET field = procedure_name(paramns); but with that amount of records it takes up to 40 min to proc...

how to displaying full stored procedure code?

How do you view a stored procedure/function? Say I have an old function without the original definition - I want to see what it is doing in pg/psql but I can't seem to figure out a way to do that. using Postgres version 8.4.1 ...

SQL - Best way to GroupByDay from 6AM to 6AM

I need to query a large number of rows (containing a timestamp column) and aggregate the results by day. The trick is I need the aggregate functions to be grouped for each day from 6AM until next day at 6AM, not from midnight to midnight. Obviously, I can do some sort of "group by DATEPART(day,Timestamp-6 hours)" but for millions of row...

PostgreSQL indices- are these redundant?

CREATE INDEX alias_pub_idx2 ON info.palias USING btree (publisher_id, player_id, pub_player_id); CREATE INDEX alias_pub_idx3 ON info.palias USING btree (player_id); The first includes the three columns; the latter includes only the one. I'm thinking they are redundant- that the first btree index is sufficient, but I'm not ...