Re: [SQL] Your question in postgresql.org forum (Diff. between two times as a numeric value in a stored proc)

[Stijn Vanroye]

Hello Martin,

There's not much hint to it, here's a solution instead :)

You can make use of several built-in functions to handle strings, date/time values 
etc. Take a look at chapter 9: Functions and Operators  of your PostgreSQL 
documentation all available functions are explained there. 

To solve my problem I used the extract EXTRACT (field FROM source) function, as 
described in paragraph 9.8.1. 
You can use it to extract a certain field form your timestamp vield, for example:
extract (hour from timestamp '2004-16-04 09:21:52') returns 9

SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
Result: 442800
EPOCH will return the number of seconds. You can multiply those by n*60, depending if 
you want minutes or hours.
At first I used a different calculation, but this one should be less work :).

I hope you're problem is solved that way.

I took the liberty of CC-ing the postgresql mailinglist so other's having a similar 
problem can read about this solution too. It's all about the (open-source)-community 
isn't it :)


Kind regards,

Stijn Vanroye

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: dinsdag 15 juni 2004 19:08
To: Stijn Vanroye
Subject: Your question in postgresql.org forum


Hello Stijn,

I read your question in postgresql.org forum published under sunject "Difference 
between two times as a numeric value in a stored procedure"
I am currently solving the same problem... Did you solve it somehow? Can you give a 
hint please?

Best Regards,
Martin Tongel

---(end of broadcast)---
TIP 8: explain analyze is your friend

[SQL] How to delete the not DISTINCT ON entries

[Christoph Haller]

Referring to the DISTINCT ON example

SELECT DISTINCT ON (location) location, time, report
FROM weatherReports
ORDER BY location, time DESC;

How would I delete those entries skipped by the DISTINCT ON expression?

TIA

Regards, Christoph



---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] How to delete the not DISTINCT ON entries

[Achilleus Mantzios]

O kyrios Christoph Haller egrapse stis Jun 16, 2004 :

> Referring to the DISTINCT ON example
> 
> SELECT DISTINCT ON (location) location, time, report
> FROM weatherReports
> ORDER BY location, time DESC;

maybe smth like 

delete from weatherReports where (location,time,report) not in 
(SELECT DISTINCT ON (location) location, time, report FROM weatherReports 
ORDER BY location, time DESC)

Note:
Order by is very important, since it affects which rows are deleted.

> 
> How would I delete those entries skipped by the DISTINCT ON expression?
> 
> TIA
> 
> Regards, Christoph
> 
> 
> 
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
> 
>http://archives.postgresql.org
> 

-- 
-Achilleus


---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] How to delete the not DISTINCT ON entries

[Jean-Luc Lachance]

If you do not have foreign key restrinctions, create a temp table from 
the select as:

CREATE TEMP TABLE tmp AS SELECT DISTINCT ON (location) location, time, 
report FROM weatherReports ORDER BY location, time DESC;

TRUNCATE weatherReports; INSERT INTO weatherReports SELECT * FROM tmp;
HTH
Achilleus Mantzios wrote:
O kyrios Christoph Haller egrapse stis Jun 16, 2004 :

Referring to the DISTINCT ON example
SELECT DISTINCT ON (location) location, time, report
FROM weatherReports
ORDER BY location, time DESC;

maybe smth like 

delete from weatherReports where (location,time,report) not in 
(SELECT DISTINCT ON (location) location, time, report FROM weatherReports 
ORDER BY location, time DESC)

Note:
Order by is very important, since it affects which rows are deleted.

How would I delete those entries skipped by the DISTINCT ON expression?
TIA
Regards, Christoph

---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] Inheritance, plpgsql inserting, and revisions

[Phil Endecott]

Dear Postgresql Experts,

A few days ago I posted a message asking about INSERTing a row based on a RECORD 
variable in a plpgsql function.  I haven't had any replies - maybe it got lost in the 
weekend's spam - so I thought I'd post again giving some more background to what I'm 
trying to do.

1. Keeping old revisions of data.

Say I have a basic table:

create table T (
  idinteger  primary key,
  x text,
  ...
);

I can, of course, insert new rows, update existing rows, and delete rows.  But once 
I've made a change in one of those ways I have lost the previous information.  There 
is no way to "undo" or "revert" those changes.  (I'm not talking about transactions 
here.  Think longer-term data retention, more like UNDO in a wordprocessor or CVS 
history.)  In my application I want to be able to undo changes and/or to see the state 
of the data at some point in the past.

