= sub.b_id;
or
select * from (select distinct on (b_id) * from a order by b_id, id) sub
left join b on b.id = sub.b_id order by b_id;
It seems to me that it's enough to use 'order by' only inside wheter 'by
desc' or 'by asc' (b_id), however I'd like to
in analyze is my good friend but in this case I prefer to ask.
Mage
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
PLAN
Unique (cost=1339.24..1405.05 rows=200 width=44) (actual
time=0.000..0.000 rows=3 loops=1)
[...]
(15 rows)
mage=# EXPLAIN ANALYZE select * from (select distinct on
;
return NEW;
end;
$$ language plpgsql;
And it meant to be "insert or update".
Mage
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 02/03/2011 08:13 AM, Alban Hertroys wrote:
On 3 Feb 2011, at 2:17, Mage wrote:
The trigger looks like:
create or replace function trf_chat_room_users_insert() returns trigger as $$
begin
if NEW.active_at is null then
insert into chat_room_users (user_id
ke is that I tought the insert with select will be atomic
and I was wrong. So the trigger has concurrency problem in
multi-threaded environment. It runs flawlessly in single thread and it
does only a single insert.
Mage
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.or
On 02/03/2011 08:23 PM, Tom Lane wrote:
Mage writes:
The main question is that isn't "insert into ... select ... where not
exists" atomic?
No, it isn't: it *will* fail in the presence of other transactions doing
the same thing, because the EXISTS test will only see rows t
On 02/04/2011 06:57 AM, pasman pasmański wrote:
Mage, add "raise notice" at the begin of your buggy trigger.
There is a little bit of difference between "Your trigger is wrong. You
try to insert the same row twice" and "The trigger will be fired twice."
You stat
ging to this database system will be owned by user "mage".
This user must also own the server process.
The database cluster will be initialized with locales
COLLATE: hu_HU.UTF-8
CTYPE:hu_HU.UTF-8
MESSAGES: en_US.UTF-8
MONETARY: en_US.UTF-8
NUMERIC: en_US.UTF-8
TIME: hu_HU.UTF
Tom Lane wrote:
Mage <[EMAIL PROTECTED]> writes:
We would like to upgrade from 8.1 to 8.3. We have UTF-8 and LATIN2
databases. Any idea?
If you were running with a non-C database locale, that was always
broken in 8.1, and you are very fortunate not to have stumbled across
any
n 7.4.
Mage
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
öéá
(1 row)
teszt=# select keywords_split('AúéöÖÉÁ');
keywords_split
aúéöÖÉÁ
(1 row)
teszt=# select lower('úéöÖÉÁ');
lower
úéöÖÉÁ
(1 row)
Mage
---(end of broadcast)---
TIP 3: if posting/readi
Daniel Verite wrote:
> Mage wrote:
>
>
>
>>teszt=# select keywords_split('AúéöÖÉÁ');
>> keywords_split
>>
>> aúéöÖÉÁ
>>(1 row)
>>
>>
>
>What happens if you add
> use locale;
>in your perl function
Daniel Verite wrote:
> Mage wrote:
>
>
>
>>with use locale;:
>>
>>select keywords_split('AúéöÖÉÁ');
>>ERROR: creation of Perl function failed: 'require' trapped by operation
>>mask at (eval 6) line 2.
>>
>>
>
&g
plit('');
keywords_split
(1 row)
teszt=# select lower('AúéöÖÉÁ');
lower
-
aúéöÖÉÁ
(1 row)
Mage
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Tom Lane wrote:
>Mage <[EMAIL PROTECTED]> writes:
>
>
>>It's serious.
>>
>>
>
>That's a Perl bug not a Postgres bug: libperl should not change the
>process's locale settings, or at least if it does it should restore
>the prior se
use locale;
use POSIX qw(locale_h);
setlocale(LC_CTYPE,'hu_HU');
return '';
$$
language plperlu;
It works. And it's so nasty. I have to insert these into every plperl
function.
I am not subscribed to any perl list. Would someone report this bug?
x27;s timestamp format is not accepted by postgresql.
Mage
---(end of broadcast)---
TIP 8: explain analyze is your friend
O: ({'date': '2005-05-05 13:20:43.794401', 'id': 3},)
UPDATE 2
CREATE FUNCTION
CREATE FUNCTION
INFO: ({'date': '2005-05-05 13:20:43.793551', 'id': 4},)
ERROR: invalid input syntax for type timestamp: "2005-05-05
13:20
Dinesh Pandey wrote:
> I am using Postgres. How can I send execution log into a file.
>
>
>
psql -o filename
I also recommend:
psql -H -e -o filename
Mage
---(end of broadcast)---
TIP 7: don't forget to increase yo
Hello,
how can I return setof rows in plpython language?
I read the manual and couldn't find.
Mage
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining col
nto common_adviewnum (adid, site, day,
viewnum) values ( $1 , $2 , now()::date, $3 )"
PL/pgSQL function "common_adviewnum_increase" line 11 at SQL statement
select common_adviewnum_increase(820434,'H');
Mage
---(end of broadcast)---
Dennis Bjorklund wrote:
On Mon, 15 Aug 2005, Mage wrote:
2005-08-12 19:08:43: ERROR: duplicate key violates unique constraint
"common_adviewnum_adid_site_day_index"
Between your select and your insert someone else inserted a row making the
insert fail.
I see. I th
full-text indexed.)
Mage
---(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's datatypes do not
match
Hi,
I know that the disk space of the database server should never run
out but it can happen.
Which is the worst case? May the database be corrupted?
Mage
--
http://mage.hu
---(end of broadcast)---
TIP 9: the planner will ignore
one in
the contrib. I think none of them cares of empty tables.
This issue need to be fixed.
Mage
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
order.
Changing the order of the columns is not a frontend question but
logically. (Some) people would like to see relevant columns near to each
other, even with an admin program. I would welcome some alter table
column order feature.
Btw human beings like changing things.
have foreign keys. I used dump, edit, and
restore the whole database in the past.
Mage
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Hello,
"order by a asc b desc"
how can I create an index for this?
Mage
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
de recorded correctly, the data loss is handled by
the WAL.
AMD XP2000, 512MB RAM, PostgreSQL 7.4.6 (i686), linux-2.4.28, gcc-3.3.5,
Adaptec 29160, WD Enterprise 4360 (SCSI, SCA-80)
I made mkfs and initdb before every tests and I repeated them in reverse
order too. No quake3 ran in the bac
I run Postgres on Gentoo and it works fine.
By the way, I have to tell that the best linux is Gentoo.
Mage
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres
Hello,
sorry for the trivial question. Is there any difference between varchar
and text types in practice? I couldn't find.
Mage
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
lumn?
--+--
potyty | t
potyty | t
potyty | t
(3 rows)
psql 8.0.3, Debian.
Mage
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
3 | 6 | 6
174041 | potyty | board| 2005-02-17 00:00:13.706144 | A |
S|3 | 6 | 6
(3 rows)
Mage
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
er one), so I don't
think that we have corrupted indexes.
I can try on a newer version of postgresql on another server.
By the way, if this is a bug then it's a serious one. We have it in
production environment.
Mage
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
I can try on a newer version of postgresql on another server.
By the way, if this is a bug then it's a serious one. We have it in
production environment.
Mage
what locales do you have? encoding?
The problem can be reproduced on pgsql 8.0.3, compiled from source.
This
Mage wrote:
./configure --prefix=/usr/local/pgsql --with-python
client_encoding| LATIN2
lc_collate | hu_HU
lc_ctype | hu_HU
lc_messages| en_US
lc_monetary| en_US
lc_numeric
om common_logins
where username not ilike 'potyty' limit 10);
DELETE 10
mage=# select * from common_logins where username = 'potyty';
uid | username | password | lastlogin | status | usertype | loginnum
-+--+--+---++--+
rds. Only data sorting requires the
knowledge that they are neighbours.
I am very afraid of queries when a condition has different meanings in
the select and in the where clause. It is the worst. Even if the "tyty"
and "tty" were same, I couldn't accept the actual b
e users (including me) don't always read the "known issues" chapter,
even for a good quality software.
Mage
---(end of broadcast)---
TIP 6: explain analyze is your friend
Hello,
is there any planned date when plruby will be officially included to
postgresql source (and documentation)?
Mage
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http
row execute
procedure test();
update test set parent = 2;
---
INFO: id: 1, oldname: old
INFO: id: 1, oldname: old
CONTEXT: PL/pgSQL function "test" line 4 at SQL statement
INFO: id: 2, oldname: old
CONTEXT: PL/pgSQL function "test" line 4 at SQL statement
INFO: id: 3,
row fired the trigger. (the "old.article_id <>
article_id" condition is not necessary btw.). Does this mean, if i
change any other rows in a row level before update trigger, rows changed
won't be updated anymore in the same statement?
Mage
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Hello,
I'm wondering why pgsql doesn't support boolean typecasts like select
true::int;
Many client applications including php assign 1 to true and 0 to false
I see no use of pgsql boolean type with php, I use int2 or integer.
Mage
---(end of
Pavel Stehule wrote:
Hello, you can use own cast.
I think I have to create an own type too, because I don't want to use
typecast in every select.
You gave me the idea, thank you.
Mage
---(end of broadcast)---
TIP 7: don't
Bruce Momjian wrote:
Uh, we have EXECUTE, but I don't know if will allow you to set a local
variable. Maybe you can do:
execute doesn't support select into
Mage
---(end of broadcast)---
TIP 7: don't forget to increase
You can't escape the null value.
Mage
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Secrétariat wrote:
Hello !
Why can't I update data when I use a VIEW instead of a TABLE ?
Why can't you drive home if you drink whisky instead of coca-cola?
Read the Manual Chapter 34 about the rule system.
Mage
---(end of broadcast)---
:
field1| field2
--+---
1 | 1
1 | 2
2 | 3
--+---
You need the serial type.
Mage
--
http://mage.hu
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Hi,
can pgsql acceppt invalid date values? Sometimes it would be nice to
convert 2003-02-29 to 2003-03-01 or to 2003-02-28 automatically instead
of throwing back an error message.
Mage
---(end of broadcast)---
TIP 6: Have you
Is there any on the site? Or I shall find in the tar file?
Mage
--
http://mage.hu
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
Alvaro Herrera wrote:
On Mon, Oct 04, 2004 at 11:58:53PM +0200, Mage wrote:
Is there any on the site? Or I shall find in the tar file?
It's on http://developer.postgresql.org/docs/postgres
Thank you. And is there some comparing 7.4 with 8.0 document? I mean a
simple "new fe
52 matches
Mail list logo
