hello,all
I want to transmit data from a database to another partly,
which means only data that selected in a table will be transmit.
I can select data , and then inert or update rows one by one.
But is there any way better?
On 10/3/07, hubert depesz lubaczewski <[EMAIL PROTECTED]> wrote:
> On Wed, Oct 03, 2007 at 11:47:26AM -0400, Jerry Sievers wrote:
> > Question: Am I overlooking a simple way of doing this?
>
> yes. use plpython or plperl to do the job.
>
> depesz
>
here is a great example with pl/perl (search: p
Hello,
i'm currently designing an application that will retrieve economic data
(mainly time series)from different sources and distribute it to clients.
It is supposed to manage around 20.000 different series with differing
numbers of observations (some have only a few dozen observations, other
"Jimmy Choi" <[EMAIL PROTECTED]> writes:
> select
>metric_type,
>case metric_type
> when 0 then
> sum (1 / val)
> when 1 then
> sum (val)
>end as result
> from metrics
> group by metric_type
The reason this does not work is that the aggregate functions are
Alvaro Herrera <[EMAIL PROTECTED]> writes:
> Richard Huxton wrote:
>> Could you run Linux in a virtual-machine in OS X?
> I think it would be easier (and more performant) to define a new locale
> on OS/X (or on Linux) to match the behavior of the other system.
> (Perhaps define a new locale on bot
Richard Huxton wrote:
> Tom Lane wrote:
>> Brian Wipf <[EMAIL PROTECTED]> writes:
>>> PG tried to enforce the same LC_COLLATE and LC_CTYPE. On OS X, the value
>>> of en_US.utf8 didn't exist, so I created a soft link to en_US.UTF-8 in
>>> the /usr/share/locale/ directory. When I sort the values
Jimmy Choi escribió:
> This will work for this particular example. But what if my case
> statement is more complicated than that? Example:
>
> select
>metric_type,
>case metric_type
> when 0 then
> sum (1 / val)
> when 1 then
> sum (val)
> when 2 then
>
This will work for this particular example. But what if my case
statement is more complicated than that? Example:
select
metric_type,
case metric_type
when 0 then
sum (1 / val)
when 1 then
sum (val)
when 2 then
max (val)
when 3 then
On 10/3/07, Jimmy Choi <[EMAIL PROTECTED]> wrote:
> I expect to get the following result set:
>
> metric_type | result
> +---
> 0 | 2
> 1 | 3
Try:
SELECT metric_type
, SUM(CASE metric_type
WHEN 0
THEN 1 / val
Brian Wipf wrote:
On 3-Oct-07, at 12:46 PM, Richard Huxton wrote:
Could you run Linux in a virtual-machine in OS X?
That's an idea. Performance-wise though, I think we'd be better off
wiping OS X and installing Linux. As an added bonus, we'll be able to
get way better performance out of our
On 3-Oct-07, at 12:38 PM, Tom Lane wrote:
What this sounds like to me is a problem in your recovery procedures.
What exactly did you do to "bring the database out of recovery mode"?
The script looked for a trigger file and once found, aborts.
Unfortunately, it would abort without doing the re
Suppose I have the following table named "metrics":
metric_type | val
+-
0 | 1
0 | 1
1 | 0
1 | 3
Now suppose I run the following simple query:
select
metric_type,
case metric_type
when 0 then
sum (1 / val)
when 1
On 3-Oct-07, at 12:46 PM, Richard Huxton wrote:
Tom Lane wrote:
Brian Wipf <[EMAIL PROTECTED]> writes:
PG tried to enforce the same LC_COLLATE and LC_CTYPE. On OS X,
the value of en_US.utf8 didn't exist, so I created a soft link
to en_US.UTF-8 in the /usr/share/locale/ directory. When I so
Suppose I have the following table named "metrics":
metric_type | val
+-
0 | 1
0 | 1
1 | 0
1 | 3
Now suppose I run the following simple query:
select
metric_type,
case metric_type
when 0 then
sum (1 / val)
wh
Tom Lane wrote:
Brian Wipf <[EMAIL PROTECTED]> writes:
PG tried to enforce the same LC_COLLATE and LC_CTYPE. On OS X, the
value of en_US.utf8 didn't exist, so I created a soft link to
en_US.UTF-8 in the /usr/share/locale/ directory. When I sort the
values of product_id_from_source on both s
"Josh Tolley" <[EMAIL PROTECTED]> writes:
> On 10/1/07, S Sharma <[EMAIL PROTECTED]> wrote:
>> It would be nice to have a feature to define a default table space for
>> indexes in db conf file and all indexed are created in that table space.
> Although the most basic optimization suggested when us
Brian Wipf <[EMAIL PROTECTED]> writes:
> Last night, I brought the database out of its perpetual recovery
> mode. Here are the lines from the log when this was done:
> [2007-10-01 23:43:03 MDT] LOG: restored log file
> "000104660060" from archive
> [2007-10-01 23:45:50 MDT] LOG: c
Brian Wipf <[EMAIL PROTECTED]> writes:
> PG tried to enforce the same LC_COLLATE and LC_CTYPE. On OS X, the
> value of en_US.utf8 didn't exist, so I created a soft link to
> en_US.UTF-8 in the /usr/share/locale/ directory. When I sort the
> values of product_id_from_source on both systems usi
On Oct 3, 2007, at 12:19 PM, Scott Marlowe wrote:
On 10/3/07, Laurent ROCHE <[EMAIL PROTECTED]> wrote:
Would this work:
SELECT
'TRUNCATE TABLE ' ||
'my_schema.' ||
c.relname ||', '
FROM pg_namespace nc, pg_class c
WHERE c.relnamespace = nc.oid
AND c.relkind IN ('r' )
AND nc.nspname = 'my_sc
On 3-Oct-07, at 8:07 AM, Tom Lane wrote:
PG 8.2 does store data in the pg_control file with which it can check
for the most common disk-format-incompatibility problems (to wit,
endiannness, maxalign, and --enable-integer-datetimes). If Brian has
stumbled on another such foot-gun, it'd be good to
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Rui Lopes wrote:
> Hello,
>
> How do I backup all the roles and ACLs that have permissions to a single
> database?
>
> pg_dumpall -g does not do the trick because it dumps all the roles from
> all the databases.
roles are part of the catalog/cluster
Hello,
I have a MySQL dump file that I would like to import into our PostgreSQL 8.2
database. Is there a way to do this?
Thanks.
-Jeff
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hello,
How do I backup all the roles and ACLs that have permissions to a single
database?
pg_dumpall -g does not do the trick because it dumps all the roles from
all the databases.
I've read the system catalogs documentation [1], but I didn't figure
On Wed, Oct 03, 2007 at 11:47:26AM -0400, Jerry Sievers wrote:
> Question: Am I overlooking a simple way of doing this?
yes. use plpython or plperl to do the job.
depesz
--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA. here's my CV!" :)
http://www.depesz.c
On 10/3/07, Laurent ROCHE <[EMAIL PROTECTED]> wrote:
Would this work:
SELECT
'TRUNCATE TABLE ' ||
'my_schema.' ||
c.relname ||', '
FROM pg_namespace nc, pg_class c
WHERE c.relnamespace = nc.oid
AND c.relkind IN ('r' )
AND nc.nspname = 'my_schema'
ORDER BY relname
---(e
Laurent ROCHE wrote:
So I wrote:
SELECT 'TRUNCATE TABLE '
UNION
...
ORDER BY relname
And this fails with the following message:
ERROR: column "relname" does not exist
But I don't understand why this does not work: the 2 SELECTs produce a single
char column so from what I understand th
Hi,
I wanted to write a SELECT that generates a TRUNCATE TABLE for all the tables
in a given schema.
So I wrote:
SELECT 'TRUNCATE TABLE '
UNION
SELECT 'my_schema.' || c.relname ||', '
FROM pg_namespace nc, pg_class c
WHERE c.relnamespace = nc.oid
AND c.relkind IN ('r' )
AND nc.nspname = '
Jerry Sievers wrote:
> After for the umpteenth time bashing my head against a wall developing
> some PL funcs that use dynamic SQL, going plain bonkers trying to
> build the query string; I came up with a function like the one below
> to take a string with placeholders, an array of values to be
> i
After for the umpteenth time bashing my head against a wall developing
some PL funcs that use dynamic SQL, going plain bonkers trying to
build the query string; I came up with a function like the one below
to take a string with placeholders, an array of values to be
interpolated and a placeholder c
On Oct 3, 2007, at 6:47 AM, Richard Huxton wrote:
Sergey Konoplev wrote:
Don't forget to cc: the list.
Try not to top-post replies, it's easier to read if you reply
below the
text you're replying to.
Thanx for your advice. I'm just absolutely worned out. Sorry.
Know that feeling - let's s
On Wed, 3 Oct 2007, Alban Hertroys wrote:
Alban Hertroys wrote:
The only odd thing is that to_tsvector('dutch', 'some dutch text') now
returns '|' for stop words...
For example:
select to_tsvector('nederlands', 'De beste stuurlui staan aan wal');
to_tsvector
On Wed, 3 Oct 2007, Alban Hertroys wrote:
Oleg Bartunov wrote:
Alban,
the documentation you're refereed on is for upcoming 8.3 release.
For 8.1 and 8.2 you need to do all machinery by hand. It's not
difficult, for example:
Thanks Oleg.
I think I managed to do this right, although I had to go
Magnus Hagander <[EMAIL PROTECTED]> writes:
> Does pl/python listen to SIGINT during execution of functions? If not,
> that'd be an explanation - if it's stuck inside a pl/python function...
> AFAIK, pl/pgsql does listen for SIGINT during execution, but I don't nkow
> abuot plpython.
It does not,
Richard Huxton <[EMAIL PROTECTED]> writes:
> Brian Wipf wrote:
>> Both servers have identical Intel processors and both are running 64-bit
>> PostgreSQL 8.2.4. The original server is running 64-bit openSUSE 10.2
>> (Linux 2.6.18.2-34-default #1 SMP Mon Jul 16 01:16:32 GMT 2007 x86_64
>> x86_64 x
Alban Hertroys wrote:
> The only odd thing is that to_tsvector('dutch', 'some dutch text') now
> returns '|' for stop words...
>
> For example:
> select to_tsvector('nederlands', 'De beste stuurlui staan aan wal');
> to_tsvector
>
Oleg Bartunov wrote:
> Alban,
>
> the documentation you're refereed on is for upcoming 8.3 release.
> For 8.1 and 8.2 you need to do all machinery by hand. It's not
> difficult, for example:
Thanks Oleg.
I think I managed to do this right, although I had to google for some of
the files (we don't
On Oct 3, 2007, at 1:29 , Stefan Schwarzer wrote:
As others have noted, the query *can* be written. But it appears
to me
that you are struggling against your table layout.
The current schema he has is commonly called EAV (entity-attribute-
value) and is generally frowned upon. Now, in his
Andrus wrote:
Use
www.fyireporting.com
Open source, uses excellent PostgreSQL npgsql drivers.
Use standard RDL format
I guess I should have noted that we will need to run this on Linux clients.
--
Until later, Geoffrey
Those who would give up essential Liberty, to purchase a little
temporar
Alban,
the documentation you're refereed on is for upcoming 8.3 release.
For 8.1 and 8.2 you need to do all machinery by hand. It's not
difficult, for example:
-- sample tsearch2 configuration for search.postgresql.org
-- Creates configuration 'pg' - default, should match server's locale !!!
-
Sergey Konoplev escribió:
> > AFAIK, pl/pgsql does listen for SIGINT during execution, but I don't nkow
> > abuot plpython.
>
> How can we find it out?
Let's see one of the functions to find out if anyone else can reproduce
the problem.
--
Alvaro Herrerahttp://w
Hello,
I'm trying to get a Dutch snowball stemmer in Postgres 8.1, but I can't
find how to do that.
I found CREATE FULLTEXT DICTIONARY commands in the tsearch2 docs on
http://www.sai.msu.su/~megera/postgres/fts/doc/index.html, but these
commands are apparently not available on PG8.1.
I also foun
Sergey Konoplev wrote:
Don't forget to cc: the list.
Try not to top-post replies, it's easier to read if you reply below the
text you're replying to.
Thanx for your advice. I'm just absolutely worned out. Sorry.
Know that feeling - let's see if we can't sort this out.
1. Is it always the sa
> > Don't forget to cc: the list.
> > Try not to top-post replies, it's easier to read if you reply below the
> > text you're replying to.
> >
> > Sergey Konoplev wrote:
> > >>1. Is it always the same query?
> > >>2. Does the client still think it's connected?
> > >>3. Is that query using up CPU, o
On 10/1/07, S Sharma <[EMAIL PROTECTED]> wrote:
> Hi All,
>
> The default table space defined in db conf file is used for all database
> tables as well as indexes. So putting the indexes on another table space
> requires manually dropping and re-creating indexes.
> It would be nice to have a featur
On Wed, Oct 03, 2007 at 11:18:32AM +0100, Richard Huxton wrote:
> Don't forget to cc: the list.
> Try not to top-post replies, it's easier to read if you reply below the
> text you're replying to.
>
> Sergey Konoplev wrote:
> >>1. Is it always the same query?
> >>2. Does the client still think it
El mié, 03-10-2007 a las 00:27 -0500, Erik Jones escribió:
> On Oct 2, 2007, at 8:56 PM, Diego Gil wrote:
>
> > El jue, 27-09-2007 a las 10:32 +0200, Alban Hertroys escribió:
> >> Diego Gil wrote:
> >>> Hi,
> >>>
> >>> I have a file to import to postgresql that have an unusual date
> >>> format.
Don't forget to cc: the list.
Try not to top-post replies, it's easier to read if you reply below the
text you're replying to.
Sergey Konoplev wrote:
1. Is it always the same query?
2. Does the client still think it's connected?
3. Is that query using up CPU, or just idling?
4. Anything odd in
Sergey Konoplev wrote:
I'm sorry I mean not HUP but KILL
Hmm...
datname | usename | procpid | current_query | waiting |
query_start
---+--+-+-+-+---
transport | belostotskaya_la | 20530
I'm sorry I mean not HUP but KILL
2007/10/3, Sergey Konoplev <[EMAIL PROTECTED]>:
> Hi all,
>
> I often face with buzz queries (see below). I've looked through pg
> manual and huge amount of forums and mail archives and found nothing.
> The only solution is to restart postgres server. Moreover I
Rodrigo De León wrote:
> On 10/1/07, Abandoned <[EMAIL PROTECTED]> wrote:
>> Hi..
>> I have a id list and id list have 2 million dinamic elements..
>> I want to select what id have point..
>> I try:
>>
>> SELECT id, point FROM table WHERE id in (IDLIST)
>>
>> This is working but too slowly and i ne
Hi all,
I often face with buzz queries (see below). I've looked through pg
manual and huge amount of forums and mail archives and found nothing.
The only solution is to restart postgres server. Moreover I have to
terminate the process using HUP signal to stop the server.
transport=# select versi
Brian Wipf wrote:
We are running a production server off of a new database that was
synchronized using PITR recovery. We found that many of the btree
indexes were out of sync with the underlying data after bringing the new
server out of recovery mode, but the data itself appeared to be okay.
52 matches
Mail list logo
