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 =
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:
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
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
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
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
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
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; ?
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
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
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
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
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
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)
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
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
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
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
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
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
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
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
--- 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,
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) ...
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:
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
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
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
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
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?
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
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
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
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
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
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
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:
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
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
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/
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
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
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
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
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';
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';
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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,
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
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.
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
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=#
66 matches
Mail list logo
