A long time ago, in a galaxy far, far away, [EMAIL PROTECTED] (Frans) wrote:
> This yields the message: 'Aggregates not allowed in WHERE clause'.
>
> Can somebody help me here
What it says is right; SQL does not permit using aggregates in the
WHERE clause.
You need to look at the HAVING claus
Chris Smith wrote:
The easiest way to start is to turn on query logs for your server and
see if it gets that far.
When turning on query logs in Postgresql, I only get the content of the
prepared statement without the parameters (question mark). I there a way
to expand what is executed by the ser
On Thu, Dec 16, 2004 at 12:02:34PM +0700, Frans wrote:
> I try to use : select name, sum(quantity) from info where
> sum(quantity)>20 group by name;
> This yields the message: 'Aggregates not allowed in WHERE clause'.
For aggregates use HAVING, not WHERE:
SELECT name, SUM(quantity)
FROM info
GRO
On Wed, Dec 15, 2004 at 11:08:29PM -0500, Ron Peterson wrote:
> How are bytea values encoded internally?
>
> Or maybe a better question would be what is the proper way to access
> bytea data from within a C function? Are there utility functions for
> reading the bytea data as a stream of scalar
Frans <[EMAIL PROTECTED]> writes:
> I try to use : select name, sum(quantity) from info where
> sum(quantity)>20 group by name;
> This yields the message: 'Aggregates not allowed in WHERE clause'.
> Can somebody help me here
See
http://www.postgresql.org/docs/7.4/static/tutorial-agg.html
part
On Wed, Dec 15, 2004 at 11:08:29PM -0500, Ron Peterson wrote:
> How are bytea values encoded internally?
>
> Or maybe a better question would be what is the proper way to access
> bytea data from within a C function? Are there utility functions for
> reading the bytea data as a stream of scalar
Hi All,
I have a table (table info) with 2 column, column 'name' and column 'quantity'.
name | quantity
--
a | 5
b | 3
a | 3
c | 4
b | 6
If I want to sum the sum of all entry in table.
name | quantity
a
How are bytea values encoded internally?
Or maybe a better question would be what is the proper way to access
bytea data from within a C function? Are there utility functions for
reading the bytea data as a stream of scalar values, for example?
--
Ron Peterson
Network & Systems Manager
Mount Ho
Despite 30 minutes of googling, I am unable to find any debian
packages for Postgresql 8.0.0 RC1.
Does anyone know where I might obtain them?
Sw.
---(end of broadcast)---
TIP 8: explain analyze is your friend
On Tue, 14 Dec 2004 15:38:45 -0600, Thomas F.O'Connell <[EMAIL PROTECTED]>
wrote:
[snip]
> Would a more ideal setup be a six-drive server with a RAID 1+0 on four
> drives with 2 drives dedicated to WAL that mirrored one another? Is
> that overkill? Anyone have recommendations for an appropriate
Allan,
Postgres is probably not the ideal solution to this problem. If you'd
like to try this though, two points:
- If the table really only has 20 rows, drop the index. If the table
really only has 20 active rows at a
time, then the planner will never use that index.
(run EXPLAIN on your quer
On Wed, Dec 15, 2004 at 03:37:05PM -0800, Satan Devil wrote:
> Can a database notify all clients when something
> changes and send the changes to the clients?
Sure. Use LISTEN and NOTIFY.
--
Alvaro Herrera (<[EMAIL PROTECTED]>)
Al principio era UNIX, y UNIX habló y dijo: "Hello world\n".
No dij
Satan Devil wrote:
> Suppose that there are 3 workstations connected to a
> central database. When Workstation A changes
> something, Workstation B and C must get the changes.
Not really clear what you are trying to accomplish. The simple fact
that all 3 workstations are communicating with a cent
The easiest way to start is to turn on query logs for your server and
see if it gets that far.
If it does - copy the query into psql and run it manually - it will tell
you if there's a problem.
If it doesn't get that far, then that's another problem altogether...
Regards,
Chris Smith
Suite 30,
Can a database notify all clients when something
changes and send the changes to the clients?
Let me explain.
Suppose that there are 3 workstations connected to a
central database. When Workstation A changes
something, Workstation B and C must get the changes.
So, can any database do that? If it
Hello,
I am running an application on Postgresql 8.0.0 rc1. The application is
running on JBoss 4.0.1RC2 and I am using the JDBC driver
pg80b1.308.jdbc3.jar.
My problem is that insert that are sent to the database through a
prepared statement are not written in the database. The application is
You probably want something more like:
SELECT DISTINCT ON (id), * FROM
(
UNION ALL
);
The fact that UNION sorts at all is a side-effect of the
implementation. The distinct part is part of the SQL spec. Use UNION
ALL to get all the rows and then DISTINCT ON to do what you actually
want..
On Mon, 13 Dec 2004 23:00:45 -0700 Michael Fuhr <[EMAIL PROTECTED]> wrote:
> Do you mean that you don't need foreign key constraints to enforce
> referential integrity, but only to cascade changes to another table?
> If so, have you considered using triggers instead? Or have I
> misunderstood what
Dave Brazzeal wrote:
> I'm running postgreSQL on SLES 8
No, there isn't. If we were able to write a repair utility, we might as
well fix the code to prevent the damage in the first place.
If you have a problem that you need to repair, please provide specifics.
--
Peter Eisentraut
http://devel
[EMAIL PROTECTED] (Martijn van Oosterhout) writes:
> On Tue, Dec 14, 2004 at 05:15:08PM -0800, Tim Vadnais wrote:
>> I tried to rebuild the two indexes and got the following error. The
>> optional FORCE has no effect on the output.
>>
>> bwks=# reindex index pg_database_oid_index;
>> ERROR: shar
Miles Keaton <[EMAIL PROTECTED]> writes:
> So --- I think if you install PostgreSQL from FreeBSD ports, and start
> the server, and THEN run initdb, it will give you this error.
What this says is that you're running PG with settings that use up more
than half of the kernel's default limit on the n
I'd like to UNION two queries but the distinct-criteria for UNION should
not be all columns in the queries, but only one.
example. two tables:
test=# select id,name from t1;
id | name
+--
1 | bob
2 | mike
(2 rows)
test=# select id,name from t2;
id | name
+-
1 | bob
I'm running postgreSQL on SLES 8
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
I'm posting this here for search-engine's sake, so future people
having this same problem can find the solution here.
After installing PostgreSQL from FreeBSD's ports, and running su -
pgsql -c initdb for the first time, I got this common error:
could not create semaphores : No space left on devi
No, PostgreSQL doesn't have an equivalent to Oracle's dbms_job, but this
is a question that comes up pretty often. I think the bulk of the work
to make this happen could be done outside of the core database, so it
seems a good candidate for pgfoundry.
On Sat, Dec 11, 2004 at 07:44:03AM +0100, Egy?
Maybe this could be handled by logging the fact that the index is being
modified. Then during recovery, if an index was being modified, and
the log doesn't indicate that the modification was completed, the index
can be rebuilt?
On Dec 15, 2004, at 10:02 AM, Scott Marlowe wrote:
On Tue, 2004-12
Hi.
I have a
CREATE TYPE ccnumber AS (
lengthSMALLINT,
tail VARCHAR(4),
encrypt TEXT
);
for credit card number storage. I would like this to be completely
transparent. However I can not get it to be a drop in replacement for a
text type, no matter what casts I create. I always ha
Thank you Tom, your suggestion was exactly what I needed.
Two tables in view "tpv" were being joined on a column with different
data types.
One was "text" and the other one was "varchar(10)". The 'old' system
did not complain.
The 'new' system does not allow this sloppyness on my part. When
My mistake. There was something else going on and I misinterpreted
the cause of the problem. The foreign key references are valid within
the transaction even though the initial updates are not yet committed.
apologies for the distraction and thanks again for your help.
On Wed, 15 Dec 2004 10:3
On Tue, Dec 14, 2004 at 05:15:08PM -0800, Tim Vadnais wrote:
> I tried to rebuild the two indexes and got the following error. The
> optional FORCE has no effect on the output.
>
> bwks=# reindex index pg_database_oid_index;
> ERROR: shared index "pg_database_oid_index" can only be reindexed in
On Wed, Dec 15, 2004 at 11:53:55AM -0500, Larry White wrote:
> I have a function that calls other functions. These other functions
> are inserting rows and return the primary key for the inserted row.
> Some of the tables are related in a way that they have a foreign key
> reference to a table
I have never tested a particular scenario like this out, but would AFTER
INSERT triggers resolve this issue for you?
On Wednesday 15 December 2004 11:53 am, Larry White saith:
> Hi,
>
> I've run into a situation (I should have forseen) and was hoping
> someone could show me a way out.
>
> I have
I remember reading someone's post recently, that provided directions
for using compiler directives to optimize Postgres for the G5 chip. If
that's you, could you please repost this, or send me a link, if it's on
a web site somewhere.
Thanks,
Doug
---(end of broadcast)---
Hi,
I've run into a situation (I should have forseen) and was hoping
someone could show me a way out.
I have a function that calls other functions. These other functions
are inserting rows and return the primary key for the inserted row.
Some of the tables are related in a way that they have
On Tue, 2004-12-14 at 18:43, Dann Corbit wrote:
> Would it be possible to rebuild all non-btree indexes when a recovery
> takes place?
Considering how long I've seen some large hash indexes take to build,
that might be better left as an optional setting.
> Another thing that seems it might be nic
On Wed, Dec 15, 2004 at 10:22:31 +1100,
"Harvey, Allan AC" <[EMAIL PROTECTED]> wrote:
> Hi all,
>
> I'm after suggestions on how to keep the initial performance
> of a system I've put together for longer than 2 minutes.
>
> I have a small table about 20 rows, a constant, that is receiving
> abo
> I'm trying to write a stored procedure in plpgsql that selects a row
> and possibly increments one of its fields. I thought I would do SELECT
> INTO my_record * FROM my_table FOR UPDATE WHERE ..., but apparently
> plpgsql doesn't like the FOR UPDATE in a stored procedure. Does plpgsql
> automatic
On Wed, 2004-12-15 at 01:38 -0800, Eric Brown wrote:
>
>
> __
>
> I'm trying to write a stored procedure in plpgsql that selects a row
> and possibly increments one of its fields. I thought I would do SELECT
> INTO my_record * F
Hi,
Citing Nadia Kunkov <[EMAIL PROTECTED]>:
> Locally I have no problems connecting to the database and running psql.
> I need to set up an ODBC connection from my Windows box.
> Here is what I've done:
>
> 1.In postgresql.conf
> tcpip_socket = true
> (By the way does this eli
I've got a few plpgsql stored functions (in 7.4.x) that use the
select x into y from table where condition for update
syntax without any problem.
Maybe there's something else going on?
John Sidney-Woollett
Eric Brown wrote:
I'm trying to write a stored procedure in plpgsql that selects a row and
p
I'm trying to write a stored procedure in plpgsql that selects a row and possibly increments one of its fields. I thought I would do SELECT INTO my_record * FROM my_table FOR UPDATE WHERE ..., but apparently plpgsql doesn't like the FOR UPDATE in a stored procedure. Does plpgsql automatically lock
Eric Brown wrote:
I've got quite a few plpgsql functions that insert, update or delete.
They're all declared to return void. All other functions, I can just run
'select f(...);' from psql to test them. I don't understand how to test
these ones that return void from psql. Thanks.
I always return
I've got quite a few plpgsql functions that insert, update or delete.
They're all declared to return void. All other functions, I can just
run 'select f(...);' from psql to test them. I don't understand how to
test these ones that return void from psql. Thanks.
---(end o
Alain M. Gaudrault wrote:
I've come across a problem which is only happening intermittently, and
having a difficult time finding information to help me determine what is
the source of my frustration.
The symptom is the error reported in the subject. We invoke SQL
statements from a Java servlet
On Wed, Dec 15, 2004 at 12:19:14AM -0800, Eric Brown wrote:
> I've got quite a few plpgsql functions that insert, update or delete.
> They're all declared to return void. All other functions, I can just
> run 'select f(...);' from psql to test them. I don't understand how to
> test these ones t
Thanks. I forgot the 'return;' and the error message led me in the
wrong direction. Thanks!
On Dec 15, 2004, at 12:43 AM, Neil Conway wrote:
Eric Brown wrote:
I've got quite a few plpgsql functions that insert, update or delete.
They're all declared to return void. All other functions, I can jus
Eric Brown wrote:
I've got quite a few plpgsql functions that insert, update or delete.
They're all declared to return void. All other functions, I can just run
'select f(...);' from psql to test them. I don't understand how to test
these ones that return void from psql.
neilc=# create function
47 matches
Mail list logo
