Thanx Tom.
On Πεμ 14 ÎÎ±Ï 2013 12:17:46 Tom Lane wrote:
Achilleas Mantzios ach...@matrix.gatewaynet.com writes:
dynacom=# SELECT DISTINCT ON (qry.setid) qry.setid, qry.arragg[1:2] FROM
(select distinct sst.setid,(array_agg(vsl.name) OVER (PARTITION BY
sst.setid ORDER BY
dynacom=# SELECT DISTINCT ON (qry.setid) qry.setid, qry.arragg[1:2] FROM
(select distinct sst.setid,(array_agg(vsl.name) OVER (PARTITION BY
sst.setid ORDER BY character_length(vsl.name))) as arragg
FROM sissets sst, vessels vsl WHERE vsl.id=sst.vslid ORDER BY
sst.setid) as qry
Achilleas Mantzios ach...@matrix.gatewaynet.com writes:
dynacom=# SELECT DISTINCT ON (qry.setid) qry.setid, qry.arragg[1:2] FROM
(select distinct sst.setid,(array_agg(vsl.name) OVER (PARTITION BY
sst.setid ORDER BY character_length(vsl.name))) as arragg
FROM sissets sst, vessels
While trying to create some views I stumbled on some
problem with using the if-then clause. Here is a
simple example :
CREATE OR REPLACE VIEW public.SomeView
as select d.id,
if (true) then d.DocNumber endif from
public.Z_Documents as d;
I get the following error :
syntax error at or near
On Jan 18, 2006, at 18:18 , Emil Rachovsky wrote:
CREATE OR REPLACE VIEW public.SomeView
as select d.id,
if (true) then d.DocNumber endif from
public.Z_Documents as d;
I get the following error :
syntax error at or near then at character 72
Well, one problem is that IF ... ENDIF is the
On Jan 18, 2006, at 4:18 AM, Emil Rachovsky wrote:
While trying to create some views I stumbled on some
problem with using the if-then clause. Here is a
simple example :
CREATE OR REPLACE VIEW public.SomeView
as select d.id,
if (true) then d.DocNumber endif from
public.Z_Documents as d;
Am Samstag, 30. Juli 2005 17:15 schrieb Tom Lane:
The unconstrained join against pg_user is clearly unnecessary,
and in fact I took it out a few days ago. I'm not sure whether the
SELECT DISTINCT is still needed --- it might be, if there can be
multiple pg_depend entries linking the same
Ok. I think I found the problem is related to this Bug.
is there anywhere to check the status of this bug?
regards,
=
Riccardo G. Facchini
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
I am going to try to move this over the sql list, since it doesn't belong
on bugs.
On Tue, Feb 24, 2004 at 23:47:48 +1300,
Martin Langhoff [EMAIL PROTECTED] wrote:
Tom Lane wrote:
How about
SELECT nextval('seq'); -- ignore result
INSERT INTO ... VALUES (currval('seq'),
Herbert R. Ambos [EMAIL PROTECTED] writes:
[ drops only column of a table ]
Is this allowed in SQL?
The SQL spec forbids that. We deliberately decided to ignore the spec
restriction, because it creates too many unpleasant boundary cases for
tools that want to manipulate table definitions.
Hey guys,
I found this interesting
test=# create table t ( c char);
CREATE TABLE
test =# \d t
Table
public.t
Column |
Type | Modifiers
+--+---
c | character(1) |
test =# alter table t drop column c;
ALTER TABLE
test =# \d t
Table
I'm trying to do a create function using JDBC 3,0 in Eclipse IDE with JFaceDBC plugin.
This function doesn't work:
CREATE FUNCTION @[EMAIL PROTECTED]@[EMAIL PROTECTED] ()
RETURNS boolean
LANGUAGE SQL
AS '
ALTER TABLE @[EMAIL PROTECTED]@[EMAIL PROTECTED]
Sorry for this misplaced question.
Olivier Hubaut wrote:
[snip]
--
Ci-git une signature avortee.
** RIP **
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
: Burr, Colin [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Wednesday, November 26, 2003 8:37 AM
Subject: [SQL] Bug: Sequence generator insert
Dear Sir,
I found a sequence generator software bug associated with duplicate key
inserts that may be of interest to you.
I first created a table
for the
id (ir didn't use the default) but still used (and therefore incremented)
the sequence.
Regards
Iain
- Original Message -
From: Burr, Colin [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Wednesday, November 26, 2003 8:37 AM
Subject: [SQL] Bug: Sequence generator insert
Dear Sir,
I
Dear Sir,
I found a sequence generator software bug associated with duplicate key
inserts that may be of interest to you.
I first created a table with a primary key based on a sequence generator.
The following script provides an example.
CREATE SEQUENCE id_seq start 1 increment 1 maxvalue
On Tue, Nov 25, 2003 at 18:37:41 -0500,
Burr, Colin [EMAIL PROTECTED] wrote:
However, even though the new record failed to be inserted into the table,
the sequence generator was still updated.
The sequence generator should only be updated if the record is successfully
inserted into the
I have found a strange behaviour that I don't know if is a bug or not.
I have three tables:
* abilitazione with a primary key of (comuneid, cassonettoid, chiaveid)
* cassonetto with a primary key of (comuneid, cassonettoid)
* chiave with a primary key of (comuneid, chiaveid)
and two foreign
On Thu, 30 Oct 2003, Michele Bendazzoli wrote:
I have found a strange behaviour that I don't know if is a bug or not.
I have three tables:
* abilitazione with a primary key of (comuneid, cassonettoid, chiaveid)
* cassonetto with a primary key of (comuneid, cassonettoid)
* chiave with a
Michele Bendazzoli wrote:
I have found a strange behaviour that I don't know if is a bug or not.
I have three tables:
* abilitazione with a primary key of (comuneid, cassonettoid, chiaveid)
* cassonetto with a primary key of (comuneid, cassonettoid)
* chiave with a primary key of (comuneid,
On Thu, 2003-10-30 at 18:29, Jan Wieck wrote:
Not entirely. On which table(s) are the REFERENCES constraints and are
they separate per column constraints or are they multi-column constraints?
here are the constraints of the abilitazione table
ALTER TABLE public.abilitazione
ADD CONSTRAINT
Confirmed, that's a bug - pgsql-hackers CC'd and scipt for full
reproduction attached.
This can also be reproduced in 7.4-beta5.
My guess out of the blue would be, that the rewriter expands the insert
into one insert with the where clause, one update with the negated where
clause. Executed in
Good day.
AFAIK PostgreSQL provides the type TEXT with 4-byte prefix length
which is distinct to C's zero-terminated (char *)
That's very good.
Then I expect natural possibility to store texts having zero characters.
try
SELECT 'abc\0de';
SELECT length('abc\0de');
or insert such a value into
On Tue, 28 Oct 2003, sad wrote:
Good day.
AFAIK PostgreSQL provides the type TEXT with 4-byte prefix length
which is distinct to C's zero-terminated (char *)
That's very good.
Then I expect natural possibility to store texts having zero characters.
If you want to store zero characters (or
sad [EMAIL PROTECTED] writes:
Then I expect natural possibility to store texts having zero characters.
You expect wrong; we don't support embedded nulls in text values, nor in
literal strings. You can store nulls in BYTEA fields, but you have to
use bytea's escaping conventions to represent the
All,
Im not certain if what Im trying to do is
legal, but if I execute a statement like:
UPDATE my_table SET field1=new_value AND SET
field2=different_value WHERE my_table_id = key;
in psql, it reports that it has successfully updated one
record. However, the record does not
On Fri, Oct 03, 2003 at 09:18:44 -0400,
John B. Scalia [EMAIL PROTECTED] wrote:
All,
I'm not certain if what I'm trying to do is legal, but if I execute a
statement like:
UPDATE my_table SET field1='new_value' AND SET field2='different_value'
WHERE my_table_id = 'key';
It
Shouldn't that be UPDATE my_table SET field1 = 'new_value', field2 =
'different_value' WHERE my_table_id = 'key';?
Wei
On Fri, 3 Oct 2003, John B. Scalia wrote:
All,
I'm not certain if what I'm trying to do is legal, but if I execute a
statement like:
UPDATE my_table SET
John,
UPDATE my_table SET field1='new_value' AND SET field2='different_value'
WHERE my_table_id = 'key';
Well, your SQL is bad:
UPDATE my_table SET field1='new_value, field2='different_value'
WHERE my_table_id = 'key';
in psql, it reports that it has successfully updated one record.
John,
Yeah, I figured out my SQL was bad and had switched to the comma
separated version, instead. In my mind, the first form should have
caused an error. I've attached a cut-and-pasted session from psql where
I used this syntax on a test table. While edited for brevity and to
obscure
John B. Scalia [EMAIL PROTECTED] writes:
UPDATE my_table SET field1='new_value' AND SET field2='different_value'
WHERE my_table_id = 'key';
The other responses have focused on your obvious syntax error, but I'm
assuming you didn't actually cut-and-paste that from your psql session.
in psql,
I think i found a bug in PL/PGSQL: when i use a parameter bigint (int8)
and call the function from psql an error message which says that the
functioname(bigint) doesn't exist is displayed.
If i turn the int8 to int4 all works fine ...
Now i use two int4 instead of one int8: is advisable?
ciao,
On Fri, 22 Aug 2003, Michele Bendazzoli wrote:
I think i found a bug in PL/PGSQL: when i use a parameter bigint (int8)
and call the function from psql an error message which says that the
functioname(bigint) doesn't exist is displayed.
If i turn the int8 to int4 all works fine ...
I can't
On Friday 22 August 2003 12:59, Michele Bendazzoli wrote:
I think i found a bug in PL/PGSQL: when i use a parameter bigint (int8)
and call the function from psql an error message which says that the
functioname(bigint) doesn't exist is displayed.
If i turn the int8 to int4 all works fine ...
On Fri, 2003-08-22 at 15:05, Richard Huxton wrote:
On Friday 22 August 2003 12:59, Michele Bendazzoli wrote:
I think i found a bug in PL/PGSQL: when i use a parameter bigint (int8)
and call the function from psql an error message which says that the
functioname(bigint) doesn't exist is
The following SELECT are errors?!
tpcr=# select extract (week from date '2000-01-01');
date_part
---
52
(1 row)
tpcr=# select extract (week from date '2000-01-02');
date_part
---
52
(1 row)
Please check ...
thanks
Hans
---(end of
Minghann Ho [EMAIL PROTECTED] writes:
The following SELECT are errors?!
No, they're not. PG follows the ISO definition of week numbers:
Monday is the first day of the week, and the first week of a year
is the first one containing a Thursday.
regards, tom lane
There seems to be a bug when dumping a view which is a UNION of selects, one
of which has an ORDER BY. A pair of paranthesises around the select is
missing, and this cause a subsequent restore to fail. This is quite annoying
as the backup file must be manually edited before it can be restored,
Kristian Eide [EMAIL PROTECTED] writes:
There seems to be a bug when dumping a view which is a UNION of selects, one
of which has an ORDER BY. A pair of paranthesises around the select is
missing, and this cause a subsequent restore to fail.
Yeah. This is fixed in current sources, and I
Yeah. This is fixed in current sources, and I back-patched it into
the REL7_2 branch, but current plans don't seem to include a 7.2.2
release --- we'll be going straight to 7.3 beta instead.
Is it worth doing a 7.2.2 patch that will dump people's foreign keys as
ALTER TABLE/ADD FOREIGN KEY
Christopher Kings-Lynne [EMAIL PROTECTED] writes:
Yeah. This is fixed in current sources, and I back-patched it into
the REL7_2 branch, but current plans don't seem to include a 7.2.2
release --- we'll be going straight to 7.3 beta instead.
Is it worth doing a 7.2.2 patch that will dump
What is in the 7.2.X CVS that we would want to release?
---
Tom Lane wrote:
Christopher Kings-Lynne [EMAIL PROTECTED] writes:
Yeah. This is fixed in current sources, and I back-patched it into
the REL7_2 branch, but
Bruce Momjian [EMAIL PROTECTED] writes:
What is in the 7.2.X CVS that we would want to release?
CVS logs show the following as post-7.2.1 changes in REL7_2_STABLE branch.
Draw your own conclusions ...
regards, tom lane
2002-06-15 14:38 tgl
*
Kevin,
After doing so, you'll find that postgres actually crashes when you
try
to insert a vote into the uservote table. That's the one that has me
looking at the costs involved with migrating to Oracle.
And you think that Oracle is entirely free of bugs? ;-)
At least here, you can get a
And you think that Oracle is entirely free of bugs? ;-)
Yes, but they'd be exciting technology-oriented e-business enabled bugs!
Still, I understand your frustration.
Thanks... It's just frustrating that the bug is on something so basic,
which makes it both hard to code around and hard
Hello (mainly developer) folks!
Probably Kevin really found a bug.
When I saw his words in $50, I immediately started to look around his
problem... You probably don't think that as a student here, in Hungary I
live half a month for $50 :-
So I simplified his given schema as much as I
While we're on this subject, where is the documentation on
pg_shadow? Specifically, what it 'usetrace' for?
-Cedar
On Fri, 13 Apr 2001, Tom Lane wrote:
=?iso-8859-1?Q?Hans=2DJ=FCrgen=20Sch=F6nig?= [EMAIL PROTECTED] writes:
I have created a user called epi who is not allowed to create
I need some information about PostgreSQL user management.
I have created a user called epi who is not allowed to create database
but allowed to create users.
I have connected as user epi and have then created user kertal with
the following command:
shop=# CREATE USER kertal WITH PASSWORD
Jeff MacDonald [EMAIL PROTECTED] writes:
A person recent pointed this out to me..
seems a bit funny, because limit 1 pretty much
say's it't not gonna return multiple values.
CREATE FUNCTION vuln_port(int4) RETURNS int4 AS 'SELECT port FROM
i_host_vuln WHERE vuln = $1 GROUP BY port ORDER BY
Greetings,
How can this happen?
chris=# select name from boys_names where
boys_names.number=(random()*225+1)::int4;
name
Fred
Gunnar
Manuel
Rainer
(4 rows)
Here is the table declaration:
create table boys_names (
number serial primary key,
name
Christopher Sawtell [EMAIL PROTECTED] writes:
How can this happen?
chris=# select name from boys_names where
boys_names.number=(random()*225+1)::int4;
name
Fred
Gunnar
Manuel
Rainer
(4 rows)
Er, what's your complaint exactly? It's not obvious to me that
there's
On Mon, 19 Feb 2001, Christopher Sawtell wrote:
How can this happen?
chris=# select name from boys_names where
boys_names.number=(random()*225+1)::int4;
name
Fred
Gunnar
Manuel
Rainer
(4 rows)
It's not clear what you expected to see -- I'll guess you expected only
one
Folks,
Where do I send bug reports for 7.1 beta? I;'ve looked on the web
site, and don't see an address or bugtraq forum.
-Josh Berkus
--
__AGLIO DATABASE SOLUTIONS___
Josh
On Wed, 7 Feb 2001, Josh Berkus wrote:
Folks,
Where do I send bug reports for 7.1 beta? I;'ve looked on the web
site, and don't see an address or bugtraq forum.
Probably the best is the pgsql-bugs mailing list at:
[EMAIL PROTECTED]
On Sat, 3 Feb 2001, Tom Lane wrote:
I get
regression=# SELECT * FROM orders;
order_id | menu_id | price
--+-+---
1 | 2 |-1
(1 row)
which is the correct result given that rules are executed before the
original query. (Which is why you
Tod McQuillin [EMAIL PROTECTED] writes:
How the heck can one insert and update generate three rows?
Looks like a rule rewriter bug to me. It seems to be fixed in 7.1;
I get
regression=# SELECT * FROM orders;
order_id | menu_id | price
--+-+---
1 | 2 |-1
On Sat, 3 Feb 2001, Tom Lane wrote:
I get
regression=# SELECT * FROM orders;
order_id | menu_id | price
--+-+---
1 | 2 |-1
(1 row)
which is the correct result given that rules are executed before the
original query. (Which is why you need a
What is it actually giving you as an error
message in the failing case? Someone pointed
out a problem in deferred constraints recently
and I think this may be related.
Stephan Szabo
[EMAIL PROTECTED]
On Mon, 20 Nov 2000, Kyle wrote:
Here's an interesting test of referential integrity. I'm
Here's an interesting test of referential integrity. I'm not sure
if this is working the way it should or if it is a bug.
I'm trying to update the primary key in records that are linked together
from the two different tables. My initial assumption was that because
of the cascade, I could update
Greetings,
Working with PostGreSQL 7.02, I found the following problem:
The AM/PM designator in the to_char function does not work proper for 13:00
and 12:00.
See the following:
test= select to_char('3-12-2000 14:00'::timestamp, 'Dy, HH12:MI PM');
to_char
---
Sun, 02:00
I've notice on certain queries, I was waiting a long time for a return so I
set out to troubleshoot something here's what I ran into...
When I do a explain on a select looking for 'peripherals access' it uses
sequential scan but any other keyword uses index scan.
I've ran the vacuum analyze on
61 matches
Mail list logo