postgresql

Query max number of simultaneous events

I have a simple table of events: event_id | start_time | end_time How do I query the maximum number of simultaneous events? ...

PostgreSQL Authentication Under XP

I'm using Windows XP and I've installed PostgreSQL 8.3.5-2. I can create databases via pgAdmin but not from Powershell. When I try, I get the following error: createdb: could not connect to database postgres: FATAL: password authentication failed for user "gvkv" where postgres is the server's only user account and "gvkv" is the accoun...

Why can't I do a "upper()" in my PostgreSQL database?

I created a database in PostgreSQL with "encoding = 'UTF8'", and loaded some UTF8 data in it. Selecting works fine, but when I try to do a "WHERE UPPER(name) = 'FOO'" in a query, I get an error ERROR: invalid multibyte character for locale My research seems to indicate that this is because the PostgreSQL installation was "initdb"-ed...

I would like to use PostrgeSQL with .NET. How do I go about it?

I am working on a small practice project in .NET with Sql Server 2005. I want to learn PostgreSQL and implement my project in PostgreSQL in the final version. Please advise on all the things I need to do, to setup PostgreSQL with .NET and run it successfully. Sincere thanks fsck. ...

Storing Queries in C# or use Stored Functions in Postgres?

Should I be storing the raw SQL queries in my c# code, or should I be delegating them to stored functions in the Postgres Backend? If I should be storing them in code, since they can get quite long, what is the optimal way to store them (ie. as constants in a separate static class)? Does using stored functions have any impact on deploy...

PostgreSQL full-text search vs. NHibernate.Search via Lucene.Net

I am considering whether to choose NHibernate.Search or PostgreSQL's embedded full-text search support for my current project. We are using, as you have already guessed, PostgreSQL RDBMS with NHibernate ORM on the .NET platform. What experience have you on above mentioned full-text engines? Is there any pitfalls I should be aware of? ...

How to delete table *or* view from PostgreSQL database?

Hello. I have a name of table or view in PostgreSQL database and need to delete in in single pgSQL command. How can i afford it? I was able to select form system table to find out if there any table with such a name but stuck with procedural part: SELECT count(*) FROM pg_tables where tablename='user_statistics'; ...

PostgreSQL availability and merges

Is there a PostgreSQL HA solution that can handle a splitbrain situation gracefully. To elaborate, the system i'm working on is expected to run in several areas with users close to the servers there and connectivity between the zones is known to be questionable. I'd like for the users to be able to continue using the system in a degrad...

.Net PostgreSQL Connection String

I am using PostgreSQL in a project I am working on, and one of the queries is timing out. I need to increase the timeout on the database connection, but since I am using my DAO through a complex wrapper to Active Record and NHibernate, I am not able to adjust the timeout of the command object - so I am hoping you can change the timeout ...

How can you get the active users connected to a postgreSQL database via SQL?

This could be the userid's or number of users. Thanks in advance. ...

How do I ALTER a PostgreSQL table and make a column unique?

I have a table in PostgreSQL where the schema looks like this: CREATE TABLE "foo_table" ( "id" serial NOT NULL PRIMARY KEY, "permalink" varchar(200) NOT NULL, "text" varchar(512) NOT NULL, "timestamp" timestamp with time zone NOT NULL ) Now I want to make the permalink unique across the table by ALTER-ing the table. Ca...

PostgreSQL recompile needed after upgrading to a quad-core CPU?

I recently upgraded my server running CentOS 5.0 to a quad-core CPU from a dual-core CPU. Do I need a recompile to make use of the added cores? PostgreSQL was installed by compiling from source. EDIT: The upgrade was from an Intel Xeon 5130 to an Intel Xeon 5345. ...

PostgreSQL procedural languages: to choose?

Hi again, I have been working with postgreSQL, playing around with wikipedia's millions of hyperlinks and such, for 2 years now. I either do my thing directly by sending SQL commands, or I write a client side script in python to manage a million queries when this cannot be done productively (efficiently and effectively) manually. I wo...

Trigger to update a column during insert in postgres

I have two tables (in postgres) - ads and logs. After every insert into logs table, depending on the action it should increment the count of a column in the ads table. I have tried to write a function which the trigger will call, but it throws a error while trying to create the function. I am new to postgresql triggers and function, so...

Why does my query with LIKE '%\_' return all rows and not just those ending in an underscore?

I currently have the query running on Postgres: SELECT * FROM addenda.users WHERE users.username LIKE '%\_' But rather then returning just entries ending in an underscore, I get all results back, regardless of whether it contains an underscore or not. Running the query below returns a username which is just an underscore, so the esc...

PostgreSQL temporary tables

Hi again, I need to perform a query 2.5 million times. This query generates some rows which I need to AVG(column) and then use this AVG to filter the table from all values below average. I then need to INSERT these filtered results into a table. The only way to do such a thing with reasonable efficiency, seems to by creating a TEMPORA...

Convert Oracle stored procedure using REF_CURSOR and package global variable to Postgresql or MySQL.

This package uses two unique features of Oracle, REF_CURSOR and a package global variable. I would like to port the functionality from Oracle to Postgresql or MySQL. PACKAGE tox IS /*=======================*/ g_spool_key spool.key%TYPE := NULL; TYPE t_spool IS REF CURSOR RETURN spool%ROWTYPE; /*=======================...

How do I place an IN parameter with a LIKE with a RowSet?

I have fighting to get a IN parameter to work inside of a LIKE statement now for hours! I am using a CachedRowSet, which I understand should follow the same rules as a PreparedStatement. Here is the basic query: CachedRowSet cache; String sql = "SELECT x " + "FROM Y " + "WHERE z LIKE '?__'" cache.setComm...

Best way to use a postgresql sproc (returning ref cursor) with PHP ?

Hi. I'm using stored procs and call them from PHP (I use the same procedures in a Java app too). Right now I do it this way (which works): if(!($result = pg_query($connection, 'BEGIN; SELECT '.$query.'; FETCH ALL IN '.self::$cursor.';'))) return NULL; where $query is something like "CALL create_account('foo', 'bar', 'etc')" and $cu...

SQL - min() gets the lowest value, max() the highest, what if I want the 2nd (or 5th or nth) lowest value?

The problem I'm trying to solve is that I have a table like this: a and b refer to point on a different table. distance is the distance between the points. | id | a_id | b_id | distance | delete | | 1 | 1 | 1 | 1 | 0 | | 2 | 1 | 2 | 0.2345 | 0 | | 3 | 1 | 3 | 100 | 0 | | 4 | 2 | 1...