Re: [GENERAL] count records in two different table joined by

2017-07-07 Thread Thomas Markus 

Hi,

Am 07.07.17 um 12:16 schrieb Patrick B:

Hi guys!

I've got 2 tables, and I need to get some data between them.

test1:

WITH account_status AS (
select
CASE
  WHEN regdate = 1 THEN 'yes'
  WHEN regdate = 2 THEN 'no'
  from test1
  end as status_a
)

select status_a from account_status group by status_a

test2:

WITH user_status AS (
select
CASE
  WHEN regdate = 1 THEN 'yes'
  WHEN regdate = 2 THEN 'no'
  from test1
  join test2 as t2 on t2.test1_id = t1.id 
  end as status_a
)

select status_a from user_status group by status_a


It works fine.. but I would like to get that data in one single 
query.. How can I do that?


I'm using Postgres 9.3.

Thanks!
Patrick


one possibility is:

select distinct
case
when regdate = 1 THEN 'yes'
when regdate = 2 THEN 'no'
end as status_a
, t2.id is null as test2exists
from test1 t1 left join test2 t2 on t2.test1_id = t1.id

hth
Thomas

Re: [GENERAL] Migration from DB2 to PostgreSQL

2013-06-19 Thread Thomas Markus 

Am 19.06.2013 08:05, schrieb sachin kotwal:

While migrating my application from DB2 to PostgreSQL.

I want to migrate following functions in PostgreSQL.

Functions in DB2: BLOB()/CLOB()

Criteria:
Size of character string targeted for cast is more than 1GB.  Character
String as argument to this function.

How can I migrate this function into PostgreSQL with above mention criteria.

Hi,

as i know each value is limited to 1GB. For larger content use module lo
http://www.postgresql.org/docs/9.2/static/lo.html

Thomas

Re: [GENERAL] How to keep the last row of a data set?

2012-12-13 Thread Thomas Markus 

Hi,

create an after delete trigger with

IF (SELECT 1 FROM t1 limit 1) is null  THEN
RAISE EXCEPTION 'Must keep at least 1 row';
 


hth
Thomas



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Procedural Languages

2012-06-05 Thread Thomas Markus 


Am 31.05.2012 22:57, schrieb Scott Marlowe:

And don't be surprised if you find one not listed there.

For instance, my entire production system runs entirely on pl/bf

https://github.com/mikejs/pl-bf

It's really the only logical choice for critical and complex financial
analysis work.


yeah, wonderful maintainability :D


/me should use this for next module ...

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Binary Large Objects (LOB/BLOB) in Hibernate and JDBC: Unresolved issues

2012-01-09 Thread Thomas Markus 

Hi,

thats not a bug its a feature ;)

the combination hibernate/pgjdbc uses pg large objects for byte[] and 
Blob properties so only oids are referenced. Use 'vacuumlo' to free up 
your space.


regards
Thomas


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Adding German Character Set to PostgresSQL

2012-01-02 Thread Thomas Markus 

Hi Hagen,

all german umlaut characters works fine in postgres from my experience.
Seems you have encoding issues between windows tools/console/db-client. 
Use a utf8 capable client. Any java tool or pgadmin or similar are fine.


regards
Thomas


Am 02.01.2012 20:13, schrieb Hagen Finley:


Hi,

I am using psql (8.2.15) and I would like to input German characters 
(e.g. ä,ß,ö) into char fields I have in a database. I am having 
trouble getting the CENTOS Linux OS I am using to input German 
characters via a (apparently supported) German Keyboard Layout. 
However, that might be a separate matter. When I typed the German into 
Notepad in Windows and attempted to cut and paste the words into an 
INSERT statement, the characters do not persist:


Daß becomes DaDa and Heißt becomes HeiHeit which falls short of what I 
was hoping for.


I am wondering if I need to enable an international character set 
within Postgres before the German characters will input properly? If 
so, it's not clear from the documentation I have attempted to find how 
one enables other characters sets within Postgres? Any suggestions 
will be much appreciated. Thank you.


Hagen Finley

Boulder, CO

Re: [GENERAL] How to make a non-removable row in a table?

2011-12-19 Thread Thomas Markus 

Hi,

simple violate a contraint.

my test:

drop table if exists x;
create temp table x (
id int not null primary key,
name text
);

-- check against not null
create rule test_rule as on delete to x where old.id=1 do instead update 
x set id=null;

insert into x values( 1,'a'),(2,'b');
select * from x;

-- fails
delete from x;

delete from x where id!=1;
select * from x;



regards
Thomas


Am 19.12.2011 08:16, schrieb Капралов Александр:

I found a simple solution, but i don't know how to add raises an exception here.

