Hi all, I need to combine to a string column and the result of a row_number() calculation into one column. What I've got is column 1, containing strings and column 2, containing the row_number() result. Now I to combine both into one. What's the way of this in postgres? I figured that a simple + or & does not work out. tia K ...
I have a bunch of text that i need to search, and it's not always nicely formatted. select to_tsvector('hello.test') @@ 'test' This returns false, because it thinks hello.test is a hostname. Is there any way to have something like all non alphanumeric characters as a stopword, so that i can GIN-index the example above as 2 words? ...
Hi there, how can i use result of Agregate function in where in Doctrine? For example i want to know user with silly much numbers. SELECT u.name, COUNT(p.id) AS users_phonenumber_count FROM users u INNER JOIN phonenumbers p ON p.user_id = u.id WHERE users_phonenumber_count > 10 GROUP BY u.id How can i access the *use...
I need to be able to import an Excel spreadsheet into a PostgreSQL database. The goal is for use with a Rails application. I have looked at the Roo gem and it takes 16 seconds to convert a 2000 line XLS file to CSV which is unacceptable. So I was wondering if I could just short-circuit the Rails application and import directly into Postg...
As a newbie to Postgresql (I'm moving over because I'm moving my site to heroku who only support it, I'm having to refactor some of my queries and code. Here's a problem that I can't quite understand the problem with: PGError: ERROR: column "l_user_id" does not exist LINE 1: ...t_id where l.user_id = 8 order by l2.geopoint_id, l_user_i...
Overview I'm building a website in django. I need to allow people to begin to add flatpages, and set some settings in the admin. These changes should be definitive, since that information comes from the client. However, I'm also developing the backend, and as such will am creating and migrating tables. I push these changes to the hu...
I'm trying to find some suggestions in optimizing a query that I'm using to fetch a large group of data. The original code that I'm working on looped through a large set of users, and calculated a date range for each one of them. It would then take that date range and query how many questions they answered and how many they got correct...
I have a Postgresql databese with data. I want to create a django app with that database. How can i import the tables to django models and/or views? ...
postgres has an array data type, in this case a numeric array: CREATE TABLE sal_emp (name text, pay_by_quarter integer[]); INSERT INTO sal_emp VALUES ('one', '{1,2,3}'); INSERT INTO sal_emp VALUES ('two', '{4,5,6}'); INSERT INTO sal_emp VALUES ('three', '{2,4,6}'); SELECT * FROM sal_emp; Result: one, {1,2,3} two, {4,5,6} three, {2,4,6}...
has anyone successfully mapped a numeric array in postgres to a numeric array in java via hibernate? sql: CREATE TABLE sal_emp (name text, pay_by_quarter integer[]); INSERT INTO sal_emp VALUES ('one', '{1,2,3}'); INSERT INTO sal_emp VALUES ('two', '{4,5,6}'); INSERT INTO sal_emp VALUES ('three', '{2,4,6}'); mapping: <hibernate-ma...
I'm creating custom forum software for a site I'm building, which includes 2 tables (that are relevant to this question): topics and posts. A post belongs to a topic, and the topic contains the subject, while each post contains the body. Here is the basic table structures with the columns relevant to my question: CREATE TABLE topics (...
Last month I've installed PostgresSql 8.4.1 and Postgis 1.4 via macports on my Mac with Leopard(10.5), and everything just worked fine. I then updated to Snow Leopard(10.6) and still everything was working fine. Yesterday I've tried to install Gimp with macports, the installation failed. So I did a "port selfupdate" which seemed to destr...
Trying to recreate my SQL Server database on PostgreSQL. Everything is ok except I can't find how to recreate this index: USE [mytablename] GO CREATE NONCLUSTERED INDEX [myindex] ON [dbo].[mytablename] ([col1],[col2]) INCLUDE ([col3],[col4]) GO Will be very grateful for help. Alexey Update: http://img38.imageshack.us/...
I have a session (SQLAlchemy) on PostgreSQL, with an active uncommitted transaction. I have just passed the session to some call tree that may or may not have issued SQL INSERT/UPDATE/DELETE statements, through sqlalchemy.orm or directly through the underlying connection. Is there a way to check whether there are any pending data-modify...
I have a table with contents that look similar to this: id | title ------------ 1 | 5. foo 2 | 5.foo 3 | 5. foo* 4 | bar 5 | bar* 6 | baz 6 | BAZ …and so on. I would like to group by the titles and ignore the extra bits. I know Postgres can do this: SELECT * FROM ( SELECT regexp_replace(title, '[*.]+$', '') AS title FROM t...
I am a Ruby newbie. I am designing a web application and plan to use Ruby for coding. This project is for learning purpose but I want to host it live to see how it works. I plan to use PostgreSQL as back-end. I want to know how to host a Ruby website. Which hosting service providers provide support for it? And I also want to know whethe...
In our application we store multiple project fundings of different donors that they insert in their respective currency, e.g. EUR for Germany, SEK for Sweden etc. The idea is to provide reports for these projects in one currency.. We are currently storing the amount of funding together with it's currency in a table like this: developme...
I’m looking for a script that allows me to define my database relations in a simple model file and have the possibility to convert this scheme definition to the explicit formats for MySQL, PostgreSQL and SQLite. The format could be similar to the definitions you would do in activerecord, so if all fails, I will somehow go with AR if I ca...
I'm trying to configure a parameterized query to the effect of: SELECT field1 FROM myTable WHERE field2 IN (1,2,3,4) The database I'm using is Postgres. This query run successfully unparameterized, but I'd like to use a parameterized query with a JdbcTemplate to fill in the list for valid field2 values (which are integers). Trying v...
I have a double precision array field dblArrayFld in a table myTable and I'd like to update it using Spring's NamedParameterJdbcTemplate (I'm using Postgres). I'm running code like this: SqlParameterSource params = (new MapSqlParameterSource()) .addValue("myarray", myDblArrayListVar) .addValue("myid", 123); namedJdbcTemplate.updat...