postgresql

Conversion of IF SQL from MySQL to PostgreSQL

I'm converting a PHP script as DB has been switched from MySQL to PostgreSQL. I know PG doesn't have the IF function but does have the CASE function. What is the best way to convert this MySQL statement? SELECT albums.id, albums.albumname, sum(if(((albums.id=allalbums.albumid) and (allalbums.photoid=...

Find maximum number of logged on users in SQL

Hi, I want to keep tabs on the number of concurrent users of my application. I therefore log a time_start and a time_stop. If I now want to query the database for the maximum number of logged on users and return the start date, how would I do that. The table looks like this: id | time_start | time_stop ----+---------...

Postgresql PSQL sequence stare-down. Why does this not execute to completion?

I'm in psql trying to create a table for a python admin. It's actually an extension of a working admin, so I'm adding a new table and some relations. When I enter in the following sequence, it never executes to completion: BEGIN; CREATE TABLE "work_projectinteractiveasset" ( "id" serial NOT NULL PRIMARY KEY, "name" varchar...

How to disable Postgresql messages translation

Hello, Is there a way to disable the Postgresql translation of messages? I´m running my appl and Postgresql on a pt_BR Windows machine and when a exception is thrown the error message is translated to Portuguese, like: Caused by: org.postgresql.util.PSQLException: ERRO: relação "unidade_federacao" não existe Posição: 25 I woul...

Postgres subquery, ordering by subquery

If I have a query such as : select * from tbl where id in (10, 20, 9, 4); the results returned could potentially be in this order: 4, 9, 10, 20 but what if was looking to maintain the order of the list passed into the initial query? how would you approach this? Ultimately I'm using Django as the ORM for my app here but I'm looking ...

In postgresql; clarification on CONSTRAINT foo_key PRIMARY KEY (foo)

Sorry if this is a dead simple question but I'm confused from the documentation and I'm not getting any clear answers from searching the web. If I have the following table schema: CREATE TABLE footable ( foo character varying(10) NOT NULL, bar timestamp without time zone, CONSTRAINT pk_foo PRIMARY KEY (foo) ); and then use the ...

Run sql script from Ruby

Hi all, Using DBI::DatabaseHandle#execute or DBI::DatabaseHandle#prepare it's not possible to run an sql script (with mutiple sql statments). It fails with the following error : ERROR: cannot insert multiple commands into a prepared statement I tried to use the "unprepared" way using DBI::DatabaseHandle#do (the doc says it "goes stra...

Postgres: Issue Creating a User

I have a database called ADB. When I create a new user/role, that user cannot, by default SEE ADB and query against the tables in it. How can I change that default? ...

How to update table with ON DELETE CASCADE without dropping and recreating

During development of a PostgreSQL database, I made foreign keys in every table. The problem is that I forgot to select ON DELETE CASCADE option. I need to set that options on all tables. It seems I can only drop and recreate every constraint manually. Does anybody know any shortcuts? ...

2 Rails Apps, 1 Database (using Heroku)

I've made 2 apps, App A and App B. App A's sole purpose is to allow users to sign up and App B's purpose is to take select users from App A email them. Since App A & B were created independently & are hosted in 2 separate Heroku instances, how can App B access the users database in App A? Is there a way to push certain relevant rows from...

Avoiding duplicated data in PostgreSQL database in Python

Hello, I am working on PostgreSQL and psycopg2. Trying to get feed data which is updated every after 10 mins and keep this feeds contents in PostgreSQL database.My target is to retrieve and print those data from that table. But facing problem as duplicate data is also stored in the database every time I run that script due to insertio...

Postgres with Npgsql get periodical error-message every 5 hours

We have an application utilizing NHibernate 2.1.2, Npgsql 2.0.8 and PostgreSQL 8.3.7. We notice that the connection-pool for our application gets trashed at regular time-intervals in our production-enviroment, not occasionally but every 5 hours. When looking at the logfile for postgres, we see this: 2010-03-08 00:02:02 CET LOG: inco...

hibernate - Postgres- target lists can have at most 1664 entries

We are using hibernate, postgres 8.3x Our entities are many to one mapped with eager fetching. We have multiple associations with Many to one mapping. As we added new columns to any other existing entities, We are getting below error: target lists can have at most 1664 entries I searched internet and they say this is due to More num...

PostgreSQL pgdb driver raises "can't rollback" exception

Hello, for some reason I'm experiencing the Operational Error with "can't rollback" message when I attempt to roll back my transaction in the following context: try: cursors[instance].execute("lock revision, app, timeout IN SHARE MODE") cursors[instance].execute("insert into app (type, active, active_revision, contents, z) valu...

Foreign/accented characters in sql query

I'm using Java and Spring's JdbcTemplate class to build an SQL query in Java that queries a Postgres database. However, I'm having trouble executing queries that contain foreign/accented characters. For example the (trimmed) code: JdbcTemplate select = new JdbcTemplate( postgresDatabase ); String query = "SELECT id FROM province WHER...

Scalable and performant way to run calculations against a database

A multitennant database for use by telsales agents, a table contains the prospect leads to call. One record is displayed to the agent at a time for him to call, after the call the agent updates the record with a coded call outcome taken from the outcome table. In this table some outcomes have a recycle time, after which the record should...

PostgreSQL: Rolling back a transaction within a plpgsql function?

Coming from the MS SQL world, I tend to make heavy use of stored procedures. I'm currently writing an application uses a lot of PostgreSQL plpgsql functions. What I'd like to do is rollback all INSERTS/UPDATES contained within a particular function if I get an exception at any point within it. I was originally under the impression tha...

SQL COUNT of COUNT

I have some data I am querying. The table is composed of two columns - a unique ID, and a value. I would like to count the number of times each unique value appears (which can easily be done with a COUNT and GROUP BY), but I then want to be able to count that. So, I would like to see how many items appear twice, three times, etc. So for...

In mysql or postgres, is there a limit to the size of an IN (1,2,n) statement?

I've got quite a few SQL statements like such: SELECT foo FROM things WHERE user_id IN (1,2,3..n) Is there a known limit to the number of elements that will safely fit in an IN clause like that? ...

Is there a PostgreSQL equivalent of SQL Server profiler

I need to see the queries submitted to a PostgreSQL server. Normally I would use SQL Server profiler to perform this action in SQL Server land, but I'm yet to find how to do this in PostgreSQL. There appears to be quite a few pay-for tools, I am hoping there is a open source variant. ...