create rule protect_profile_id0_update as  on update to web.profile
where old.id = 0  do instead nothing;
create rule protect_profile_id0_delete as  on delete to web.profile
where old.id = 0  do instead nothing;




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to make a non-removable row in a table?

2011-12-18 Thread Thomas Markus 

Hi,

create a delete trigger that raises an exception

Thomas


Am 19.12.2011 07:43, schrieb Капралов Александр:

Hi all.

How to make a non-removable row in a table?

In my case, I should not accidentally delete a row with id = 0.

CREATE TABLE profile (

 id integer NOT NULL,

 name character varying(265) NOT NULL

);

CREATE SEQUENCE profile_id_seq
 START WITH 1
 INCREMENT BY 1
 NO MAXVALUE
 NO MINVALUE
 CACHE 1;

ALTER TABLE profile ALTER COLUMN id SET DEFAULT
nextval('profile_id_seq'::regclass);

ALTER TABLE ONLY profile ADD CONSTRAINT profile_pkey PRIMARY KEY (id);

INSERT INTO profile VALUES (0,'non-removable Profile');




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Grouping logs by ip and time

2011-11-08 Thread Thomas Markus 

Hi Alex,

in PG9 you can use a query like this:

with a as ( select a.*, rank() over (partition by a.ip,a.date order by 
a.log_type, a.time) from log_table a )
select a.*, b.*, b.time-a.time from a join a b on a.ip=b.ip and 
a.date=b.date and a.rank+1=b.rank


this orders entry by time grouped by ip and date and selects entries 
with there successors. In older versions this is not so easy. It should 
work like this:

create temp sequence s;
create temp table a as select a.*, nextval('s') as rank from ( select 
a.* from log_table a order by a.ip, a.date, a.time) a;
select a.*, b.*, b.time-a.time from a a join a b on a.ip=b.ip and 
a.date=b.date and a.rank+1=b.rank;


Thomas

Am 08.11.2011 18:59, schrieb Alex Thurlow:

Hello all,
I have a table which stores action logs from users.  It looks 
something like this:

log_type text,
date date,
time time without time zone,
ip inet

The log type can be action1, action2, action3, action4, or action5.  I 
know that each user session will have a max of one of each log and it 
will always start with action1.  It may not have every action though.  
I also know that each session will take no longer than one minute.


What I'd like to do is be able to group these logs by sessions based 
on the IP and the time range so I can figure out the time taken 
between each action.


I know how to script it, although it's very slow.  I was hoping there 
was some way to do this in SQL.  I'm running Postgresql 8.3.7 on this 
machine right now, but if there's something that would make this 
easier and doesn't exist there yet, I would be willing to upgrade.


Thanks,
Alex




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] JDBC Connection Errors

2011-08-24 Thread Thomas Markus 

Hi,

- check for open server socket: netstat -tulpen | grep postgres
- try to force ipv4 for java with system property (a recent jre prefers 
ipv6): -Djava.net.preferIPv4Stack=true


regards
Thomas

Am 24.08.2011 00:47, schrieb Sam Nelson:

Hi list,

A client is hitting an issue with JDBC:
org.postgresql.util.PSQLException: Connection refused. Check that the
hostname and port are correct and that the postmaster is accepting
TCP/IP connections.

-pg_hba.conf is set to trust 0.0.0.0/0 (IPv4 only)
-listen_addresses is *
-I can find no evidence of iptables running on the server.
-PGAdmin connects just fine.
-psql connects just fine.
-I can find no errors in the log file from that day for the user that
the client is trying to log in as.

We're working on getting access to more details about how they're
trying to connect, but in the mean time, does anyone know if JDBC has
any issues connecting that psql and PGAdmin wouldn't have?  Is it
possible that JDBC is somehow susceptible to connection issues that
JDBC and psql are not?
---
===
Samuel Nelson
Consistent State
www.consistentstate.com
303-955-0509
===



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to tame a gigantic (100+ lines) query in a web app?

2011-08-15 Thread Thomas Markus 

Hi,

use WITH queries, I use this regularly and it works fine.
http://www.postgresql.org/docs/9.0/static/queries-with.html

regards
Thomas

Am 14.08.2011 16:39, schrieb W. Matthew Wilson:

I'm sure I'm not the first person to end up with a gigantic query that
does lots of left joins and subselects.

It seems to work, but I would love to break it up into smaller chunks.

I'm thinking about rewriting the query to make several temporary
tables that are dropped on commit, and then joining them at the end.

I can't just use views for everything because I use parameters passed
in from the web app.  I am using a few views where I can.

Is there anything dangerous about making temporary tables in this way?
  I started two transactions simultaneously and they were both able to
make their own temporary tables.

More generally, how to tame this big ol' query?

