This is a very abbr version of my problem, but what would be the best
way to do this...
DECLARE
column_name_var TEXT;
BEGIN
RETURN NEW.column_name_var;
END;
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/
"Spotts, Christopher" writes:
> Using postgres 8.4.1, I thought the upgrade from 8.4.0 would help with
> array_agg.
> Using ARRAY() with a subselect is yielding results hundreds times faster
> than array_agg even though its plan looks much worse...
Huh, you sure your server is on 8.4.1? I can'
LISA 2009 (the Large Installation Systems Administration Conference) is coming
up next month (November 1-6th).
We're looking for a few folks in Baltimore/DC area who are attending the
conference or in the area who can spare a few hours to staff the Postgres
booth. We need people all day Wedne
Alvaro Herrera writes:
> I think the confusion is about what SIMILAR TO supports. ? it doesn't.
Actually, upon looking into SQL:2008, it seems it's supposed to support
? now, and also {m,n} style bounds. Those weren't there in SQL99 ...
I've changed the similar_escape code to not escape ? and
Alvaro Herrera writes:
> Michal Szymanski wrote:
>> In old version of Postgres we have to execute 'VACUUM FULL' to solve
>> problem of transaction ID wraparound, do we need to execute 'VACUUM
>> FULL' in Postgres 8.3 or 8.4 to avoid this problem?
> No, plain VACUUM suffices.
Just to clarify: pl
Alvaro Herrera writes:
> Doug Gorley escribió:
>> Trying to match some numbers, and I'm having some regexp problems.
>> I've boiled it down to the following:
>>
>> /* (1) */ select '3.14' similar to E'^\\d+\\.\\d+$'; -- true
>> /* (2) */ select '3.14' similar to E'^\\d+(\\.\\d+)$';
Mike Diehl writes:
> I'm doing a routine vacuum on my database and it seems to be getting "stuck."
> I've got a "servers" table with only a few rows and columns in it and this is
> what vacuum has to say:
> # VACUUM ANALYZE VERBOSE servers;
> INFO: vacuuming "public.servers"
> INFO: scanned i
Gerhard Wiesinger writes:
> I've one idea, which is not ideal, but may work and shouldn't be much
> effort to implement:
> As in the example above we read B1-B5 and B7-B10 on a higher level outside
> of normal buffer management with large request sizes (e.g. where hash
> index scans and sequent
Michal Szymanski wrote:
> Hi,
> In old version of Postgres we have to execute 'VACUUM FULL' to solve
> problem of transaction ID wraparound, do we need to execute 'VACUUM
> FULL' in Postgres 8.3 or 8.4 to avoid this problem?
No, plain VACUUM suffices.
> How to check using SQL if transaction ID
Doug Gorley escribió:
> Trying to match some numbers, and I'm having some regexp problems.
> I've boiled it down to the following:
>
> /* (1) */ select '3.14' similar to E'^\\d+\\.\\d+$'; -- true
> /* (2) */ select '3.14' similar to E'^\\d+(\\.\\d+)$'; -- true
> /* (3) */ select '3
Hi,
In old version of Postgres we have to execute 'VACUUM FULL' to solve
problem of transaction ID wraparound, do we need to execute 'VACUUM
FULL' in Postgres 8.3 or 8.4 to avoid this problem?
How to check using SQL if transaction ID is close to wraparound?
Michal Szymanski
http://blog.szymanski
Consider a wiki that lets you edit rows in a db table. Each page is a
row in the table, and has fields that anyone can edit. Like all wikis,
it keeps a history of edits (including who made the edits), and lets
you revert an edit, or even delete a row (page) completely.
Has anyone implemented somet
On Fri, Oct 9, 2009 at 1:32 PM, Stephan Szabo
wrote:
> On Fri, 9 Oct 2009, Joshua D. Drake wrote:
>
>> On Thu, 2009-10-08 at 19:16 -0600, Scott Marlowe wrote:
>> > On Thu, Oct 8, 2009 at 2:22 PM, Merlin Moncure wrote:
>> > >
>> > > The #1 tool you have at your disposal is the human brain. I
>> >
Trying to match some numbers, and I'm having some regexp problems. I've
boiled it down to the following:
/* (1) */ select '3.14' similar to E'^\\d+\\.\\d+$'; -- true
/* (2) */ select '3.14' similar to E'^\\d+(\\.\\d+)$'; -- true
/* (3) */ select '3.14' similar to E'^\\d+(\\.\\d+
Using postgres 8.4.1, I thought the upgrade from 8.4.0 would help with
array_agg.
Using ARRAY() with a subselect is yielding results hundreds times faster
than array_agg even though its plan looks much worse...
And the entire system is seriously slugglish and near non-responsive
while running th
Whit Armstrong wrote:
> That's exactly what I needed. Thanks very much!
If you have 8.4, you can use the build-in function array_agg() instead:
test=*# select * from x;
a | b
---+---
1 | 1
1 | 2
1 | 3
2 | 1
2 | 4
(5 Zeilen)
Zeit: 0,203 ms
test=*# select a, array_agg(b) from x group by
Hi all.
I'm doing a routine vacuum on my database and it seems to be getting "stuck."
I've got a "servers" table with only a few rows and columns in it and this is
what vacuum has to say:
# VACUUM ANALYZE VERBOSE servers;
INFO: vacuuming "public.servers"
INFO: scanned index "servers_id" to re
On Oct 9, 2009, at 12:14 PM, Mike Christensen wrote:
I will definitely create a domain for this (and probably for some
other types in my app since I now know about this). However, is the
CHECK really necessary? A numeric(5,5) already has a maximum value of
10^0, so it would already create an o
On Fri, 2009-10-09 at 11:46 -0700, Christophe Pettus wrote:
> Domains are basically type aliases with an optional CHECK clause, so
> you could do something like:
>
> CREATE DOMAN sales_tax_rate AS DECIMAL(5,5) CHECK (VALUE >= 0);
>
> Then, you can use the type "sales_tax_rate" in your tab
Thanks for the info! I was thinking this would be a short thread but
I definitely appreciate all the information.
I will definitely create a domain for this (and probably for some
other types in my app since I now know about this). However, is the
CHECK really necessary? A numeric(5,5) already
On Fri, Oct 9, 2009 at 12:18 PM, Joshua D. Drake wrote:
> On Fri, 2009-10-09 at 09:52 -0700, Tim Bruce - Postgres wrote:
>
>>
>> I would also add that there are a LOT of performance issues with MS
>> Access. For one, in some cases filtering out records with a where clause
>> can be performed on t
On Oct 9, 2009, at 11:36 AM, Mike Christensen wrote:
Can you explain what you mean by "put it in a domain" - I'd love extra
style points, but this sounds like a feature I haven't learned about
yet.
http://www.postgresql.org/docs/8.4/interactive/sql-createdomain.html
Domains are basi
Can you explain what you mean by "put it in a domain" - I'd love extra
style points, but this sounds like a feature I haven't learned about
yet.
On Fri, Oct 9, 2009 at 3:38 AM, Peter Eisentraut wrote:
> On Fri, 2009-10-09 at 00:10 -0700, Mike Christensen wrote:
>> Wouldn't (4,3) let me store 0.00
So back to my question about representing sales tax, it looks like I
have two choices:
1) Use a numeric(5,5) column. This has the advantage of storing the
sales tax in the exact representation of a percent (I can directly
multiply it against any subtotal to get the sales tax). It also
"looks" ni
Hi
This is probably a naive question, but what would I gain from using
snowball and ispell dictionaries in tsearch2, as described here:
http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch-V2-intro.html
?
Presumably there is a performance hit associated with doing this (more
On Fri, 2009-10-09 at 10:32 -0700, Stephan Szabo wrote:
> > Good lord people. Not be helpful much?
> > [...]
> > JD... Who sits in bewilderment
>
> I'm fairly bewildered as well. I mean, why would someone who is emailing
> with an address from a company that presumably should care about how it
>
On Fri, 9 Oct 2009, Joshua D. Drake wrote:
> On Thu, 2009-10-08 at 19:16 -0600, Scott Marlowe wrote:
> > On Thu, Oct 8, 2009 at 2:22 PM, Merlin Moncure wrote:
> > >
> > > The #1 tool you have at your disposal is the human brain. I
> > > personally think GUI database tools are counter productive
On Oct 9, 2009, at 9:46 AM, Scott Ribe wrote:
The range of a twos-complement 32-bit integer is -2147483648 through
2147483647. Yet in Postgres:
# select -2147483647::int4;
?column?
-
-2147483647
(1 row)
# select -2147483648::int4;
ERROR: integer out of range
Is this a bug? Or
On Fri, 2009-10-09 at 09:52 -0700, Tim Bruce - Postgres wrote:
>
> I would also add that there are a LOT of performance issues with MS
> Access. For one, in some cases filtering out records with a where clause
> can be performed on the client side - meaning it grabs all records and
> pulls them
On Fri, October 9, 2009 09:06, Andrew Gould wrote:
> On Fri, Oct 9, 2009 at 10:15 AM, Joshua D. Drake
> wrote:
>> On Thu, 2009-10-08 at 19:16 -0600, Scott Marlowe wrote:
>>> On Thu, Oct 8, 2009 at 2:22 PM, Merlin Moncure
>>> wrote:
>>> >
>>> > The #1 tool you have at your disposal is the human br
Is there a way to identify whether the given execution is in nested
trigger or not?
Hi everybody, I need your help. I have a hierarchy of tables, and
other table that has a foreign key with the top table of the
hierarchy, can I insert a value into the "other" table where the value
it reference is not on the parent table? (it's in one of its child)
My best regards.
--
Sent via p
The range of a twos-complement 32-bit integer is -2147483648 through
2147483647. Yet in Postgres:
# select -2147483647::int4;
?column?
-
-2147483647
(1 row)
# select -2147483648::int4;
ERROR: integer out of range
Is this a bug? Or something required by the SQL standard?
(8.
That's exactly what I needed. Thanks very much!
-Whit
On Fri, Oct 9, 2009 at 12:29 PM, Raymond O'Donnell wrote:
> On 09/10/2009 17:17, Whit Armstrong wrote:
>> Is there any easy way to get this data:
>>
>> kls_dev=# select * from ary_values;
>> agent_name | myval
>> +---
>>
David W Noon wrote:
> On Tue, 6 Oct 2009 12:20:11 +0600, Nurzhan Kirbassov wrote about
> [GENERAL] numeric field overflow:
>
> [snip]
> >So, does the precision part of the numeric type really means number of
> >digits to the left of the decimal point, or what ?
>
> No.
>
> The precision is the *
On 09/10/2009 17:17, Whit Armstrong wrote:
> Is there any easy way to get this data:
>
> kls_dev=# select * from ary_values;
> agent_name | myval
> +---
> a | 1
> a | 2
> a | 3
> b | 4
> b | 5
> b |
On Friday 9. October 2009, Scott Marlowe wrote:
>On Fri, Oct 9, 2009 at 8:49 AM, Pedro Doria Meunier
>
> wrote:
>> -BEGIN PGP SIGNED MESSAGE-
>> Hash: SHA1
>>
>> Hi Sam,
>> Thanks for your answer! :)
>>
>> The matter of fact is that the script itself is the connected client
>> where the pos
Reid Thompson wrote:
> In the case where a_text is null, I essentially want the same result as the
> case when a_text = ''.
>
> would this:
> select a_int || coalesce(a_text,'') from test1 where a_int = 102;
> be the proper way?
Yes.
Andreas
--
Really, I'm not out to destroy Microsoft.
Is there any easy way to get this data:
kls_dev=# select * from ary_values;
agent_name | myval
+---
a | 1
a | 2
a | 3
b | 4
b | 5
b | 6
(6 rows)
to look like this:
kls_dev=# select * from ary_tes
In the case where a_text is null, I essentially want the same result as the
case when a_text = ''.
would this:
select a_int || coalesce(a_text,'') from test1 where a_int = 102;
be the proper way?
postgres=# \d test1
Table "public.test1"
Column |
On Fri, Oct 9, 2009 at 10:15 AM, Joshua D. Drake wrote:
> On Thu, 2009-10-08 at 19:16 -0600, Scott Marlowe wrote:
>> On Thu, Oct 8, 2009 at 2:22 PM, Merlin Moncure wrote:
>> >
>> > The #1 tool you have at your disposal is the human brain. I
>> > personally think GUI database tools are counter pr
On Fri, 9 Oct 2009, Greg Smith wrote:
On Sat, 3 Oct 2009, Gerhard Wiesinger wrote:
I wouldn't read 128k blocks all the time. I would do the following:
When e.g. B0, B127, B256 should be read I would read in 8k random block
I/O.
When B1, B2, B3, B4, B5, B7, B8, B9, B10 are needed I would mak
Alban Hertroys wrote:
> On 9 Oct 2009, at 8:48, Mitesh51 wrote:
> >Alban Hertroys-3 wrote:
>
> What's this? Did my name change suddenly? Or is yahoo-mail a bad
> mail client?
Mystifying eh? It's nabble.com's way of identifying unique posters.
You have probably posted from other three different
On Thu, 2009-10-08 at 19:16 -0600, Scott Marlowe wrote:
> On Thu, Oct 8, 2009 at 2:22 PM, Merlin Moncure wrote:
> >
> > The #1 tool you have at your disposal is the human brain. I
> > personally think GUI database tools are counter productive and huge
> > time wasters. SQL requires lateral think
On Fri, Oct 9, 2009 at 8:49 AM, Pedro Doria Meunier
wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Hi Sam,
> Thanks for your answer! :)
>
> The matter of fact is that the script itself is the connected client
> where the postgresql server is concerned.
>
> It then connects to another
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi Sam,
Thanks for your answer! :)
The matter of fact is that the script itself is the connected client
where the postgresql server is concerned.
It then connects to another server (the M$SQL one) and, on a row
basis, inserts data coming from the afo
On Fri, Oct 09, 2009 at 11:39:47AM +0100, Pedro Doria Meunier wrote:
> I cannot simply use "Set Client Encoding to LATIN1" as the "client"
> itself is the script ... :)
What does this mean? surely if it's just a "script" you can put another
line at the beginning of the script that says this?
If y
On Fri, Oct 9, 2009 at 6:39 AM, Pedro Doria Meunier
wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Hi All,
>
> I have this PHP script that simply dumps (inserts) data from a
> Postgresql server to a customer's M$SQL server.
>
> The database in the PGSQL server is UTF-8 encoded where
On Thu, 8 Oct 2009, Lew wrote:
That will fail for the scenario that Christope Pettus pointed out.
California is not the only state with sales tax specified to a quarter
point., or even a tenth of a point as in Missouri, where a local sales tax
can be 9.241%, and equal or exceed 10%, as in Alabam
On Thu, Oct 08, 2009 at 11:48:29PM -0700, Mitesh51 wrote:
> I am dealing with backup of databases. Now eventhough there are not many
> changes in the DB, it creates WAL file of 16 mb. Now because I am working on
> a application which takes full & inc backup of db after specific
> intervals..if for
On Fri, Oct 09, 2009 at 12:10:41AM -0700, Mike Christensen wrote:
> Wouldn't (4,3) let me store 0.000 through 9.999? Maybe I'm still not
> following what both numbers mean.
I think Rich was getting confused about how you wanted to represent your
percentages.
> I understand the point about states
On Sat, 3 Oct 2009, Gerhard Wiesinger wrote:
I wouldn't read 128k blocks all the time. I would do the following:
When e.g. B0, B127, B256 should be read I would read in 8k random block I/O.
When B1, B2, B3, B4, B5, B7, B8, B9, B10 are needed I would make 2 requests
with the largest possible bl
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi All,
I have this PHP script that simply dumps (inserts) data from a
Postgresql server to a customer's M$SQL server.
The database in the PGSQL server is UTF-8 encoded where the M$SQL
database uses LATIN1.
Accents, as expected, turn to gibberish...
On Fri, 2009-10-09 at 00:10 -0700, Mike Christensen wrote:
> Wouldn't (4,3) let me store 0.000 through 9.999? Maybe I'm still not
> following what both numbers mean.
Yes. If you want 0.000 through 0.999, use numeric(3,3). Adding a check
constraint might increase clarity. And put it in a domain
On 9 Oct 2009, at 8:48, Mitesh51 wrote:
I am dealing with backup of databases. Now eventhough there are not
many
changes in the DB, it creates WAL file of 16 mb. Now because I am
working on
a application which takes full & inc backup of db after specific
intervals..if for small changes it cr
Hi,
Is there a way to exclude all queries from a specific user, or only
log queries from a specific user in pg_stat_statements?
The slony/replication user fills all the entries more or less immediately.
--
Eld på åren og sol på eng gjer mannen fegen og fjåg. [Jøtul]
2009 Tore Halvorsen || +05
In the madness I did
- setting statistics target to 1000 for all join & filter columns
- cluster the playing tables
- reindex the playing tables
- analyze the playing tables
and it helped now. I'm at ~50ms which satisfies me completely.
If no hints - some debug for explain would b
Wouldn't (4,3) let me store 0.000 through 9.999? Maybe I'm still not
following what both numbers mean.
I understand the point about states/counties with 3 decimal digits of
sales tax, so I'd probably want to do (5,5) which should give me
0.0 - 0.9, and store 9.825% sales tax as .09825. I
58 matches
Mail list logo
