postgresql

How to write this function as a pL/pgSQl function ?

I am trying to implement some business logic in a PL/pgSQL function. I have hacked together some pseudo code that explains the type of business logic I want to include in the function. Note: This function returns a table, so I can use it in a query like: SELECT A.col1, B.col1 FROM (SELECT * from some_table_returning_func(1, 1, 2, 3) a...

Efficient job progress update in web application

Hi, Creating a web application (Django in my case, but I think the question is more general) that is administrating a cluster of workers doing queued jobs, there is a need to track each jobs progress. When I've done it using database UPDATE (PostgreSQL in this case), it severely hits the database performance, because each UPDATE create...

How to get the top most parent in PostgreSQL

I have a tree structure table with columns: id,parent,name. Given a tree A->B->C, how could i get the most top parent A's ID according to C's ID? Especially how to write SQL with "with recursive"? Thanks! ...

PostgreSQL cross server query?

Is there a way that I might query a database located on "Server 2" and get my data in "Server 1" ? That is return a set of records from a remote server to my local one. PS: Not cross database query on same server because I know how to do that with dblink. Update: great ty. http://www.postgresonline.com/journal/index.php?/archives/44-...

Is it possible to use Django's testing framework without having CREATE DATABASE rights?

Since I don't have a hundred bazillion dollars, my Django app lives on a shared host, where all kinds of crazy rules are in effect. Fortunately, they gave me shell access, which has allowed me to kick butts and take names. However I can't do anything about not having CREATE DATABASE rights. I'm using postgresql and have a killer test su...

Using pg_dump to only get insert statements from one table within database

I'm looking for a way to get all rows as insert statements from one specific table within a database using pg_dump in postgres. eg I have table A and all rows in table a I need as INSERT STATEMENTS, it should also dump those staements to a file Is this possible? ...

How do I insert null fields with Perl's DBD::Pg?

I have a Perl script inserting data into Postgres according to a pipe delimited text file. Sometimes, a field is null (as expected). However, Perl makes this field into an empty string and the Postgres insert statement fails. Here's a snippet of code: use DBI; #Connect to the database. $dbh=DBI->connect('dbi:Pg:dbname=mydb','mydb',...

Postgres column casting...

I have a query SELECT assetid, type_code, version, name, short_name, status, languages, charset, force_secure, created, created_userid, updated, updated_userid, published, published_userid, status_changed, status_changed_userid FROM sq_ast WHERE assetid = 7 which doesn't work and throws ERROR: operator does not exist: character vary...

SQL update statement to change the value of a field and not replace it

I'm in the process of migrating some databases. I have this table that has a couple of hundred rows, and has a filename column. To each record in this table, the filename column needs to be altered and part of a path needs to be prepended to the value that is in that field. The table is like: | 1 | filename1 | | 2 | filename2 | and n...

Postgre database ignoring created index ?!

I have an Postgre database and a table called my_table. There are 4 columns in that table (id, column1, column2, column3). The id column is primary key, there are no other constrains or indexes on columns. The table has about 200000 rows. I want to print out all rows which has value of column column2 equal(case insensitive) to 'value12'...

PostgreSQL - Why are some queries on large datasets so incredibly slow

Hello, I have two types of queries I run often on two large datasets. They run much slower than I would expect them to. The first type is a sequential scan updating all records: Update rcra_sites Set street = regexp_replace(street,'/','','i') rcra_sites has 700,000 records. It takes 22 minutes from pgAdmin! I wrote a vb.net function...

SQL indexes for "not equal" searches

The SQL index allows to find quickly a string which matches my query. Now, I have to search in a big table the strings which do not match. Of course, the normal index does not help and I have to do a slow sequential scan: essais=> \d phone_idx Index "public.phone_idx" Column | Type --------+------ phone | text btree, for table "publ...

Anyone using ASP.NET MembershipProvider with Nhibernate?

Hi, I'm trying to implement Membership controls in a mvc 2 application and i'm having trouble dealing with the MembershipUser class. I have my own data store (in Postgresql) and I'm using Nhibernate to deal with it from C#. The thing is, I have my own user class, but I can't use it with any provider I found that implements Membership, ...

Testing stored procedures

Hi , How to test procedures with record type parameters.I have a procedure which takes test_ap ,basic and user_name as inputs.where test_ap is of record/row type,basic record array type and user_name charater varying. I need to test the procedure in pgadmin. test_client(test_ap test_base, basic test_base_detail[], user_name character ...

How to count number of occurences for all different values in database column?

I have a Postgre database that has say 10 columns. The fifth column is called column5. There are 100 rows in the database and possible values of column5 are c5value1, c5value2, c5value3...c5value29, c5value30. I would like to print out a table that shows how many times each value occurs. So the table would look like this: Value(of col...

Deadlock between select and truncate (postgresql)

Table output_values_center1 (and some other) inherits output_values. Periodically I truncate table output_values_center1 and load new data (in one transaction). In that time user can request some data and he got error message. Why it ever happens (select query requests only one record) and how to avoid such problem: 2010-05-19 14:43:17 ...

column aliases in postges for calculated values

I tried to uses aliases in postgres for this query, but postgres stops and complains that ERROR: column "subtotal" does not exist SELECT SUM(price) AS subtotal, subtotal * 3.0 AS fees, (subtotal + fees) AS total FROM cart You cannot use aliases as part of your next column? ...

PostgreSQL insert on primary key failing with contention, even at serializable level

I'm trying to insert or update data in a PostgreSQL db. The simplest case is a key-value pairing (the actual data is more complicated, but this is the smallest clear example) When you set a value, I'd like it to insert if the key is not there, otherwise update. Sadly Postgres does not have an insert or update statement, so I have to e...

Optimize date query for large child tables: GiST or GIN?

Problem 72 child tables, each having a year index and a station index, are defined as follows: CREATE TABLE climate.measurement_12_013 ( -- Inherited from table climate.measurement_12_013: id bigint NOT NULL DEFAULT nextval('climate.measurement_id_seq'::regclass), -- Inherited from table climate.measurement_12_013: station_id integer...

How to use a command line parameter to supply input in subsequent prompt (batch file)

I am using batch file to run certain operation in my application. The command I am using does not take password as a parameter instead it prompts for it while running. This is coming in the way of automating this script. I would like to know how I can take password as a parameter and provide to the application when it prompts. ...