I'm building a Django site and is looking for a search engine. A few candidates: Lucene/Lucene with Compass/Solr Sphinx Postgresql built-in full text search MySQl built-in full text search Selection criteria: result relevance and ranking searching and indexing speed ease of use and ease of integration with Django resource requirem...
This is with Postgresql. A column in a table contains string values with punctuations. The values are "aac", ".aaa", "aa_b", etc. When this column is specified in order by clause, the order of results is almost random. The strings starting with a period should appear at the top, which doesn't happen. They appear somewhere in the middl...
In Postgre, why does select abc from (select 1) as abc produces: (1) and select * from (select 1) as abc produces: 1 That's really strange to me. Is that the case with MySQL, Oracle, etc? I spent hours figuring out why my conditions were failing... ...
I'm using Postgres and I'm trying to write a query like this: select count(*) from table where datasets = ARRAY[] i.e. I want to know how many rows have an empty array for a certain column, but postgres doesn't like that: select count(*) from super_eds where datasets = ARRAY[]; ERROR: syntax error at or near "]" LINE 1: select count...
I want to add a constraint to my table student such that any value entered in its majors column must also exist in majors table. How can I do this? ...
How can I use select * from some_table in a function in postgres? ...
I need to make a select query with joins. This should be written in a function. My approach didn't work: CREATE OR REPLACE FUNCTION test2() RETURNS SETOF record AS' DECLARE r record; BEGIN for r in SELECT * FROM messages_wall INNER JOIN location ON messages_wall.id = location.id loop return next r; end ...
Hi everyone, I have 2 machines sitting on my desk and connected to the same IP network. I'm trying to connect an application from one machine to the postgres database on the other. At first the host with the application on it was giving me the PSQLException: FATAL: no pg_hba.conf entry for host "192.168.1.18", user "user1", database "...
Given 2 timestamps in postgres, how do you calculate the time difference without counting whole Saturdays and Sundays? OR How do you count the number of Saturdays and Sundays in a given time interval? ...
Hello! Which database is more suitable for a limited VPS: MySQL or PostgreSQL ? I am developing a small hobby project which I plan to host in a limited XEN based VPS. At work we use PostgreSQL but we also use dedicated servers for DB and our hardware are very good. I've never had an extensive experience with MySQL but wouldn't it be mo...
I got this function to concat fields in my pgSQL-server: BEGIN IF acc IS NULL OR acc = '' THEN RETURN instr; ELSE RETURN acc || ';' || instr; END IF; END; It works great, but now I want this function to distinct equal entries. How can I do this? ...
Hi guys, I'm just looking for a management tool like TOAD for Postgres. Anyone used a good one? Edit - I work mostly within the data itself and the database already has a mature model/design. I use the edit windows the most (well, in TOAD for Oracle anyway.) As far as I know, Toad only exists naturally for: Oracle, MS SQL, DB2 and My...
I have a PostgreSQL DB on a remote VPS server (CentOS 5) and I'd like to connect to have a Rails application connect to it from my local Mac laptop. On my laptop, I have the ActiveRecord PostgreSQL adapter installed -- postgres (0.7.9.2008.01.28). I read in the PostgreSQL docs: The password-based authentication methods are md5, cryp...
Hi! Is it possible to return multiple result sets from a Postgres function, like in MSSQL: ` CREATE PROCEDURE test AS SELECT * FROM first_table SELECT * FROM second_table ` ...
In postgresql, you can use the && operator to return t (true) if two arrays have common members, i.e. they overlap. Is there a function/operator that will return what those common members are? i.e. something like this select arrray_intersection(ARRAY[1, 4, 2], ARRAY[2, 3]); ARRAY[2] ...
I need to measure the performance of a hard disk using python. What is the best/fastest/shortest/easiest approach to do it? It doesn't have to be overly accurate, just a ballpark value. My actual goal is to write a small utility which will adjust the postgres settings to the best configuration for the given hardware. My naive approach ...
I need to programattically insert 10's of millions of records into a postgres database. Presently I am executing 1000's of insert statements in a single "query". Is there a better way to do this, some bulk insert statement I dont know about? ...
Consider the following text from the PostgreSQL documentation on binary copying: 11-byte sequence PGCOPY\n\377\r\n\0 — note that the zero byte is a required part of the signature. (The signature is designed to allow easy identification of files that have been munged by a non-8-bit-clean transfer. This signature will be ch...
My state model looks like this: class State < ActiveRecord::Base belongs_to :country named_scope :order_by_name, :order => :name validates_presence_of [:country, :name] def <=>(other) name <=> other.name end end My country model looks like this: class Country < ActiveRecord::Base has_many :states named_scope :ord...
I'd like to create a user in PostgreSQL that can only do SELECTs from a particular database. In MySQL the command would be... GRANT SELECT ON mydb.* TO 'xxx'@'%' IDENTIFIED BY 'yyy'; What is the equivalent command or series of commands in PostgreSQL? I tried... postgres=# CREATE ROLE xxx LOGIN PASSWORD 'yyy'; postgres=# GRANT SELECT...