To achieve this, I never update or delete the table.  I always insert new rows.   Each 
row has an "edit date" field.  The current state of the data is found by selecting 
rows with the most recent edit date for each id.  Old rows can periodically be purged 
to keep the size of the database manageable, if necessary.  So I have this table:

create table T_d (
  idinteger,
  editdate  timestamp  default current_timestamp,
  x text,
  ...
  primary key (id, editdate)
);

Then I have a view that gets just the current state:

create view T as
select distinct on (id) id, x from T_d order by id, editdate desc;

When I select from T, I don't need to worry about the existence of this extra stuff, I 
can treat it like an "ordinary" table.  Using rules I can convert changes to T into 
inserts into T_d, again transparently:

create rule ins_T as on insert to T do instead
   insert into T_d (id, x) values (new.id, new.x);

create rule upd_T as on update to T do instead
   insert into T_d (id, x) values (new.id, new.x);

To support deletes I add an extra boolean field to T_d called deleted.  It defaults to 
false.  If the most recent revision of a row has deleted true, that row doesn't exist:

create table T_d (
  idinteger,
  editdate  timestamp default current_timestamp,
  deleted   boolean   default false,
  x text,
  ...
  primary key (id, editdate)
);

The view that deals with this is:

create view T as
  select id, x from (
select distinct on (id) * from T_d order by id, editdate desc
  ) as q where not deleted;

I can then create a rule for delete that inserts a row with deleted true:

create rule del_T as on delete to T do instead
   insert into T_d (id, deleted, x) values (old.id, true, old.x);

This all works well.  It should be applicable to any simple database design, and I'd 
recommend the approach to anyone faced with similar requirements.


2. Inheritance

In my application I'm also using inheritance.  So as well as the "base" table T that 
I've described above, I have "derived" tables M and N:

create table M_d (
  mm  text
) inherits (T_d);

create table N_d (
  nn integer
) inherits (T_d);

If I define similar views and rules, I can get the same "history recording" for these 
derived tables as well... almost.

It's fine when I access M and N themselves.  The problem is if I try to access data in 
M or N via the base table T.  Having inserted a row into M, I should be able to change 
the shared field x in that row by doing an update on the base table T.  With "real" 
tables this works fine.  With my rules I "do instead insert" a new row into T.  But 
this row is in T, not in the derived table M.  As far as M is concerned, this new row 
is invisible (and if it were visible, it would not have any values for M's local field 
mm).  A similar problem exists for deletes.

This is the problem that I have been trying to solve over the last few days.  So far, 
I have got this far:

- When I want to do an update or delete on the base table, I use this to find out what 
derived table the affected row actually belongs to:

select pc.relname from T_d td join pg_class pc on (td.tableoid=pc.oid)

- I then need to insert a new row into this table, based on the values from the most 
recent row with the selected id and any changes for an update, or with deleted set for 
a delete.

I don't think this can be done directly within a CREATE RULE statement's commands, so 
I've been trying to write a plpgsql function to do it.  I presume that I can then call 
the function from the rule, though I have yet to try this.

Considering just the delete for the now, this is what I have managed so far.  This 
gets called with the id of the row to be deleted:

create function del ( integer ) returns void as '
  declare
del_id alias for $1;
table text;
r record;
  begin
select pc.relname into table from T_d td join pg_class pc
  on (td.tableoid=pc.oid) where td.id=del_id;
if not found then
  raise exception ''object % not found'', del_id;
end if;
-- following "loop" executes once.

Re: [SQL] [JDBC] Prepare Statement

[Jie Liang]

Kris,
Thank you for your valuable response, I used the code you list
following:
import java.sql.*;

