I would like to force user to specify origin of update to some table (sometbl), eg. to specify 'local' or 'remote' (for col2) - but checking of that requirement should occur at DB level when UPDATE statement is executed so: UPDATE sometbl SET col1 = 'abc'; should throw error (exception), but: UPDATE sometbl SET col1 = 'abc', col2 = '...
I have two tables, stats and stat_log. The stat log is like this: user_id,stat_id,value,registered and It logs the value of the stats for the given times. I need every last value from every stat for a given user. So i want something like this, but with values: select stat,max(registered) from stat_log where uid = 1 group by stat; ...
I'm testing out the PostgreSQL Text-Search features, using the September data dump from StackOverflow as sample data. :-) The naive approach of using LIKE predicates or POSIX regular expression matching to search 1.2 million rows takes about 90-105 seconds (on my Macbook) to do a full table-scan searching for a keyword. SELECT * FROM P...
Hi there, We have a Postgres database which contains 2 millions entries. We have tried using equality search and it works instantly (SELECT * FROM a WHERE b = "asd") But we would like "LIKE '%asd%'" operation to be fast too. How do we do that? ...
I've found a number of resources that talk about tuning the database server, but I haven't found much on the tuning of the individual queries. For instance, in Oracle, I might try adding hints to ignore indexes or to use sort-merge vs. correlated joins, but I can't find much on tuning Postgres other than using explicit joins and recomme...
I'm creating a test which will perform some stress testing of the app which talks to a Postgres database. In order to make it replicable I'd like to recreate the database on every run. Startup method decorated with org.junit.Before annotation will drop the database (if it exists) and restore it from a dump file. Doing this form shell/com...
In Postgresql 8 why this is ok select * from prod where code like '1%' select * from prod where code like '%1' but this returns 0 rows (there are codes begining/ending with digit 1) select * from prod where code like '1%1' Update That happens in my current instalation: # psql --version psql (PostgreSQL) 8.3.7 create table a(co...
Can I use PostgreSQL or MySQL to write an application for my intranet for my company? Will I be violating any licensing if the software is not for distribution and only used within my company? Thank you ...
Hello All, a bit newb question: in my DB I've got a table with a binary field. I'd like to fetch not that whole field in a request, but rather part of it, preferably using ODBC. Is it at all possible? I'm using PostgreSQL 8.3 - in case a solution exists for that specific DBMS. Thanks. Getting data part-by-part seems to be possible with ...
Hi, I'm trying to use libpq, the client library for PostgreSQL in a Cocoa app. First thing, adding the header files: I tried adding the path to the PostgreSQL header files under User Header Search Path on my project's properties and then compile but for some reason XCode can't see them. The solution was to manually add the files and a ...
Just a quick and simple question: in PostgreSQL, how do you list the names of all stored functions/stored procedures using a table using just a SELECT statement, if possible? If a simple SELECT is insufficient, I can make do with a stored function. My question, I think, is somewhat similar to this other question, but this other question...
I need to generate a list of insert statements (for postgresql) from html files, is there a library available for python to help me properly escape and quote the names/values? in PHP i use PDO to do the escaping and quoting, is there any equivalent library for python? Edit: I need to generate a file with sql statements for execution lat...
I'm trying to import several modules that come bundled with 8.4.1 postgres, and all the commands to do so (such as contrib.import etc) do not work or cannot be found. Please help me. ...
I am getting the following exception: Caused by: org.postgresql.util.PSQLException: ERROR: character 0xefbfbd of encoding "UTF8" has no equivalent in "WIN1252" Is there a way to eradicate such characters, either via SQL or programmatically? (SQL solution should be preferred). I was thinking of connecting to the DB using WIN1252 but i...
I have been seeing quite a large variation in response times regarding LIKE queries to a particular table in my database. Sometimes I will get results within 200-400 ms (very acceptable) but other times it might take as much as 30 seconds to return results. I understand that LIKE queries are very resource intensive but I just don't und...
I'm trying to introspect a postgres 8.3 database to retrieve details of its foreign keys. Imagine I have the following schema: CREATE TABLE "a" ( "id" SERIAL PRIMARY KEY ); CREATE TABLE "b" ( "one" integer, "two" integer, "a_id" integer REFERENCES "a", PRIMARY KEY ("one", "two") ); CREATE TABLE "c" ( "id" SERIAL PRIMARY KEY, "...
How to create a backup of an POSTGRES DB using bash? ...
I've problem with default modifiers on postgres 8.4. (I think version is its not important) I've debian ubuntu. When Im creating migrations on rails AR: class CreateUserMails < ActiveRecord::Migration def self.up create_table :user_mails do |t| t.string :title, :limit=> 128, :default=> '' t.string :place, :limit=> ...
Hi, I am going to create an app for managing grid-like model - cells at [X,Y] coordinates. This model will be persisted in a RDBMS. I want to keep much of the processing right in the DB server. So I am looking for an PL/SQL library for 2D grid processing (each row represents one cell). It should know to do algorithmic things like s...
I'm busy backing up my postgres db, I run the following command pg_dump -d data -U postgres -h 127.0.0.1 -CdiOv > data.sql I however have one problem I get asked for the password, if I want to add this into a cron I do not want to get promted for the password. Is there any way to do this without the password prompt? ...