postgresql

Problem building Postgis 1.5.x for Pg 8.4 on Ubuntu 9.10

Here are things installed: $ sudo apt-get install postgresql-server-dev-8.4 libpq5 libpq-dev Here is a past to my config.out: http://pastebin.com/8Nk6pr96 And, here are some hints I got from IRC (names concealed) < foo> it's NOT failing to find libpq. < foo> libpq is present, but not compilable without adding a boatload of other -l ...

Where postgresql logs are on mac os x?

I would like to take a look to postgresql log files to see what my app tells him but I can't find them. Any ideas? ...

How can I call a PL/Perl function from another PL/Perl function?

CREATE FUNCTION foo() RETURNS text LANGUAGE plperl AS $$ return 'foo'; $$; CREATE FUNCTION foobar() RETURNS text LANGUAGE plperl AS $$ return foo() . 'bar'; $$; I'm trying to compose results using multiple functions, but when I call foobar() I get an empty result. ...

Updating specific area of table in postgresql

Ok I am trying to update a specific area of a table in postgresql I want it to find the user that goes along with the table and then update the information I have like in this case the email is the user name that it needs to look for. it needs to add in areas like $aboutSelf, $hobbies, $music, $tv, $sports so ya I have no idea how t...

PHP error can't figure it out something to do with SQL stuff I think

Ok the error is showing up somewhere in this here code if($error==false) { $query = pg_query("INSERT INTO chatterlogins(firstName, lastName, gender, password, ageMonth, ageDay, ageYear, email, createDate) VALUES('$firstNameSignup', '$lastNameSignup', '$genderSignup', md5('$passwordSignup'), $monthSignup, $daySignup, $yearSi...

How can I execute pl/pgsql code without creating a function?

With SQL Server, I can execute code ad hoc T-SQL code with full procedural logic through SQL Server Management Studio, or any other client. I've begun working with PostgreSQL and have run into a bit of a difference in that PGSQL requires any logic to be embedded in a function. Is there a way to execute PL/PGSQL code without creating an...

Get most left|right|top|bottom point contained in box

I'm storing Points Of Interest (POI) in PostgreSQL database, and retrieve them via PHP script to Android application. To reduce internet usage I want my mobile app to know if there are any points in the neighborhood of currently displayed area. My idea is to store bounds of the rectangle containing all points already retrieved (in other...

Problems with Date, preparedStatement, JDBC and PostgreSQL

I Have to get a movie from a PostgreSQL database that matches a given title and release date. title is a character(75) and releaseDate is a date. I Have this code: String query = "SELECT * FROM \"Movie\" WHERE title = ? AND \"releaseDate\" = ?)"; Connection conn = connectionManager.getConnection(); PreparedStatement stmt = conn.prepareS...

Can I install postgresql8.2 via command prompt or running any batch or registry file?

Hi, Is it possible to install the entire database(postgresql8.2) via command prompt or batch file or registry file bypassing the trivial procedure for installation. But then to a question comes that, how can we supply default parameters such as name,password,language,default location of database? Currently I'm working on 'Windows XP' pl...

Foreign keys with Rails' ActiveRecord::Migration?

Hello, I'm new to Ruby on Rails (I know Ruby just decently though) and looking at the Migration tools, it sounds really awesome. Database schemas can finally (easily) go in source control. Now my problem with it. When using Postgres as the database, it does not setup foreign keys. I would like the benefits of foreign keys in my schema ...

Storing info in a PostgreSQl database issue

Ok I am making a registry for my website. First page asks for some personal info if($error==false) { $query = pg_query("INSERT INTO chatterlogins(firstName, lastName, gender, password, ageMonth, ageDay, ageYear, email, createDate) VALUES('$firstNameSignup', '$lastNameSignup', '$genderSignup', md5('$passwordSignup'), $m...

Excessive httpd processes to stack up on my Rails + Apache2 + Passenger production setup?

I have a Rails + Apache2 + Postgres + Passenger application running in production mode in OSX Snow Leopard. The application serves as a data warehouse for another application in the cloud so I'm constantly getting API calls to my OSX production build. After a recent reboot, I'm finding a ton of httpd processes stacking up and eventually...

Search a string to find which records in table are inside said string

Hello, Say I have a string. Then I have a number of unique tokens or keywords, potentially a large number in a database. I want to search and find out which of these database strings are inside the string I provide (and get the IDs of them). Is there a way of using a query to search the provided string or must it be taken to applicat...

SQL Server switch to MySQL/PostgreSQL for startup?

I just checked out the licensing for SQL Server and well...i can't afford it since im funding this project myself. I have been tinkering with MySQL and PostgreSQL a bit the past few weeks and at this point I can't really decide which to go with. MySQL has a large user base and lots of people using it so finding out how to do various item...

Mulitple full joins in Postgres is slow

I have a program to use the IMDB database and am having very slow performance on my query. It appears that it doesn't use my where condition until after it materializes everything. I looked around for hints to use but nothing seems to work. Here is my query: SELECT * FROM name as n1 FULL JOIN aka_name ON n1.id = aka_name.person_i...

PostgreSQL: Table creation time

Hi all, How can I find the table creation time in Postgresql. Example , If I created a file I can find the file creation time like that I want to know the table creation time. ...

JDBC Realm: GlassFish v2.1 = OK; GlassFish v3 = fail with invaliduserreason

In my J2EE 5 application I have a JDBC Realm based security with Form method. Encrypt method is MD5 as default. The database is PostgreSQL 8.4 installed locally (or 8.3 available via lan). My app used to work finely on GlassFish v2.1 server with PostgreSQL 8.3, but now I need to deploy it on GlassFish v3. I am absolutely sure I have ...

Transitive SQL query on same table

Hey. consider d following table and data... in_timestamp | out_timestamp | name | in_id | out_id | in_server | out_server | status timestamp1 | timestamp2 | data1 |id1 | id2 | others-server1 | my-server1 | success timestamp2 | timestamp3 | data1 | id2 | id3 | my-server1 | my-server2 | success timestamp3 | time...

How Implement a system to determine if a milestone has been reached

I have a table named stats player_id team_id match_date goal assist` 1 8 2010-01-01 1 1 1 8 2010-01-01 2 0 1 9 2010-01-01 0 5 ... I would like to know when a player reach a milestone (eg 100 goals, 100 assists, 500 goals...) I would like to know also when a te...

Transaction Isolation on select, insert, delete

What could possibly go wrong with the following transaction if executed by concurrent users in the default isolation level of READ COMMITTED? BEGIN TRANSACTION SELECT * FROM t WHERE pid = 10 and r between 40 and 60 -- ... this returns tid = 1, 3, 5 -- ... process returned data ... DELETE FROM t WHERE tid in (1, 3, 5) INSERT INTO t (tid...