public class ServerSidePreparedStatement
{

public static void main(String args[]) throws Exception
{
Class.forName("org.postgresql.Driver");
String url = "jdbc:postgresql://localhost:5432/test";
Connection conn =
DriverManager.getConnection(url,"test","");

PreparedStatement pstmt = conn.prepareStatement("SELECT
?");

// cast to the pg extension interface
org.postgresql.PGStatement pgstmt =
(org.postgresql.PGStatement)pstmt;

// on the third execution start using server side
statements
pgstmt.setPrepareThreshold(3);

for (int i=1; i<=5; i++)
{
pstmt.setInt(1,i);
boolean usingServerPrepare =
pgstmt.isUseServerPrepare();
ResultSet rs = pstmt.executeQuery();
rs.next();
System.out.println("Execution: "+i+", Used
server side: " + usingServerPrepare + ", Result: "+rs.getInt(1));
rs.close();
}

pstmt.close();
conn.close();
}
}
Then, the compiler complaint:
ServerSidePreparedStatement.java:20: cannot resolve symbol symbol  :
method setPrepareThreshold  (int)
location: interface org.postgresql.PGStatement
pgstmt.setPrepareThreshold(3);
I downloaded pg74.213.jdbc2.jar and pg74.213.jdbc2ee.jar at
http://jdbc.postgresql.org/download.html
And had a try, I got same error msg.

I use java 1.3.1, postgresql -7.4.2, FreeBSD 4.7

What I need to do to make it work??

Thanks.



Jie Liang

-Original Message-
From: Kris Jurka [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 15, 2004 11:00 AM
To: Jie Liang
Cc: Tom Lane; [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: [JDBC] Prepare Statement




On Mon, 14 Jun 2004, Jie Liang wrote:

> I have a question about performance, in SQL commands: there is a 
> prepare/execute command, document says it will improve the performance

> while repeatly execute a statement. In java.sql: there is a 
> PreparedStatement object, which can store precompiled SQL statement, 
> document says it can improve the performance also. If I use java jdbc 
> to connect postgresql database, which one I should use? Can I use 
> both?
> 

When using JDBC it is best to use the standard
Statement/PreparedStatement 
interfaces.  It is possible to directly use PREPARE/EXECUTE, but this
can 
be handled by the driver.  Let me give you a run down of the different 
driver versions and their capabilities:

Current released version: can enable using PREPARE/EXECUTE behind the 
scenes on PreparedStatement by casting the prepared statement to 
PGStatement and issuing setUseServerPrepare.

Current cvs version: can enable using PREPARE/EXECUTE by setting an 
execution threshold that will turn it on when reached.  This threshold
can 
be set at a number of levels, see the following for more information

http://www.ejurka.com/pgsql/docs/cvs/ch09s05.html

Soon to be committed cvs version: can directly use server prepared
statements without using the SQL level PREPARE/EXECUTE.

Kris Jurka

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] Is there a faster way to do this?

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
 
  
Hard to imagine it taking that long, even for a table that large,
but we don't know the details of your system. I got a sample
table with 800,000 records down to 92 seconds using the function
below. See how it does for you. Notes follow.
  
CREATE OR REPLACE FUNCTION percentile_calc(INTEGER) RETURNS text AS '
 DECLARE
  v_tile ALIAS FOR $1;
  v_interval integer;
  v_record record;
  v_rowcount integer := 0;
  v_percentile integer := 1;
  
 BEGIN
  
  SELECT COUNT(*)/v_tile FROM cdm_indiv_mast WHERE val_purch_com > 0
INTO v_interval;
  
  FOR v_record IN
SELECT ctid FROM cdm_indiv_mast WHERE val_purch_com>0
  ORDER BY val_purch_com DESC
  LOOP
v_rowcount := v_rowcount + 1;
UPDATE cdm_indiv_mast SET percentiler=v_percentile WHERE ctid = v_record.ctid;
IF v_rowcount >= v_interval THEN
  v_percentile := v_percentile + 1;
  v_rowcount := 0;
END IF;
  END LOOP;
  
  RETURN  \'DONE\';
END;
' LANGUAGE plpgsql STABLE STRICT;
  
CREATE OR REPLACE FUNCTION percentile_calc() RETURNS text
  AS 'SELECT new10(100);' LANGUAGE SQL;
  
Notes:
  
Since 100 seemed to be hard-coded into the original function, there was
no need for v_count. Instead, I made "v_tile" a variable, with a default
of "100" if the function is called with no argument.
  
There may be a false assumption here. If the values of val_purch_com are
not unique, then two items with the same val_purch_com may have different
percentiler values. If this is the case, you may want to at least enforce
some ordering of these values by adding more to the ORDER BY clause.
Without knowing the full table structure, I can't recommend what columns
to add there.
  
To really speed this up, make sure that you do not have any indexes on
the table. By using tids, we neatly avoid having to use any indexes in the
function itself. Unless you are using oids and really need them (highly
unlikely because of the "indiv_key" column), you should remove them:
  
ALTER TABLE cdm_indiv_mast SET WITHOUT OIDS;
  
Of course, vacuuming completely and often is recommended for a table this
size as well, especially when updating this many rows at once. I'd
recommend a VACUUM FULL immediately before running it.
 
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200406162303
 
-BEGIN PGP SIGNATURE-
 
iD8DBQFA0QpdvJuQZxSWSsgRAk88AKDtGrs6+/ypaaNU6DWqvhCgtsrM0gCg+2ve
J8JKOPgxp42c54Nx/rzHdxs=
=sNFW
-END PGP SIGNATURE-



---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match