Hello all, PostgreSQL / Django / newbie here. I've got a bunch of JSON data with venues and lat/long information, and I'm looking to import it into my database. So far I've fenagled a script to format my JSON into SQL statements, so I can import them with a slew of INSERT statements. But my 'location' field (a PointField) is giving me...
I'm trying to optimise a PostgreSQL 8.4 query. After greatly simplifying the original query, trying to figure out what's making it choose a bad query plan, I got to the point where running the query under EXPLAIN ANALYZE takes only 0.5s, while running it normally takes 2.8s. It seems obvious then, that what EXPLAIN ANALYZE is showing me ...
Hello good people. In PostgreSQL I have a query like the following which will delete 250k rows from a 1m row table: DELETE FROM table WHERE key = 'needle'; The query takes over an hour to execute and during that time, the affected rows are locked for writing. That is not good because it means that a lot of update queries have to wait ...
I have a table Inputs and a derived table Parameters CREATE TABLE Configurables ( id SERIAL PRIMARY KEY ); CREATE TABLE Inputs ( configurable integer REFERENCES Configurables( id ), name text, time timestamp, PRIMARY KEY( configurable, name, time ) ); CREATE TABLE Parameters ( configurable integer, name text, time time...
Can you tell me if this is done right or if I need to improve some of them I have a hard time understanding the query's when there is an associative entity. List all donors SELECT* from Donor; List the first and last names of all donors SELECT dfname, dlname FROM donor List the phone numbers of donors number 106 and 125 SEL...
I have a decent sized PostgreSQL database (approx 6GB & growing). A full backup/export of the database is done every few hours via cron & pg_dump. Specifically, can I export only the changes to the database since the last export? Or perhaps run a utility that compares the two exports and appends the differences to the original, etc? I...
Hey guys! I'm starting to design a new application that will be used by about 50000 devices. Each device generates about 1440 registries a day, this means that will be stored over 72 million of registries per day. This registries keep coming every minute, and I must be able to query this data by a Java application (J2EE). So it need to ...
i have some functionality i'd like to get from the server-side code into the database. i can't figure out how to set the values of a multi-demensional varchar array in plpgsql. here is an example of what i'm trying to do: `CREATE OR REPLACE FUNCTION my_function (my_arg integer) RETURNS text[][] AS $$ DECLARE my_arr varchar[]...
It's not the fault of the django (iPython) shell, actually. The problem is developers who open the django shell ./manage.py shell run through some queries (it often only generates selects), and then either leave the shell running or somehow kill their (ssh) session (actually, I'm not sure if the latter case leaves the transaction open - ...
I've been using mysql forever. never really needed anything fancier. But I'm using heroku a lot and while I'm working, I like free search, so I'm using the acts_as_tsearch plugin. If you go to the git repository, it tells you: ---begin paste--- * Preparing your PostgreSQL database Add a text search configuration 'default': CREATE ...
Does Postgresql and Mysql use different SQL:s? I have a book, Head First SQL. If they are different, how do I know which database SQL it is? ...
My PostgreSQL tables have id's of type bigserial, meaning they are generated at the time rows are inserted (and thus, the id column's value is not supplied in the INSERT statement). I'm having difficulty finding the proper value for the <generator class="..."> attribute in my XML mapping file. The code below is the closest I've found t...
Could someone help me or suggest a solution? I want to connect from a computer that has firewall to other where the postgres server run. The problem is that computer (client) has a firewall and I have not access to configure it, or open ports, ping does not respond. The computer (server) where PostgreSQL has open ports but I can not conn...
I am using the above combo in a new web app Im doing just to try to learn to build new stuff to expand my knowledge. Im hoping to go live if I do a good job.. Im kind of new at MVC and the other products so I was trying to find a link to a good tutorial that set all of these up together. If anyone knows of one or maybe 2 that set up 3 of...
Hi, I work on manage.py command which creates about 200 threads to check remote hosts. My database setup allows me to use 120 connections, so I need to use some kind of pooling. I've tried using separated thread, like this class Pool(Thread): def __init__(self): Thread.__init__(self) self.semaphore = threadin...
I have a data structure that looks like this: Model Place primary key "id" foreign key "parent" -> Place foreign key "neighbor" -> Place (symmetryc) foreign key "belongtos" -> Place (asymmetric) a bunch of scalar fields ... I have over 5 million rows in the model table, and I need to insert ~50 million rows into ...
I have a database table that contains a list of demographic records, some of those participant might have multiple/duplicate records, e.g. NOTE: Gender: 119 = Male 118 = Female Race: 255 = white 253 = Asian UrbanRural: 331 = Urban 332 = Rural participantid, gender, race, urbanrural, moduletypeid, hibernateid, and more fields 1, 11...
I'm updating a table from another table in the same database - what would be the best way to count the updates/inserts? I can think of a couple of methods: Join the tables and count (i.e. inner join for update, left join where null for inserts) then perform the update/insert Use the modification date in the target table (this is maint...
Hi, I'm not particularly accustomed to generating complex SQL queries and am having difficulty in mixing my understanding of procedural languages and of set-based operations in devising a recursive query for network traversal. I wish to find the set of edges that lie 'upstream' of a particular node through conducting a depth first sear...
I've got a postgres 8.3 database of hotels, each with an associated longitude and latitude stored as a point, and a resort stored as a resort id. I'd like to find the central or average point of the resort. I can do this using a simple query: select avg(lat_long[0]) as latitude, avg(lat_long[1]) as longitude, resort_id from accomm g...