Given I have data like the following, how can I select and group by portions of a string? Version Users 1.1.1 1 1.1.23 3 1.1.45 1 2.1.24 3 2.1.12 1 2.1.45 3 3.1.10 1 3.1.23 3 What I want is to sum up the users using version 1.1.x and 2.2.x and 3.3.x etc, but I'm not sure how I can group on a partial string in a selec...
Is it possible to alter a table to add a new column and make that column a foreign key to another table in a single command in Postgresql? "alter table x add column y id references z(id)" doesn't seem to work as I had hoped. ...
As of Postgres 8.4 the database fts does not support exact phrase match, nor does it support proximity match if given 2 terms. For example, there is no way to tell Postgres to match on content that have word #1 which is in a specified proximity of word #2. Any one know the plan of Postgres and possibly which version will phrase and proxi...
Hi Guys, I am a newbie to Postgresql and was trying with it. I have created a simple table: CREATE table items_tags ( ut_id SERIAL Primary KEY, item_id integer, item_tags_weights text[] ); where: item_id - Item Id with these tags are associated item_tags_weights - Tags associa...
Hi. I am using a PostgreSQL database, and in a table representing some measurements I've two columns: measurement, and interpolated. In the first I've the observation (measurement), and in the second the interpolated value depending on nearby values. Every record with an original value has also an interpolated value. However, there are a...
I have two tables with the following schema: CREATE TABLE sales_data ( sales_time date NOT NULL, product_id integer NOT NULL, sales_amt double NOT NULL ); CREATE TABLE date_dimension ( id integer NOT NULL, datestamp date NOT NULL, day_part integer NOT NULL, week_part integer NOT NULL, month_part intege...
Hi , I want to write a stored procedure that gets an array as input parameter and sort that array and return the sorted array. Kindly help. ...
Edits/Additions at bottom... Environment: Mac OS X 10.6 Snow Leopard PHP 5.3 Kohana 3.0.4 When I try to configure and use a connection to a postgresql database on localhost I get the following error: ErrorException [ Warning ]: mysql_connect(): [2002] No such file or directory (trying to connect via unix:///var/mysql/mysql.sock)...
I have a Java Enum: public enum Equipment { Hood, Blinkers, ToungTie, CheekPieces, Visor, EyeShield, None;} and a corresponding Postgres enum: CREATE TYPE equipment AS ENUM ('Hood', 'Blinkers', 'ToungTie', 'CheekPieces', 'Visor', 'EyeShield', 'None'); Within my database I have a table which has a column containing an array of "equi...
What is the fastest way to reorder (resort) a table so that the physical representation most closely matches the logical, using PostgreSQL? ...
Hi, we are running java6/hibernate/c3p0/postgresql stack. Our JDBC Driver is 8.4-701.jdbc3 I have a few questions about Prepared Statements. I have read excellent document about Prepared Statements But i still have a question how to configure c3p0 with postgresql. At the moment we have c3p0.maxStatements = 0 c3p0.maxStatementsPer...
I have this simple Blog model: class Blog(models.Model): title = models.CharField(_('title'), max_length=60, blank=True, null=True) body = models.TextField(_('body')) user = models.ForeignKey(User) is_public = models.BooleanField(_('is public'), default = True) When I insert a blog in admin interface, I get this err...
Background I have climate data (temperature, precipitation, snow depth) for all of Canada between 1900 and 2009. I have written a basic website and the simplest page allows users to choose category and city. They then get back a very simple report (without the parameters and calculations section): The primary purpose of the web appli...
Hi, I have a requirement where I need to store the records at rate of 10,000 records/sec into a database (with indexing on a few fields). Number of columns in one record is 25. I am doing a batch insert of 100,000 records in one transaction block. To improve the insertion rate, I changed the tablespace from disk to RAM.With that I am a...
I'm just getting to grips with JPA in a simple Java web app running on Glassfish 3 (Persistence provider is EclipseLink). So far, I'm really liking it (bugs in netbeans/glassfish interaction aside) but there's a thing that I want to be able to do that I'm not sure how to do. I've got an entity class (Article) that's mapped to a database...
I am using Postgres-8.3.7 on fedora core 2 linux box. And Postgres service is crashing. When I restart the system, it is working fine for some time. At some random time it is crashing again. What could be the possible reasons for this segfaults which are random? FATAL: the database system is in recovery mode LOG: autovacuum launcher ...
PostgreSQL comes with the excellent pgAdmin-tool for managing the database but lacks GUI data-modeling utility? I found this list: http://www.databaseanswers.org/modelling_tools.htm but don't have time to evaluate them. Which one do YOU recommend? ...
Possible Duplicate: Hidden Features of SQL Server What are those pro/subtle techniques that SQL provides and not many know about which also cut code and improve performance? eg: I have just learned how to use CASE statements inside aggregate functions and it totally changed my approach on things. Are there others? UPDATE: ...
I am having difficulty with: Listing the R packages and functions available to PostgreSQL. Installing a package (such as Kendall) for use with PL/R Calling an R function within PostgreSQL Listing Available R Packages Q.1. How do you find out what R modules have been loaded? SELECT * FROM r_typenames(); That shows the types that a...
Error of text2ltree I can't use text2ltree. Any suggestion? SELECT text2ltree(to_char(243, '09999999')) *** Error *** ERROR: syntax error at position 0 SQL state: 42601 ...