How can you use a distinct on () including the whole union.
eg
select distinct on (valutaid) valutaid, short from valuta UNION select
landid, land from land order by valutaid;
table: valuta
valutaidvaluta
1 USD
2 SEK
table: land
landid land
1
Greetings,
the following is an MySQL statement that I would like to
translate to PostgreSQL:
Could someone point me to a documentation of a coresponding
Systax for an "IF" clause in the a SELECT,
or is the some other way to do this
select
if(spektrum is null,' ','J'),
if(s19 is null,' ','
this should work,
#
SELECT date_trunc('day',endtime),count(*)
FROM eg_event where endtime >= '2006-02-01' and endtime < '2006-03-01'
GROUP BY 1
ORDER BY 1;
hope this helps
best regards,
Stefan
Am Donnerstag, 16. März 2006 06:18 schrieb Bryce Nesbitt:
> I've got a working query:
>
An: pgsql-sql@postgresql.org
Betreff: Re: [SQL] returning an array as a list fo single-column rows?
The following will return the elements of an array each in its
Own row. Using both array_lower() and array_upper() the number of array
Elements and their internal index may vary from record to
Poul L. Christiansen writes:
> Isn't easier to reduce the table every day and make a daily vacuum which only
> lasts a few seconds?
I doubt that it would last just a few seconds. From my experience, VACUUM
on large tables can sap your I/O subsystem, slowing down overall
performance for everyone
Hi All,
I'm planning to have data in a tree structure when fetched.
e.g.
NODE1
+ --- NODE1_1
+ --- NODE1_2
|+ --- NODE1_2_1
+ --- NODE1_3
Is this possible? How?
I would really appreciate any help.
Sherwin
FROM test.pl of DBD-Pg-0.93.tar ...
# test large objects
# create large object from binary file
my ($ascii, $pgin);
foreach $ascii (0..255) {
$pgin .= chr($ascii);
};
my $PGIN = '/tmp/pgin';
open(PGIN, ">$PGIN") or die "can not open $PGIN";
print PGIN $pgin;
close
You can try the script I made for exporting all my Pg database.
Ideas were borrowed from pg_dumplo-0.0.5.
Make it sure that you have "Perl5 extension for PostgreSQL - Pg.pm"
installed.
sherwin
#!/usr/bin/perl -w
#
# Export large objects of all Pg database
# - Sherwin T. Daganato ([EMAIL PROTEC
xid loop detected, giving up
My observation: The tree (directories) were created but 1131 large objects
were not there.
The lo_dump.index file is not readable
(contains garbage).
peace,
sherwin
[EMAIL PROTECTED] writes:
>
>On Wed, 1 Nov 2000, pgsql-sql
Is it safe to rename 'postgres' to any__name?
like
update pg_shadow set usename = 'any__name' where usename = 'postgres';
Sherwin
SELECT is returning bogus data.
migrate=# select version();
version
---
PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc 2.95.3
(1 row)
migrate=# select userid from users where userid = '[EMAIL PROTECT
[EMAIL PROTECTED] writes:
>"pgsql-sql" <[EMAIL PROTECTED]> writes:
>> migrate=# select userid from users where userid = '[EMAIL PROTECTED]';
>>userid
>> -
>> [EMAIL PROTECTED]
>> admin
>> (2 rows)
&g
[EMAIL PROTECTED] writes:
>"pgsql-sql" <[EMAIL PROTECTED]> writes:
>> ERROR: copy: line 3910, Bad timestamp external representation
>'2000-01-05
>> 00:00:60.00+08'
>
>> Weird because those timestamps were generated by default now().
>
Thanks to you Tom and Thomas.
Now I know.
- sherwin
[EMAIL PROTECTED] writes:
>Ah ha (or rather, ha ha ha)! I'd suggest using the RPMs posted on the
>postgresql.org ftp site, which include a sample .rpmrc file which fixes
>disasterous bugs in Mandrake's default compiler settings for building
>RP
You can use 'DBI'
from test.pl of DBD::Pg
# create large object from binary file
my ($ascii, $pgin);
foreach $ascii (0..255) {
$pgin .= chr($ascii);
};
my $PGIN = '/tmp/pgin';
open(PGIN, ">$PGIN") or die "can not open $PGIN";
print PGIN $pgin;
close PGIN;
# begin transaction
$dbh->{AutoCo
you can also do it by a shell script
grantall.sh:
## start of grantall.sh ###
#!/bin/ash
SQL="SELECT relname FROM pg_class WHERE (relkind = 'r' OR relkind = 'S')
AND relname !~ '^pg_' ORDER BY relname"
OBJ=`psql -t -c "${SQL}" $1`
# OBJ=`echo ${OBJ} | sed 's/EOF//g'`
OBJ=`echo ${OBJ} | se
you can also do it by a shell script
grantall.sh:
## start of grantall.sh ###
#!/bin/ash
SQL="SELECT relname FROM pg_class WHERE (relkind = 'r' OR relkind = 'S')
AND relname !~ '^pg_' ORDER BY relname"
OBJ=`psql -t -c "${SQL}" $1`
# OBJ=`echo ${OBJ} | sed 's/EOF//g'`
OBJ=`echo ${OBJ} | se
Hello Everyone,
Here's my simple question.
I just want to know/get the recent changes made to a table.
Deeper? I wanted the Postgresql server to *publish* every
changes made to a table (similar to replication, incremental transfer,
etc.).
What is the best way to go about it?
My idea is to creat
Thanks Cedar, Jan, and Andy.
Actually the setup is something like this...
There are two remote servers-remoteA and remoteB.
The table of remoteA needs to be sychronized with the
table of remoteB all the time (well, there could be an interval).
remoteB will *publish* every changes and remoteA is *
the functions
execution, but currently only the whole function can be regarded as a
transaction, and nested transactions is not (yet) supported.
Some digging in the mailinglist archives pointed to isolation levels.
Apparently 'dirty reads' in theory:
[quote http://archives.postgresql.or
want the select to return:
ID TS (HH:MM)
---
0 20:00
0 20:05
0 20:10
1 20:03
1 20:09
Does my question make sense?
Thanks in advance,
Wayne
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
; > 0 20:00
> > 1 20:03
> > 1 20:04
> > 0 20:05
> > 1 20:05
> > 0 20:08
> > 1 20:09
> > 0 20:10
> >
> > I'd want the select to return:
> >
> > ID TS (HH:MM)
> > --
5) is included for the
opposite reason.
Let me restate my requirement again with a little more detail. I want to
select records grouped by ID, ordered by timestamp, in ascending order so I'm
starting with the oldest, that are at least X minutes apart.
I hope that helps.
Thanks again,
Wayne
SELECT *
> FROM Yourtable AS B
> WHERE B.id = A.id
>AND B.ts > A.ts - INTERVAL '5 MINUTES'
>AND B.tx < A.ts )
>
> ORDER BY id, ts;
>
> --
> Regards,
> Richard Broersma Jr.
--
Se
I'd want the select to return:
> >
> > ID TS (HH:MM)
> > ---
> > 0 20:00
> > 0 20:05
> > 0 20:10
> > 1 20:03
> > 1 20:09
> >
> >
> > Does my question make sense?
> >
> > Thanks in a
---
> > 0 20:00
> > 1 20:03
> > 1 20:04
> > 0 20:05
> > 1 20:05
> > 0 20:08
> > 1 20:09
> > 0 20:10
> >
> > I'd want the select to return:
> >
> > ID TS (HH:MM)
> > ---
> &
ll I manage unique column names for this output?
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/Usage-of-function-retruning-record-in-query-tp4549140p4550092.html
> Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
>
> --
>
5 | 1.55 | 45.00| 1.23 | 23.25
> > 6 | 3.60 | 69.00| 2.98 | 62.66
> > How will I manage unique column names for this output?
> >
> > --
> > View this message in context:
> > http://postgresql.1045698.n5.nabble.com/Usage-of-fu
gt; }
> foreach $patt (keys %patterns){
> my $sql="insert into patterns values('".$patt."',".$patterns{$patt}.")";
> spi_exec_query($sql);
> }
> return '';
> $BODY$
> LANGUAGE plperl VOLATILE
> COST 100;
>
&
if (! defined $patterns{$patt}) {
> > > $patterns{$patt}=1;
> > > }else{
> > > $patterns{$patt}++;
> > > }
> > > }
> > > }
> > > foreach $patt (keys %patterns){
> > > my $sql="insert into patterns
> > values('".$patt."',".$patterns{$patt}.")";
> > > spi_exec_query($sql);
> > > }
> > > return '';
> > > $BODY$
> > > LANGUAGE plperl VOLATILE
> > > COST 100;
> > >
> > >
> > >
> > > --
> > > ---
> > > Viktor Bojovi??
> > > ---
> > > Wherever I go, Murphy goes with me
> >
>
>
>
> --
> ---
> Viktor Bojovi??
> ---
> Wherever I go, Murphy goes with me
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
How do I use LIKE to search for strings with an underscore? The
documentation (well, Bruce's book) says to use 2 underscores (__) but it
doesn't work. For example:
create table liketest (
somestr varchar(50)
);
insert into liketest values ('foo_bar');
insert into liketest values ('foobar');
HI!
The SQL below is too slow.
SELECT name FROM office, office_application
WHERE code = office_code
AND name NOT IN
(SELECT DISTINCT name FROM office, office_application
WHERE active = 't' AND code = office_code);
Can anyone tell me how to optimize it?
Thanks.
[EMAIL PROTECTED] writes:
>How does the output of the above differ from:
>
>SELECT name FROM office, office_application
>WHERE code = office_code
>AND active != 't';
>
>Without knowing the table structures (which tables to active, code,
>and office_code belong to?) it's hard to suggest much els
After digging through all the discussions of "INSERT waiting" problems I am
still not clear about the concensus about solving it.
I am running ration 6:1 SELECT:INSERT (insert fires up an UPDATE trigger
that hits a column in a table holding keys used by SELECT). I am looking at
doing about 2,00
> > After digging through all the discussions of "INSERT waiting" problems I am
> > still not clear about the concensus about solving it.
> > ...
> > The only thing that I do not particulary like is that every INSERT
> > into this table has to adjust a counter column in a corresponding row of the
>
Are there performance advantages that can be achieved by wrapping a
complex SELECT into a stored procedure?
Alex
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Is there a way to execute an external i.e. system command from inside a
pl/pgsql function?
Alex
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column&#
Hi,
I realize I probably lost my marbles but I've been having a god
awful time with a single query:
control:
controller_id pk;
datapack:
controller_id fk;
I need to get all entries from the table contr
38 matches
Mail list logo
