Re: [GENERAL] Why overlaps is not working

Andrus wrote: If he casts all his dates to timestamps then this might be a good option. Thank you. where (a::timestamp, coalesce(b, '1231')::timestamp) overlaps (c::timestamp, coalesce(d, '1231')::timestamp) would be simplest solution. I thought the suggested solution was to use in

Re: [GENERAL] encoding advice requested

>> My database locale is en_US, and by default my databases are UTF8. >> >> My application code allows the user to paste text into a box and submit >> it to the database. Sometimes the pasted text contains non UTF8 >> characters, typically the "fancy" forms of quotes and apostrophes. The >> da

[GENERAL] kubuntu & pgadmin

Hi all, anyone knows the source to get with (k)ubuntu the latest version of pgadmin and its admin pack? Thanks, Luca ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

[GENERAL] Inserting data in composite types

  Hi, I have a question about how to insert data in composite types!     Imagine the exemple:   CREATE TYPE t_time AS (   a date,   b date );   CREATE TABLE salary (    salary numeric(10,2),    t_date t_time );   I know that if I want to insert data in the table SALARY I just

Re: [GENERAL] Why overlaps is not working

I thought the suggested solution was to use infinity, hence the requirement to cast to timestamps. That'd mean something along the lines of: where (a::timestamp, coalesce(b, 'infinity')::timestamp) overlaps (c::timestamp, coalesce(d, 'infinity')::timestamp) select (date'20060101'::timesta

Re: [GENERAL] Why overlaps is not working

Andrus wrote: I thought the suggested solution was to use infinity, hence the requirement to cast to timestamps. That'd mean something along the lines of: where (a::timestamp, coalesce(b, 'infinity')::timestamp) overlaps (c::timestamp, coalesce(d, 'infinity')::timestamp) select (date'200

Re: [GENERAL] Why overlaps is not working

What you meant to test is: select (date '20060101'::timestamp, coalesce(NULL, 'infinity'::timestamp)) overlaps (date '20060102'::timestamp, coalesce(NULL, 'infinity'::timestamp)) Which returns true. Alban, If first period end and second period start dates are the same, I need that

Overlap flags (Was: Re: [GENERAL] Why overlaps is not working)

Andrus wrote: What you meant to test is: select (date '20060101'::timestamp, coalesce(NULL, 'infinity'::timestamp)) overlaps (date '20060102'::timestamp, coalesce(NULL, 'infinity'::timestamp)) Which returns true. Alban, If first period end and second period start dates are the sam

[GENERAL] schema synchronization

Hi all, I've got a productional database that is running, while on my laptop I use a copy of the database to develop an application. Sometimes I have to change the schema of the database in order to implement new application features, and I'd like to know if there's a way to automatically synchr

Re: [GENERAL] schema synchronization

Slony might help you there with the EXECUTE SCRIPT option.Thanks,-Shoaib MirEnterpriseDB (www.enterprisedb.com)On 11/13/06, Luca Ferrari <[EMAIL PROTECTED]> wrote: Hi all,I've got a productional database that is running, while on my laptop I use acopy of the database to develop an applicat

[GENERAL] SQL - update table problem...

Hi, i'm trying to make update on multiple tables but don't know how. is something like this posible with postgresql? update table1 join table2 on (table1.id=table2.t1) join table3 on (table2.id=table3.t2) set table1.name='test', table2.sum=table1.x+table2.y, table3.cell='123456789' where table

Re: [GENERAL] SQL - update table problem...

Hi! According to the synopsis of UPDATE you just can update one table at a time. Just use more UPDATE commands inside a transaction. -- Matthias > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Marko Rihtar > Sent: Monday, November 13, 2006 2:21 PM

[GENERAL] system tables...

Hi, is there a system table in which postgres stores all data about newly created database? together with database objects, tables, columns, functions etc. thanks _ FREE pop-up blocking with the new MSN Toolbar - get it now! htt

Re: [GENERAL] SQL - update table problem...

Marko Rihtar wrote: Hi, i'm trying to make update on multiple tables but don't know how. is something like this posible with postgresql? update table1 join table2 on (table1.id=table2.t1) join table3 on (table2.id=table3.t2) set table1.name='test', table2.sum=table1.x+table2.y, table3.cell='12

Re: [GENERAL] system tables...