The temporary tables mean I'm only pulling data from the database one
time.  ORMs often pull data from one query and then use that data to
write the next query.  This seems slow to me.

Matt





--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Filling null values

2011-08-08 Thread Thomas Markus 

hi,

try this. If your table name is mytable:

select
  a.homeid
, a.city
, coalesce(a.date, (select b.date from mytable b where 
b.homeid=a.homeid and b.date is not null order by b.prepost=a.prepost 
desc limit 1) ) as date

, a.measurement
, a.prepost
from
mytable a



Thomas

Am 05.08.2011 18:32, schrieb jeffrey:

I have a table that looks like this:

homeidcity  date measurement  pre/post
123   san francisco  1/2/2003 1458 pre
123   san francisco  NULL  1932 post
124   los angeles2/4/2005  938   pre
124   NULLNULL   266   pre
124   los angeles7/4/2006  777   post

I'd like to write a query so that I get the following result:

homeidcity  date measurement  pre/post
123   san francisco  1/2/2003 1458 pre
123   san francisco  1/2/2003  1932post
124   los angeles2/4/2005  938   pre
124   los angeles2/4/2005   266  pre
124   los angeles7/4/2006  777   post

If a city or date is null, then it will fill from other not null
values with the same homeid.  If given the choice, it will
preferentially fill from a row where homeid AND pre/post match.  But
if that doesn't match, then it will still fill from the same homeid.

Does anyone have ideas for this?

Thanks,
Jeff




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] histogram

2011-05-01 Thread Thomas Markus 

hi,

group by 1 means group by first output column
order by 2 means order by second output column ascending



Am 30.04.2011 19:00, schrieb Joel Reymont:

What is the meaning of

group by 1 order by 2

e.g. what to the numbers 1 and 2 stand for?

What would change if I do the following?

group by 1 order by 1

On Apr 30, 2011, at 5:48 PM, Thomas Markus wrote:


Hi,

try something like this:

select
trunc(random() * 10.)/10.
, count(*)
from
generate_series(1,200)
group by 1 order by 2

--
- for hire: mac osx device driver ninja, kernel extensions and usb drivers
-++---
http://wagerlabs.com | @wagerlabs | http://www.linkedin.com/in/joelreymont
-++---






--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] histogram

2011-04-30 Thread Thomas Markus 

Hi,

try something like this:

select
trunc(random() * 10.)/10.
, count(*)
from
generate_series(1,200)
group by 1 order by 2

regards
Thomas

Am 30.04.2011 18:37, schrieb Joel Reymont:

I have a column of 2 million float values from 0 to 1.

I would like to figure out how many values fit into buckets spaced by 0.10, 
e.g. from 0 to 0.10, from 0.10 to 0.20, etc.

What is the best way to do this?

Thanks, Joel

--
- for hire: mac osx device driver ninja, kernel extensions and usb drivers
-++---
http://wagerlabs.com | @wagerlabs | http://www.linkedin.com/in/joelreymont
-++---







--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] many schemas or many databases

2011-02-08 Thread Thomas Markus 

hi,

i would prefer many schemas. advantages:
- one backup/restore for all (or selective)
- one connection pool
- simple access to all schemas

regards
thomas


Am 08.02.2011 09:30, schrieb Szymon Guz:

Hi,
is there any noticeable difference between a cluster with many databases and
a database with many schemas?

I've got a quite huge database on Oracle with about 400 logically disjoint
schemas.
I could import that into PostgreSQL as many different databases, or as one
database with many schemas.

 From the application point of view it could be easier to have different
databases, as for now the applications log in into different schemas, so
this behavior wouldn't change.

Do you see any drawbacks of any of the solutions?


regards
Szymon




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Out of memory on update of a single column table containg just one row.

2010-07-05 Thread Thomas Markus 

 Hi,

i tried a simple test:
create temp table _t as select repeat('x',382637520) as test;
update _t set test=test||test;

pg 8.3 32bit fails with
[Error Code: 0, SQL State: 53200]  ERROR: out of memory
  Detail: Failed on request of size 765275088.

pg 8.4.4 64bit works fine

so upgrade to 64bit

regards
Thomas

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Uncable to commit: transaction marked for rollback

2010-07-01 Thread Thomas Markus 
Hi,

it happens fi at transaction timeout or you executed a broken/failed
statement und catched the exception

try {  call invalid sql  } catch (Exception e) {}
connection.commit() - exception throws here, happens automatically
inside your appserver


regards
Thomas

