Andreas Kretschmer wrote:
> Can you show an example for 8.4?
It's not 100% certain that it will be possible for 8.4, probably though.
select row_number() over (order by employeeid) as nrow,* from employee order
by employeeid
It's important to have both the order bys
There is more information on
Scott Marlowe wrote:
I think that this is a strange question, but: I need to revoke the
select permission on a table, but I also need to leave, with a function,
a user do a query on column.
A real case can be that a user "test" cannot have the permissions for do
a "select * from articles", but fo
Hi,
I have several Postgres DB's not showing correct daylight savings time.
>From maillist etc I believe these are patched up to the right levels to have
the correct time zones - but they don't seem to be working still.
The unix date command show the correct dates.
Server 1: postgresql-8.2.4 (U
am Wed, dem 15.10.2008, um 12:23:42 -0700 mailte Richard Broersma folgendes:
> On Wed, Oct 15, 2008 at 12:08 PM, ries van Twisk <[EMAIL PROTECTED]> wrote:
> > May be this function can help :
> >
> > http://www.postgresql.org/docs/8.3/static/functions-srf.html
>
> Using generate series won't numbe
Bill Thoen wrote:
Does PG (8.1) ever use existing indexes when executing an UPDATE?
I've got some tables with millions of records and whenever I update a
column that involves most or all the records the EXPLAIN command seems
to indicate that it isn't using the pre-existing indexes. This result
Chris Henderson wrote:
pg_dumpall archive. If you look at the backup file, you'll find that
it's just straight SQL. If you want to restore a particular database
out of it and not all of them, then you will need to edit the sql file
to include only what you want to restore. Then you simply pass
> pg_dumpall archive. If you look at the backup file, you'll find that
> it's just straight SQL. If you want to restore a particular database
> out of it and not all of them, then you will need to edit the sql file
> to include only what you want to restore. Then you simply pass it
> through psq
Hello
2008/10/15 Bruce Momjian <[EMAIL PROTECTED]>:
> Vladimir Dzhuvinov wrote:
> -- Start of PGP signed section.
>> Hi Ivan,
>>
>> > It seems anyway that the usefulness of this feature largely depends
>> > on the language library.
>> > eg. I can't see a way to support it with php right now but it
hi freinds,
I have an application software which has connection with MS Access through
DAO.I want to use same software without any changing with my new databank
Postgresql through Odbc.
I dont think so,that i can change it,I must develope whole software with odbc
for postgresql.
can any bod
Vladimir Dzhuvinov wrote:
-- Start of PGP signed section.
> Hi Ivan,
>
> > It seems anyway that the usefulness of this feature largely depends
> > on the language library.
> > eg. I can't see a way to support it with php right now but it is
> > supported by python.
> > Am I missing something?
>
>
Below is a very good summary of the limitations of our function
capabilities compared to procedures, e.g.:
o no transaction control in functions
o no multi-query return values without using special syntax
I don't think we can cleanly enable the second capability, but could we
a
Jeff Frost wrote:
> Chris Henderson wrote:
>
>> I backup all my databases by using pg_dumpall - pg_dumpall >
>> /tmp/postgres.backup.`hostname`. It should backup four DBs: analyze,
>> postgres, template0 and template1
>> I guess this backs up the schemas as well.
>>
>> Now I want to restore o
Chris Henderson wrote:
> I backup all my databases by using pg_dumpall - pg_dumpall >
> /tmp/postgres.backup.`hostname`. It should backup four DBs: analyze,
> postgres, template0 and template1
> I guess this backs up the schemas as well.
>
> Now I want to restore one of the databases and schema fr
On 15/10/2008 22:19, Chris Henderson wrote:
> I backup all my databases by using pg_dumpall - pg_dumpall >
> /tmp/postgres.backup.`hostname`. It should backup four DBs: analyze,
> postgres, template0 and template1
> I guess this backs up the schemas as well.
>
> Now I want to restore one of the d
On Wed, Oct 15, 2008 at 3:19 PM, Chris Henderson <[EMAIL PROTECTED]> wrote:
> I backup all my databases by using pg_dumpall - pg_dumpall >
> /tmp/postgres.backup.`hostname`. It should backup four DBs: analyze,
> postgres, template0 and template1
> I guess this backs up the schemas as well.
>
> Now
I backup all my databases by using pg_dumpall - pg_dumpall >
/tmp/postgres.backup.`hostname`. It should backup four DBs: analyze,
postgres, template0 and template1
I guess this backs up the schemas as well.
Now I want to restore one of the databases and schema from this backup
dump file onto a di
David Wall wrote on 15.10.2008 23:01:
Is there a way to get the PG version string from JDBC? I'm using PG 8.3.
Thanks,
David
In a portable manner:
http://java.sun.com/j2se/1.5.0/docs/api/java/sql/DatabaseMetaData.html#getDatabaseProductName()
http://java.sun.com/j2se/1.5.0/docs/api/java/sql/
D. Dante Lorenso wrote:
PERL can remember variables in your session. Here's a function I wrote
that sets a "global" variable in PL/PERL:
Perl can do anything- that's cheating :-)
Actually, I use Perl heavily but the advantage of being able to do the
sort of analysis being discussed in a sin
David Wall wrote:
Is there a way to get the PG version string from JDBC? I'm using PG 8.3.
Thanks,
David
SELECT version() ?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Select version();
version
-
PostgreSQL 8.3.3, compiled by Visual C++ build 1400
(1 row)
On Wed, Oct 15, 2008 at 2:01 PM, David Wall <[EMAIL PROTECTED]> wrote:
> Is there a way to get the PG version string from JDBC? I'm u
Is there a way to get the PG version string from JDBC? I'm using PG 8.3.
Thanks,
David
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Mark Morgan Lloyd wrote:
Is there an easy way to assign a sequential number, possibly based on an
arbitrary minimum (typically 0 or 1) to each row of an ordered result
set, or do I have to work with explicit sequences?
I need to do quite a lot of maths on successive rows, extracting numeric
a
Thanks everybody- I'm watching with a lot of interest. I was worried
that I was asking something stupid with an obvious answer...
ries van Twisk wrote:
May be this function can help :
http://www.postgresql.org/docs/8.3/static/functions-srf.html
Thanks, that's already turning out to be useful
On Wed, Oct 15, 2008 at 1:33 PM, Michele Petrazzo - Unipex srl
<[EMAIL PROTECTED]> wrote:
> I think that this is a strange question, but: I need to revoke the
> select permission on a table, but I also need to leave, with a function,
> a user do a query on column.
> A real case can be that a user "
On Wed, Oct 15, 2008 at 12:32 PM, Scott Marlowe <[EMAIL PROTECTED]> wrote:
> Can't you put the query into a subselect with an offset 0 and join to
> that to get the generate_series to work correctly?
I've never heard of doing it that way, but I'm very interestes in
seeing how it is done. This i
I think that this is a strange question, but: I need to revoke the
select permission on a table, but I also need to leave, with a function,
a user do a query on column.
A real case can be that a user "test" cannot have the permissions for do
a "select * from articles", but for do a "select has_art
On Wed, Oct 15, 2008 at 1:23 PM, Richard Broersma
<[EMAIL PROTECTED]> wrote:
> On Wed, Oct 15, 2008 at 12:08 PM, ries van Twisk <[EMAIL PROTECTED]> wrote:
>> May be this function can help :
>>
>> http://www.postgresql.org/docs/8.3/static/functions-srf.html
>
> Using generate series won't number the
Tom Lane wrote:
> "Matthew T. O'Connor" <[EMAIL PROTECTED]> writes:
> > Tom Lane wrote:
> >> Mind you, I find the SysV APIs uselessly baroque too, but there is one
> >> feature that we have to have that is not in mmap(): the ability to
> >> detect other processes attached to a shmem block.
>
> > D
On Wed, Oct 15, 2008 at 12:08 PM, ries van Twisk <[EMAIL PROTECTED]> wrote:
> May be this function can help :
>
> http://www.postgresql.org/docs/8.3/static/functions-srf.html
Using generate series won't number the rows that way that you would
want. You basically will end up with a cross join betw
May be this function can help :
http://www.postgresql.org/docs/8.3/static/functions-srf.html
Ries
On Oct 15, 2008, at 1:44 PM, Mark Morgan Lloyd wrote:
Is there an easy way to assign a sequential number, possibly based
on an arbitrary minimum (typically 0 or 1) to each row of an ordered
res
Is there an easy way to assign a sequential number, possibly based on an
arbitrary minimum (typically 0 or 1) to each row of an ordered result
set, or do I have to work with explicit sequences?
I need to do quite a lot of maths on successive rows, extracting numeric
and timestamp differences h
I was wondering if I can set fill factor without breaking slony
replication. It's technically DDL, but it's not really altering the
table in the way I'd expect to be an issue for slony. Anyone know
before I set up a replication set and experiment on it?
--
Sent via pgsql-general mailing list (p
On Wed, Oct 15, 2008 at 10:17 AM, Richard Broersma
<[EMAIL PROTECTED]> wrote:
> On Wed, Oct 15, 2008 at 9:03 AM, Reg Me Please <[EMAIL PROTECTED]> wrote:
>
>> Both are perfectly right, indeed.
>> Nonetheless, in my opinion a NATURAL JOIN exploiting the FKs
>> instead of the column names would be m
On Wed, Oct 15, 2008 at 10:42 AM, Bill Thoen <[EMAIL PROTECTED]> wrote:
> Does PG (8.1) ever use existing indexes when executing an UPDATE?
>
> I've got some tables with millions of records and whenever I update a column
> that involves most or all the records the EXPLAIN command seems to indicate
Tom Lane wrote:
I think the subtext there is that the Linux kernel hackers hate the SysV
IPC APIs and wish they'd go away. They are presently constrained from
removing 'em by their desire for POSIX compliance, but you won't get
them to make any changes that might result in those APIs becoming mo
"Matthew T. O'Connor" <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> Mind you, I find the SysV APIs uselessly baroque too, but there is one
>> feature that we have to have that is not in mmap(): the ability to
>> detect other processes attached to a shmem block.
> Didn't we solve this problem on
Tom,
This question is too vague to be answerable --- especially if you want
an answer that applies across all 8.x releases. I'd suggest
experimenting a bit using EXPLAIN ANALYZE to see what happens in your
actual application.
Thank you very much.
I cannot experiment with application currently
"Andrus" <[EMAIL PROTECTED]> writes:
> Is it OK to put all filters to outer query WHERE clase?
> Or should I move as many filter conditions as possible to inner query so
> that inner query returns 1 records instead of 50 records.
> Is there difference in perfomance if inner query returs l
Tom,
Don't worry about it. All modern Postgres versions ignore columns that
aren't actually used in the query --- at least for examples as simple as
this one. In cases where you intentionally defeat optimization (eg via
OFFSET 0 in a sub-select) it's possible that the sub-select will compute
a
On Wed, Oct 15, 2008 at 10:42 AM, Bill Thoen <[EMAIL PROTECTED]> wrote:
> Does PG (8.1) ever use existing indexes when executing an UPDATE?
>
> I've got some tables with millions of records and whenever I update a column
> that involves most or all the records the EXPLAIN command seems to indicate
Does PG (8.1) ever use existing indexes when executing an UPDATE?
I've got some tables with millions of records and whenever I update a
column that involves most or all the records the EXPLAIN command seems
to indicate that it isn't using the pre-existing indexes. This result in
a slow update,
Peter Eisentraut <[EMAIL PROTECTED]> writes:
> Scott Marlowe wrote:
>> Since you can check which columns have changed, it's pretty easy to
>> write a trigger that just skips its logic when none of the trigger
>> columns have changed.
> I think column-level triggers actually fire when one of the co
On Wed, Oct 15, 2008 at 9:03 AM, Reg Me Please <[EMAIL PROTECTED]> wrote:
> Both are perfectly right, indeed.
> Nonetheless, in my opinion a NATURAL JOIN exploiting the FKs
> instead of the column names would be much more helpful and much less error
> prone!
>
> As far as I know there is no way t
Greg Smith <[EMAIL PROTECTED]> writes:
> If there were ever any Linux distributions that increased this value from
> the tiny default, you might have a defensible position here (maybe
> Oracle's RHEL fork does, they might do something here). I've certainly
> never seen anything besides Solaris
Il Wednesday 15 October 2008 17:55:03 Tom Lane ha scritto:
> "Richard Broersma" <[EMAIL PROTECTED]> writes:
> > For this reason, clients passing natural joins to the server can have
> > dangerous result sets returned with no warning.
>
> Yeah. A lot of people consider that NATURAL JOIN is simply a
looks like most ppl nowdays have two simple problems, and try to work
against it. Instead they all should focus on getting their data organized
properly, and queries writeen for project before they start to code other
stuff.
The problems are: trying to outsmart db, still belive that you can catch
"Richard Broersma" <[EMAIL PROTECTED]> writes:
> For this reason, clients passing natural joins to the server can have
> dangerous result sets returned with no warning.
Yeah. A lot of people consider that NATURAL JOIN is simply a bad idea
and shouldn't be used ever --- it's too easy to shoot your
"Andrus" <[EMAIL PROTECTED]> writes:
> I have lot of autogenerated from projection queries in form
> SELECT source.c1, source.c2, t1.col1, t1.col2, ...
> FROM (SELECT c1, c2, c3, . , c20 FROM ... WHERE ... ) source
> LEFT JOIN t2 USING (somecolumn)
> Main SELECT uses only few columns (source.c1
On 10/15/08, Alvaro Herrera <[EMAIL PROTECTED]> wrote:
> Vladimir Dzhuvinov wrote:
>
> > > That feature alone can help you enormously. Lest you think I'm
> > > biased, I dba a mysql box professionally...every time I pop into the
> > > mysql shell I feel like I'm stepping backwards in time about 5
On Tue, Oct 14, 2008 at 3:50 PM, Isak Hansen <[EMAIL PROTECTED]> wrote:
> On Mon, Oct 13, 2008 at 2:57 AM, justin <[EMAIL PROTECTED]> wrote:
>> [...] Also you want to split out the debit and credits instead of
>> using one column. Example one column accounting table to track values
>> entered how
On Wednesday 15 October 2008 04:19:59 Laurent Wandrebeck wrote:
> 2008/10/15 Scott Marlowe <[EMAIL PROTECTED]>:
> > You'll probably have to ask that in -hackers. I'm guessing it's one
> > of those things that if one wrote a sufficiently large check one could
> > find a hacker to implement it. But
Vladimir Dzhuvinov wrote:
> > That feature alone can help you enormously. Lest you think I'm
> > biased, I dba a mysql box professionally...every time I pop into the
> > mysql shell I feel like I'm stepping backwards in time about 5 years.
> > Don't let the inability to return multiple sets trip
On Tue, Oct 14, 2008 at 11:17 PM, regme please <[EMAIL PROTECTED]> wrote:
> Well, it could make some sense to extend the semantics when you have
> explicit "REFERENCES" to tables in the JOINs.
> Or at least warn or notice the user that the "NATURAL (INNER) JOIN" has
> actuallt been converted into
I have lot of autogenerated from projection queries in form
SELECT source.c1, source.c2, t1.col1, t1.col2, ...
FROM (SELECT c1, c2, c3, . , c20 FROM ... WHERE ... ) source
LEFT JOIN t2 USING (somecolumn)
Main SELECT uses only few columns (source.c1 and source.c2 in this sample)
from source p
Hi Merlin,
>> A function is... hmm, a function, a mapping: given a set of arguments it
>> returns a single and well defined value: f(x,y) -> z
>>
>> The purpose of stored procedures, on the other hand, is to encapsulate
>> an (arbitrary) bunch of SQL commands, a mini-program of sort.
> I think yo
Hi,
where can i find which user account has which default schema ?
thanks a lot,
--
Alain
Windows XP SP3
PostgreSQL 8.2.4 / MS SQL server 2005
Apache 2.2.4
PHP 5.2.4
C# 2005-2008
Hello Eduardo
On Tue, 2008-10-14 at 15:40 -0500, Eduardo Arévalo wrote:
> I installed the 8.3 postgres
> the amount of giving the command:
> bash-3.2$ /usr/local/postgres_8.3/bin/initdb -D /base/data
>
that command only initializes the underlying filesystem database files,
directories and conf
Hey list,
so I was wondering. Since many ppl depend on 'affected rows', we have here a
trigger running on delete. It will update the table, and set certain fields
to false on delete, return NULL - so it will look like:
CREATE OR REPLACE FUNCTION ondelete_update() returns trigger as
$_$
BEGIN
IF
Hi,
I've recently split my log table into time-based partitions, which really
improves insert speed and query times for certain queries.
However, I can't help thinking the query optimizer is really suboptimal
here.
My partitions look like this:
CREATE TABLE log_cdf
(
id serial NOT NULL,
tsta
2008/10/15 Scott Marlowe <[EMAIL PROTECTED]>:
>
> You'll probably have to ask that in -hackers. I'm guessing it's one
> of those things that if one wrote a sufficiently large check one could
> find a hacker to implement it. But I can't imagine it being a weekend
> project, and if it's not already
Scott Marlowe wrote:
Since you can check which columns have changed, it's pretty easy to
write a trigger that just skips its logic when none of the trigger
columns have changed.
I think column-level triggers actually fire when one of the columns is
written to, not only when the value there is
61 matches
Mail list logo
