postgresql

PHP/PostGres regexp to match only if a word in the string has 2 or more capitals, and only has letters

This seems like a simple expression, but all of my tinkering has failed in one place or another. I am pulling data out of a PostgreSQL database. I would rather filter in PostgreSQL, but if I need to do it in PHP that is fine too. The column will have a string, and I need to search for matches where any word (defined by spaces) has two...

PostGIS: register a "geometry" column without AddGeometryColumn

The usual way to create a geometry column is AddGeometryColumn, however I have to work with pre-existing columns, so I can't use that function (as far as I know). Thanks to the PostGIS docs, I can already register the column in the "geometry_columns" table, however AddGeometryColumn seems to do more than create a column and add a row in...

PostgreSQL full text search randomly dropping lexemes

(All this was done with PostgreSQL 9.0.1 on Ubuntu 10.04, FWIW.) I've tried writing my own lexize function for PostgreSQL specifically for names, based on the double metaphone algorithm. To produce the C lexize function, I started with the dict_xsyn example, and basically swapped double metaphone in for the synonym lookup. But maybe 20...

How to generate a virtual table to generate a sequence of dates in PostgreSQL?

I'd like to generate a list of dates with the hopes of joining with another table, but I don't know what syntax to use, something similar to this: SELECT dates.date, transactions.account_id, transactions.amount FROM (...) as dates LEFT JOIN transactions ON transactions.date = dates.date WHERE dates.date >= '2010-01-01' AND dat...

Query Idle Periods from Usage Information stored in SQL Database

I have a table in a PostgreSQL database which tracks usage of various resources. The (simplified) Schema of the table is that each row has a ResourceID, StartTime Timestamp and EndTime Timestamp. Each row in the table represents a timespan in which the resource was in use, so the table might look like: (Note, timestamps also include date...

Increment field with not null and unique constraint in PostgreSQL 8.3

I have a table "items" with a column "position". position has a unique and not-null constraint. In order to insert a new row at position x I first try increment the positions of the subsequent items: UPDATE items SET position = position + 1 WHERE position >= x; This results in a unique constraint violation: ERROR: duplicate key valu...

Why is JPA choosing String for JoinColumn regardless of key types?

Using the EclipseLink JPA2 implementation (not sure if it's the same with the Hibernate implementation) I have a simple structure where an Organization entity has contracts. Here's the sql I exported from postgres to create the Organization CREATE TABLE organization ( key bigint NOT NULL, version integer ); If I specify the...

pl/pgsql array as input to an aggregate function

hello. i am writing pl/pgsql function that does some statistics processing. using 8.2 of postgres. i want to use this handy aggregate function: regr_slope(Y, X) but, i have my X and Y data stored as local arrays in the pl/pgsql function: y double precision[]; x double precision[]; trouble is when i use this as a line in the pl/pgs...

are associations dependent on a database in Rails?

I tested an association on mysql as follows and it works: User.campaigns These are the associations in the Models: Campaign belongs_to :user User has_many :campaigns However, when I run it on heroku which uses Postgres, I get the following: CampaignsController#index (ActiveRecord::StatementInvalid) "PGError: ERROR: column campaig...

Very slow bitmap heap scan in Postgres

I have the following simple table that contains traffic measurement data: CREATE TABLE "TrafficData" ( "RoadID" character varying NOT NULL, "DateID" numeric NOT NULL, "ExactDateTime" timestamp NOT NULL, "CarsSpeed" numeric NOT NULL, "CarsCount" numeric NOT NULL ) CREATE INDEX "RoadDate_Idx" ON "TrafficData" USING btree ("RoadI...

changes to the schema while hibernate is running

Hi all, I'm working on a high usage system which maps a few "read only" tables and views. At various points in time I need to replace the data in these tables with a newer version by connecting to the database externally. We are using postgres 8.3+ and hibernate 3.2.6. The external "update" process will be connecting to the db via PSQL...

UPDATE each item in a column

I'm having trouble figuring out a solution to this. I have several tables in my database with attributes having a date type. However, in one of my tables I was not thinking during the design process so the attribute is not of date but is a varchar. The dates in the "incorrect" table is formatted as dd-MMM-yyyy whereas all of the other...

distance calculation between two ports with PostgreSQL and Postgis

I'm using PostgreSQL with PostGis and have shapes of all countries loaded. How can I calculate the shortes sea route between two ports (without intersecting a country shape) is there a 'standard solution'? ...

PostgreSql + Escape Enter Key from Query Statement ie Enter Key pressed betweent the data.

Suppose we have a textarea in which we put example string. Textbox contain : Earth is revolving around the Sun. But at the time of saving, I just pressed a enter key after "the sun". Now the statements in texbox :: Earth is revolving around the Sun. Now I am trying to fetch the data but unable just because of that enter key press...

FULLTEXT query with scores/ranks in Postgresql

Im new to Postgres and I dont know how to translate this MySQL query to postgres: SELECT pictures.id, MATCH (title, cached_tag_list) AGAINST ('phrase') AS score FROM pictures WHERE MATCH (title, cached_tag_list) AGAINST ('phrase') ORDER BY score DESC; ...

postgres- start process under administrator account

Is it possible to start postgres process with a user account that has "administrative" privileges? I am on windows XP. --Error shown is --- (Not really error, it is a security feature) The server must be started under an unprivileged user ID to prevent possible system security compromises. See the documentation for more information on...

postgreSQL select works but delete does not.

I'm trying to change the following query into one that deletes the returned rows. SELECT bad_rows.* FROM votes as bad_rows INNER JOIN( SELECT MIN(id) as min_id, votes.object_id, votes.user_id FROM votes WHERE is_archived=FALSE AND votes.direction < 20 GROUP BY votes.object_id, votes.user_id, votes.content_type_id having COU...

PostgreSQL's libpq: Encoding for binary transport of ARRAY[]-data ?

Hello, after hours of documentations/boards/mailinglists and no progress I may ask you: How do I 'encode' my data to use it for binary transport using libpq's PQexecParams(.) ? Simple variables are just in big endian order: PGconn *conn; PGresult *res; char *paramValues[1]; int paramLengths[1]; int paramFormats[1]; conn = PQconnectdb...

Python and Postgres on Red Hat

I've been having trouble installing psycopg2 on linux. I receive the following error when I try to import psycopg2. Python 2.6.4 (r264:75706, Nov 19 2009, 14:52:22) [GCC 3.4.6 20060404 (Red Hat 3.4.6-3)] on linux2 Type "help", "copyright", "credits" or "license" for more information. >>> import psycopg2 Traceback (most recent call last...

Installing PostgreSQL on Non-Admin Mac User Account

Simple question here. Quote from the PostgreSQL Getting Started guide: PostgreSQL can be installed by any unprivileged user; no superuser (root) access is required. Instructions on the PostgreSQL Installation Procedure: ./configure gmake su gmake install ... Notice that 'su' in there? It requires superuser access. ...