Am 01.07.2010 20:06, schrieb David Kerr:
 I'm intermittantly getting this error message in a java app. 
 using Geronimo / Hibernate / Postgres 8.3.9

 javax.transaction.RollbackException: Unable to commit: transaction marked for
 rollback

 Can someone give me a scenario where this would happen? unable to commit
 makes everyone immediatly go to database issue. But it seems like an app 
 issue to me.

 I was thinking that maybe it's a 2 phase commit / XA or something like that.
 (TX open, phase 1 commit fails, phase 2 commit throws this error?)

 I can't imagine how this would happen within a single transaction setup.

 Thanks!

 Dave

   


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] date_trunct() and start of week

2009-11-27 Thread Thomas Markus 
Hi,

not all to zero : that are less significant than the selected one set
to zero (or one, for day and month)

so

select extract('dow' from date_trunc('week', current_date))

returns always 1 (i think accordingly to ISO-8601)

see
http://www.postgresql.org/docs/8.1/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT
dow:
The day of the week (0 - 6; Sunday is 0)

regards
Thomas



Thomas Kellerer schrieb:

 Hmm, I don't see that in there.
 It just states that the field will be set to zero. But does zero
 refer to a Monday or a Sunday?
 Regards
 Thomas




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] column does not exist error

2009-11-18 Thread Thomas Markus 
Hi,

try

SELECT title FROM node WHERE type='client';


hth
Thomas


Dave Coventry schrieb:
 Tearing my hair out, can anyone see what I'm doing wrong?

   SELECT title FROM node WHERE type=client;

 ERROR:  column client does not exist
 LINE 1: SELECT title FROM node WHERE type=client;

 Yet this works:

  SELECT type FROM node;
   type
 
  client
  client
  client
  client
  client
 (5 rows)

   


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Cannot login for short period of time

2009-05-12 Thread Thomas Markus 
Hi,

check your hardware (especially harddrive) for errors.

regards
Thomas

Henry schrieb:

 Greets,

 Pg:  8.3.7

 I'm trying to diagnose why I cannot login to Pg on occasion.  The psql
 command will just hang (so I cannot get in to see what it's doing) and
 a telnet into 5432 will give the usual:

 Connected to localhost (127.0.0.1).
 Escape character is '^]'.

 indicating the backend accepting the connection, but not proceeding.

 The number of connections at the time are small, file descriptor usage
 will be small, etc, server load low.

 It will then spontaneously recover as if it's just completed doing
 something IO intensive.

 My config:

 listen_addresses = '1.2.1.1,127.0.0.1'
 port = 5432
 max_connections = 2048
 shared_buffers = 520MB
 temp_buffers = 128MB
 work_mem = 1512MB
 max_fsm_pages = 512000
 fsync = off
 checkpoint_segments = 32
 effective_cache_size = 512MB
 constraint_exclusion = on
 logging_collector = on
 track_counts = on
 autovacuum = on
 autovacuum_freeze_max_age = 20

 I'm rerunning things with statement logging turned on to get a clue.
 Incidentally, I did have to reindex the system tables in single-user
 mode (got a Cannot find namespace 0 error at some point).

 Anyone have an idea what this strange connectivity-delay could be about?

 Cheers
 Henry


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Posgres Adding braces at beginning and end of text (html) content

2009-04-02 Thread Thomas Markus 

hi,

i'm not a tcl user but it looks like an array representation. try to 
remove braces [] from page_content.


regards.
thomas

linnewbie schrieb:


into the text area field, save:

set page_content  [ ncgi::value  textarea_field_name]

database connect dbh $datasource $dbuser $dbpassword

set sql INSERT INTO profile (page_content) \
VALUES('$page_content') 

dbh $sql

  



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] field with Password

2009-02-04 Thread Thomas Markus 
what do you expect from such type? try to use a text field for plain 
passwords or better store only hashvalues. see md5()


regards
thomas

Iñigo Barandiaran schrieb:

Hi.


I would like to create a new table where one of the field would be a 
user password. Is there any data type for supporting this 
functionality? Something like Password DataType. I've taken a look of 
the available data types in PgAdmin Application and there is nothing 
similar to this.


Thanks in advance.

Best,




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] embedded pgsql media-failure

2009-02-03 Thread Thomas Markus 

Hi,

on a linux system try software raid1 for pg data. check if pg is the 
right choice for your needs here. maybe flat files for config+log is 
less problematic.


regards
thomas

Kokas Zsolt schrieb:

Hi!

I'd need some advice.
I'm working on a quite special field, I have to set up an embedded DB,
which will store logs (measured values) and gives configuration to the
machine and alsothis configuration can be changed.
The system will consist of a CF card (with wear leveling) and an Intel
atom CPU. The config data and the measured values together will take
up around 2-3000 rows + a couple of ten thousand rows.
The businees's side wants to make it as secure as it is possible,
meaning, that the CF card will have two partitions, and the DB should
be mirrored or distributed somehow on this two partiton, in case of a
one-point disk-error the system should stay stable.
Even though I've never used them, but as I see is, that standard
replication like Slony, Heartbeat+DRBD or Postgres-R are not really
able to cope with such kind of setup. Maybe I got something wrong :)
So my problem is: without a network, on one single CF card with two
partitions and only one CPU and only one server running how can it be
managed to protect the data part against media-failure.

 Thanks for your help!
