On 15 Mar 2011, at 22:33, Alexander Farber wrote:
I'm trying to change it to a sum, but get the error:
# select u.id, u.first_name, sum(m.completed)
from pref_users u, pref_match m
where u.id=m.id and u.id like 'DE%' and
sum 30 group by u.id, u.first_name
order by sum desc limit 3;
#
Try using dynamic sql:
EXECUTE 'SELECT lions, tigers, bears, statecode FROM WildLife WHERE
state_pt = ' || statecode INTO ...;
Thanks Igor. It was a nice try -- and I thought it would work, but the
Planner had other plans. Basically, I tried the interactive method using a
PREPARE
On 16 Mar 2011, at 6:25, tushar nehete wrote:
Hi All,
In Postgresql we can use savepoint and rollback to savepoint in transaction.
But we cannot use savepoint in function.
Currently I am doing migration from Informix to Postgresql 8.4.
In Informix they used Savepoint.
Please advice how to
create table mock (id int);
begin;
truncate table mock;
insert into mock values (1),(2);
savepoint A;
insert into mock values (3),(4);
savepoint B;
rollback to savepoint A;
end;
Following link may help you.
http://www.postgresql.org/docs/current/static/sql-rollback-to.html
Date:
I know the intensity of ilikes but I don't see another way to solve it.
But that shouldn't be the problem because the query runs on another
server (not as powerful as the actual machine) with postgres 8.3 in
acceptable time (same data, same query).
Each of the collumns of the relation table has
On 3/16/2011 12:40 AM, Alban Hertroys wrote:
Try using dynamic sql:
EXECUTE 'SELECT lions, tigers, bears, statecode FROM WildLife WHERE
state_pt = ' || statecode INTO ...;
Thanks Igor. It was a nice try -- and I thought it would work, but the Planner
had other plans. Basically, I tried the
Hi,
I am having trouble writing my first plpgsql query. Any idea why the
following plpgsql does not work??
test=# drop function if exists testfunc() ;
DROP FUNCTION
test=# create function testfunc()
test-# returns table (id int, code char(1)) as $$
test$# BEGIN
test$# return
Hi,
test=# create function testfunc()
test-# returns table (id int, code char(1)) as $$
test$# BEGIN
test$# return query select id, code from
record_table where id 2;
test$# END;
test$# $$ language plpgsql;
That's due to a clash in the identifiers' names. As you see you
On 15.03.2011 17:24, Merlin Moncure wrote:
well, regardless of the version, you're doing a gazillion sequential
scans on relation tags. This looks like the primary culprit (I had to
look up the ~~* operator...it's 'ilike'):
(
(k ~~* 'boundary'::text) OR
(
(k ~~* 'type'::text)
Hello,
I’m using dblink package to execute queries between two postgresql
databases.
Access definitions for the dblink are created using foreign data wrapper,
server and user mappings.
When I set user and password as options then postgres saves these parameters
in four system tables (table
-Original Message-
From: Alexander Farber [mailto:alexander.far...@gmail.com]
Sent: Tuesday, March 15, 2011 5:45 PM
To: pgsql-general@postgresql.org
Subject: Re: A join of 2 tables with sum(column) 30
And same for a simple select-query from1 table (w/o join):
# select id
Let me explain a few things about our dataset. We are using a system named
Sesame [1] that stores and queries RDF data. In our case, it uses Postgres
as a relational backend. In RDF, data are triples. Here is an example of an
RDF triple:
ex:Postgres rdf:type ex:RDBMS
Triples are stored in
On Thu, Mar 10, 2011 at 3:54 PM, Merlin Moncure mmonc...@gmail.com wrote:
On Thu, Mar 10, 2011 at 3:21 PM, Reece Hart re...@harts.net wrote:
On Wed, Mar 9, 2011 at 9:16 AM, Merlin Moncure mmonc...@gmail.com wrote:
create type validation_flags as
(
cluster bool,
freq bool
);
Wow. That
Hello,
Is there any way of casting (reinterpreting) a varchar/text field
containing arbitrary backslashes to bytea without making an escaped
copy of the varchar/text first? In the examples below I am using a
constant E'...' for clarity, the value normally comes from a
varchar/text column in a
Vlad Romascanu wrote:
Hello,
Is there any way of casting (reinterpreting) a varchar/text field
containing arbitrary backslashes to bytea without making an escaped
copy of the varchar/text first? In the examples below I am using a
constant E'...' for clarity, the value normally comes from a
Hi, Bruce,
Yes, I essentially want to reinterpret text as bytea without any
conversion or actual backslash logic coming in the process, in the
same way pg_convert_from internally reinterprets the bytea return
value from pg_convert as text without any additional logic. I.e.
given the text field
Bruce Momjian br...@momjian.us writes:
Vlad Romascanu wrote:
Is there any way of casting (reinterpreting) a varchar/text field
containing arbitrary backslashes to bytea without making an escaped
copy of the varchar/text first?
Well, the '\\' is being converted to '\' because of the
When I run the following query in Postgres 8.0, it runs in 61,509.372 ms
When I run it in Postgres 8.4, it runs in 397,857.472 ms
Here is the query:
select
course_id AS EXTERNAL_COURSE_KEY,
user_id AS EXTERNAL_PERSON_KEY,
'Student' AS ROLE,
'Y' AS AVAILABLE_IND
from course_user_link
where
Output of explain (and as likely, explain analyze) for this would be
helpful.
A
On Wed, Mar 16, 2011 at 10:49:24AM -0500, Davenport, Julie wrote:
When I run the following query in Postgres 8.0, it runs in 61,509.372 ms
When I run it in Postgres 8.4, it runs in 397,857.472 ms
Here is the
When I run the following query in Postgres 8.0, it runs in 61,509.372 ms
When I run it in Postgres 8.4, it runs in 397,857.472 ms
As Andrew already pointed out, we need to se EXPLAIN ANALYZE output from
both machines to see why this happens. Are you running both queries on the
same data, or is
Hi, Tom,
Why does:
CREATE CAST (text AS bytea) WITHOUT FUNCTION;
SELECT E'C:\\something'::text::bytea;
work as expected, but (with the original text-bytea cast in place):
CREATE DOMAIN my_varlena AS text;
CREATE CAST (my_varlena AS bytea) WITHOUT FUNCTION;
SELECT
Hello,
I'm writing a variable size custom datatype in C. The variable part is
an array of unsigned long, and it needs to be aligned. I further need
to store a few flags, for which a single byte would be more than
enough (I would actually need just a single bit, but I'd probably keep
some bits to
Vlad Romascanu vromasc...@accurev.com writes:
Hi, Tom,
Why does:
CREATE CAST (text AS bytea) WITHOUT FUNCTION;
SELECT E'C:\\something'::text::bytea;
work as expected, but (with the original text-bytea cast in place):
CREATE DOMAIN my_varlena AS text;
CREATE CAST (my_varlena
On Wed, Mar 16, 2011 at 12:19 PM, Daniele Varrazzo
daniele.varra...@gmail.com wrote:
Hello,
I'm writing a variable size custom datatype in C. The variable part is
an array of unsigned long, and it needs to be aligned. I further need
to store a few flags, for which a single byte would be more
On Wed, Mar 16, 2011 at 10:49 AM, Davenport, Julie jdavenp...@ctcd.edu wrote:
When I run the following query in Postgres 8.0, it runs in 61,509.372 ms
When I run it in Postgres 8.4, it runs in 397,857.472 ms
Here is the query:
select
course_id AS EXTERNAL_COURSE_KEY,
user_id AS
On Wed, Mar 16, 2011 at 2:14 PM, Davenport, Julie jdavenp...@ctcd.edu wrote:
Hello Merlin,
Thank you very much for your reply.
I don't see any setting for lc_collate. I assume it would be in
postgresql.conf file if it were there? These are the only lc_... settings I
see in
Dne 16.3.2011 20:32, Merlin Moncure napsal(a):
On Wed, Mar 16, 2011 at 2:14 PM, Davenport, Julie jdavenp...@ctcd.edu wrote:
Hello Merlin,
Thank you very much for your reply.
I don't see any setting for lc_collate. I assume it would be in
postgresql.conf file if it were there? These are the
Hey Manos,
2011/3/16 Manos Karpathiotakis m...@di.uoa.gr
Let me explain a few things about our dataset. We are using a system named
Sesame [1] that stores and queries RDF data. In our case, it uses Postgres
as a relational backend. In RDF, data are triples. Here is an example of an
RDF
I'd like to pass configuration options to the postgres installer on
ubuntu if possible. Mostly I'm concerned about setting the location of
the data directory, and where the logs are stored.
I'm familiar with how to configure these after postgres has been
installed with the default settings, but
Le 16/03/2011 19:37, fjania a écrit :
I'd like to pass configuration options to the postgres installer on
ubuntu if possible. Mostly I'm concerned about setting the location of
the data directory, and where the logs are stored.
I'm familiar with how to configure these after postgres has been
On Wed, 16 Mar 2011, fjania wrote:
I'd like to pass configuration options to the postgres installer on ubuntu
if possible. Mostly I'm concerned about setting the location of the data
directory, and where the logs are stored.
You can with the pg_createcluster command. It is an Ubuntu-specific
Dne 16.3.2011 21:38, Davenport, Julie napsal(a):
OK, I did the explain analyze on both sides (using a file for output instead)
and used the tool you suggested.
8.0 - http://explain.depesz.com/s/Wam
8.4 - http://explain.depesz.com/s/asJ
Great, that's exactly what I asked for. I'll repost
2011/3/16 Tomas Vondra t...@fuzzy.cz:
Dne 16.3.2011 21:38, Davenport, Julie napsal(a):
OK, I did the explain analyze on both sides (using a file for output
instead) and used the tool you suggested.
8.0 - http://explain.depesz.com/s/Wam
8.4 - http://explain.depesz.com/s/asJ
Great, that's
I'm getting really frustrated, I can't get a simple COPY command to work.
I've tried running psql and both the postgres user and the owner of the
database with the same error.
psql -U postgres -d database1
database1=# BEGIN;
BEGIN
database1=# TRUNCATE TABLE grades;
TRUNCATE TABLE
Dne 16.3.2011 22:31, Pavel Stehule napsal(a):
2011/3/16 Tomas Vondra t...@fuzzy.cz:
Dne 16.3.2011 21:38, Davenport, Julie napsal(a):
OK, I did the explain analyze on both sides (using a file for output
instead) and used the tool you suggested.
8.0 - http://explain.depesz.com/s/Wam
8.4 -
I'm getting really frustrated, I can't get a simple COPY
command to work. I've tried running psql and both the
postgres user and the owner of the database with the same error.
Sorry about the, I see the file name requires a quote.
On Wednesday, March 16, 2011 2:36:25 pm runner wrote:
I'm getting really frustrated, I can't get a simple COPY command to work.
I've tried running psql and both the postgres user and the owner of the
database with the same error.
psql -U postgres -d database1
database1=# BEGIN;
OK, so the cost constants are equal in both versions (the only
difference is due to change of the default value).
Just out of curiosity, have you tried to throw a bit more work_mem at
the query? Try something like 8MB or 16MB so - just do this
db=$ set work_mem=8192
and then run the query (the
On Wed, Mar 16, 2011 at 6:29 PM, Merlin Moncure mmonc...@gmail.com wrote:
question: if you are storing just flags and bytes, why not use a bytea
and store the flags out of line?
I'm not sure I understand your question. I am writing a custom
datatype with variable size more or less following
Dear George,
Do you see this issue on 9.0.3, the current and the recommended 9.x version?
Best,
Aleksey
On Tue, Mar 15, 2011 at 11:38 AM, George Woodring
george.woodr...@iglass.net wrote:
We recently upgraded from 8.3.something to 9.0.1. With 9.0.1, we have a
huge spike in vacuums every 8
Dear all,
please forgive me, but I am so impressed that Oleg, besides his splendid
work on TSearch, gives us such unbelievable pictures:
http://www.dailymail.co.uk/sciencetech/article-1366794/Rainbow-cloud-towers-Mount-Everest.html
I stumbled about this completely by accident, and can't
There seems to be inadequate info on filling arrays from a table, (vs
keyboarding), and
how to do a lookup in an array. I've tried to almost no avail.
- - - - - - -
I have a loop that I'm executing thousands of times and inside of it I have
SELECT INTO myvar column1 FROM mytable
2011/3/16 Davenport, Julie jdavenp...@ctcd.edu:
Yes, the column course_begin_date is a timestamp, so that would not work in
this instance, but I will keep that in mind for future use elsewhere. I
agree, there are ways to rewrite this query, just wondering which is best to
take advantage of
43 matches
Mail list logo
