[GENERAL] A better BETWEEN for DATEs, TIMEs and TIMESTAMPs?

2009-01-14 Thread Reg Me Please
While I do understand that the BETWEEN operator is actually synctactic sugar, from time to time I find myself wondering about a better BETWEEN for DATEs, TIMEs and TIMESTAMPs (but not only these ones). Infact I always have managed ranges where the lower part is to be matched with the =

[GENERAL] inconsistency in aliasing

2009-01-14 Thread Louis-David Mitterrand
Hi, This works: critik=# select current_timestamp::abstime::int4 as score order by score; This doesn't: critik=# select current_timestamp::abstime::int4 as score order by score + 1; ERROR: column score does not exist LINE 1:

Re: [GENERAL] inconsistency in aliasing

2009-01-14 Thread Reg Me Please
On Wednesday 14 January 2009 11:46:11 Louis-David Mitterrand wrote: Hi, This works: critik=# select current_timestamp::abstime::int4 as score order by score; This doesn't: critik=# select current_timestamp::abstime::int4 as score order by score + 1; ERROR: column score

Re: [GENERAL] inconsistency in aliasing

2009-01-14 Thread A. Kretschmer
In response to Louis-David Mitterrand : Hi, This works: critik=# select current_timestamp::abstime::int4 as score order by score; This doesn't: critik=# select current_timestamp::abstime::int4 as score order by score + 1; ERROR: column score does not exist

Re: [GENERAL] inconsistency in aliasing

2009-01-14 Thread ludwig
I don't know, if this is an inconsistence or a bug, but here a possible workaround: select current_timestamp::abstime::int4 as score, current_timestamp::abstime::int4 + 1 as score + 1 order by score + 1; Ludwig This works: critik=# select current_timestamp::abstime::int4 as

Re: [GENERAL] inconsistency in aliasing

2009-01-14 Thread dbalinglung
what for of the syntax command +1 on order by ? maybe just wrong to given result about the error query on order by, it's BUG ? dbalinglung DataproSoft Developer - Original Message - From: Louis-David Mitterrand vindex+lists-pgsql-gene...@apartia.org To: pgsql-general@postgresql.org

Re: [GENERAL] inconsistency in aliasing

2009-01-14 Thread Louis-David Mitterrand
On Wed, Jan 14, 2009 at 06:31:55PM +0700, dbalinglung wrote: what for of the syntax command +1 on order by ? maybe just wrong to given result about the error query on order by, it's BUG ? *PARSE ERROR* -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to

Re: [GENERAL] inconsistency in aliasing

2009-01-14 Thread A. Kretschmer
In response to Louis-David Mitterrand : Yes, you can't use the alias in the ORDER BY. Use the real column-name. select current_timestamp::abstime::int4 as score order by current_timestamp::abstime::int4; Did you try select current_timestamp::abstime::int4 as score order by score; ?

Re: [GENERAL] inconsistency in aliasing

2009-01-14 Thread Scott Marlowe
On Wed, Jan 14, 2009 at 3:46 AM, Louis-David Mitterrand vindex+lists-pgsql-gene...@apartia.org wrote: Hi, This works: critik=# select current_timestamp::abstime::int4 as score order by score; This doesn't: critik=# select current_timestamp::abstime::int4 as score order by

Re: [GENERAL] [ADMIN] Problem with pg_dump

2009-01-14 Thread Emanuel Calvo Franco
2009/1/13 tyrrill...@emc.com: Hi All, I am a developer of a product that uses a postgresql database (currently version 8.2.3.1). We dump the database using pg_dumpall. We are finding data corruption in the dump files about twice a month with a few thousand installations. I have been

Re: [GENERAL] inconsistency in aliasing

2009-01-14 Thread Daniel Verite
Reg Me Please wrote: critik=# select current_timestamp::abstime::int4 as score order by score + 1; ERROR: column score does not exist LINE 1: ...urrent_timestamp::abstime::int4 as score order by score + 1 ... Any idea ? Thanks, Looks and smells like a bug. Read

[GENERAL] Logging on Gentoo