___
Kokas Zsolt

Save a tree...please only print this e-mail if it is genuinely required.

  



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PGSQL and Javascript

2009-01-30 Thread Thomas Markus 

look at apache cocoon for serverside javascript (rhino engine)
you can direct access java classes. write all db stuff in java and 
access it from js


var dao = Packages.my.own.package.DAOController.findAll();
Packages.java.lang.System.out.println(dao.myproperty);


regards
thomas

Reg Me Please schrieb:

Hello all.

Is there a way to directly access PGSQL from a Javascript application?
With no application server intervention, I mean.
Just like libq allows access from C/C++.

  


begin:vcard
fn:Thomas Markus
n:Markus;Thomas
org:proventis GmbH
adr:;;Zimmerstr. 79-80;Berlin;Berlin;10117;Germany
email;internet:t.mar...@proventis.net
tel;work:+49 30 29 36 399 22
x-mozilla-html:FALSE
url:http://www.proventis.net
version:2.1
end:vcard


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Data comparison SQL in PG 8.2.9

2009-01-12 Thread Thomas Markus 

check explain analyze

tried this on 8.3 with real life data:

-- all rows, index useless
explain analyze select id from cl_customer where modified  '2008-01-01'

Seq Scan on cl_customer  (cost=0.00..38958.79 rows=1448639 width=8) 
(actual time=0.030..682.940 rows=1448783 loops=1)

 Filter: (modified  '2008-01-01 00:00:00'::timestamp without time zone)
Total runtime: 1015.394 ms

-- small subset
explain analyze select id from cl_customer where modified  '2009-01-01'
Index Scan using i_cl_customer_modified on cl_customer  
(cost=0.00..12.93 rows=144 width=8) (actual time=0.018..0.110 rows=175 
loops=1)
 Index Cond: (modified  '2009-01-01 00:00:00'::timestamp without time 
zone)

Total runtime: 0.169 ms



Phoenix Kiula schrieb:

Thanks. But it used to work without this, and more importantly, this
doesn't explain why the  queries are so exceedingly slow now! Any
thoughts?
  


begin:vcard
fn:Thomas Markus
n:Markus;Thomas
org:proventis GmbH
adr:;;Zimmerstr. 79-80;Berlin;Berlin;10117;Germany
email;internet:t.mar...@proventis.net
tel;work:+49 30 29 36 399 22
x-mozilla-html:FALSE
url:http://www.proventis.net
version:2.1
end:vcard


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Data comparison SQL in PG 8.2.9

2009-01-11 Thread Thomas Markus 

be sure to use correct data types. I suppose psql uses timestamps so

select id from users where modify_date = '2009-01-08' limit 1;

is converted to

select id from users where modify_date = '2009-01-08 00:00:00'::timestamp limit 
1;


try

select id from users where modify_date::date = '2009-01-08'::date limit 1;

regards
thomas



Phoenix Kiula schrieb:

Hi. I have a large table that has a modify_date field in it, which is
timestamp without time zone.

I used to be able to do queries like these in 8.2.3 --

  select id from users where modify_date = '2009-01-08' limit 1;
  select id from users where modify_date  '2009-01-08' limit 1;

Suddenly these are returning:

   id
  
  (0 rows)

  Time: 11.635 ms

I can see through other SQL that there are rows with these dates in
them! My \d users shows these two relevant entries about
modify_date:


Table public.users
  Column |Type |
Modifiers
  
---+-+--
  modify_date   | timestamp without time zone |
  
  Indexes:
  new_idx_modify_date btree (modify_date) WITH (fillfactor=75)


Any ideas why? I ran an explain analyze and this is the output:


**
=# explain analyze select id from users where modify_date =
'2009-01-08' limit 1;
QUERY PLAN
--
 Limit  (cost=0.00..0.37 rows=1 width=8) (actual time=0.082..0.082
rows=0 loops=1)
   -  Index Scan using new_idx_modify_date on users  (cost=0.00..4.12
rows=11 width=8) (actual time=0.074..0.074 rows=0 loops=1)
 Index Cond: (modify_date = '2009-01-08 00:00:00'::timestamp
without time zone)
 Total runtime: 19.484 ms
(4 rows)

Time: 19.940 ms
**

Another weird thing is that a query that has  a certain
modify_date, even just yesterday as the date condition, takes a LONG
time and is almost unusable.

