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
> P
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
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: Wed
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 a
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 que
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 h
> 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 ~~
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 pg_
> -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):
>
> # sel
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 Postgre
On Thu, Mar 10, 2011 at 3:54 PM, Merlin Moncure wrote:
> On Thu, Mar 10, 2011 at 3:21 PM, Reece Hart wrote:
>> On Wed, Mar 9, 2011 at 9:16 AM, Merlin Moncure wrote:
>>>
>>> create type validation_flags as
>>> (
>>> cluster bool,
>>> freq bool
>>> );
>>
>> Wow. That solution is nearly sexy, and
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 tab
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 f
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 'C
Bruce Momjian 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 single-quotes,
> a
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 instru
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 t
> 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
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 E'C:\\something
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 s
Vlad Romascanu 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 AS bytea) WITHOU
On Wed, Mar 16, 2011 at 12:19 PM, Daniele Varrazzo
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 than
> enough (I would
On Wed, Mar 16, 2011 at 10:49 AM, 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 query:
>
>
>
> select
>
> course_id AS EXTERNAL_COURSE_KEY,
>
> user_id AS EXTER
On Wed, Mar 16, 2011 at 2:14 PM, Davenport, Julie 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 postgresql.conf:
>
> lc_messag
Dne 16.3.2011 20:32, Merlin Moncure napsal(a):
> On Wed, Mar 16, 2011 at 2:14 PM, Davenport, Julie 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_..
Hey Manos,
2011/3/16 Manos Karpathiotakis
> 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:P
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 it
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
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 rep
2011/3/16 Tomas Vondra :
> 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 ex
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
datab
Dne 16.3.2011 22:31, Pavel Stehule napsal(a):
> 2011/3/16 Tomas Vondra :
>> 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 c
On Wed, Mar 16, 2011 at 6:29 PM, Merlin Moncure 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 the guidelines in
<
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
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 days only on one of our
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 rejec
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 WHE
2011/3/16 Davenport, Julie :
> 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 8.4.
> Thanks m
43 matches
Mail list logo
