SQL explain plan: what is Materialize?
I asked PostgreSQL to explain my query. Part of the explanation was: table_name --> Materialize What does materialize do? I'm joining two tables, not views or anything like that. ...
postgresql
Installing psycopg2 (postgresql) in virtualenv on windows
I installed psycopg2 in virtualenv using easy_install psycopg2. I did not see any errors and looks like installation went fine.. there is an egg file created in the site-packages dir for psycopg2.. but when I run import psycopg2 in the interpreter, I am getting following error.. any clue? How can I fix it.. any other way to install psyc...
Making Postgres SQL minimal size. How?
I want to cut Postgres to its minimal size for purpose of including just database function with my application. I'm using Portable Postgres found on internet. Any suggestions what I can delete from Postgres installation which is not needed for normal database use? ...
Put indexes on a table with lots of BOOL/TINYINT?
An old mentor once told me to place indexes on most things you use a WHERE clause for. Should I put an index on BOOL/TINYINT's? There is a significant amount of them in the table I am working with, and often results are filtered by anywhere from 1-20 of these boolean conditions. ...
Symmetric ds issue
Hi , I am using postgresql8.3 and include symmetris ds 1.5.1 in my application.But the replication is done fine for client to server . but the replication is not done from server to client. I am newer to use the symmetric ds .Can anyone plese let me know the checklist of using symmetric ds for cheching that my symmetric ds is well config...
Where is temporary table created?
Where can I find created temporary table in posgresql folders?!? If I do select * from temp_table; than I got result, but cannot see it structure of my database in the PgAdmin?! ...
Encoding in SQL to CSV
When do I execute query COPY TO ... CSV, I create CSV file. BUt when open it column with names in excel that should be with national characters are not as it should be. So my question is, If it is possible within a sql query to change this encoding to utf8? Or something else? Because I want that new created CSV file to be as final produc...
pg_dump and pg_restore: input file does not appear to be a valid archive
I have used pg_dump on one machine and copied result file to another, where I tried to restore it. I believe schema is the same. However, I get: pg_restore: [archiver] input file does not appear to be a valid archive I have done following operations: pg_dump -a -f db.txt dbname and: pg_restore -a -d dbname db.txt What might be w...
django, postgresql: moving dumped database data from one machine to another
I am trying to move dumped data from postgresql database to another. However, when syncdb is run on django, it inserts some values into the database. When I then want to insert the dump into the database, I get conflicts. What is the right way of moving some data from one django built database to another? ...
Postgresql: Implicit lock acquisition from foreign-key constraint evaluation
So, I'm being confused about foreign key constraint handling in Postgresql. (version 8.4.4, for what it's worth). We've got a couple of tables, mildly anonymized below: device: (id, blah, blah, blah, blah, blah x 50)… primary key on id whooooole bunch of other junk device_foo: (id, device_id, left, right) Foreign key (...
How to optimize my PostgreSQL DB for prefix search?
I have a table called "nodes" with roughly 1.7 million rows in my PostgreSQL db =#\d nodes Table "public.nodes" Column | Type | Modifiers --------+------------------------+----------- id | integer | not null title | character varying(256) | score | double precision | Indexe...
MySQL to PostreSQL and Named Scope
I've got a named scope for one of my models that works fine. The code is: named_scope :inbox_threads, lambda { |user| { :include => [:deletion_flags, :recipiences], :conditions => ["recipiences.user_id = ? AND deletion_flags.user_id IS NULL", user.id], :group => "msg_threads.id" }} This works fine on my local copy of the app wit...
problem with TEMPORARY TABLE
Within PHP I do: 1.) A temporary table is created: CREATE TEMP TABLE new_table AS SELECT .... FROM ...; 2.) AFter that I want to use this table to create a shape file: shell_exec ("pgsql2shp .... -u username -P password ...); Separetly those two things work, but by creating a temporary table and after that using this table in pgsql2sh...
I'm trying to install psycopg2 onto Mac OS 10.6.3; it claims it can't find "stdarg.h" but I can see it's there; what should I do?
I'm desperately trying to successfully install psycopg2 but keep running into errors. The latest one seems to involve it not being to find "stdarg.h" (see code below). However I can see with my own eyes that a file called stdarg.h exists at /Developer/SDKs/MacOSX10.4u.sdk/usr/include/stdarg.h (where it claims it can't find anything) so I...
n-grams from text in PostgreSQL
I am looking to create n-grams from text column in PostgreSQL. I currently split(on white-space) data(sentences) in a text column to an array. enter code hereselect regexp_split_to_array(sentenceData,E'\s+') from tableName Once I have this array, how do I go about: Creating a loop to find n-grams, and write each to a row in another t...
How to find all points away from some polygon?
What I need is to find all points away from rectangle for 10km. Points geometry is the_geom1, rectangles (polygon) geometry is the_geom2. SRID of them is 4258. I tried: SELECT * FROM table1,table2 WHERE ST_DWithin(table1.the_geom1,table2.the_geom2,10000) and table1.gid=2; But the result is not Ok. ...
postgresql is incrementing an update by 2 ?
I'm migrating our model to postgresql for the FTS and data integrity update myschema.counters set counter_count= (counter_count+1) where counter_id =? Works as expected in mysql, however in postgres it is incrementing by 2 each time? It is simple int field I believe, I don't have anything special going on. ...
Continents/Countries borders in PostGIS (Polygon vs Linestring)
Hello guys, I would like to insert the polygon containing Europe in my PostGIS database. I have the follwoing extremes points: NW = NorthWest Border(lat=82.7021697 lon=-28.0371000) NE = NorthEast Border(lat=82.7021697 lon=74.1357000) SE = SouthEast Border(lat=33.8978000 lon=74.1357000) SW = SouthWest Border(lat=33.8978000 lon=-28....
Activerecord-PostgreSQL Adapter Error
When running "rake db:migrate", i receive the following error: Please install the postgresql adapter: gem install activerecord-postgresql-adapter (dlopen(/Library/Ruby/Gems/1.8/gems/pg-0.9.0/lib/pg_ext.bundle, 9): no suitable image found. Did find: /Library/Ruby/Gems/1.8/gems/pg-0.9.0/lib/pg_ext.bundle: mach-o, but wrong architecture ...
Escaping slash in Postgresql
I'm trying to write an sql function in Postgresql that will parse a file path. I want to return just the file name. I cannot get past getting an accurate text string in the function. Here is the function: Function: job_page("inputText" text) DECLARE $5 text; BEGIN $5 = quote_literal("inputText"); return $5; END When I run this: sele...