Appreciate any pointers.

Thx!

  


--
Thomas Markus


proventis GmbH | Zimmerstr. 79-81 | D-10117 Berlin |
Tel +49 (0)30 2936399-22 | Fax -50 | t.mar...@proventis.net
-
Geschäftsführer: Norman Frischmuth | Sitz: Berlin
Handelsregister: AG Berlin-Charlottenburg, HR 82917
-
Blue Ant-webbasiertes Projektmanagement - aktuelle Termine 2008:
http://www.proventis.net/website/live/blueant/veranstaltungen.html


begin:vcard
fn:Thomas Markus
n:Markus;Thomas
org:proventis GmbH
adr:;;Zimmerstr. 79-80;Berlin;Berlin;10117;Germany
email;internet:t.mar...@proventis.net
tel;work:+49 30 29 36 399 22
x-mozilla-html:FALSE
url:http://www.proventis.net
version:2.1
end:vcard


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] create a 'range' of dates/numbers?

2008-12-19 Thread Thomas Markus 

try function generateseries

thomas

Erwin Moller schrieb:

Hi,

Is it possible to create a range of dates or numbers inside a query 
without wrining in procedural language?
I am looking for something that creates some kind of 'temp table' 
inside a query.

eg:
[wrong/fantasy syntax]
SELECT (1 to 3 step 1) as number;
desired output:
number
1
2
3

I have been looking through the manual, but cannot find anything. 
Maybe it doesn't exist.


Thanks for your time.

Regards,
Erwin Moller




--
Thomas Markus


proventis GmbH | Zimmerstr. 79-81 | D-10117 Berlin |
Tel +49 (0)30 2936399-22 | Fax -50 | t.mar...@proventis.net
-
Geschäftsführer: Norman Frischmuth | Sitz: Berlin
Handelsregister: AG Berlin-Charlottenburg, HR 82917
-
Blue Ant-webbasiertes Projektmanagement - aktuelle Termine 2008:
http://www.proventis.net/website/live/blueant/veranstaltungen.html


begin:vcard
fn:Thomas Markus
n:Markus;Thomas
org:proventis GmbH
adr:;;Zimmerstr. 79-80;Berlin;Berlin;10117;Germany
email;internet:t.mar...@proventis.net
tel;work:+49 30 29 36 399 22
x-mozilla-html:FALSE
url:http://www.proventis.net
version:2.1
end:vcard


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Returning schema name with table name

2008-11-24 Thread Thomas Markus 
it shows all except toast entries. for included values see 
http://www.postgresql.org/docs/8.3/static/functions-admin.html#FUNCTIONS-ADMIN-DBSIZE 
function |pg_total_relation_size|(oid)



Andrus schrieb:

my standard query (adapted to 1mb size) is:


Thank you very much.
This query shows toast files in a cryptic way:

db_owner pg_toast pg_toast_40552_index 1352 kB

How to change it so that it shows also relation name whose data 
pg_toast_40552_index contains?
It is not possible to determine from this query output which data is 
contained in pg_toast_40552_index file.


Andrus.



--
Thomas Markus


proventis GmbH | Zimmerstr. 79-81 | D-10117 Berlin |
Tel +49 (0)30 2936399-22 | Fax -50 | [EMAIL PROTECTED]
-
Geschäftsführer: Norman Frischmuth | Sitz: Berlin
Handelsregister: AG Berlin-Charlottenburg, HR 82917
-
Blue Ant-webbasiertes Projektmanagement - aktuelle Termine 2008:
http://www.proventis.net/website/live/blueant/veranstaltungen.html


begin:vcard
fn:Thomas Markus
n:Markus;Thomas
org:proventis GmbH
adr:;;Zimmerstr. 79-80;Berlin;Berlin;10117;Germany
email;internet:[EMAIL PROTECTED]
tel;work:+49 30 29 36 399 22
x-mozilla-html:FALSE
url:http://www.proventis.net
version:2.1
end:vcard


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Returning schema name with table name

2008-11-23 Thread Thomas Markus 

Hi,

my standard query (adapted to 1mb size) is:
select
 t.spcname as tablespace
   , pg_get_userbyid(c.relowner) as owner
   , n.nspname as schema
   , relname::text as name
   , pg_size_pretty(pg_total_relation_size(c.oid))::text as total size
   , case
   when c.relkind='i' then 'index'
   when c.relkind='t' then 'toast'
   when c.relkind='r' then 'table'
   when c.relkind='v' then 'view'
   when c.relkind='c' then 'composite type'
   when c.relkind='S' then 'sequence'
   else c.relkind::text
 end as type
from
   pg_class c
   left join pg_namespace n on n.oid = c.relnamespace
   left join pg_tablespace t on t.oid = c.reltablespace
