I have a table that contains intervals: CREATE TABLE tbl ( user_id: INTEGER, start: TIMESTAMP, end: TIMESTAMP, billable: BOOLEAN ); I want to create a view that looks like this: user_id | billable_time | unbillable_time I can get one of these two using a query like this: SELECT user_id, sum(end - start) AS billable...
Hello friendly Overflowers, I have GNU R installed (the S-like statistics package; version 2.8.1) and PostgreSQL (8.4.1) but I cannot connect GNU R to my RDBMS. When I first did this (years ago - code lost) DBI for R didn't exist. Now it does. I am also confused as to which R package to use. A quick search returns: - RPostgreSQL seems...
For the sake of the example, consider a table create table foo ( contents text NOT NULL, is_active boolean NOT NULL DEFAULT false, dt_active date ) I insert a record: insert into foo (contents) values ('bar') So far, so good. Later on, I now want to 'activate' the record: update foo set is_active = true What I would like t...
The npgsql ado.net provider claims to support the entity framework. Is there any documentation/guide how to set this up, and get me jump started here ? ...
I'm trying to get my dev environment back up and running after upgrading to Snow Leopard. In particular, I need postgresql and postgis working, but I'm running into the following problem. After installing both with the following: sudo port install postgresql84 postgresql84-server postgis I get errors like the following when I try to...
I just tried Ora2Pg on my Ubuntu Jaunty Jackalope. First of all, the installation was hard, but after downloading a few debs & rpms here & there, I was finally managed to install ora2pg via synaptic. However, when I try to run this command ora2pg /tmp/ora2pg.conf I am getting a install_driver(Oracle) failed: Can't load '/usr/l...
I have a multi-million row table with a varchar field. Some of the rows of the varchar are floating point numbers. There is no constraint on other fields that can completely isolate which rows are numbers and which are not. I want to create queries with an ORDER BY on the rows with the numeric value in the varchar field (ignoring non-...
trigger: CREATE TRIGGER "tr_update_ts" BEFORE INSERT OR UPDATE ON "public"."test" FOR EACH ROW EXECUTE PROCEDURE "public"."update_ts"(); and the function is: CREATE OR REPLACE FUNCTION "public"."update_ts" () RETURNS trigger AS $body$ DECLARE BEGIN NEW.ts := now(); RETURN NEW; END; $body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON ...
My votes table looks like this: id: integer vote: boolean voteable_id: integer voteable_type: string voter_id: integer voter_type: string The vote column determines whether the row represents an "up vote" (vote = true) or a "down vote" (vote = false). voteable_type is the class of the thing being voted on, voteable_id is the id of th...
Ok, you have login form and you want to ban users for 5 minutes if they get password/user wrong. User can try few times in a row until banned. LOGIN_FAILS table: id PK, serial/auto_increment ipaddress int/text/whatever you prefer added DATETIME/TIMESTAMP SQL: SELECT IF(COUNT(id) < 3, 'false', 'true') AS is_banned FROM LOGIN_FAILS W...
Hi' I'm currently evaluating many FullText indexing solutions, and I'm playing with native postgres FT. I'm trying to index my data using GIN indices. But there's a limitation in the field size, I encounter some errors saying "huge tuple" while inserting data As far as I understand, it's directly related to the field size. But this li...
I am in need of an application that can periodically transmit select rows from a PostgreSQL database across a network to a second PostgreSQL server. Typically these will be the most recent row added, pulled and transmitted every 10-30 seconds. The primary servers run in a MS Windows environment with a high-latency, and occasionally int...
I want to vacuum a PostgreSQL database from PHP. I have tried this: pg_query($conn,"vacuum analyse;"); How can I tell if this works? ...
I am changing from MySQL to PostgreSQL but can't find equivalent to MySQL's collation utf8_general_ci. What I'm trying to achieve here is to be able to compare strings using general language rules rather then binary comparison, i.e. í = i, š = s, ḩ = h, etc... Is there a way how to make PostgreSQL search for strings using general langua...
I want to to select the time of a date from database. How can I do this using postgresql database? Thanks in advance ...
i wanna to make a query that select users that have same username and same hour of creation date by using postgresql database ...
I am deploying a rails app to heroku which uses PostgreSQL as its back-end. In my database migration I normally set the ID field for things likes reports etc to at least 1000, most clients don't seem to like starting at 1. Normally I use mysql and I simply add an sql specific after my table creation: def self.up create_table :repor...
Hi, I've enabled logging in my Postgres database (running on Ubuntu 32 bits) and I'd like to log only queries that a re performed by user applications. I've configured postgres as follows: log_destination = 'syslog' syslog_facility = 'L*emphasized text*OCAL0' syslog_ident = 'postgres' log_min_messages = notice log_min_duration_statement...
When defining a database access object for use in cl-postmodern, what should the :col-type be for a Many to Many field? ...
I did some changes to a postgres table and I want to revert it back to a previous state. There is no back up of the database. Is there a way to do it? As in, does postgres take auto snap shots and store it somewhere or the original data is lost forever? ...