postgresql

Rownum in postgresql

Is there any way to simulate rownum in postgresql ? ...

Comparison of geospatial support for mongodb and postgis

I am in the process of creating a consumer facing location based mobile app that will heavily need GIS support. I have narrowed down the databases to Postgres (PostGIS) and mongodb. I was wondering how someone who has experience with both would rate the geospatial support particularly in the area of: Performance Query expressibility ...

Foreign keys in postgresql

Hi everyone, I've created some tables in postgres, added a foreign key from one table to another and set ON DELETE to CASCADE. Strangely enough, I have some fields that appear to be violating this constraint. Is this normal behaviour? And if so, is there a way to get the behaviour I want (no violations possible)? Edit: I orginaly cr...

Ruby on Rails: I installed the native pg gem on Mac OS Snow Leopard 10.6.3, but I still get an error "no such file to load -- pg"

I'm using Rails 3.0.1. I installed the native pg gem for postgresql with this command: $ export ARCHFLAGS='-arch i386' $ sudo gem install pg -- --with-pg-config=/Library/PostgreSQL/8.4/bin/pg_config When I run the gem list, I can see that pg 0.9 is installed. However when I set the postgres database adaptor and re-run the server...

how to create Postgres conversion from big5 to utf8

i use postgreSQL,in my server encoding is utf8 and at client_encoding is big5. when i insert chinese character always failed.. any idea? thanks guys ...

How to use declared parameters as component of a query in Pl/pgSQL ?

Guys, I am tryingto do a generic function in pl/pgsql. I am facing a problem that I can't figure out. See in the declare block I assigned value to some parameters. I would like to know how to use them as component of a query. (example : the BEGIN/END block, groupby_join_field) CREATE OR REPLACE FUNCTION drillThrough(whereclause any...

Why does Perl makes the system very slow when I made more than 4,000 database connections?

I was writing a code to find the speed of my database using a Perl script. My intention was to make a 4,000 database connection after each fork (which would act as a 4,000 different clients) and sleep, and I issue the update command when I get the signal but the system itself becomes very slow and almost hangs for making the connection...

Getting all records that are 3months old in Postgres

I need to find all records that are exactly 3months old in Postgres My query looks as follows. SELECT * FROM adds WHERE adtype = 'CL' AND DATEDIFF(dstart,DATE(now())) = DATE_SUB(curdate(),interval 3 month); But this does not seem to work. Any advise help with this query will be helpful. I can calculate this in PHP but want to find out...

Postgresql locks deadlock

I am developing a system using Django + Postgresql. It's my first time with postgresql but I chose it because I needed the transactions and foreign key features. In a certain view I have to lock my tables with AccessExclusiveLock, to prevent any read or write during this view. That's because I do some checks on the whole data before I s...

How do you create a random string in Postgresql?

I'd like to make a random string for use in session verification using postgresql. I know I can get a random number with "Select random()", so I tried "select md5(random())", but that doesn't work. How can I do this? ...

Postgres/PHP PDO::PDOStatement->bindParam() to character(1) field

I have code similar to the following: $data['someField'] = (isset($_POST['someField'])) ? 'Y' : 'N'; $stmt = $db->prepare("INSERT INTO public.someTable (someField) VALUES (':someField');"); $stmt->bindParam(':someField', ($data['someField']), PDO::PARAM_STR, 1); $db->beginTransaction(); $stmt->execute(); $db->commit(); The field is a ...

I'm trying to write a function for postgresql to do some string manipulation...

The purpose of the function is to take a string and, if it contains parens, delete everything in the parens. Here's what I have: CREATE FUNCTION clearmethodparams(IN qname text) RETURNS text AS $BODY$ IF position($o$($o$ in qname) = 0 THEN return qname; ELSE return substring(qname from 0 for position($p$($p$ in qn...

PostgreSQL: How to constrain more than just existence on foreign key?

In PostgreSQL, what's the simplest way to enforce more than just existence on a foreign key? For example, given the following tables: create table "bar" ( bar_id serial primary key, status boolean not null ) create table "foo" ( foo_id serial primary key, bar_id integer references "bar" ) How could f...

One big object or one small plus one big info object?

Hi, For a Ruby on Rails app, I'm having this Movie object which has several attributes. Pretty much every page needs to compare some movies to a full list of these movies sorted by ratings, so I used to cache this big sorted list. It was useful, because I could directly use this list of Movies and all their attributes without any other ...

postgresql union

I have this data in postgresql id | rate | hrv | activity | orientation | timestamp | user_id | status ----------+-----------+-----+----------+-------------+------------------------+---------+-------------- 66764728 | 72 | 1 | 0 | 90 | 2010-06-10 18:54:54+00 | 397 | t 66764729 | ...

How to optimize slow delete query (delete data which is not used in another table) in Postgresql

I have 2 tables (name(fields)): data(object_id, property_id, value_id) and string(id, value) All the data is in "string" table. "data" only refers to corresponding strings. For example, I have: data(1,2,3) data(1,4,5) data(6,4,7) string(1, 'car') string(2, 'color') string(3, 'red') string(4, 'make') string(5, 'audi') string(6, '...

Rails/ActiveRecord: Get a record made *AFTER* a particular date

So currently I have an issue where I am populating a database from xml files through my client's API. Each record has an expiration date in it, with the following format: <offer_ends_at>2010-10-20T07:59:59-04:00</offer_ends_at> Now, I need to run a query where I only get records that have an expiration in the future. My current code i...

How do I insert a CURRENT_TIMESTAMP into a PostGreSQL configured Drupal instalation

Hey there, I'm using drupal and a pgsql database, but after long searches I still can't figure out how to put a CURRENT_TIMESTAMP or now() into the database when inserting a row into a table. Putting now() on the default value of a column won't work, because drupal won't accept it on the database schema, so that's out of question. The c...

Postgresql Special Order BY

I need to use an order by on a table but keep some special rows on top. The rows that have name='developer' for example. ...

Postgres inheritance for maintainability

Edit: TMI in initial question, cut to essentials. I'm thinking of a schema to support updating entries and version tracking. This is for a slowly changing dimensions scenerio, with a twist. To support the behavior I want, the basic schema is replicated three times: public tables, private tables, and change tracking tables This...