This is a cross post from an email I sent to the PostGIS mailing list So far in my endeavor to create a line between a point and its projected location on a line has been long but I'm almost there. As of yesterday, and before including any nearest neighbor analysis, I got the results shown in this image: As you can see, each point in...
Is there any way to increase the size of pgsql/data? I believe that my database is crashing because I have run out of space. ...
Two part question. Using postgres in python. 1) How do I replace all fields from the same column that match a specified value? For example, let's say I want to replace any fields that match "green" with "red" in the "Color" column. 2) How to delete all fields from the same column that match a specified value? For example, I'm trying to...
I've see an article about how to change the backend database of openldap in linux,one of step in that guide is compile openldap with the --enable-sql option. But I'am working on windows and I only got a binary-version of openldap, could I use postgres to change the original and how? ...
I have a table in Postgresql, I run a query on it with several conditions that returns multiple rows, ordered by one of the columns. In general it's: SELECT <some columns> FROM mytable <maybe some joins here> WHERE <various conditions> ORDER BY date DESC Now I'm only interested in getting the first and the last row from this query. I ...
On a project I work I have sometimes have to deal with changes on the table structure, like adding fields. Is there a script that can I use for generate a migration SQL file with only the changes? PHP, bash or Perl would be nice. The database is postgresql. ...
Hi, I was wandering if it's possible to create a user on a postgres database (version higher than 8.3) which could access only a certain specified schema. The problem is, that on my database I have a few schemas. If I revoke all privileges from a certain user on all schemas except one, he can still browse the database using the i.e. PgA...
Thanks to this answer I managed to come up with a temporary solution to my problem. However, with a list of 6000 points that grows everyday it's becoming slower and slower. I can't use a third party service* therefore I need to come up with my own solution. Here are my requirements: Clustering of the coordinates need to work with ...
I have a function that has a very useful name: has_useful_state(param). I have a second function that will be returning a SETOF RECORDs of these results: CREATE OR REPLACE FUNCTION set_of_useful_things(param TEXT, OUT has_useful_state) RETURNS SETOF RECORD AS $_$ BEGIN SELECT some_key, COUNT(has_useful_state(some_key)) FROM .... ...
Let's say you have a postgres 8.3 table as follows: CREATE TABLE t1 (body text, body_vector tsvector); I want to be able to search it for phrases using the full text index (GiST, GiN or both on the tsvector column). The best workaround I've been able to find is to first do the full text search on both words (boolean AND) and then do a...
I don't have SQL Server on my box, but why won't the following answer return 360 on Postgres? select exp(sum(log(val))) from (values(4),(5),(3),(6)) as tbl(val) returns 12.888075 ...
This probably sounds like a really stupid question, but how do I declare a variable for use in a PostgreSQL 8.3 query? In MS SQL Server I can do this: DECLARE @myvar INT SET @myvar = 5 SELECT * FROM somewhere WHERE something = @myvar How do I do the same in PostgreSQL? According to the documentation variables are declared simply as ...
I've just installed ArcGIS Server Enterprise Advanced with ArcSDE and PostgreSQL, on a virtual Windows Server 2008 box. After installing, I've been trying to import a feature class (stored in a shapefile) into the geodatabase. In order to do this I've created a connection to ArcSDE (not a direct database connection) using ArcCatalog -...
I'm converting a db from postgres to mysql. Since i cannot find a tool that does the trick itself, i'm going to convert all postgres sequences to autoincrement ids in mysql with autoincrement value. So, how can i list all sequences in a Postgres DB (8.1 version) with information about the table in which it's used, the next value etc w...
I have fairly long and complex SQL query that is run against PostgreSQL 8.3. Part of the query involves filtering on a range of dates ending with today, like this: where ... and sp1.price_date between current_date::date - '1 year'::interval and current_date::date and sp4.price_date between current_date::date - '2 weeks'::interval an...
I'm using PostgreSQL full text search for a project where traditional stop words ('a', 'the', 'if' etc.) should be indexed and searchable, which is not the default behaviour. For example, I might want my users to find results for the query 'to be or not to be'. The documentation indicates that I could achieve this by creating an empty ...
I have a function written in pl/pgsql which works in the same way as the one described below: CREATE FUNCTION reffunc(text) RETURNS refcursor AS ' BEGIN OPEN $1 FOR SELECT col FROM test WHERE c1=$1; RETURN $1; END; ' LANGUAGE plpgsql; I want to be able to use this with a single select command as opposed to the documented way w...
I am currently setting up a master-slave app using Ruby on Rails that needs to have a Master-Slave backend. I'm currently looking at using Slony for the replication component, and Masochism for handling the read/write connections to the different DBs. This is my first time setting up master-slave DBs with Ruby on Rails, and these are t...
I am a complete novice at php and postgres. I have been searching all over the net for an answer but no joy! I have a postgres table called workorders. It has a primary key (ident). I am trying to up date it using a php script/program. Basically the ident is the workorder number. I have written a php script where i type in the workorder ...
I want a SQL query to check if a given timestamp falls within the open hours of a business. These open hours change depending on the time of year (seasonal hours) and the business is closed for certain holidays. I have a list of these dates, although they are subject to change. What I want is a good structure for storing this informat...