In an application I need to query a Postgres DB where I expect tens or even hundreds of millions of rows in the result set. I might do this query once a day, or even more frequently. The query itself is relatively simple, although may involve a few JOINs. My question is: How smart is Postgres with respect to avoiding having to seek ar...
Can this be done in PGSQL? I have a view which I created where hostname,ip, and datacenter are from one table, and ifdesc and if stats from another table. the view output looks like this: hostname | ip | datacenter | ifdesc | ifadminstat | ifoperstat| ---------- -------------------------------------------------------------...
What is the best Django syncdb crash debugging technique ? I've previously asked a question about a problem with manage.py syncdb returning an exception and the answer was that the app has a wrong import. http://stackoverflow.com/questions/2734721/django-manage-py-syncdb-not-working I'd like to know the technique used to find the plac...
I'm running a Django project on Postgresql 8.1.21 (using Django 1.1.1, Python2.5, psycopg2, Apache2 with mod_wsgi 3.2). We've recently encountered this lovely error: OperationalError: FATAL: connection limit exceeded for non-superusers I'm not the first person to run up against this. There's a lot of discussion about this error, speci...
I need to transfer some data from another Database. The old database is called paw1.moviesDB and the new database is paw1. The schema of each table are the following Awards (name of the table)(new DB) Id [PK] Serial Award Nominations (name of the table) (old DB) Id [PK] Serial nominations I want to copy the data fr...
Hello! Is there any way in PostgreSQL to convert UTF-8 characters to "similar" ASCII characters? String glāžšķūņu rūķīši would have to be converted to glazskunu rukisi. UTF-8 text is not in some specific language, it might be in Latvian, Russian, English, Italian or any other language. This is needed for using in where clause, so it m...
In the title is what I need. CREATE TABLE newTable1 AS SELECT t2.name,t2.the_geom2 FROM t1,t2 WHERE ST_Contains(ST_Expand(t2.the_geom2,0.05),t1.the_geom1) and t1.gid=2; CREATE TABLE newTable2 AS SELECT t1.the_geom,t1.label FROM t1 WHERE t1.gid=2; First query result is all points within polygon and apart from it for 5min where ...
Hi, I'm doing massive tests on a Postgres database... so basically I have 2 table where I inserted 40.000.000 records on, let's say table1 and 80.000.000 on table2 after this I deleted all those records. Now if I do SELECT * FROM table1 it takes 199000ms ? I can't understand what's happening? can anyone help me on this? ...
IMPORTANT NOTE: I recieved many answers and I thank you all. But all the answers are more comments than answers. My question is related on the number of roundtrips per RDBMS. An experienced person told me that MySQL has less roundtrips than Firebird. I would like that the answer stays in the same area. I agree that this is not the first ...
Hi, I'm having strange error with installing fixture from dumped data. I am using psycopg2, and django1.1.1 silver:probsbox oleg$ python manage.py loaddata /Users/oleg/probs.json Installing json fixture '/Users/oleg/probs' from '/Users/oleg/probs'. Problem installing fixture '/Users/oleg/probs.json': Traceback (most recent call last):...
I have a trigger function that I only want to fire on certain instances of INSERTS, in this case, if do_backup = true. If it fires in all instances, I get an infinite loop. The logic seems pretty simple to me, and the rest of the function works. But the trigger function does not seem to register my conditional and always runs, even when ...
This is probably a super simple question, but I'm struggling to come up with the right keywords to find it on Google. I have a Postgres table that has among its contents a column of type text named content_type. That stores what type of entry is stored in that row. There are only about 5 different types, and I decided I want to change ...
Okay no seriously, if a postgresql guru can help out I'm just getting started. Basically what I want is a simple table like such: CREATE TABLE schema.searches ( search_id serial NOT NULL, search_query character varying(255), search_count integer DEFAULT 1, CONSTRAINT pkey_search_id PRIMARY KEY (search_id) ) WITH ( OIDS=FALSE ...
I need to create postgresql function CREATE FUNCTION date_ranges (_start date, end date) RETURNING TABLE(day_in_range date) AS... if I call date_ranges('2010-06-01', 2010-06-05') I should receive 2010-06-01 2010-06-02 2010-06-03 2010-06-04 2010-06-05 Any Ideas how to do it? ...
Although I found PL/Java a powerful add-on for PostgreSQL, I couldn't find a way of remotely debug the clases loaded on the PSQL DBMS. Is this possible to achieve? Thanks in advance! ...
I think I'm going to use crontab to run a bunch of scripts that will: close all expired posts accept uncontested disputes add interest charges email out invoices send "about to expire" notifications I want the expired stuff to be removed pretty shortly after the event occurs, so I'm thinking about writing one script that will run and...
I recently created a SQL dump of a database behind a Django project, and after cleaning the SQL up a little bit was able to restore the DB and all of the data. The problem was the sequences were all mucked up. I tried adding a new user and generated the Python error IntegrityError: duplicate key violates unique constraint. Naturally I ...
I've got a simple web app that should store data into my postgreSQL database. There's a class "Person" which is annotated as @Entity, there's a JDBC Connection Pool and a JDBC Resource. When I try to deploy it by the command asadmin deploy --name=miniejb --force=true --dropandcreatetables=true ~/workspace/miniejb/bin I get error me...
PostgreSQL has allowed me to name a column "campaign_$". I like the name because it's short and to the point, and other potential names like "campaign_receipts" seem longer and less clear. BUT, I wonder if I'll eventually regret putting a $ symbol in a column name, either in PHP or in some other distant part of the architecture. Shoul...
I've read like 10 or so "tutorials", and they all involve the same thing: Pull a count of the data set Pull the relevant data set (LIMIT, OFFSET) IE: SELECT COUNT(*) FROM table WHERE something = ? SELECT * FROM table WHERE something =? LIMIT ? offset ?` Two very similar queries, no? There has to be a better way to d...