where
   (pg_total_relation_size(c.oid)20)0 and c.relkind!='t'
order by
   c.relkind desc, pg_total_relation_size(c.oid) desc


Andrus schrieb:

SELECT oid, relname::char(35) as Table_Name,
  pg_size_pretty(pg_total_relation_size(oid))::VARCHAR(15) as 
Total_Table_Size

FROM pg_class
where  pg_total_relation_size(oid)/(1024*1024)0
ORDER BY pg_total_relation_size(oid) desc

returns table names with size greater than 1 MB

How to modify this so that schema name is also returned?
I have lot of tables with same name and thus this output is difficult 
to understand.

pg_class seems not contain schema names.

Andrus.




--
Thomas Markus


proventis GmbH | Zimmerstr. 79-81 | D-10117 Berlin |
Tel +49 (0)30 2936399-22 | Fax -50 | [EMAIL PROTECTED]
-
Geschäftsführer: Norman Frischmuth | Sitz: Berlin
Handelsregister: AG Berlin-Charlottenburg, HR 82917
-
Blue Ant-webbasiertes Projektmanagement - aktuelle Termine 2008:
http://www.proventis.net/website/live/blueant/veranstaltungen.html


begin:vcard
fn:Thomas Markus
n:Markus;Thomas
org:proventis GmbH
adr:;;Zimmerstr. 79-80;Berlin;Berlin;10117;Germany
email;internet:[EMAIL PROTECTED]
tel;work:+49 30 29 36 399 22
x-mozilla-html:FALSE
url:http://www.proventis.net
version:2.1
end:vcard


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Query m:n-Combination

2008-10-24 Thread Thomas Markus 

hi,

try

select
   r.*
from
   rivers r
   join jointable j1 on r.r_id=j1.mn_2_r_id join communities c1 on 
j1.mn_2_c_id=c1.c_id and c1.C_Name='community_1'
   join jointable j2 on r.r_id=j2.mn_2_r_id join communities c2 on 
j2.mn_2_c_id=c2.c_id and c2.C_Name='community_2'
   join jointable j3 on r.r_id=j3.mn_2_r_id join communities c3 on 
j3.mn_2_c_id=c3.c_id and c3.C_Name='community_3'
   join jointable j4 on r.r_id=j4.mn_2_r_id join communities c4 on 
j4.mn_2_c_id=c4.c_id and c4.C_Name='community_4'

where
   r.R_Name='river_1'

/tm


Ludwig Kniprath schrieb:

Dear list,
I have to solve a simple Problem, explained below with some sample-Data.

A typical M:N-constellation, rivers in one table, communities in the 
other table, m:n-join-informations (which river is running in which 
community) in a third table.


Table rivers:
R_ID  R_Name
1 river_1
2 river_2
3 river_3
4 river_4
5 river_5

Table communities :
C_ID   C_Name
1  community_1
2  community_2
3  community_3
4  community_4
5  community_5

Join-table
mn_2_r_id   mn_2_c_id
1   1
1   2
1   3
1   4
2   1
3   2
3   5
4   3
...

(in real database this relation is an gis-relation with thousands of 
rivers and countries, related by spatial join, but the problem is the 
same...)


I want to know, which river is running through communities 1,2,3 *and* 4?
You can see the solution by just looking at the data above (only 
river_1 is running through all these countries), but how to query 
this by sql?


Thanks in advance
Ludwig



begin:vcard
fn:Thomas Markus
n:Markus;Thomas
org:proventis GmbH
adr:;;Zimmerstr. 79-80;Berlin;Berlin;10117;Germany
email;internet:[EMAIL PROTECTED]
tel;work:+49 30 29 36 399 22
x-mozilla-html:FALSE
url:http://www.proventis.net
version:2.1
end:vcard


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] SELECT Query returns empty

2008-07-09 Thread Thomas Markus 

hi,

have a look at transaction isolation in docs

/tm

begin:vcard
fn:Thomas Markus
n:Markus;Thomas
org:proventis GmbH
adr:;;Zimmerstr. 79-80;Berlin;Berlin;10117;Germany
email;internet:[EMAIL PROTECTED]
tel;work:+49 30 29 36 399 22
x-mozilla-html:FALSE
url:http://www.proventis.net
version:2.1
end:vcard


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] good experience with performance in 8.2 for multi column indexes

2008-04-18 Thread Thomas Markus 

Hi,

your query cant perform well on 8.1
better use a query like

delete from pluext1 using pluext2 where pluext1.plunmbr = 
pluext2.plunmbr and pluext1.pluexttype = pluext2.pluexttype


it should perform much faster. be sure to use indizes

regards
thomas

