Hi
We have a 100GB database (16GB dumped) running on 8.2.
Since the bandwidth in South Africa isn't that freely available it is
difficult for us to get a new copy of out DB in our office (our fastest
link in the office is 4Mbps).
What can you recommend for me to get an up to date copy of my
Ian Meyer wrote:
> That's entirely possible.. which is the reason for cleanup.. we're
> moving to a model where members can be queried by name, and UTF-8
> isn't allowed in URLs, so we need to rename/remove users with those
> types of names.
Depending on your webserver, Unicode characters should b
>
> Can you describe, or point me to somewhere which describes, all the
> things you can do with a rule that you can't do with a trigger? The
> only examples of rules in the manual are (1) logging, which I've just
> been told is much better done with a trigger, and (2) making update,
> insert, an
That's entirely possible.. which is the reason for cleanup.. we're
moving to a model where members can be queried by name, and UTF-8
isn't allowed in URLs, so we need to rename/remove users with those
types of names. A lot of these members are from years ago where we
were on mysql with not enough e
Hi Ian,
Ian Meyer wrote:
Ah, so I forgot to mention the one caveat to this (sorry!) was there
was a ton of punctuation/spaces and other ilk.. so this is what I came
up with:
bco=# select name from member where not (name ~ '^[A-Za-z0-9[:punct:] ]*$');
name
--
Señor
In another thread, Tom Lane <[EMAIL PROTECTED]> wrote:
> Well, the rule system is fundamentally a macro-expansion mechanism,
> and multiple-evaluation risks come with that territory. There are
> things you can do with macro expansion that can't be done any other
> way, so I don't think that that d
Thomas Kellerer wrote:
Steve Atkins wrote on 23.06.2008 20:21:
> In real use you're unlikely to hit any limits, theoretical or practical,
I imagine that the 1GB column-value limit is something that could be
reached though. Especially for BLOB (aka bytea) or CLOB (aka text) columns.
No, since
"Michael Shulman" <[EMAIL PROTECTED]> writes:
> On Mon, Jun 23, 2008 at 8:46 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
>> Multiple evaluations of NEW in the text of a rule are a great way
>> to cause yourself trouble --- consider what happens if there's
>> a volatile function such as nextval() involv
On Mon, Jun 23, 2008 at 8:46 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
> Hmm ... that might be a bug, but in any case, wouldn't it be wiser to do
>
> CREATE RULE _insert AS ON INSERT TO tv DO INSTEAD
> INSERT INTO test (name) VALUES (NEW.name) RETURNING test.*;
Well, what I'm really trying to do is
"Michael Shulman" <[EMAIL PROTECTED]> writes:
> CREATE RULE _insert AS ON INSERT TO tv DO INSTEAD
> INSERT INTO test (name) VALUES (NEW.name) RETURNING NEW.*;
> ERROR: invalid reference to FROM-clause entry for table "*NEW*"
> LINE 2: INSERT INTO test (name) VALUES (NEW.name) RETURNING NEW.*;
Hi,
What are the rules about what tables/views can be referenced from the
RETURNING clause of an INSERT?
I am particularly puzzled by the following. Given these definitions:
CREATE TABLE test (id serial primary key, name varchar);
CREATE VIEW tv AS SELECT * FROM test;
This works:
CREATE RULE
Ah, so I forgot to mention the one caveat to this (sorry!) was there
was a ton of punctuation/spaces and other ilk.. so this is what I came
up with:
bco=# select name from member where not (name ~ '^[A-Za-z0-9[:punct:] ]*$');
name
--
Señorita Lolita
Long Pig
täkÃ
Daniel Futerman wrote:
Is it possible to have UPDATE JOIN queries in PostgreSQL?
Yes:
UPDATE target
FROM othertable;
As far as I know Pg can only do an inner join on the update target. This
can be easily be turned into an outer join with something like:
UPDATE target
FROM
On Jun 23, 2008, at 1:58 PM, Ian Meyer wrote:
So I have a column that contains usernames that have characters such
as Ã(c)(R), for example: fuchÃ(c)r.. is there any way to find names
with non A-Za-z0-9?
... WHERE column ~* '[^a-z0-9]'
Cheers,
Steve
--
Sent via pgsql-general mailing list
"Ian Meyer" <[EMAIL PROTECTED]> writes:
> So I have a column that contains usernames that have characters such
> as Ã(c)(R), for example: fuchÃ(c)r.. is there any way to find names
> with non A-Za-z0-9?
Hmm, none of the responses so far look right to me. How about
WHERE NOT (col ~ '^[A-Z
Antonio Perez wrote:
> example
>
> SELECT name FROM table1 where name
> ~* '*Ã*'
Actually this regex is flawed. It looks like a common shell "glob"
pattern (I don't know the real name of these things), which is a very
different and simpler animal from a regex.
--
Alvaro Herrera
use this
regular expression
~
regular expression - case insensitive
~*
example
SELECT name FROM table1 where name
~* '*Ã*'
check here
http://www.postgresql.org/docs/current/static/functions-matching.html
--- El lun 23-jun-08, Ian Meyer
Ian Meyer wrote:
So I have a column that contains usernames that have characters such
as Ã(c)(R), for example: fuchÃ(c)r.. is there any way to find names
with non A-Za-z0-9?
...WHERE col ~ '[^a-zA-Z0-9]';
Someone with a bit more regex fu can probably condense down the regex.
--
Tommy Gildseth
So I have a column that contains usernames that have characters such
as Ã(c)(R), for example: fuchÃ(c)r.. is there any way to find names
with non A-Za-z0-9?
Thanks in advance!
- Ian
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http:/
Steve Atkins wrote on 23.06.2008 20:21:
> In real use you're unlikely to hit any limits, theoretical or practical,
I imagine that the 1GB column-value limit is something that could be reached
though. Especially for BLOB (aka bytea) or CLOB (aka text) columns.
Thomas
--
Sent via pgsql-genera
I have the following error:
Postgres 8.3 via psycopg 1.1.21 and zope 2.10.
ProgrammingError Error Value: ERROR: character 0xe28099 of encoding "UTF8" has
no equivalent in "LATIN1" select distinct teachers.teacherid,teachers.teacherid
as thisteacherid,teachers.initials,
reports.reporttext,report
--- El lun 23-jun-08, Daniel Futerman <[EMAIL PROTECTED]> escribió:
De: Daniel Futerman <[EMAIL PROTECTED]>
Asunto: [GENERAL] Update Join Query
A: pgsql-general@postgresql.org
Fecha: lunes, 23 junio, 2008, 4:43 pm
Hi,
Looking for the correct syntax for an UPDATE LEFT JOIN query in PostgreSQL.
On Monday 23. June 2008, salman wrote:
>This is what I use: SELECT
>pg_size_pretty(pg_database_size(current_database()));
Great, I remember that I saw it, but couldn't remember the command. Now
I've made a function:
CREATE OR REPLACE FUNCTION db_size() RETURNS TEXT AS $$
SELECT pg_size_pret
--- El lun 23-jun-08, Daniel Futerman <[EMAIL PROTECTED]> escribió:
De: Daniel Futerman <[EMAIL PROTECTED]>
Asunto: [GENERAL] Update Join Query
A: pgsql-general@postgresql.org
Fecha: lunes, 23 junio, 2008, 4:43 pm
Hi,
Looking for the correct syntax for an UPDATE LEFT JOIN query in PostgreSQL.
On Mon, Jun 23, 2008 at 2:45 PM, Kynn Jones <[EMAIL PROTECTED]> wrote:
> Actually, the DB I have in mind would certainly be approaching "silly
> territory." I'm looking at a schema with around 10 thousand tables (or
> views).
What kind of app would require such a schema? Just curious...
--
Sent
"Kynn Jones" <[EMAIL PROTECTED]> writes:
> Actually, the DB I have in mind would certainly be approaching "silly
> territory." I'm looking at a schema with around 10 thousand tables (or
> views). Unfortunately, as far as I can tell,
> http://www.postgresql.org/about/ says nothing about maximum nu
update foo
set foo_id = g.goo_id
from goo g
where foo.foo_id = g.goo_id and foo.foo_id is not null
I think. :)
-Mark
On Mon, 2008-06-23 at 21:43 +0200, Daniel Futerman wrote:
> Hi,
>
> Looking for the correct syntax for an UPDATE LEFT JOIN query in
> PostgreSQL.
>
> The equivalent MySQL query
On Mon, Jun 23, 2008 at 2:21 PM, Steve Atkins <[EMAIL PROTECTED]> wrote:
>
> In real use you're unlikely to hit any limits, theoretical or practical,
> but if you start to use a silly number of tables and so on you're likely to
> hit performance issues eventually. I'm not sure where that threshold
Hi,
Looking for the correct syntax for an UPDATE LEFT JOIN query in PostgreSQL.
The equivalent MySQL query is :
UPDATE
Foo f LEFT JOIN Goo g on f.Foo_ID = g.Goo_ID
SET
f.Foo_ID = g.Goo_ID
WHERE
f.Foo_ID IS NOT NULL;
When I try to run this in Postgres, i get
Brad Nicholson wrote:
On Mon, 2008-06-23 at 10:50 -0700, Reynolds, Gregory A wrote:
In past versions there was a contrib/dbsize command that would tell
you how much storage space was being taken by a table. Is that is
still a supported feature in 8.3, and if so what is the syntax or
where is th
Thanks for all of the replies.
Best Regards,
Michael Gould, Manager Information Technology
All Coast Intermodal Services, Inc.
904-226-0978
_
From: Tom Lane [mailto:[EMAIL PROTECTED]
To: Roberts, Jon [mailto:[EMAIL PROTECTED]
Cc: [EMAIL PROTECTED], pgsql-general General [mailto:[EMAIL PR
On Mon, 2008-06-23 at 10:50 -0700, Reynolds, Gregory A wrote:
> In past versions there was a contrib/dbsize command that would tell
> you how much storage space was being taken by a table. Is that is
> still a supported feature in 8.3, and if so what is the syntax or
> where is the documentation on
On Mon, Jun 23, 2008 at 10:29 AM, Shahaf Abileah <[EMAIL PROTECTED]> wrote:
> Regarding the community survey on the website:
> http://www.postgresql.org/community/
>
>
>
> It would be great to have a little more detail about each of the TODO's –
> what it means, what's in scope, what's out of scope
On Mon, 2008-06-23 at 11:21 -0700, Steve Atkins wrote:
>
>
> http://www.postgresql.org/about/ has some of the theoretical limits.
>
> In real use you're unlikely to hit any limits, theoretical or
> practical, but if you start to use a silly number of tables and so
> on
> you're likely to hi
"Roberts, Jon" <[EMAIL PROTECTED]> writes:
> Character will use more disk space than varchar so it does make a
> difference.
char also has very peculiar comparison semantics. Unless your strings
are really truly fixed-length, you should just about always use varchar.
rega
On Mon, 23 Jun 2008, Reynolds, Gregory A wrote:
In past versions there was a contrib/dbsize command that would tell you
how much storage space was being taken by a table. Is that is still a
supported feature in 8.3, and if so what is the syntax or where is the
documentation on how to use it?
T
Character will use more disk space than varchar so it does make a
difference.
http://www.postgresql.org/docs/8.3/interactive/datatype-character.html
Values of type character are physically padded with spaces to the
specified width n, and are stored and displayed that way. However, the
paddi
In past versions there was a contrib/dbsize command that would tell you
how much storage space was being taken by a table. Is that is still a
supported feature in 8.3, and if so what is the syntax or where is the
documentation on how to use it?
Greg Reynolds
http://www.postgresql.org/about/
On Mon, 2008-06-23 at 13:56 -0400, Kynn Jones wrote:
>
>
>
>
>
>
>
> How can I find the limits (if any) on things such as the maximum
> number of tables, views, indices, columns-per-table, size of database,
> etc.?
>
>
> (At the moment I'm particularly int
On Jun 23, 2008, at 10:56 AM, Kynn Jones wrote:
How can I find the limits (if any) on things such as the maximum
number of tables, views, indices, columns-per-table, size of
database, etc.?
(At the moment I'm particularly interested any limits that my exist
on the numbers of tables
We are converting our system from using Sybase's SQL Anywhere 10 to PostGres
8.3. In SQL Anywhere there technically isn't any difference in how a char and
varchar is stored. They are all an array of char[1]. So we always just defined
everything as a char since right truncation is the default.
"Collin Peters" <[EMAIL PROTECTED]> writes:
> I have read the post and understand the issue. I am wondering why
> this is not mentioned in the documentation.
It is. Per
http://www.postgresql.org/docs/8.3/static/datatype-datetime.html#DATATYPE-TIMEZONES
"One should be wary that the POSIX-style
How can I find the limits (if any) on things such as the maximum number of
tables, views, indices, columns-per-table, size of database, etc.?
(At the moment I'm particularly interested any limits that my exist on the
numbers of tables and views that may exist in any one database.)
TIA!
Kynn
Regarding the community survey on the website:
http://www.postgresql.org/community/
It would be great to have a little more detail about each of the TODO's
- what it means, what's in scope, what's out of scope, etc.
For example, PITR looks like a popular option, but there is already some
PI
Collin Peters escribió:
> I have read the post and understand the issue. I am wondering why
> this is not mentioned in the documentation. Or even worse why the
> PostgreSQL documentation explicitly lists all the timezones correctly
> in table B-4
> http://www.postgresql.org/docs/8.1/static/datet
I have read the post and understand the issue. I am wondering why
this is not mentioned in the documentation. Or even worse why the
PostgreSQL documentation explicitly lists all the timezones correctly
in table B-4
http://www.postgresql.org/docs/8.1/static/datetime-keywords.html#DATETIME-TIMEZON
> >> 2) Is there any hostility about the notion of implementing this feature
> >> into Postgres?
> >
> > Probabably --- it seems like a narrow use case.
>
> I'll consider this to be the definite answer unless I hear a dissenting
> opinion in the next few days.
Yea, I might be wrong.
I th
Patrick TJ McPhee wrote:
In article <[EMAIL PROTECTED]>, Bill Thoen <[EMAIL PROTECTED]> wrote:
% I've got to load some large fixed-legnth ASCII records into PG and I was
% wondering how this is done. The Copy command looks like it works only
% with delimited files, and I would hate to have to c
On Fri, 2008-06-20 at 18:38 -0400, Bruce Momjian wrote:
> Laurent Birtz wrote:
> > > No. The closest thing we have is log_lock_waits in 8.3. I wonder if
> > > you could hack up something to monitor the server logs for such messages
> > > and cancel the queries.
> >
> > Assuming I can monitor th
In article <[EMAIL PROTECTED]>, Bill Thoen <[EMAIL PROTECTED]> wrote:
% I've got to load some large fixed-legnth ASCII records into PG and I was
% wondering how this is done. The Copy command looks like it works only
% with delimited files, and I would hate to have to convert these files to
% IN
50 matches
Mail list logo
