I have a query which I've been using for sometime however I have a new scenario which I'm not sure how to handle. The query below extracts avg pricing from a table called availables. The problem I have now is that, the data which is contained in this table can come from one of two sources. So I added a source field to the table. Th...
I have two datasources in my Web application (principalDB and backupDB) on two Postgresql DBs, and a web container managed transaction manager (with Atomikos) for them. Spring FW and Hibernate are my building blocks for the application. The problem I am running into is that Jetty 6.1.3 web container does not seem to load the app specific...
hello, I have two table: deck(id) and card(deck,color,value) deck have those constraints: CHECK (fifty_two_cards_deck(id)) PRIMARY KEY (id) CREATE FUNCTION fifty_two_cards_deck(deck integer) RETURNS boolean LANGUAGE sql STABLE STRICT AS $_$ SELECT COUNT(*)=52 FROM card WHERE deck=$1 $_$; and card have those constraints: ...
Hi, Given a From Date, To Date, Fiscal Year system. I want to get all the split-up duration within the given duration based on the breakup asked for. Breakups are: 1) Weekly: This should be followed from Monday through Sunday. 2) Monthly: This should be 1st of a month through end of the month. 3) Quarterly Since the Fiscal ...
How to write a postgresql query for getting only the date part of timestamp field, from a table ...
I know that database locales on Postgres are responsible for proper order of national characters, proper lower/upper-casing etc. But why there are two language-neutral locales: posix and c? Is there any difference between them or is it just one neutral locale with two different names? UPDATE As Magnus Hagander states in his answer, POS...
In PostgreSQL, there is a BLOB datatype called bytea. It's just an array of bytes. bytea literals are output in the following way: '\\037\\213\\010\\010\\005`Us\\000\\0001.fp3\'\\223\\222%' See PostgreSQL docs for full definition of the format. I'm trying to construct a Perl regular expression which will match any such string. It sh...
I want to partition a very large PostgreSQL 8.3 database. Quoting the manual, Partitioning can provide several benefits: ... Seldom-used data can be migrated to cheaper and slower storage media. What's the right way to relocate tables to another media or computer? Adam ...
This is the default behavior for PostgreSQL is a search path like so: SHOW search_path; search_path -------------- "$user",public Is there a way to make it so the "$user" is case insensitive? For instance I have a user tp1, and a schema TP1... I'd like them both to be seen as "equal" Is this even possible? ...
I'm currently running a Rails migration where I am adding a datatype specific to Postgres, the tsvector. It holds search information in the form that Postgres expects for its built-in text searching capabilities. This is the line from my migration: t.column "search_vectors", :tsvector Everything seems to be working fine, and the sea...
I have two tables with similar information. Let's call them items_a and items_b. They should be one, but they are coming from different sources, so they aren't. When I full-join the two table, some rows end up with data from either one or both tables. One of the columns in both tables is category_id. I would like to cross the combined ta...
I have a PostgreSQL database with some Unicode values. For example "vaishali" in Marathi. I want to fire a query SELECT * FROM table WHERE name LIKE vaishali (I type "vaishali" in Marathi, so I first convert to unicode in my prog). But it matches nothing. Why? ...
i am using Postgre sql database in my database there is one table mumbaipropertydetails in that one column zone has unicode data. when i execute query select mumbaipropertydetails."zone" from mumbaipropertydetails; it gives output like this. "\u092A\u093F\u0902\u092A\u0930\u0940 \u0935\u093E\u0918\u0947\u0930\u0947" "\u092A\u093F\u090...
What is the postgres equivalent of the below mysql code CREATE TABLE t1 ( created TIMESTAMP DEFAULT CURRENT_TIMESTAMP, modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); CREATE TABLE t2 ( created TIMESTAMP DEFAULT CURRENT_TIMESTAMP, modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP...
I'm just starting to look into using LINQ for my database (and XML, and data object!) needs, but need to decide on which database to go with. I've been reading Pro LINQ, and it says that currently, LINQ in .NET 3.5 only supports SQL Server. I have done some googling, and have found references to using LINQ with MySQL and PostgreSQL (my...
I'm having trouble getting the reports_as_sparkline plugin working on PostgreSQL (it works fine on SQLite). Here's an example error: >> Annotation.creations_report ActiveRecord::StatementInvalid: PGError: ERROR: operator does not exist: ` character varying LINE 1: ... grouping = E'day' AND aggregation = E'count' AND `condition... ...
I have a rails application running over Postgres. I have two servers: one for testing and the other for production. Very often I need to clone the production DB on the test server. The command I'm runnig via Vlad is: rake RAILS_ENV='test_server' db:drop db:create The problem I'm having is that I receive the following error: Active...
I am doing a bulk insert of records into a database from a log file. Occasionally (~1 row out of every thousand) one of the rows violates the primary key and causes the transaction to fail. Currently, the user has to manually go through the file that caused the failure and remove the offending row before attempting to re-import. Given th...
Apparently there is a database "postgres" that is created by default on each postgresql server installation. Can anyone tell me or point me to documentation what it is used for? ...
I there a way to copy the existing schema and generate new schema with another name in the same database in postgres. ...