I'm using Postgres and also the Datasources plugin. Rails has a property called schema_search_path. Is there an equivalent in Grails? I have two schemas, hk and public in the database. Where can I specify a search path for the schema or a specific schema name? I was able to make this work using the syntax like the following on the i...
I am trying to get a count of each value in a table using the following SQL: SELECT col, COUNT(col) FROM table GROUP BY col (There's a WHERE clause in the real code, but it has no impact). When I run this I get results like so: a - 5 b - 4 <null> - 0 It doesn't matter how many null entries I have, it always shows a coun...
hi all i need to monitor my postgres server. i need to get an alarm if there is no change in certain tables after a given time. i've been trying to get xymon and nagios to do this and i have not been able to. please help ...
hi folks, I'm writing a shell script (will become a cronjob) that will: 1: dump my production database 2: import the dump into my development database Between step 1 and 2, I need to clear the development database (drop all tables?). How is this best accomplished from a shell script? So far, it looks like this: #!/bin/bash time=`dat...
For development I find myself needing to copy table information from one table to another quite often. I am just curious what are the easiest solutions to do this for Postgres. I have PGAdminIII but it looks like it really only support the long drawn out Backup/Restore. Is there a python or bash script somewhere or something that I ca...
I have a table with thousands of addresses as points. Is there a function in postgis that will allow me to get a bounding polygon around these points and return it as a polygon? update I am looking for a more complex polygon than just a bounding rectangle ...
I am trying to CROSS JOIN two tables, customers and items, so I can then create a sales by customer by item report. I have 2000 customer and 2000 items. SELECT customer_name FROM customers; --Takes 100ms SELECT item_number FROM items; --Takes 50ms SELECT customer_name, item_number FROM customers CROSS JOIN items; Takes 200000ms I k...
I'm on a project that uses the EclipseLink implementation of JPA to talk to a PostgreSQL database. I have a task for which PostgreSQL NOTIFY/LISTEN seems like a perfect fit. Unfortunately, I'm a JPA newb, and am struggling to figure out how to make it work. So I guess I really have two questions; answering either one will make me happ...
I'm working on a Ruby on Rails/PostgreSQL web app that a mobile device communicates with. The mobile device has the ability to post to a primary server and geographically redundant secondary server at the same time. I would to replicate my user and profile data from the primary server to the secondary server instantaneously. Is ActiveRes...
I am trying to retrieve a blob from a postgres database using the jdbc drivers. It is too big to have in memory so I want to stream it as a download. I tried using the getBinaryStream method on ResultSet, but it turns out that this method actually reads it all into memory, so doesn't work for large file. Apparently, one can use the ge...
I'm using WASCE as application server and defined a data source to PosgreSQL. I get the DB Connection from JNDI. The problem is I want to access specific method in the PostgreSQL java.sql.Connection but WASCE wrapped this connection with their own Connection. Specifically, I want to do something like this: ((org.postgresql.Connection)...
Is there a way to remove a value from an array in pgSQL? Or to be more precise, to pop the last value? Judging by this list the answer seems to be no. I can get the result I want with an additional index pointer, but it's a bit cumbersome. ...
Am trying to run a C# application which fetches data from PostgreSQL database(8.4) and am using the PostgreSQL OLE DB Provider This is my connection string string connString = @"Provider=PostgreSQL OLE DB Provider;Data Source=localhost;location=;User ID=;password=;timeout=1000"; The error am getting is The 'PostgreSQL...
hello i'm newbie in sql (postgresql) i have 2 tables as result of 2 differen selects all calls our customer contacts number contact_id and contact_id name 3213 12 12 jonh 3213 34 16 michael 3213 43 ...
Hello i'm trying to modify a table inside my postgresql database, but it says there is duplicate! what is the best way to find a duplicate value inside a table? kinda a select query? ...
Possible Duplicate: Good Postgres resources? Hi, I decided to get started on SQL and Databases management. After some research I decided to go for PostgreSQL. I'd like some advice about which book to buy on the subject. I'm looking for a book which teach you from scratch to an advanced level without taking you for a moron and...
If you have to use PostgreSQL instead of Oracle or DB2, what feature would you miss the most? I'm specially interested in features which are important for high availability OLTP applications. Thanks! Edit: I'm looking more into technical limitations. Let's assume you have the same level of expertise in PostgreSQL, Oracle and DB2. ...
I just pushed my local rails app to heroku and then ran heroku rake db:migrate This created all the tables and stuff on my heroku server. However, I have just 4 rows in a table which I would like to insert into tables just created on heroku. So I used heroku db:push But this gives me an error Auto-detected local database...
I have a table in postgis, which has 4 columns: ID, name, the_geom, SRID. I need to write an SQL query which will insert this exterior polygon (Element 1) in the table named "woods", ID = 44, the name of that exterior polygon is "oak", and SRID is "4412". The coordinate values for Element 1 and Element 2 (the hole): Element 1= [P1(6,15)...
I am trying to move from a mysql set to a postgres one, and one of the fields that I'm having trouble moving is a mysql LBLOB. I am trying to move it into a LargeObject type in postgres and I'm having some speed issues. I'm doing this in Java/Groovy and, frankly, the streaming business has me confused. I've tried two approaches: Hold...