Is it possible to change the constraint name in Postgres? I have a PK added with: ALTER TABLE contractor_contractor ADD CONSTRAINT commerce_contractor_pkey PRIMARY KEY(id); And I want to to have different name for it, to be consistent with the rest of the system. Shall I delete the existing PK constraint and create a new one? Or is th...
I'm searching an answer for this question and all I found on http://postgis.refractions.net/documentation/manual-1.3/ch04.html#id2572194 is SELECT road_id, road_name FROM roads WHERE roads_geom && GeomFromText('POLYGON((...))',-1); should I put the boundary vertexes in this SQL replacing the '...' ? ...
I have a Postgres table with about 5 million records and I want to find the closest match to an input key. I tried using trigrams with the pg_trgm module, but it took roughly 5 seconds per query, which is too slow for my needs. Is there a faster way to do fuzzy match within Postgres? ...
I have a field in a table that is boolean, a range of records have no value (not true or false). How do I write my SQL statement to find these? I have tried the following SQL statements without success: 1) SELECT * FROM table WHERE field = NULL 2) SELECT * FROM table WHERE field != TRUE AND field != FALSE Any help would be greatly ap...
In postgres we have a constraint defined that essentially allows us to limit the number of entries in a table with a certain value to one. We created this constraint: create unique index list$default$uk on list_group(visitor_uid) where list_type = 'default'; Which means that the unique constraint is only applied when the list_type='de...
A single row in a table has a column with an integer value >= 1 and must be selected however many times the column says. So if the column had '2', I'd like the select query to return the single-row 2 times. How can this be accomplished? ...
Is there any way to share same transaction between two threads in a django-based code? The problem is that I have 1.1's TestCase (those that wrap individual tests into transactions) that are intended to test code that is running in a different thread [a sort of asynchronous testing]. So these test create some data that is intended to be...
I'm trying to map java.util.Date to postgresql timestamp without time zone type. I'm using a custom sql-insert statement (because the table is partitioned and postgresql won't return number of rows updated on a normal insert), and I keep getting an error saying the function does not exist. I suspect this is due to a problem mapping the...
i've written a maintenance script for our database and would like to run that script on whichever tables most need vacuuming/reindexing during our down time each day. is there any way to determine that within postgres? i would classify tables needing attention like this: tables that need vacuuming tables that need reindexing (we find ...
does anyone know of a postgres view / function / tool that could report on the slowest and most often used slower queries? i think this would be so useful for all sysadmins. thanks! ...
Hey guys what is a good way to horizontal shard in postgresql 1. pgpool 2 2. gridsql which is a better way to use sharding also is it possible to paritition without changing client code It would be great if some one can share a simple tutorial or cookbook example of how to setup and use sharding ...
I've been working in Postgis for point in poly's and i have this function: select * from table_name where st_contains(column, st_setsrid(st_makepoint(-92.095109, 46.804100),4326)); the column is a multipoly(((points))) I'm trying to find a way to do a similar exercise with MySQL, and wondered if there were similar functions to do so. ...
Simple rails app using Postgres DB, getting 'integer out of range' error when trying to insert 2176968859. Should be an easy fix to the migrations, but I'm not sure. Right now I've got... create_table :targets do |t| t.integer :tid ... ...
I would like to use postgresql with foreign keys to define relationships in data so that other platforms/apps would also be able to easily use the same database. Having some kind of ruby DSL to define database schema with migration support would also be great. Which framework would you recommend for me? Is there some kind of framework f...
hello, it's been two days, i've checked every solution online i could find but still no help. here's the thing: my os is Windows 7 RC 64 bit. i have Qt 4.5.1 development environment and PostgreSQL 8.3 development libraries (installed via PostgrePlus one-click installer). so i have some dll's and header files, but no "libpq.lib". so acc...
Why are batch inserts faster? Is it because the connection and setup overhead for inserting a single row is the same for a set of rows? What other factors make batch inserts faster? How do batch updates work? Assuming the table has no uniqueness constraints, insert statements don't really have any effect on other insert statements in th...
I have situation, where running a query that filters by an indexed column in a partitioned table, performs a full table scan. Apparently , this is a known issue in postgresql, and it's explained in detail here. Is there a more elegant way around this other than performing a query on each partition, and then performing a UNION on all of...
I'm looking for a tool to export data from a PostgreSQL DB to an Oracle data warehouse. I'm really looking for a heterogenous DB replication tool, rather than an export->convert->import solution. Continuent Tungsten Replicator looks like it would do the job, but PostgreSQL support won't be ready for another couple months. Are there an...
Saw this in our logs on an internal test cluster: "java.lang.RuntimeException: Got exception during XAResource.end : org.postgresql.xa.PGXAException: tried to call end without corresponding start call" I've had some previous issues with getting XA transactions running smoothly under Glassfish 2.1 in a clustered deployment. Those arose...
what's the best way to emulate "insert ignore" and "on duplicate key update" with postgresql ? ...