SELECT
f.year,
f.id,
c.name,
(f.value / p.value) AS per_capita
FROM
fish_catch AS f
JOIN
pop_total AS p
USING
(year, id)
INNER JOIN
countries AS c ON f.id = c.id
ORDER BY
(year = 2005), value, name
Seems to never end Why is redesigning tables so difficult?! :-))
And fur
Where does PostgreSQL stand with storing /really/ large amounts of data
offline? Specifically, if a FUSE is used to move a tablespace to something
like a tape archiver can the planner be warned that access might take an
extended period?
I know that at one point (v6?) there were hooks in the co
We have a system that came with pg 8.1.9. When I try to uninstall
those RPMs, it works for all the rpms except for libs:
> rpm -ev postgresql-libs-8.1.9-1.el5
error: Failed dependencies:
libpq.so.4 is needed by (installed) apr-util-1.2.7-6.i386
I am not sure what this is about and h
On 14/09/2007, Phoenix Kiula <[EMAIL PROTECTED]> wrote:
> We have a system that came with pg 8.1.9. When I try to uninstall
> those RPMs, it works for all the rpms except for libs:
>
>
> > rpm -ev postgresql-libs-8.1.9-1.el5
> error: Failed dependencies:
> libpq.so.4 is needed by (insta
"Mark Morgan Lloyd" <[EMAIL PROTECTED]> writes:
> Where does PostgreSQL stand with storing /really/ large amounts of data
> offline? Specifically, if a FUSE is used to move a tablespace to something
> like
> a tape archiver can the planner be warned that access might take an extended
> period?
N
Gregory Stark wrote:
Where does PostgreSQL stand with storing /really/ large amounts of data
offline? Specifically, if a FUSE is used to move a tablespace to something like
a tape archiver can the planner be warned that access might take an extended
period?
No, Postgres can't deal with this. Y
"Mark Morgan Lloyd" <[EMAIL PROTECTED]> writes:
> Thanks. If the tables were in a tablespace that was stored on something that
> looked like a conventional filesystem would the server code be prepared to
> wait
> the minutes that it took the operating system and FUSE implementation to load
> the
Hi,
novnov wrote:
OK, this has been very informative and I'd like to thank the three of you.
Asynchronous replication to readonly slaves is something I will look into.
I've never touched posgtres replication; and Scott mentioned that he was not
familiar with PGCluster, so there must be some ot
Thanks Markus
Markus Schiltknecht <[EMAIL PROTECTED]> wrote: Hello Sharmi Joe,
sharmi Joe wrote:
> Is there a way to get the oracle's rank() over partition by queries in
> postgresql?
These are known as window functions. AFAIK Gavin Sherry is working on an
implementation for Postgres.
Regards
Hello Sharmi Joe,
sharmi Joe wrote:
Is there a way to get the oracle's rank() over partition by queries in
postgresql?
These are known as window functions. AFAIK Gavin Sherry is working on an
implementation for Postgres.
Regards
Markus
---(end of broadcast)
Phoenix Kiula wrote:
>
> We have a system that came with pg 8.1.9. When I try to uninstall
> those RPMs, it works for all the rpms except for libs:
>
> > rpm -ev postgresql-libs-8.1.9-1.el5
> error: Failed dependencies:
> libpq.so.4 is needed by (installed) apr-util-1.2.7-6.i386
>
>
Hi,
I'm supporting Sun Grid Engine and it uses Postgres DB as a backend
server for ARCo accounting and reporting module.
One of my customers is asking how to recover data if Postgres DB server
got crashed.
SGE constantly generates accounting data and records them into Postgres DB.
I think on
Hello,
Is there some way of locking all database tables in a transaction
without knowing their names
or even better just locking the entire database? I know this is bad
tactics but there is a specific
case where i need it. Can it be done?
Thank you
Panagiotis
---(end
Gregory Stark wrote:
Thanks. If the tables were in a tablespace that was stored on something that
looked like a conventional filesystem would the server code be prepared to wait
the minutes that it took the operating system and FUSE implementation to load
the tables onto disc?
Ah, I see what y
On 9/14/07, Panagiotis Pediaditis <[EMAIL PROTECTED]> wrote:
> ... there is a specific case where i need it.
Don't really know, but, explain what the case is, and maybe someone
could help you.
---(end of broadcast)---
TIP 4: Have you searched our li
On 9/14/07, Chansup Byun <[EMAIL PROTECTED]> wrote:
> Is there any other way?
See:
http://www.postgresql.org/docs/8.2/static/backup.html
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an i
Well the problem is I am working on rdf query engine for persistent RDF
data. The data is stored/structured in a specific way in the database.
When i perform updates in parallel, because there are cross table
dependencies, I end up with inconsistencies, For example One transaction
reads to see
On 9/14/07, Panagiotis Pediaditis <[EMAIL PROTECTED]> wrote:
> Hello,
>
> Is there some way of locking all database tables in a transaction
> without knowing their names
> or even better just locking the entire database? I know this is bad
> tactics but there is a specific
> case where i need it. C
On Fri, Sep 14, 2007 at 05:45:07PM +0300, Panagiotis Pediaditis wrote:
> Well the problem is I am working on rdf query engine for persistent RDF
> data. The data is stored/structured in a specific way in the database.
> When i perform updates in parallel, because there are cross table
> dependen
Panagiotis Pediaditis, 14.09.2007 16:45:
Well the problem is I am working on rdf query engine for persistent RDF
data. The data is stored/structured in a specific way in the database.
When i perform updates in parallel, because there are cross table
dependencies, I end up with inconsistencies,
Thanks, recreating the table solved my problems. Our team is working on
implementing some performance tuning based on other recommendations from
the list (FSM, etc).
Mike
Joshua D. Drake wrote:
> At this point, you are in a world of hurt :). If you stop a vacuum you
> have created a huge mess o
Change made. Thanks. Your documentation changes can be viewed in five
minutes using links on the developer's page,
http://www.postgresql.org/developer/testing.
---
Andrew Maclean wrote:
> In Table 9.4 of the documentation
A simpler example,
In the context of one transaction i do many queries of the form
INSERT INTO table value WHERE value NOT IN TABLE;
If i have 2 processes running the same 100s of these at the same time i
end up with duplicates.
Even with isolation set to serializable
any ideas?
thn
On 9/14/07, Ketema Harris <[EMAIL PROTECTED]> wrote:
> I have the following table set up:
>
> CREATE TABLE states
> (
>state_id integer NOT NULL DEFAULT nextval
> ('state_province_id_seq'::regclass),
>state character(2),
>full_name character varying,
>timezone character varying,
>
On 9/14/07, Panagiotis Pediaditis <[EMAIL PROTECTED]> wrote:
> A simpler example,
> In the context of one transaction i do many queries of the form
> INSERT INTO table value WHERE value NOT IN TABLE;
>
> If i have 2 processes running the same 100s of these at the same time i
> end up
On Friday 14 September 2007, Ketema Harris <[EMAIL PROTECTED]> wrote:
> as expected I can do select * from states and get everything out of
> the child table as well. What I can't do is create a FK to the
> states table and have it look in the child table as well. Is this on
> purpose? Is it pos
On Sep 14, 2007, at 10:35 AM, Ketema Harris wrote:
I have the following table set up:
CREATE TABLE states
(
state_id integer NOT NULL DEFAULT nextval
('state_province_id_seq'::regclass),
state character(2),
full_name character varying,
timezone character varying,
CONSTRAINT "PK_stat
Hi,
Ketema Harris wrote:
as expected I can do select * from states and get everything out of the
child table as well. What I can't do is create a FK to the states table
and have it look in the child table as well. Is this on purpose? Is it
possible to have FK that spans into child tables?
I have the following table set up:
CREATE TABLE states
(
state_id integer NOT NULL DEFAULT nextval
('state_province_id_seq'::regclass),
state character(2),
full_name character varying,
timezone character varying,
CONSTRAINT "PK_state_id" PRIMARY KEY (state_id)
)
CREATE TABLE canadian
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 09/14/07 04:06, Mark Morgan Lloyd wrote:
> Where does PostgreSQL stand with storing /really/ large amounts of data
> offline? Specifically, if a FUSE is used to move a tablespace to
> something like a tape archiver can the planner be warned that acc
Hi all;
I have a bit of concern about writing applications which use Pgsql roles
for security.
Since the utility statements are not parameterized, the easiest way to
manage the roles in an application is to use stored procedures which
EXECUTE strings to create SQL queries. These EXECUTE st
Ron Johnson wrote:
I know that at one point (v6?) there were hooks in the code for
experimental Berkeley code to do this sort of thing but as far as I know
there has never been anything publicly available.
While tertiary media certainly was relevant 10 years ago, is it
really necessary in 2007
Andrew Hammond wrote:
> On 9/13/07, Bruce Momjian <[EMAIL PROTECTED]> wrote:
> >
> > Alvaro Herrera wrote:
> > > Bruce Momjian wrote:
> > > >
> > > > Is this item closed?
> > >
> > > No, it isn't. Please add a TODO item about it:
> > > * Prevent long-lived temp tables from causing frozen-Xid adva
Well, after further searching and reviewing the code I believe the problem
was the NOINHERIT in the login role creation.
So the remaining question is:
On another question, if I want to grant privileges to all tables I have to
do them ONE BY ONE. Granting the privileges on the database or the sch
A question related to tsearch2 functionality in postgres:
When I run the following query:
select to_tsvector('default', 'website.com')
I get "'website.com':1".
What I need to get back is 'website':1 instead. I can see that the parser
correctly determines term website.com as a host token, which
I am working with PostgreSQL 8.2.4.
I need to use the SQL COPY (COPY table FROM file) statement to
populate my database.
I have created a normalized data model (up to 3NF). Is it possible to
use COPY TO on a particular table (that is linked to other tables
using foreign keys) whilst keeping the
I've done everything I can find related to this error including su -
instead of su useradd instead of adduser but nothing is helping, is
there anyone out there using bash on mac os who knows how to fix this?
Thanks,
Jason
---(end of broadcast)--
Hi,
I am looking for a way to get the original query that caused a trigger
to fire. I need to be able to get this query either inside the
trigger itself (and then send it to the function the trigger calls) or
get it in the end function. Is this doable? The reason i am asking is
that I would like
Hi
Why is '' not considered null is postgres (8.1.3) Currently I have to
use the following workaround where I have zero length strings in char
fields.
select * from security.users where length(us_username)=0;
Surely this a null. Apparently not in Postgres.
Currently I have to use the followin
"Gustav Lindenberg" <[EMAIL PROTECTED]> writes:
> select * from security.users where length(us_username)=0;
> Surely this a null.
Surely not.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 5
What would cause psql to hang indefinitely when the backend disappears?
We have a script that uses psql to insert a record (TCP connection to DB
on different machine). The command is basically
psql -c "insert into..."
A while back I had to restart the server and today discovered that some
In response to finecur <[EMAIL PROTECTED]>:
> On Sep 10, 9:55 pm, finecur <[EMAIL PROTECTED]> wrote:
> > Hi
> >
> > Here is my table:
> >
> > Table School
> > (
> > id integer,
> > name text
> > );
> >
> > Table Department
> > (
> > id integer,
> > school_id integer reference schoo
On Sep 12, 3:05 pm, [EMAIL PROTECTED] ("D. Dante Lorenso") wrote:
> Pavel Stehule wrote:
> > 2007/9/12, Jay Dickon Glanville <[EMAIL PROTECTED]>:
> >> - I write a function (it doesn't matter what language it's in:
> >> PL/pgSQL, PL/Java, etc)
> >> - I register that function as a "post-commit" callb
On Sep 10, 9:55 pm, finecur <[EMAIL PROTECTED]> wrote:
> Hi
>
> Here is my table:
>
> Table School
> (
> id integer,
> name text
> );
>
> Table Department
> (
> id integer,
> school_id integer reference school(id),
> name text
> );
>
> Table Course
> (
> department_id intege
Hello,
Thanks everyone for your input. Then, it sounds like I won't use an
array of foreign keys. I was just curious about the array
functionality.
However, I didn't think about setting up a view above the intermediary
table with an array_accum, now I have never heard of array_accum. I
did some r
Have you tried sudo? su won't do anything if you haven't explicitly
enabled the root account.
On Sep 12, 2007, at 1:53 AM, Jason Nerida wrote:
I've done everything I can find related to this error including su
- instead of su useradd instead of adduser but nothing is helping,
is there any
Dear all,
I'm a bit confused about privilege management in PostgreSQL.
I have a database "db1" , schema "schema1" and table "table1", created with
a superuser.
Now, following the documentation (and what I've learnt about user
management), I created a group called admin and a user login, and gav
Hi
Here is my table:
Table School
(
id integer,
name text
);
Table Department
(
id integer,
school_id integer reference school(id),
name text
);
Table Course
(
department_id integer references department(id),
name text,
course_number text
)
I would like to make
On Tue, 11 Sep 2007, Laimonas Simutis wrote:
A question related to tsearch2 functionality in postgres:
When I run the following query:
select to_tsvector('default', 'website.com')
I get "'website.com':1".
What I need to get back is 'website':1 instead. I can see that the parser
correctly de
Hi all,
I have a column declared as "timestamp without time zone" that I vainly
want to insert a raw timestamp into (i.e. in the format returned by
Perl's or PHP's time()). I know of SQL NOW(), but I want to insert a
"cooked" timestamp from the outside most efficiently. How?
Thanks.
---
> Hi,
>
> I am looking for a way to get the original query that caused a trigger
> to fire. I need to be able to get this query either inside the
> trigger itself (and then send it to the function the trigger calls) or
> get it in the end function. Is this doable? The reason i am asking is
> that
I normally use (for php) something like date('Y-m-d H:i:s', time())
to get a string version that Postgres will accept.
On Sep 14, 2007, at 3:23 PM, rihad wrote:
Hi all,
I have a column declared as "timestamp without time zone" that I
vainly want to insert a raw timestamp into (i.e. in the
On Sep 12, 2007, at 7:32 PM, Andrew Hammond wrote:
Does anyone know where I could find a tool which allows importing
schema information from a postgres database into visio? The boss
guys want some pretty pictures...
See SQLFairy. it can generate pretty pictures directly from the
schemas
[EMAIL PROTECTED] (Chris Travers) writes:
> Since the utility statements are not parameterized, the easiest way to
> manage the roles in an application is to use stored procedures which
> EXECUTE strings to create SQL queries. These EXECUTE statements
> include user-supplied data, and since these
Hi
I had installed postgresql-8.2.4. After some problems with tsearch2
installation i had the next problem:
prueba=# select to_tsvector('espanol','melón');
ERROR: Affix parse error at 506 line
If execute:
prueba=# select lexize('sp','melón');
lexize
-
"Gustav Lindenberg" <[EMAIL PROTECTED]> writes:
> Why is '' not considered null is postgres (8.1.3)
Because they're different. The SQL spec says that an empty string
is different from NULL, and so does every database in the world except
Oracle. Oracle, however, does not define the standard.
56 matches
Mail list logo