2009-01-14 Thread Thom Brown
Hi, I've configured Postgres to log to stderr and defined my log directory correctly with permissions to postgres on both user and group. I've restarted postgres (not reloaded) but nothinig is coming out. Upon reading the documentation for using stderr, it mentions the need to change the

Re: [GENERAL] Logging on Gentoo

2009-01-14 Thread Richard Huxton
Thom Brown wrote: Hi, I've configured Postgres to log to stderr and defined my log directory correctly with permissions to postgres on both user and group. I've restarted postgres (not reloaded) but nothinig is coming out. Upon reading the documentation for using stderr, it mentions the

[GENERAL] Strange invalid constrain problem with PostgreSQL 8.3.1

2009-01-14 Thread ries van Twisk
hey All, (Resend from novice) I if a problem that apparently I can insert a record into my table with a constrain while in fact the reference doesn't exist: On the table acc_ops.tbl_part_status I have the following constrain added: CONSTRAINT fk_tbl_part_status_2 FOREIGN KEY (part_num)

Re: [GENERAL] Logging on Gentoo

2009-01-14 Thread Thom Brown
Tsk... that was it after all. I didn't have logging_collector set to on, and thanks to that helpful query you mentioned, I could see that the default was off. The reason I didn't change it was because I was trying to copy the settings from a server I use at work which does output logging, but

[GENERAL] simple limit of decimals