Marko Rihtar wrote: Hi, is there a system table in which postgres stores all data about newly created database? together with database objects, tables, columns, functions etc. Try the manuals under "System Catalogs". -- Richard Huxton Archonet Ltd ---(end of bro

Re: [GENERAL] SQL - update table problem...

You can try using a combination of a view and rule attached to it for achieving the same...Thanks,---Shoaib MirEnterpriseDB (www.enterprisedb.com) On 11/13/06, Richard Huxton wrote: Marko Rihtar wrote:> Hi,>> i'm trying to make update on multiple tables but don't know how.> i

Re: [GENERAL] encoding advice requested

Albe Laurenz wrote: My database locale is en_US, and by default my databases are UTF8. My application code allows the user to paste text into a box and submit it to the database. Sometimes the pasted text contains non UTF8 characters, typically the "fancy" forms of quotes and apostr

[GENERAL] chop off non-meaningful digits

What would be the easiest way to get back only the meaningful digits of a numeric value in a pgsql function? eg? 1.002 --> 1.002 1.020 --> 1.02 1.200 --> 1.2 1.000 --> 1 Thanks SWK ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Trying to Install PhP-4 Module for PostgreSQL from

Desmond Coughlan wrote: Hi. As title. I'm getting a host of syntax errors, the last ten lines of which are ... NV_READ' undeclared (first use in this function) /usr/ports/databases/php4-pgsql/work/php-4.4.4/ext/pgsql/pgsql.c:1937: error: `INV_WRITE' undeclared (first use in this function)

Re: [GENERAL] chop off non-meaningful digits

On 13 Nov 2006 at 5:45, SunWuKung wrote: > What would be the easiest way to get back only the meaningful digits of > a numeric value in a pgsql function? eg? There are various rounding functions built in... have a look at http://www.postgresql.org/docs/8.1/static/functions-math.html --Ray. --

Re: [GENERAL] chop off non-meaningful digits

ROUND function might help you there:select round(1.2000::numeric, 1);Regards,Shoaib MirEnterpriseDB (www.enterprisedb.com) On 13 Nov 2006 05:45:44 -0800, SunWuKung <[EMAIL PROTECTED]> wrote: What would be the easiest way to get back only the meaningful digits ofa numeric value in a pgsql fu

Re: [GENERAL] chop off non-meaningful digits

am Mon, dem 13.11.2006, um 5:45:44 -0800 mailte SunWuKung folgendes: > What would be the easiest way to get back only the meaningful digits of > a numeric value in a pgsql function? eg? > > 1.002 --> 1.002 > 1.020 --> 1.02 > 1.200 --> 1.2 > 1.000 --> 1 You can use trim for this: test=*> select

Re: [GENERAL] encoding advice requested

Rick Schumeyer skrev: I will have to try the WIN1252 encoding. On the client side, my application is a web browser. On the server side, it is php scripts on a linux box. The data comes from copying data from a browser window (pointing to another web site) and pasting it into an html textare

Re: [GENERAL] chop off non-meaningful digits

Yep, I think this is it: select trim(trailing '0.' from 1.020) Many thanks. SWK "A. Kretschmer" wrote: > am Mon, dem 13.11.2006, um 5:45:44 -0800 mailte SunWuKung folgendes: > > What would be the easiest way to get back only the meaningful digits of > > a numeric value in a pgsql function? eg?

Re: [GENERAL] Inserting data in composite types

> Hi, I have a question about how to insert data in composite types! > > CREATE TYPE t_time AS ( > a date, > b date > ); > > CREATE TABLE salary ( >salary numeric(10,2), >t_date t_time > ); > > I know that if I want to insert data in the table SALARY I > just have to do like: > >

Re: [GENERAL] Request for replication advice

[EMAIL PROTECTED] ("Brendan Jurd") writes: > On 11/11/06, Chris Browne <[EMAIL PROTECTED]> wrote: >> Let me point out one possible downside to using Slony-I log shipping; >> it may not be an issue for you, but it's worth observing... >> >> Log shipping works via serializing the subscription work do

Re: [GENERAL] AutoVacuum on demand?

Glen Parker wrote: I would like a way to run the autovacuum daemon on demand periodically. Every night at 2 AM, for example. Anybody know if this is possible? If not, it's a feature request :-) Autovacuum can be enabled / disabled on the fly using the GUC settings. Perhaps you can write

Re: [GENERAL] Inserting data in composite types

On Mon, 2006-11-13 at 09:37 -0300, Rodrigo Sakai wrote: > Hi, I have a question about how to insert data in composite types! > > > > Imagine the exemple: > > > > CREATE TYPE t_time AS ( > > a date, > > b date > > ); > > > > CREATE TABLE salary ( > >salary numeric(10,2),

Re: [GENERAL] system tables...

I guess pg_class, pg_database and pg_proc can help hereThanks,--Shoaib MirEnterpriseDB (www.enterprisedb.com)On 11/13/06, Marko Rihtar <[EMAIL PROTECTED]> wrote: Hi,is there a system table in which postgres stores all data about newlycreated database?together with database objects, tables,

Re: [GENERAL] Why overlaps is not working

If first period end and second period start dates are the same, I need that in this case expression returns true. Is it possible to implement this using OVERLAPS operator ? I think the best workaround is a function of some kind in whichever language you choose. I think you could actually clo

Re: [GENERAL] Speed of postgres compared to ms sql, is this

On Sat, 2006-11-11 at 19:15, novnov wrote: > I agree with what you say. And I'd still be interesting in hearing of first > hand experience with the speed of the two databases from someone who is > 'good' at both. The article commentor was obviously not a pgsql expert. I've > heard recently that pgs

Re: [GENERAL] Request for replication advice

On 11/14/06, Chris Browne <[EMAIL PROTECTED]> wrote: [EMAIL PROTECTED] ("Brendan Jurd") writes: > This seems to be getting rather messy. I wonder if I might not be > better off just writing AFTER triggers on all the tables I'm > interested in, which replicate the query to the slave system with >

Re: [GENERAL] Speed of postgres compared to ms sql, is this

On 11/11/06, novnov <[EMAIL PROTECTED]> wrote: I agree with what you say. And I'd still be interesting in hearing of first hand experience with the speed of the two databases from someone who is 'good' at both. The article commentor was obviously not a pgsql expert. I've heard recently that pgsq

Re: [GENERAL] AutoVacuum on demand?

Matthew T. O'Connor wrote: Glen Parker wrote: I would like a way to run the autovacuum daemon on demand periodically. Every night at 2 AM, for example. Anybody know if this is possible? If not, it's a feature request :-) Autovacuum can be enabled / disabled on the fly using the GUC settin

Re: [GENERAL] Speed of postgres compared to ms sql, is this

OK, thanks everyone, I gather from the responses that postgres performance won't be an issue for me then. If MS SQL Server and Postgres are in the same ballpark performance-wise, which seems to be the upshot of your comments, no problem. I'd only have worried if there was something like the major

Re: [GENERAL] Speed of postgres compared to ms sql, is this

PostgreSQL follows SQL compliance, as does MS SQL Server. 'complicated queries' are left to the creativity of the SQL'er - squeler ;) There are of course proprietary differences in 'enhancements.' Some things that you can write in MS SQL, don't apply to Oracle/PostgreSQL/MySQL and many permutat

Re: [GENERAL] [NOVICE] Creating a new server

> Okay, I tried > >pg_dumpall > sync-up.db > Connected to template1... > Psql: FATAL 1: user "jcarlson" does not exist > > > I tried to use the -U option without success. How do I add a user ore > determine a user that will work for pg_dumpall I see, you are actually having a problem with pg_d

Re: [GENERAL] Speed of postgres compared to ms sql, is this

On Mon, 2006-11-13 at 15:36, novnov wrote: > OK, thanks everyone, I gather from the responses that postgres performance > won't be an issue for me then. If MS SQL Server and Postgres are in the same > ballpark performance-wise, which seems to be the upshot of your comments, no > problem. I'd only h

Re: [GENERAL] Table and Field namestyle best practices?

On Sun, Nov 12, 2006 at 02:21:10PM -0800, novnov wrote: > > OK, thanks. I'm having a major internal debate about how I'm going to adjust > my habits to pgsql's 'lowercase is simplest' reality, all of this is > helpful. Well, it's more like "no quoting is simplest", then all the identifiers are ca

Re: [GENERAL] AutoVacuum on demand?

Glen Parker wrote: > Matthew T. O'Connor wrote: > >Glen Parker wrote: > >>I would like a way to run the autovacuum daemon on demand > >>periodically. Every night at 2 AM, for example. > >> > >>Anybody know if this is possible? If not, it's a feature request :-) > > > >Autovacuum can be enabled

Re: [GENERAL] encoding advice requested

Rick Schumeyer wrote: > I will have to try the WIN1252 encoding. > > On the client side, my application is a web browser. On the server > side, it is php scripts on a linux box. The data comes from copying > data from a browser window (pointing to another web site) and pasting it > i

Re: [GENERAL] AutoVacuum on demand?

On Nov 13, 2006, at 1:05 PM, Glen Parker wrote: Matthew T. O'Connor wrote: Glen Parker wrote: I would like a way to run the autovacuum daemon on demand periodically. Every night at 2 AM, for example. Anybody know if this is possible? If not, it's a feature request :-) Autovacuum can be

Re: [GENERAL] Table and Field namestyle best practices?

> I'm having a major internal debate about how I'm going to adjust > my habits to pgsql's 'lowercase is simplest' reality.. FYI, I adapted by going to "quote everything". It takes getting used to, but now I never have to worry about any keyword conflicts, ever, past present or future. And I don't

[GENERAL] can't start postgresql

Hi All, I've got a problem w/ one of pgsql installations. It can't start: [EMAIL PROTECTED] ~]$ /usr/pgsql/bin/postgres -D /usr/pgsql/data PANIC: failed to re-find parent key in "23724" Aborted This is PostgreSQL 8.1.5 compiled from sources, on Fedora Core4. Originally it was 8.1.2, and the bu

Re: [GENERAL] chop off non-meaningful digits

"SunWuKung" <[EMAIL PROTECTED]> writes: > Yep, I think this is it: > select trim(trailing '0.' from 1.020) Um, I think not: regression=# select trim(trailing '0.' from 1000.000); rtrim --- 1 (1 row) regards, tom lane ---(end of broadcast)-

Re: [GENERAL] chop off non-meaningful digits

am Tue, dem 14.11.2006, um 0:58:56 -0500 mailte Tom Lane folgendes: > "SunWuKung" <[EMAIL PROTECTED]> writes: > > Yep, I think this is it: > > select trim(trailing '0.' from 1.020) > > Um, I think not: > > regression=# select trim(trailing '0.' from 1000.000); > rtrim > --- > 1 > (1 row)