Re: [GENERAL] many schemas or many databases

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 wit

Re: [GENERAL] Migration from DB2 to PostgreSQL

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 f

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

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

[GENERAL] howto set a variable in transaction context

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 |

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

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

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

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

Re: [GENERAL] histogram

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 valu

Re: [GENERAL] histogram

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

Re: [GENERAL] Filling null values

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 Th

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

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

Re: [GENERAL] JDBC Connection Errors

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.pos

Re: [GENERAL] Procedural Languages

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, w

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

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

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

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 D

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

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

Re: [GENERAL] SELECT Query returns empty

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

Re: [GENERAL] Query m:n-Combination

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

Re: [GENERAL] Returning schema name with table name

tal_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

Re: [GENERAL] Returning schema name with table name

. 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

Re: [GENERAL] column does not exist error

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; > >

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

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.htm

Re: [GENERAL] Grouping logs by ip and time

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

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

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 char

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

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 * fr

Re: [GENERAL] Adding German Character Set to PostgresSQL

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,

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

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

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

LECT (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 | Z

Re: [GENERAL] Data comparison SQL in PG 8.2.9

t;>" 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 ==

Re: [GENERAL] Data comparison SQL in PG 8.2.9

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: (modi

Re: [GENERAL] PGSQL and Javascript

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 s

Re: [GENERAL] embedded pgsql media-failure

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 D

Re: [GENERAL] field with Password

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 t

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

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 s

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

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 t