postgresql

trigger function

how to create trigger functions? where it called? in PostgreSQL.. ...

is view in the database updatable ?

is view in database updatable ? if yes , how ? if no, why ? ...

Postgresql optimalization question

Hi! I am wondering if there is any difference between adding LIMIT and OFFSET in plpgsql function body and function call. CREATE FUNCTION test () RETURNS record AS $body$ DECLARE BEGIN select * from producent order by id limit 5 offset 10; END; $body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; CREATE FUNCTI...

Hibernate and Postgresql - generator class in hibernate mapping file

Hi, The ids in my postgresql database are auto-incremental (there are sequences defined in a database). When creating a hibernate mapping files I set the class generator to increment: <class name="model.Names" schema="public" table="names"> <id name="id" type="int"> <column name="id"/> <generator class="increment"/> ...

Database that can handle >500 millions rows

I am looking for a database that could handle (create an index on a column in a reasonable time and provide results for select queries in less than 3 sec) more than 500 millions rows. Would Postgresql or Msql on low end machine (Core 2 CPU 6600, 4GB, 64 bit system, Windows VISTA) handle such a large number of rows? Update: Asking this q...

Oracle to Postgres data transfer

A couple of years ago I wrote a small utility to move data from an Oracle db to a Postgres db. I used Java and JDBC to accomplish this because I wanted Java to handle the data formatting for data used in a prepared statement to do the insert. The original version of the utility assumed that the table names and column names were the same...

How to split start/end time columns into discreet chunks with PostgreSQL?

We have some tables, which have a structure like: start, -- datetime end, -- datetime cost -- decimal So, for example, there might be a row like: 01/01/2010 10:08am, 01/01/2010 1:56pm, 135.00 01/01/2010 11:01am, 01/01/2010 3:22pm, 118.00 01/01/2010 06:19pm, 01/02/2010 1:43am, 167.00 Etc... I'd like to get this into a format (with ...

How to get mutiple results from group by in postgres

I have a table with some data that looks like: id | type | timestamp | 1 | 1 | 0001 | 2 | 2 | 0002 | 3 | 3 | 0003 | 4 | 2 | 0004 | 5 | 2 | 0005 | 6 | 2 | 0006 | 7 | 2 | 0007 | 8 | 2 | 0008 | 9 | 2 | 0009 | 10 | 3 | 0010 | What I need is the 5 mos...

PostgreSQL+PostGIS function that gives angular seperation for a pair of (lat,long) points.

I wrote the following code (after spending a few hours trying to get the quoting correct): CREATE FUNCTION ast3angsep(double precision, double precision, double precision, double precision) RETURNS double precision AS $$ SELECT ST_Distance( ST_GeographyFromText('SRID=4326;POINT('||$1||' '||$2||')'), ST_GeographyFromText('SRID=4326;POINT...

Databaseconnection

what id differents between CloseConnection(con) and rollbackAndCloseConnection(con)? ...

Nested transactions - Rollback scenario

Hi, A(){ con.begin; ......... ......... B(); ........ ........(con.rollback;) con.commit; } B{ con.begin; ....... ....... con.commit; } In the above code, I begin a new DB transaction at A(). It executes some transaction successfully. After that B() starts executing and it also executes some transaction successfully now the control r...

select * from foo (100 000 rows) takes 4 seconds, is this normal ?

I have postgres 8.4 installed on ubuntu server with 4 GB Ram and Intel E5504 2Ghz I've created one table create table foo ( id serial primary key, fname varchar(30), lname varchar(30) ) the insert of 10 000 rows takes about 4 seconds first time and 1 second after but the select of 100 000 rows takes 4 seconds always, select * from...

Indexing a column used to ORDER BY with a constraint in PostgreSQL

I've got a modest table of about 10k rows that is often sorted by a column called 'name'. So, I added an index on this column. Now selects on it are fast: EXPLAIN ANALYZE SELECT * FROM crm_venue ORDER BY name ASC LIMIT 10; ...query plan... Limit (cost=0.00..1.22 rows=10 width=154) (actual time=0.029..0.065 rows=10 loops=1) -> In...

How to handle group by automatically with PGSQL ?

I have to do calculations on my database. When I was using mysql, it wasn't a problem, but now that I use PGSQL, I see the following issue : I have 3 tables : dogs, users and dogs_users, the join table (it's a n-n, every dog can have multiple users and every user can have multiple dogs) When I do the following : ree-1.8.7-2010.02 > Us...

splitting strings in postgres

I have a string with some spaces in it, and would like to split on the last space, and return the part of the string before that space. Does Postgres support this? I have not been able to solve this with the usual split_part-type functions. Example : "fort worth tx" -> "fort worth" ...

No Row inserted when using hibernate example

Hi, I, trying to use Hibernate 3.5.3 with Postgresql 8.4 and PostGreSQL-8.4.701.jdbc4.jar and after transaction completed no actually data inserted into table. this is the table: CREATE TABLE doolloop2.dluser ( id bigint NOT NULL, firstname character varying(255), lastname character varying(255), email character varying(2...

equivalent of rowlock xlock holdlock (sql server) for postgres

in sql server begin tran select * from foos with (rowlock, xlock, holdlock) where id =7 ... commit tran will lock the row for reading and writing and it will hold the lock until the end of the transaction, is there an equivalent of this in postgresql ? ...

Ms Access frontend, PostgreSQL backend efficient INSERT INTO ... RETURNING ... statement

HI, I'm using MS Access as a frontend to PostgreSQL database. And I have run into dead end: How would I get return value of passthrough SQL INSERT statement? I tried something like this: strSQL = "INSERT INTO someTable (col1, col2) VALUES ('val1', 'val2') RETURNING col0" 'Create passtrhough query dynamically (another function) Create...

simple db query function

I want to be able to call a query function and based on the results they are put into an object that I can call outside the function. This is what I have: <?php function query($sql) { global $r; $database = "theatre"; $db_conn = pg_connect ("host=localhost port=5432 dbname=$database user=postgres"); if (!$db_conn): ?> <h1>Failed conn...

Postgres Exception : No results were returned by the query.

hi I am trying to insert some rows in to a table... I am using postgressql-7.2.jar. I get the following exception org.postgresql.util.PSQLException: No results were returned by the query. at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:255) I have already Googled and the possible reasons su...