postgresql

Hibernate + Postgresql with case insensitive searching

Hi, I've been googling around for a straight-forward solution on getting our new postgresql + hibernate setup working with case insensitive search, without luck. We had previously been using mysql which has a policy on case insensative searching however Postgresql seems to lack this. For example, I'd like Hibernate/Postgresql to return ...

Retrieve Image from postgres database using PHP

I am working in PHP with Postgresql as backend. The database is already designed by others. Now I want to retrieve image from the database using PHP. But I'm not able to retrieve image from the database. I using this code to retrieve image but it shows only unreadable characters in the webpage. Sometimes it shows a small icon (like when...

connect PHP with postgreSQL8.3

Hi, I was trying to connect my PHP page to PostgreSQL, but it's not working. The code which I have tried is given below: <?php $connection = pg_connect("host=localhost dbname=mydb user=postgres password=pgsql"); if (!$connection) { echo "Couldn't make a connection!"; } ?> During the time of installation, the system aske...

Is MySQL more resistant to SQL injection attack than PostgreSQL (under Perl/DBI) ?

I am reviewing a Linux based perl web application that contains a login handler with the ubiquitous my $sth = $DB->prepare("SELECT password from passwords where userid='$userid'") or die; $sth->execute or die; ... where $userid is initialized from (unsafe, unfiltered) web user input. It is well known that the DBI documentation recomme...

JDBC converting dates to UTC

Hi all, I have some code inserting a timestamp in a Postgres table. Here is the definition of the field where the timestamp is inserted: datelast timestamp without time zone I use this Java code to update data in the field: PreparedStatement sqlStatement = connection.prepareStatement( "UPDATE datetest SET datelast = ? WHERE ...

postgresql challenge: how to select sum of one column having another column like 'ab%'

fips value scc param 1 1 123 CO 1 1.1 124 CO 1 1.2 125 notCO 2 1.3 122 CO 2 1.4 142 CO I would like to select the sum of value where scc starts with "12" and param="CO" and have them grouped by fips. SCC is unique per fips (as in multiple fips might have same s...

Postgres full text search across multiple related tables

This may be a very simplistic question, so apologies in advance, but I am very new to database usage. I'd like to have Postgres run its full text search across multiple joined tables. Imagine something like a model User, with related models UserProfile and UserInfo. The search would only be for Users, but would include information fro...

View vs ODBC query

I am designing a database in PostgreSQL on a dedicated server. I want certain complex queries to be run often, and the results to be accessible with ODBC/JDBC clients such as MS-Access or reporting software. As I see it, I have the option to either define a VIEW within PostgreSQL and import it using ODBC, or to define the query within t...

php postgresql connection

Hi i couldnt solve the above below error.. somebody please help me.. when i was trying to connect with postgresql using pg_connect(), it says that "Fatal error.. call to undefined function pg_connect..". please help me.... Thanks & regards Tismon Varghese ...

deleting and returning data in PostgreSQL

Hello, I have the following problem: my table is big enought (millions of data rows), this is temporary data which I process. I need to select max and min of one column accrding to some criteria, process this information and remove data according to the same criteria. Actually, the simplest implementation looks like: select max(col), m...

SQL to extract matlab date from postgres db

I'd like to construct a query to "convert" a postgresql datetime to a matlab datenum. Experience with other DBs has shown me that converting the date on the DB side is much faster than doing it in matlab. Matlab stores dates as number of days (including fractions) since an arbitrary epoch of a gregorian, non-existent date of 00-00-0000...

Django db_index issue

Currently, I have 3 models, A, B and C C has foreign key to B B has foreign key to A class C(models.Model): name = models.CharField(max_length=50, db_index=True, unique=True) b = models.ForeignKey(B) class B: ...similar to C class A ...similar to C except for the FK However, the SQL generated by manage.py sqlindexes app doesn...

Abstracting a date subtraction SQL query with Rails

I'm using the following as part of an ActiveRecord find method: :conditions => "created_at > date_sub(now(), INTERVAL 7 DAY)" This works properly on my development server where I'm using MySQL, but I'm deploying to postgreSQL which doesn't have a date_sub function. Ignoring the fact that I'm using two different databases, does Rails h...

Efficient querying of multi-partition Postgres table

I've just restructured my database to use partitioning in Postgres 8.2. Now I have a problem with query performance: SELECT * FROM my_table WHERE time_stamp >= '2010-02-10' and time_stamp < '2010-02-11' ORDER BY id DESC LIMIT 100; There are 45 million rows in the table. Prior to partitioning, this would use a reverse index scan and s...

Is there a way to limit a left outer join?

I have a log table with the following format (and sample data): date | time 2010/01/02 | 10:00 2010/01/02 | 13:00 2010/01/04 | 02:34 I want to make an SQL query that will retrieve results with the following format: date1 | time1 | date2 | time2 2010/01/02 | 10:00 | 2010/01/02 | 13:00 2010/01/02 | 13:00 | 2010/01/04 |...

postgresql help with create table syntax

I'm more a mysql person, but I have to do a db in pg and the following CREATE TABLE keeps generating syntax errors... I just get an error: ERROR: syntax error at or near "(" and error: ERROR: syntax error at or near ")" Googling around didn't give me much help... I'm sure that I'm doing something mysql-esque and that's causing problems...

How can I match things like "C#" and "C++" in PostgreSQL's full text search?

Unfortunately the parser discards all punctuation, so that 'C', 'C++' and 'C#' all end up as 'c'. The only thing I can think of (aside from replacing the parser which isn't really an option right now), would be to implement my own miniature synonym dictionary before using any of the FTS functions, replacing "C++" with "Cpp" and "C#" wit...

SQL - Combining multiple like queries

Hey my first question on SO! Anywho... Still relatively a newb at SQL so I think I might be missing something here. My question is I currently have a table full of phone numbers. I want to have a query where I search for phone numbers that are similar to a list I have. So for example, I want to find phone numbers that begin with '555123...

Is there a way to make a query respect the order of the inputted parameters?

(Please let me know if this is completely absurd, that is probably a reason why I haven't found anything on this.) This story has two models Ranking and Artist, Ranking is generically related to Artist (object_id, content_type... the whole shebang). I have a list of objects returned by Ranking.objects.values_list() ordered by a certain...

What's a good book for learning Postgres?

I just need a book for an intermediate/advanced database developer in MySQL and SQL Server to start learning Postgres. Any recommendations? ...