Michael Enke schrieb:

Hi lists,
I want to let you take part in my experience of performance boost for 
delete operations

where more than one column is part of a primary key.

For my setup, in 8.1 a delete query which deletes 20 entries 
depending on rows in another table

runs about 7h, in 8.2 (and later) it runs 9s!

I have two tables looking exactly the same, with two columns in the 
pk, one varchar(20) and one char(1).

Both tables contain the same contents.

Explain produces the following difference:
8.1:

tplinux= explain delete from pluext1 where (plunmbr,pluexttype) in 
(select plunmbr,pluexttype from pluext2);

   QUERY PLAN
 


 Hash Join  (cost=24267.10..155886.35 rows=48236 width=6)
   Hash Cond: (outer.pluexttype = inner.pluexttype)
   Join Filter: (outer.plunmbr = inner.plunmbr)
   -  Seq Scan on pluext1  (cost=0.00..6945.00 rows=138900 width=46)
   -  Hash  (cost=24116.37..24116.37 rows=13891 width=40)
 -  Unique  (cost=23074.62..24116.37 rows=13891 width=40)
   -  Sort  (cost=23074.62..23421.87 rows=138900 width=40)
 Sort Key: pluext2.plunmbr, pluext2.pluexttype
 -  Seq Scan on pluext2  (cost=0.00..6945.00 
rows=138900 width=40)

(9 rows)

(this delete took 7h)

8.2:

tplinux= explain delete from pluext1 where (plunmbr,pluexttype) in 
(select plunmbr,pluexttype from pluext2);

   QUERY PLAN
- 


 Nested Loop IN Join  (cost=0.00..13362.14 rows=41106 width=6)
   -  Seq Scan on pluext1  (cost=0.00..6411.25 rows=128225 width=46)
   -  Index Scan using pluext2_pk on pluext2  (cost=0.00..0.50 rows=3 
width=40)
 Index Cond: ((pluext1.plunmbr = pluext2.plunmbr) AND 
(pluext2.pluexttype = pluext1.pluexttype))

(4 rows)

(this delete took 9s)

I could not find an explanation for this in the release notes for 8.2,
I thought it was much earlier that multi column indexes could be used.
Anyway, it saved my life that new version is fast. Many thanks to the 
developer!


BTW I do not understand the output of the 8.2 explain:
From my understanding it should do a seq scan on pluext2 (and not 
pluext1)

and an index scan on pluext1 (and not pluext2).

Regards,
Michael



begin:vcard
fn:Thomas Markus
n:Markus;Thomas
org:proventis GmbH
adr:;;Zimmerstr. 79-80;Berlin;Berlin;10117;Germany
email;internet:[EMAIL PROTECTED]
tel;work:+49 30 29 36 399 22
x-mozilla-html:FALSE
url:http://www.proventis.net
version:2.1
end:vcard


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] howto set a variable in transaction context

2008-04-10 Thread Thomas Markus 

hi list,

how can i set a variable, which content is only visible in current 
transaction?

i know the way to create a temp table with

create temp table ... on commit drop

but i dont like that way. something like
|
set_config(setting_name, new_value, is_local)

is it possible?

regards
thomas
|
begin:vcard
fn:Thomas Markus
n:Markus;Thomas
org:proventis GmbH
adr:;;Zimmerstr. 79-80;Berlin;Berlin;10117;Germany
email;internet:[EMAIL PROTECTED]
tel;work:+49 30 29 36 399 22
x-mozilla-html:FALSE
url:http://www.proventis.net
version:2.1
end:vcard


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] howto set a variable in transaction context

2008-04-10 Thread Thomas Markus 

thanks, it works

A. Kretschmer schrieb:

am  Thu, dem 10.04.2008, um 13:32:04 +0200 mailte Thomas Markus folgendes:
  

hi list,

how can i set a variable, which content is only visible in current 
transaction?

i know the way to create a temp table with

create temp table ... on commit drop

but i dont like that way. something like
|
set_config(setting_name, new_value, is_local)

is it possible?



Yes, you can use this:
http://www.postgresql.org/docs/current/interactive/runtime-config-custom.html


How to use?
http://groups.google.de/group/pgsql.general/browse_thread/thread/f914569b73d17258/5c0cb606d0ce698d?lnk=stq=#5c0cb606d0ce698d


HTH, Andreas
  


begin:vcard
fn:Thomas Markus
n:Markus;Thomas
org:proventis GmbH
adr:;;Zimmerstr. 79-80;Berlin;Berlin;10117;Germany
email;internet:[EMAIL PROTECTED]
tel;work:+49 30 29 36 399 22
x-mozilla-html:FALSE
url:http://www.proventis.net
version:2.1
end:vcard


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general