2009-01-14 Thread pere roca
hi all, I update a column based on a division over two real values. It causes a huge number of decimals that I wanna to limit. UPDATE point_pol SET taxa_record = ((SELECT ((CASE WHEN numtax0 THEN numtax/numreg ELSE 1 END))) where userid='hello' taxa_record values are real and with real I

Re: [GENERAL] inconsistency in aliasing

2009-01-14 Thread Louis-David Mitterrand
On Wed, Jan 14, 2009 at 12:06:47PM +0100, A. Kretschmer wrote: In response to Louis-David Mitterrand : Hi, This works: critik=# select current_timestamp::abstime::int4 as score order by score; This doesn't: critik=# select current_timestamp::abstime::int4 as score

Re: [GENERAL] simple limit of decimals

2009-01-14 Thread Scott Marlowe
On Wed, Jan 14, 2009 at 5:43 AM, pere roca pero...@gmail.com wrote: hi all, I update a column based on a division over two real values. It causes a huge number of decimals that I wanna to limit. UPDATE point_pol SET taxa_record = ((SELECT ((CASE WHEN numtax0 THEN numtax/numreg ELSE 1

Re: [GENERAL] simple limit of decimals

2009-01-14 Thread pere roca
dear Andreas, thanks for the answer; yes, round should be the first, but it gives an error: Function round(real, integer) does not exists following this mail (http://archives.postgresql.org/pgsql-bugs/2003-01/msg00074.php) I created a new function and now works! best regards, Pere

Re: [GENERAL] Use PSQLFS for photo storage

2009-01-14 Thread Reid Thompson
On Tue, 2009-01-13 at 18:22 -0600, Jason Long wrote: Never used Python or Perl. I use primarily Java. I was thinking of doing something like INSERT INTO pictures (filename,data) VALUES ('filename','/path/to/my/image/img0009.jpg'); But, this syntax doesn't seem to be supported. Maybe I

Re: [GENERAL] simple limit of decimals

2009-01-14 Thread A. Kretschmer
In response to pere roca : hi all, I update a column based on a division over two real values. It causes a huge number of decimals that I wanna to limit. UPDATE point_pol SET taxa_record = ((SELECT ((CASE WHEN numtax0 THEN numtax/numreg ELSE 1 END))) where userid='hello' taxa_record

Re: [GENERAL] Strange invalid constrain problem with PostgreSQL 8.3.1

2009-01-14 Thread Richard Huxton
ries van Twisk wrote: hey All, (Resend from novice) I if a problem that apparently I can insert a record into my table with a constrain while in fact the reference doesn't exist: Well, a test case is what's really needed, but while you're busy with that the obvious points are: 1. Your

Re: [GENERAL] inconsistency in aliasing

2009-01-14 Thread Lennin Caro
--- On Wed, 1/14/09, Louis-David Mitterrand vindex+lists-pgsql-gene...@apartia.org wrote: From: Louis-David Mitterrand vindex+lists-pgsql-gene...@apartia.org Subject: Re: [GENERAL] inconsistency in aliasing To: pgsql-general@postgresql.org Date: Wednesday, January 14, 2009, 11:27 AM On Wed,

Re: [GENERAL] simple limit of decimals

2009-01-14 Thread A. Kretschmer
In response to pere roca : dear Andreas, thanks for the answer; yes, round should be the first, but it gives an error: Function round(real, integer) does not exists UPDATE point_pol SET taxa_record = round(((SELECT ((CASE WHEN numtax0 THEN numtax/numreg ELSE 1 END)))::numeric,2) ...

Re: [GENERAL] inconsistency in aliasing

2009-01-14 Thread Louis-David Mitterrand
On Wed, Jan 14, 2009 at 05:53:57AM -0800, Lennin Caro wrote: --- On Wed, 1/14/09, Louis-David Mitterrand vindex+lists-pgsql-gene...@apartia.org wrote: From: Louis-David Mitterrand vindex+lists-pgsql-gene...@apartia.org Subject: Re: [GENERAL] inconsistency in aliasing To:

Re: [GENERAL] inconsistency in aliasing

2009-01-14 Thread Daniel Verite
Lennin Caro wrote: select score,score+1 as score2 from ( select current_timestamp::abstime::int4 as score) order by score2 That additional score2 is not needed in the select output. This works just fine: = select score from (select current_timestamp::abstime::int4 as score) subsel

Re: [GENERAL] inconsistency in aliasing

2009-01-14 Thread Emanuel Calvo Franco
2009/1/14 Daniel Verite dan...@manitou-mail.org: Lennin Caro wrote: select score,score+1 as score2 from ( select current_timestamp::abstime::int4 as score) order by score2 That additional score2 is not needed in the select output. This works just fine: = select score from (select

Re: [GENERAL] simple limit of decimals

2009-01-14 Thread Emanuel Calvo Franco
2009/1/14 A. Kretschmer andreas.kretsch...@schollglas.com: In response to pere roca : dear Andreas, thanks for the answer; yes, round should be the first, but it gives an error: Function round(real, integer) does not exists UPDATE point_pol SET taxa_record = round(((SELECT ((CASE WHEN

[GENERAL] fire trigger for a row without update?

2009-01-14 Thread Gerhard Heift
Hello, is it possible to call a trigger for a row in a table without updating the row? I want to do it in plpgsql. Something like UPDATE table WHERE id = 10; or PERFORM table.trigger('UPDATE', row) WHERE id = 10; Regards, Gerhard signature.asc Description: Digital signature

Re: [GENERAL] Polymorphic setof record function?

2009-01-14 Thread Merlin Moncure
On 1/13/09, Christian Schröder c...@deriva.de wrote: Hi list, I have written a function that returns a setof record. The function has a table name as a parameter and the resulting records have the same structure as this table. Is there any easy way to specify this when I call the function?

Re: [GENERAL] Multi-tenant cluster

2009-01-14 Thread Roderick A. Anderson
Scott Marlowe wrote: On Wed, Dec 24, 2008 at 8:17 AM, Roderick A. Anderson raand...@acm.org wrote: I've done some searching using Google and found a few papers and articles on multi-tenant databases. I still have to read through many of them but there didn't appear to be much reference to

[GENERAL] Change empty database from ASCII to UTF-8 encoded

2009-01-14 Thread Thom Brown
I've just noticed my postgres database is encoded in ASCII. Is there any way to convert it to UTF-8? Is there any negative consequence to dropping the database and recreating it? Like are there special hidden tables or functions in there? Thanks Thom

Re: [GENERAL] Polymorphic setof record function?

2009-01-14 Thread Ivan Sergio Borgonovo
On Wed, 14 Jan 2009 11:46:29 -0500 Merlin Moncure mmonc...@gmail.com wrote: On 1/13/09, Christian Schröder c...@deriva.de wrote: Hi list, I have written a function that returns a setof record. The function has a table name as a parameter and the resulting records have the same structure

Re: [GENERAL] Change empty database from ASCII to UTF-8 encoded

2009-01-14 Thread Raymond O'Donnell
On 14/01/2009 19:14, Thom Brown wrote: I've just noticed my postgres database is encoded in ASCII. Is there any way to convert it to UTF-8? Is there any negative consequence to dropping the database and recreating it? Like are there special hidden tables or functions in there? AFAIK you

[GENERAL] Odd duplicate database

2009-01-14 Thread Madison Kelly
Hi all, My devel server has some wierdness happening. I tried to drop the database (reload from a copy from the production server) and I got this weird error: pg_dump: query returned more than one (2) pg_database entry for database nexxia So I logged in as postgres and checked, and

Re: [GENERAL] Change empty database from ASCII to UTF-8 encoded

2009-01-14 Thread Thom Brown
If it's not important, I'll just drop and re-create it. :) Thanks Thom 2009/1/14 Raymond O'Donnell r...@iol.ie On 14/01/2009 19:14, Thom Brown wrote: I've just noticed my postgres database is encoded in ASCII. Is there any way to convert it to UTF-8? Is there any negative consequence to

Re: [GENERAL] Odd duplicate database

2009-01-14 Thread Madison Kelly
Forgot to mention, this is PostgreSQL 8.3.5 on Linux (Ubuntu 8.10, hey, it's a devel machine!). :) Madi Madison Kelly wrote: Hi all, My devel server has some wierdness happening. I tried to drop the database (reload from a copy from the production server) and I got this weird error:

Re: [GENERAL] Odd duplicate database

2009-01-14 Thread Alvaro Herrera
Madison Kelly wrote: Hi all, My devel server has some wierdness happening. I tried to drop the database (reload from a copy from the production server) and I got this weird error: I guess this is an old release, and you haven't been vacuuming pg_database regularly, yes? -- Alvaro

Re: [GENERAL] Use PSQLFS for photo storage

2009-01-14 Thread Jason Long
Reid Thompson wrote: On Tue, 2009-01-13 at 18:22 -0600, Jason Long wrote: Never used Python or Perl. I use primarily Java. I was thinking of doing something like INSERT INTO pictures (filename,data) VALUES ('filename','/path/to/my/image/img0009.jpg'); But, this syntax doesn't seem to be

Re: [GENERAL] Odd duplicate database

2009-01-14 Thread Alvaro Herrera
Madison Kelly wrote: Forgot to mention, this is PostgreSQL 8.3.5 on Linux (Ubuntu 8.10, hey, it's a devel machine!). :) Huh. Please send along select xmin, xmax, ctid, cmin, cmax, datname from pg_database; -- Alvaro Herrerahttp://www.CommandPrompt.com/

Re: [GENERAL] Odd duplicate database

2009-01-14 Thread Madison Kelly
Alvaro Herrera wrote: Madison Kelly wrote: Forgot to mention, this is PostgreSQL 8.3.5 on Linux (Ubuntu 8.10, hey, it's a devel machine!). :) Huh. Please send along select xmin, xmax, ctid, cmin, cmax, datname from pg_database; template1=# select xmin, xmax, ctid, cmin, cmax, datname from

[GENERAL] Select CASE when null ?

2009-01-14 Thread Camilo Sperberg
Hi list :) How are you today? Being fast: I have the following table with the following data in it: users: mid --- id_group --- username 1 -2 --- test 2 -2 --- blabla 3 -4 --- etcetc and the following select: SELECT CASE mid WHEN NULL THEN CAST(0 AS

[GENERAL] pgdiff equiv

2009-01-14 Thread Bill Moran
I'm looking for something to help compare databases to find the differences between them. We're in a migration process in how we manage our schemas, and we need something to validate that the new system is still doing it right. pgdiff looked very promising, until I realized that it hasn't been

[GENERAL] Select CASE when null ?

2009-01-14 Thread Camilo Sperberg
Hi list :) How are you today? Being fast: I have the following table with the following data in it: users: mid --- id_group --- username 1 -2 --- test 2 -2 --- blabla 3 -4 --- etcetc and the following select: SELECT CASE mid WHEN NULL THEN CAST(0 AS

Re: [GENERAL] Select CASE when null ?

2009-01-14 Thread Mark Styles
On Wed, Jan 14, 2009 at 05:35:55PM -0300, Camilo Sperberg wrote: SELECT CASE mid WHEN NULL THEN CAST(0 AS integer) ELSE mid END AS mid, CASE id_group WHEN NULL THEN CAST(0 AS integer) ELSE id_group END AS id_group FROM users WHERE username = 'test';

Re: [GENERAL] Select CASE when null ?

2009-01-14 Thread Mark Styles
On Wed, Jan 14, 2009 at 05:35:55PM -0300, Camilo Sperberg wrote: SELECT CASE mid WHEN NULL THEN CAST(0 AS integer) ELSE mid END AS mid, CASE id_group WHEN NULL THEN CAST(0 AS integer) ELSE id_group END AS id_group FROM users WHERE username = 'test';

Re: [GENERAL] Select CASE when null ?

2009-01-14 Thread Camilo Sperberg
On Wed, Jan 14, 2009 at 17:56, Mark Styles postg...@lambic.co.uk wrote: SELECT COALESCE(mid,0) AS mid, COALESCE(id_group,0) AS id_group FROM users WHERE username = 'test' UNION SELECT 0, 0 WHERE NOT EXISTS (SELECT 1 FROM users WHERE username = 'test'); -- Mark http://www.lambic.co.uk

Re: [GENERAL] Select CASE when null ?

2009-01-14 Thread Tom Lane
Camilo Sperberg unrea...@chilehardware.com writes: SELECT CASE mid WHEN NULL THEN CAST(0 AS integer) ELSE mid END AS mid, BTW, the reason this doesn't work is the same reason mid = NULL doesn't work, because that's exactly what the CASE condition is treated as. The COALESCE

Re: [GENERAL] A better BETWEEN for DATEs, TIMEs and TIMESTAMPs?

2009-01-14 Thread Jeff Davis
On Wed, 2009-01-14 at 11:40 +0100, Reg Me Please wrote: Infact I always have managed ranges where the lower part is to be matched with the = comparison operator while the higher part is matched with the . In other words, until now I've always encountered intervals of the type [...) and not

Re: [GENERAL] Odd duplicate database

2009-01-14 Thread Tom Lane
Madison Kelly li...@alteeve.com writes: Alvaro Herrera wrote: Please send along select xmin, xmax, ctid, cmin, cmax, datname from pg_database; template1=# select xmin, xmax, ctid, cmin, cmax, datname from pg_database; xmin | xmax | ctid | cmin | cmax | datname

Re: [GENERAL] A better BETWEEN for DATEs, TIMEs and TIMESTAMPs?

2009-01-14 Thread Jeff Davis
On Wed, 2009-01-14 at 13:35 -0800, Jeff Davis wrote: I think the best solution is to make first-class interval types (for time as well as other types). Those intervals can then have operators like contains and contained by which would solve your problem. Additionally, it would allow lots of

Re: [GENERAL] A better BETWEEN for DATEs, TIMEs and TIMESTAMPs?

2009-01-14 Thread Reg Me Please
On Wednesday 14 January 2009 22:38:07 Jeff Davis wrote: On Wed, 2009-01-14 at 13:35 -0800, Jeff Davis wrote: I think the best solution is to make first-class interval types (for time as well as other types). Those intervals can then have operators like contains and contained by which would

Re: [GENERAL] Odd duplicate database

2009-01-14 Thread Madison Kelly
Tom Lane wrote: Madison Kelly li...@alteeve.com writes: Alvaro Herrera wrote: Please send along select xmin, xmax, ctid, cmin, cmax, datname from pg_database; template1=# select xmin, xmax, ctid, cmin, cmax, datname from pg_database; xmin | xmax | ctid | cmin | cmax | datname

Re: [GENERAL] Odd duplicate database

2009-01-14 Thread Tom Lane
Madison Kelly li...@alteeve.com writes: PS - If I've run into a PgSQL bug, is there anything I can provide to help? A sequence that reproduces it would be the best thing ... regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make

Re: [GENERAL] A better BETWEEN for DATEs, TIMEs and TIMESTAMPs?

2009-01-14 Thread Reg Me Please
On Wednesday 14 January 2009 22:38:07 Jeff Davis wrote: On Wed, 2009-01-14 at 13:35 -0800, Jeff Davis wrote: I think the best solution is to make first-class interval types (for time as well as other types). Those intervals can then have operators like contains and contained by which would

[GENERAL] Postgresql 8.3.3 refuces to start after increasing shared_buffers

2009-01-14 Thread A B
Setting shared_buffers = 28MB makes the startup script say /etc/init.d/postgresql-8.3 restart * Service postgresql-8.3 starting * Starting PostgreSQL ... waiting for server to start...could not start server

Re: [GENERAL] A better BETWEEN for DATEs, TIMEs and TIMESTAMPs?

2009-01-14 Thread Jeff Davis
On Wed, 2009-01-14 at 22:59 +0100, Reg Me Please wrote: I got some compilation error (I run Ubuntu) that needs investigation. From the documentation it seems exactly what I was looking for! Please discuss my module in the mailing list for the project itself, or email me directly with more

Re: [GENERAL] Postgresql 8.3.3 refuces to start after increasing shared_buffers

2009-01-14 Thread A B
Ah, it seems to be a problem with the SHMMAX variable. I think I've solved it now. -- 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] pg_get_serial_sequence Strangeness/Unreliable?

2009-01-14 Thread Bruce Momjian
Alvaro Herrera wrote: Jaime Casanova wrote: can we make \d show if the sequence is owned by the table (ie: serial or manually created and owned) or is a manually created and maked default sequence? maybe a flag? My thought as well Added to TODO: Have \d on a sequence

Re: [GENERAL] Odd duplicate database

2009-01-14 Thread Madison Kelly
Tom Lane wrote: Madison Kelly li...@alteeve.com writes: PS - If I've run into a PgSQL bug, is there anything I can provide to help? A sequence that reproduces it would be the best thing ... regards, tom lane I guess the trick is, I have no idea what's happened or

Re: [GENERAL] Odd duplicate database

2009-01-14 Thread Alvaro Herrera
Madison Kelly wrote: Tom Lane wrote: Madison Kelly li...@alteeve.com writes: PS - If I've run into a PgSQL bug, is there anything I can provide to help? A sequence that reproduces it would be the best thing ... I guess the trick is, I have no idea what's happened or what I did to cause

[GENERAL] Query question

2009-01-14 Thread mailinglists
Hello, Thanks to the replier (Martijn -- thank you very much!) to an earlier question I had about MVCC, I've decided to re-think entirely my use of the status column in a table. What I've decided to do is to put all of my new or changed records into a holding table, then after they are indexed,

[GENERAL] Autovacuum daemon terminated by signal 11

2009-01-14 Thread Justin Pasher
Hello, I have a server running PostgreSQL 8.1.15-0etch1 (Debian etch) that was recently put into production. Last week a developer started having a problem with his psql connection being terminated every couple of minutes when he was running a query. When I look through the logs, I noticed this

Re: [GENERAL] fire trigger for a row without update?

2009-01-14 Thread Albe Laurenz
Gerhard Heift wrote: is it possible to call a trigger for a row in a table without updating the row? I want to do it in plpgsql. Something like UPDATE table WHERE id = 10; or PERFORM table.trigger('UPDATE', row) WHERE id = 10; Think twice if you really need that - it sounds a little odd.

[GENERAL] one-click installer postgresql-8.3.5-1-linux.bin failed

2009-01-14 Thread m zyzy
I had this weird problem in CentOS 5 and Fedora 10 . the one-click binary installer failed execute this ./postgresql-8.3.5-1-linux.bin shows Segmentation fault also with chmod 755 filename prior to execute the binary also showed the same 'Segmentation fault ' message. Thank you in advance

[GENERAL] how can I returns a set of integer in a plpgsql function?

2009-01-14 Thread Yi Zhao
hi, all: there is a function in postgresql contrib int_arrgreagte: CREATE OR REPLACE FUNCTION int_array_enum(int4[]) RETURNS setof integer AS '$libdir/int_aggregate','int_enum' LANGUAGE C IMMUTABLE STRICT; I can use this function like this: chry=# SELECT int_array_enum('{1,2}'); or chry=#