I want to record user states and then be able to report historically based on the record of changes we've kept. I'm trying to do this in SQL (using PostgreSQL) and I have a proposed structure for recording user changes like the following. CREATE TABLE users ( userid SERIAL NOT NULL PRIMARY KEY, name VARCHAR(40), status CHAR NOT ...
Why is the order of tables important when combining an outer & an inner join ? the following fails with postgres: SELECT grp.number AS number, tags.value AS tag FROM groups grp, insrel archiverel LEFT OUTER JOIN ownrel ownrel ON grp.number = ownrel.dnumber LEFT OUTER JOIN tags tags ON tags.number = ownrel.snumber...
I have a problem with an SQL query on Postgresql. This select clause is an example from a lecture on databases: 1 select t.CourseNr, t.StudentsPerCourse, g.StudentCount, 2 t.StudentsPerCourse/g.StudentCount as Marketshare 3 from (select CourseNr, count(*) as StudentsPerCourse 4 from taking 5 group by CourseNr) t, 6 ...
Hi, currently install pgsql is running on port 1486 now I want to change this port to 5433, So how I've proceed for this....????? Thanx in advance.. ...
Is it possible to change the language of system messages from PostgreSQL? In MSSQL for instance this is possible with the SQL statement SET LANGUAGE. ...
Does anyone knows of an automated tool that works for migrating table structure and data? The tool has to resolve concerns such as transforming PostgreSQL fields on cases where a direct equivalent isn't available. I.e, a field type like boolean being used. ...
I and two others are working on a project at the university. In the project we are making a prototype of a MMORPG. We have decided to use PostgreSQL as our database. The other databases we considered were MS SQL-server and MySQL. Does somebody have a good reference which will justify our choice? (preferably written during the last yea...
I'm trying to debug an application (under PostgreSQL) and came across the following error: "current transaction is aborted, commands ignored". As far as I can understand a "transaction" is just a notion related to the underlying database connection. If the connection has an auto commit "false", you can execute queries through the same ...
Hello, I need to do a query that search for a text with 'Nome % teste \ / ' as prefix. I'm doing the query using: where "name" ILIKE 'Nome a% teste \ /%' ESCAPE 'a' (using a as scape character). There is a row that match this, but this query returns nothing. Removing the slash ('Nome % teste \'), it works. But I don't see why the sla...
I have a postgres table. I need to delete some data from it. I was going to create a temporary table, copy the data in, recreate the indexes and the delete the rows I need. I can't delete data from the original table, because this original table is the source of data. In one case I need to get some results that depends on deleting X, in ...
Hi Everyone, I'm trying to get a postgres jdbc connection working in eclipse. It would be nice to use the Data Source Explorer, but for now I'm just trying to get a basic connection. What I have done so far is download the postgres JDBC connector. I then tried two different things. First, Preferences-> Data Management, I tried to add th...
I need a virgin postgresql virtual appliance. I would prefer it on linux. ...
Let's say I wanted to have an application that could easily switch the DB at the back-end. I'm mostly thinking of SQL Server as the primary back-end, but with the flexibility to go another DB engine. Firebird and PostGreSQL seem to have (from my brief wikipedia excursion) the most in common w/ SQL Server (plus they are free). How simil...
I'm trying to create an access control system. Here's a stripped down example of what the table I'm trying to control access to looks like: things table: id group_id name 1 1 thing 1 2 1 thing 2 3 1 thing 3 4 1 thing 4 5 2 thing 5 And the access control table looks like...
I'm running a mySQL database locally for development, but deploying to Heroku which uses postgres. Heroku handles almost everything, except that my case insensitive Like statements become case sensitive. I know I could use iLike statements, but my local mySQL database can't handle that. What is the best way to write a case insensitive q...
In PostgreSQL 8.3 database I have "bookings" table referencing "booking_transactions" table by ID. So that each booking belongs to a single transaction. It's possible to delete bookings from the database. How can I make sure that a "booking_transactions" row is automatically deleted when all the "bookings" referencing it are gone? I su...
I'm working on scripts that apply database schema updates. I've setup all my SQL update scripts using start transaction/commit. I pass these scripts to psql on the command line. I now need to apply multiple scripts at the same time, and in one transaction. So far the only solution I've come up with is to remove the start transaction/com...
In a web app that support more than 5000 users, postgres is becoming the bottle neck. It takes more than 1 minute to add a new user.(even after optimizations and on Win 2k3) So, as a design issue, which other DB's might be better? ...
I need to implement some kind of metric space search in Postgres(*) (PL or PL/Python). So, I'm looking for good sources (or papers) with a very clear and crisp explanation of the machinery behind these ideas, in such way that I can implement it myself. I would prefer clarity over efficiency. (*) The need for that is described better he...
Hello...my question is similar to other friend posted here...we are trying to develop an application that supports possibly terabytes of information based on a land registry in Paraguay with images and normal data. The problem is that we want to reduce the cost of operation to minimum as possible because it´s like a competition between...