Hi,
I understand, that this is 'general SQL' question rather then 'general
postgres'. But may be someone here could help me with it anyways.
I have a *single* table:
CREATE TABLE test (id int not null unique, thread int not null, info
text);
The ID, although unique, is not continues. A sample
On 6/26/07, Rafal Pietrak [EMAIL PROTECTED] wrote:
Is there an SQL construct to get it?
select
distinct on (t1.id)
t1.*, t2.*
from
test t1
join test t2 on t2.id t1.id
order by t1.id asc, t2.id asc
should do the trick.
depesz
--
http://www.depesz.com/ - nowy, lepszy depesz
On 6/26/07, 金星星 [EMAIL PROTECTED] wrote:
Are there any solutions based on PostgreSQL that can support
distributing partitions (horizontal fragmentations) across different
nodes. It doesn't need to support distributed transaction, since data
inconsistent is not a critical problem in my
Now, I'd like to make a JOIN-ed query of that table with itself, so that
I'd get rows paiwise: every row containing data from *two* rows of the
original TEST table so, that those data come from rows of consequtive
ID's - not neceserly (depending on the TEST table contents) continuesly
[EMAIL PROTECTED] wrote:
I'm getting duplicate rows returned. I don't know know and can't find
out how to construct the SQL to return what I want. I have an old
version of postgres which I cannot upgrade. I'm not even sure how to
get the version. Does this help?
$ grep -i version
cha wrote:
But am not able to import the tables with batch file, though am able to run
the same command from the prompt successfully.
Why? What errors are you getting?
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 5: don't
Murali Doss wrote:
I like to know about postgresql varchar[ ] data type
equivalent in Oracle.
The best I can think of is a VARRAY, though you cannot access
it by index in SQL (you need a stored procedure or client API
for that). Quite clumsy.
Example:
SQL CREATE TYPE VARCHAR_A AS
Richard Broersma Jr wrote:
--- Alfred Zhao [EMAIL PROTECTED] wrote:
Suppose I want to update a statistics table S after an
insertion into an activity table A. In order to update S
automatically, I can either create a rule on A or create
an after trigger on A. What's the performance
Marvelous! Thenx!
-R
On Tue, 2007-06-26 at 10:06 +0200, hubert depesz lubaczewski wrote:
On 6/26/07, Rafal Pietrak [EMAIL PROTECTED] wrote:
Is there an SQL construct to get it?
select
distinct on (t1.id)
t1.*, t2.*
from
test t1
join test t2 on t2.id t1.id
Hi Rafal,
Just a note that this is not standard SQL... 'distinct on' is an
extension to SQL provided by postgres.
Following query utilizes the standard SQL to get the same results:
selectt1.id as id, t2.id as id+1,
t1.thread as thread, t2.thread as thread+1,
t1.info as
On Sat, Jun 23, 2007 at 09:43:01PM -0600, gary jefferson wrote:
Is there a way to use a variable as the name of a table or column in
plpgsql?
No, plpgsql is statically typed, it can't handle the possiblity of the
types of variables changing. Use a more dynamic
language(perl/tcl/python/etc...)
OK. Have tried this one looks like close to 6 times slower then the
'non-standard' phrase with 'distinct on'.
On the small dataset that I've included in my original post (ten rows of
data within TEST), I've run both queries through EXPLAIN ANALYSE, with
the following result summary (for
I missed the ORDER BY clause... Here it goes:
selectt1.id as id, t2.id as id+1,
t1.thread as thread, t2.thread as thread+1,
t1.info as info, t2.info as info+1
from test as t1, test as t2
where t2.id = ( select min(id) from test as t3 where t3.id t1.id )
order by t1.id asc;
Albe Laurenz [EMAIL PROTECTED] writes:
Richard Broersma Jr wrote:
My understanding is that Triggers offer better performance
than rules do.
A trigger FOR EACH STATEMENT will execute the trigger function
for each row affacted by the statement.
Huh? That would be true for a FOR EACH ROW
Martin Langhoff wrote:
Trying to understand how I can get Pg 8.2 to match 'martín' when I
search for 'martin', and going through the documentation, specially
http://www.postgresql.org/docs/8.2/static/locale.html
http://www.postgresql.org/docs/8.1/static/functions-matching.html
Here is
Dear General,
I have stolen some code from information_schema.applicable_roles, so that i
can query the roles for a user, without having to become that user (a
superuser executes this).
The problem is that pg_has_role does not recognize the usernames when they
are escaped by quote_literal or
On Sun, Jun 24, 2007 at 06:45:00PM -, danmcb wrote:
into two tables, one for the originals, one for the translations (the
objects are actually phrases in many languages). But I'd rather avoid
that because in all other ways, the objects have the same properties,
reference the same objects,
Hi!
I use PG 8.1.5
I execute in psql next comands:
create table t_table (gid serial, name varchar);
select pg_catalog.pg_get_serial_sequence('t_table', 'gid');
pg_get_serial_sequence
public.t_table_gid_seq
create table t_table_1() inherits (t_table);
\d t_table_1
A trigger FOR EACH STATEMENT will execute the trigger function
for each row affacted by the statement.
Huh? That would be true for a FOR EACH ROW trigger, but a STATEMENT
trigger fires once per statement.
Argh. I intended to write FOR EACH ROW.
Thanks for the correction.
A rule would
In article [EMAIL PROTECTED],
Richard Huxton [EMAIL PROTECTED] writes:
$the_sql = SELECT projectname, username, sum(hours);
$the_sql .= FROM timerecs;
$the_sql .= WHERE projectname = projects.projectname ;
$the_sql .= AND projectname = restrictions.projectname;
$the_sql .= AND
Tom Lane wrote:
Albe Laurenz [EMAIL PROTECTED] writes:
Richard Broersma Jr wrote:
A rule would only
execute one additional statement. So if you can do it with a rule
conveniently, the rule will probably be faster.
I find this unlikely. The overhead involved in setting up a rule
is
Gurjeet Singh skrev:
I missed the ORDER BY clause... Here it goes:
selectt1.id http://t1.id as id, t2.id http://t2.id as id+1,
t1.thread as thread, t2.thread as thread+1,
t1.info http://t1.info as info, t2.info http://t2.info as
info+1
from test as t1, test as t2
where
It seems that insertion triggers on views will/may be added in version 8.3.
http://archives.postgresql.org/pgsql-general/2007-02/msg01466.php
However, Tom mentioned that adding update and deletion triggers may be a bad
idea:
http://archives.postgresql.org/pgsql-general/2007-02/msg01466.php
...
news.gmane.org [EMAIL PROTECTED] writes:
Gurjeet Singh skrev:
Also note that this query is much cheaper that the 'distinct on' query
by more than two orders on magnitude ( 217.86 vs. 98040.67):
No it isn't. The estimate is much lower, but the actual times are very
close:
[explain of
chandresh rana wrote:
Hi Richard,
I have eliminated certain tables while exporting as they the size of the
data in the tables are huge.Am having the schema of all the tables.
Now want to import certain set of records/rows from the eliminated tables.
Is it possible to do that ?? If yes, then
to impact that update you'd have to have a rule on
account_login. No rule on a child table will matter.
Well i had the same perception In order to double check this i created
an On insert do nothing rule on the child table and did an insertion to the
master table i.e (account_login) but
Willy-Bas Loos [EMAIL PROTECTED] writes:
The problem is that pg_has_role does not recognize the usernames when they
are escaped by quote_literal or quote_ident.
Don't use quote_ident here. In fact, I'd say that you appear to have
completely misunderstood the appropriate rules for quoting at
Hello List
I have been a user since 7.x version. My current server version is 8.1.2.
As many of you know the disk usage keeps growing for postgresql unless one
regularly reindex and/or fully vacuum. The problem with either method is that
they lock the tables and it is not practical for
[EMAIL PROTECTED] wrote:
Hello List
I have been a user since 7.x version. My current server version is 8.1.2.
As many of you know the disk usage keeps growing for postgresql unless one
regularly reindex and/or fully vacuum. The problem with either method is that
they lock the tables and it
A quote in the data might be real data or quoting, and you have no way to
tell the difference.
It´s not so bad, because a parameter of type name cannot start with a quote.
But you´re right, i have been having a hard time with the use of quoting.
The rule i used until now was: every string that
Hello,
I use the following command SELECT * FROM employees ORDER BY name
(a very simple sql statement) the main thing here is that I get a
list ordered without taking into accound the spaces. For example, I
get the following listing:
ABAB
AB CD
ABD E
AB EF
and what I
In response to [EMAIL PROTECTED]:
Hello List
I have been a user since 7.x version. My current server version is 8.1.2.
As many of you know the disk usage keeps growing for postgresql unless one
regularly reindex and/or fully vacuum. The problem with either method is
that they lock
On Jun 25, 2007, at 19:01 , Michael Glaesemann wrote:
The message is just a string. Assign the message to a variable and
use the variable in place of the message. For example, in PL/pgSQL:
k_error_message := 'Boom! %';
RAISE EXCEPTION k_error_message, v_foo.id;
I was wrong. The message is
i decide this weekend to upgrade to the latest stable version from an
8.1.4 . Upgrade went smootly, as usual, but today, i've got some
phonecalls of something weird. The query is as follows :
registratie=# select * from module_info where type_module_id = 1;
naam | korte_beschrijving |
[EMAIL PROTECTED] wrote:
Hello,
I use the following command SELECT * FROM employees ORDER BY name
(a very simple sql statement) the main thing here is that I get a
list ordered without taking into accound the spaces. For example, I
get the following listing:
ABAB
AB CD
ABD E
On Jun 26, 2007, at 14:41 , [EMAIL PROTECTED] wrote:
and what I need is the following (old fashion, that is, the
SPACE is another character whose ASCII value is before any other
LATIN letter's!!)
AB CD
AB EF
ABAB
ABD E
Sorting is defined by the locale settings of your computer. I get the
jef peeraer wrote:
i decide this weekend to upgrade to the latest stable version from an
8.1.4 . Upgrade went smootly, as usual, but today, i've got some
phonecalls of something weird. The query is as follows :
registratie=# select * from module_info where type_module_id = 1;
i combine this
On Jun 26, 2007, at 14:52 , jef peeraer wrote:
The order is completely ignored, although there is an order by in
the view
'module_info'
I don't know what has caused the change in your system, but IIRC, the
spec does not require a view to return rows in any particular order—
I'm not even
Willy-Bas Loos [EMAIL PROTECTED] writes:
A quote in the data might be real data or quoting, and you have no way to
tell the difference.
It's not so bad, because a parameter of type name cannot start with a quote.
Sure it can.
regression=# select 'Fooey'::name;
name
-
Fooey
(1
Richard Huxton [EMAIL PROTECTED] writes:
jef peeraer wrote:
registratie=# select * from module_info where type_module_id in
(select * from get_parent_type_modules(1));
The order is completely ignored, although there is an order by in the view
'module_info'
You're applying a where clause to
I see. (Have actually tried it on a larger dataset - to see it for
myself ... it is optimised :)
Thenx again!
-R
On Tue, 2007-06-26 at 19:56 +0530, Gurjeet Singh wrote:
It _is_ the optimised version as you can see from the explain
plans posted in the other mail, the planner shows
Alvaro Herrera wrote:
Martin Langhoff wrote:
# this is apparently the right way to
# select base character based on the equivalence class
# as defined in the LC_CTYPE
=# select * from test where value ~ 'mart[=i=]n';
I think it would be much easier if you did
er.. i guess i was misinformed. (by Korry Susan Douglas´ second edition,
maybe this is relatively new?)
However, could you please answer my question?
On 6/26/07, Tom Lane [EMAIL PROTECTED] wrote:
Willy-Bas Loos [EMAIL PROTECTED] writes:
A quote in the data might be real data or quoting, and
On Jun 26, 2007, at 14:41 , [EMAIL PROTECTED] wrote:
and what I need is the following (old fashion, that is, the SPACE
is another character whose ASCII value is before any other LATIN
letter's!!)
AB CD
AB EF
ABAB
ABD E
What you don't want :
peufeu= SELECT column1 FROM (VALUES
OK, check...
test= CREATE TABLE test (id INTEGER PRIMARY KEY);
test= INSERT INTO test SELECT random()*5 + n*10 FROM
generate_series( 1,10 ) AS n;
test= SELECT * FROM test LIMIT 10;
id
-
11
23
31
41
52
63
70
85
94
103
test= ANALYZE test;
ANALYZE
Alvaro Herrera wrote:
Bruce McAlister wrote:
Hi All,
I have enabled autovacuum in our PostgreSQL cluster of databases. What I
have noticed is that the autovacuum process keeps selecting the same
database to perform autovacuums on and does not select any of the others
within the cluster. Is
Bruce McAlister wrote:
I have just checked the pg_stat_all_tables in the pg_catalog schema and
I can see the index scans etc table values incrementing. The data in the
tables seems to be updating. Just an FYI, I've enabled manual vacuum
analyze runs on the blueface-service database up until
On Jun 26, 2007, at 3:31 PM, Bill Moran wrote:
VACUUM FULL and REINDEX are not required to maintain disk usage.
Good old-
fashoned VACUUM will do this as long as your FSM settings are high
enough.
I find this true for the data but not necessarily for indexes. The
other week I
On 6/26/07, Tom Lane [EMAIL PROTECTED] wrote:
news.gmane.org [EMAIL PROTECTED] writes:
Gurjeet Singh skrev:
Also note that this query is much cheaper that the 'distinct on' query
by more than two orders on magnitude ( 217.86 vs. 98040.67):
No it isn't. The estimate is much lower, but the
49 matches
Mail list logo
