[GENERAL] Encodings
When restoring a dump from sql_ascii encoding to latin9/utf8 the "THIS STRING|" result to "THIS STRINGŠ" thus it aborted the restore. When I tried to dump a latin9/utf8 and restore it with the same encoding having same string it goes well but it took a very long time to copy all data. (1.6gb dump for 24/hrs) So far I don't know what seems to be the problem why it took so long to copy the dump if it is already in latin9/utf8 encoding to latin9/utf8 database.
[GENERAL] psql Copy question
Hi, I am using copy command in psql to load several million rows from a file Is it possible to have a progress indicator print a dot after every 100,000 rows are inserted ? Regards, Virag
Re: [GENERAL] PostgreSQL Functions / PL-Language
On Mon, Feb 20, 2006 at 02:36:04PM +0800, Jan Cruz wrote: > BTW I also got something like this: > > CREATE FUNCTION func2() RETURNS SETOF foo as $$ > DECLARE > row foo; > BEGIN > SELECT INTO ROW * from FOO; >return next foo; > END; > $$ LANGUAGE plpgsql STABLE; Please post the actual code instead of "something like" it. Trying to create the above function fails; I'm guessing you really have "return next row" instead of "return next foo". > select * from func2(); > > It did return the 2 rows (all rows) when I first test it. > Then today I tried the same function and test it then it return only 1 row. As written the function above should return only one row because it doesn't loop through the results. I suspect the difference between the earlier test and the most recent one is that you removed the loop. -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PostgreSQL Functions / PL-Language
On 2/19/06, Michael Fuhr <[EMAIL PROTECTED]> wrote: On Sat, Feb 18, 2006 at 04:48:55PM +0800, Jan Cruz wrote: CREATE TABLE foo (id integer, t text);INSERT INTO foo VALUES (1, 'one'); INSERT INTO foo VALUES (2, 'two'); Thanks for the correct syntaxing Mike. BTW I also got something like this: CREATE FUNCTION func2() RETURNS SETOF foo as $$ DECLARE row foo; BEGIN SELECT INTO ROW * from FOO; return next foo; END; $$ LANGUAGE plpgsql STABLE; select * from func2(); It did return the 2 rows (all rows) when I first test it. Then today I tried the same function and test it then it return only 1 row. I wonder.. BTW, I'm using PostgreSQL 8.1.3.
[GENERAL] simple explain output.
Hi all, I'm pretty sure the answer to this is "not possible" but I'll ask just in case. Is there a way to simplify the output that comes from explain? I'm writing a script to check my database logs to see whether I am missing any indexes. Basically the process goes like this: - Find a select query in the logs - Run it through the database using 'explain' - Check whether it's using an index scan or "other" Yes, I know not every query is going to use an index, but this will cut down on the queries I have to check. At the moment the explain output can get complicated to parse and process, so I'm trying to think of another approach to use. Any suggestions are welcome :) Chris. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Domains
On Feb 18, 2006, at 20:46 , Harald Armin Massa wrote: I do not know about the word "domains" in this usage. http://www.postgresql.org/docs/8.1/interactive/sql-createdomain.html Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Backslashes in data in version 8.1.2
> Could this be a locale issue? The one where it does not work uses the C > locale, the others use the default locale, en_US.UTF-8. Nope, it's not a locale issue, it works on the test system using the C locale as well as the default locale. I though I had the backslash issue under control in my PHP app, whatever changed is apparently affecting both Postgres and PHP. Any ideas? Some kind of library issue maybe, such as readline? -- Mike Nolan ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Backslashes in data in version 8.1.2
> This has not changed from prior versions. It looks like you are > neglecting to allow for the fact that backslash is an escape character > both at the string-literal level and at the regex-pattern level. > Therefore you must write twice as many backslashes as you normally > would write in a regex pattern. In particular, '' to match a > literal backslash. Something must have changed, Tom, because neither of the following work on the system where I now have 8.1.2 but do work on another system running 7.4.5, and in both 8.0.2 and 8.1.2 on a third system: select * from backtest where field ~ ''; field --- (0 rows) select * from backtest where field like '%%'; field --- (0 rows) Could this be a locale issue? The one where it does not work uses the C locale, the others use the default locale, en_US.UTF-8. -- Mike Nolan ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Backslashes in data in version 8.1.2
Mike Nolan <[EMAIL PROTECTED]> writes: > When I moved up to 8.1.2 one of my PHP programs appears to be broken, > I am getting backslashes in my data that I don't want. > Investigating further, I have found some inconsistencies in how verion > 8.1.2 handles data with backslashes in it: This has not changed from prior versions. It looks like you are neglecting to allow for the fact that backslash is an escape character both at the string-literal level and at the regex-pattern level. Therefore you must write twice as many backslashes as you normally would write in a regex pattern. In particular, '' to match a literal backslash. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] restoring under a different owner?
"Christopher J. Bottaro" <[EMAIL PROTECTED]> writes: > I want to use pg_dump to backup an entire Postgres database, including BLOB > data. Then I want to restore it on a different machine with a different db > owner and all the tables restored under that owner. I want the restore to > create the db and all the tables. Something involving --no-owner should work for you. See the pg_dump man page. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] restoring under a different owner?
On Feb 19, 2006, at 3:25 PM, Christopher J. Bottaro wrote: I want to use pg_dump to backup an entire Postgres database, including BLOB data. Then I want to restore it on a different machine with a different db owner and all the tables restored under that owner. I want the restore to create the db and all the tables. What are the commands to do this? See the --no-owner option with pg_restore (also available with pg_dump). http://www.postgresql.org/docs/8.0/interactive/app-pgrestore.html John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] restoring under a different owner?
Hi, I want to use pg_dump to backup an entire Postgres database, including BLOB data. Then I want to restore it on a different machine with a different db owner and all the tables restored under that owner. I want the restore to create the db and all the tables. What are the commands to do this? Thank you. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Backslashes in data in version 8.1.2
When I moved up to 8.1.2 one of my PHP programs appears to be broken,
I am getting backslashes in my data that I don't want.
Investigating further, I have found some inconsistencies in how verion
8.1.2 handles data with backslashes in it:
uscf=> \d backtest;
Table "mikenolan.backtest"
Column | Type | Modifiers
+--+---
field | text |
uscf=> insert into backtest values ('ABCDEFG');
insert into backtest values ('ABCDEFG');
INSERT 417194901 1
uscf=> insert into backtest values (E'ABC\\DEFG');
insert into backtest values (E'ABC\\DEFG');
INSERT 417194902 1
uscf=> select * from backtest;
select * from backtest;
field
--
ABCDEFG
ABC\DEFG
(2 rows)
uscf=> select * from backtest where field like E'%\\%';
select * from backtest where field like E'%\\%';
field
---
(0 rows)
select * from backtest where field like E'%\\134%'
field
---
(0 rows)
uscf=> select * from backtest where field ~ E'\\';
select * from backtest where field ~ E'\\';
ERROR: invalid regular expression: invalid escape \ sequence
uscf=> select * from backtest where field ~ E'\\134';
select * from backtest where field ~ E'\\134';
field
--
ABC\DEFG
(1 row)
So far the only way I have found to change data with backslashes in it
is something like the following:
update backtest
set field = replace(field,'\\','')
where
field ~ E'\\134';
UPDATE 1
uscf=> select * from backtest;
select * from backtest;
field
-
ABCDEFG
ABCDEFG
(2 rows)
--
Mike Nolan
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] pgplsql and notifications
=?ISO-8859-1?Q?Ken=E9z_Attila?= <[EMAIL PROTECTED]> writes: > I would like to implement a function in plpgsql (or sql if it is possible) > that can say me if I had some notification of some listened table. Notifications are delivered to the client (and then promptly forgotten by the backend). There is no way to track accumulated notifications except on the client side. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Domains
Hi,
Thanks for the suggestion. However I just wanted to give a brief
description of something I want to achieve. I believe such feature will
be very useful in more complicated environments.
Kind regards,
Peter
Michael Glaesemann wrote:
On Feb 19, 2006, at 2:12 , Stephan Szabo wrote:
On Sat, 18 Feb 2006, Peter wrote:
Hello,
I am migrating to postgresql from another database. I want to take
advantage of using domains. Let's suppose I create domain
'email'(varchar 128). Then I change my mind and want to increase all
columnst that have type 'emaill' to varchar(255). How do I change the
domain 'email' to the new datatype.
As Stephan pointed out, I don't believe there's a general way to do
this. However, if something you're looking to use domains for is to
check length of text, you may want to implement this as a check
constraint on the domain. This check constraint can then be altered in
the future using alter domain. For example:
test=# create domain email as text
constraint assert_maximum_length check (length(value) <= 128);
CREATE DOMAIN
test=# create table accounts
(
account_id serial primary key
, email email not null unique
);
NOTICE: CREATE TABLE will create implicit sequence
"accounts_account_id_seq" for serial column "accounts.account_id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"accounts_pkey" for table "accounts"
NOTICE: CREATE TABLE / UNIQUE will create implicit index
"accounts_email_key" for table "accounts"
CREATE TABLE
test=# insert into accounts (email) values ('this is a very very very
very very very very very very very very very very very very very very
long text string that is not actually a valid email address but will
serve for this example that is just checking for length');
ERROR: value for domain email violates check constraint
"assert_maximum_length"
test=# insert into accounts (email) values ('this is a very very very
very very very very very very very very very very very very very very
long text string');
INSERT 0 1
test=# alter domain email drop constraint assert_maximum_length;
ALTER DOMAIN
test=# alter domain email add constraint assert_maximum_length check
(length(value) <= 256);
ALTER DOMAIN
test=# insert into accounts (email) values ('this is a very very very
very very very very very very very very very very very very very very
long text string that is not actually a valid email address but will
serve for this example that is just checking for length');
INSERT 0 1
This more flexible technique can be used for more general situations
too, such as checking format with a regex match.
Michael Glaesemann
grzm myrealbox com
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] Mixing different LC_COLLATE and database encodings
On Sat, Feb 18, 2006 at 08:16:07PM -0800, Bill Moseley wrote: > Is the Holy Grail encoding and lc_collate settings per column? Well yes. I've been trying to create a system where you can handle multiple collations in the same database. I posted the details to -hackers and got part of the way, but it's a lot of work. As for encodings, to be honest, I'm not sure whether it's a great idea to support multiple encodings simultaneously. Things become a lot easier if you know everything is the same encoding. If you set the client_encoding automatically on startup it has pretty much the same effect as having the server always use that encoding. It's just a bit of time wasted in conversion, but the client doesn't need to care. By way of example, see ICU which is an internationalisation library we're considering to get consistant locale support over all platforms. It supports one encoding, namely UTF-16. It has various functions to convert other encodings to or from that, but internally it's all UTF-16. So if we do use that, then all encodings (except native UTF-16) will need to conversion all the time, so you don't buy anything by having the server in some random encoding. The problem ofcourse being that the SQL standard requires some encoding support. No-one has really come up with a proposal for that yet. IMHO, that's a parser issue more than anything else. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. signature.asc Description: Digital signature
[GENERAL] PostgreSQL New RPM Sets for FC/RH
- PostgreSQL New RPM Sets 2006-02-19 Versions: 8.1.3, 8.0.7, 7.4.12, 7.3.14 Set labels: 8.1.3-1PGDG, 8.0.7-1PGDG, 7.4.12-1PGDG, 7.3.14-1PGDG - - Release Info: PostgreSQL RPM Building Project has released RPMs for 7.3.14, 7.4.11, 8.0.7 and 8.1.3 and they are available in main FTP site and its mirrors. We currently have RPMs for: - Fedora Core 2 - Fedora Core 2-x86_64 - Fedora Core 3 - Fedora Core 4 - Fedora Core 4-x86_64 - Red Hat Enterprise Linux Enterprise Server 3.0 - Red Hat Enterprise Linux Enterprise Server 3.0-x86_64 - Red Hat Enterprise Linux Enterprise Server 4 - Red Hat Enterprise Linux Enterprise Server 4-x86_64 - Red Hat Enterprise Linux Advanced Server 4 - Red Hat Enterprise Linux Advanced Server 4-x86_64 More may (will) come later. I want to thank every package builder for this great number of supported platforms. Support for Red Hat 9, RHEL 2.1 and Fedora Core 1 may be completely abandoned in future releases. Please let us know if you can assist us in building RPMS of missing Red Hat / Fedora Core platforms. For complete list of changes in RPM sets, please refer to the changelogs in the RPMs. Use rpm -q -changelog package_name for querying the changelog. Point releases generally do not require a dump/reload from the previous Point, but please see the Release Notes to confirm procedures for upgrading, especially if your current version is older than the last point release. For RPMs 8.1.3, we again included the PDF documentation into the -docs RPMs. The SRPMs are also provided. Please note that we have one SRPM for all platforms. We also have a howto document about RPM installation of PostgreSQL: http://pgfoundry.org/docman/?group_id=148 Please follow the instructions before installing/upgrading. Almost each RPM has been signed by the builder, and each directory contains CURRENT_MAINTAINER file which includes the name/email of the package builder and link to their PGP key. If you experience problems with the RPMs or if you have feature requests, please join pgsqlrpms-general ( at ) pgfoundry ( dot ) org More info about the list is found at: http://lists.pgfoundry.org/mailman/listinfo/pgsqlrpms-general The project page is: http://pgfoundry.org/projects/pgsqlrpms Please do not use these resources for issue running or using PostgreSQL once it is installed. Please download these files from: http://www.postgresql.org/ftp/binary/v8.1.3/linux/ http://www.postgresql.org/ftp/binary/v8.0.7/linux/ http://www.postgresql.org/ftp/binary/v7.4.12/ http://www.postgresql.org/ftp/binary/v7.3.14/ or from Bittorrent (Thanks to David Fetter and Magnus Hagander) : http://www.postgresql.org/download/bittorrent Regards, -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: PL/php, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] pgplsql and notifications
Hi all, I would like to implement a function in plpgsql (or sql if it is possible) that can say me if I had some notification of some listened table. I mean something like this: create procedure do_i_have_notifications(text) returns boolean as ' ... ' language (plpg)sql; (or: create procedure received_notifications() returns setof text as ' ... ' langugae (plpg)sql; ) Is there a way to do this? Thanks, Attila Kenéz ---(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
Re: [GENERAL] Same data, different results in Postgres vs. FrontBase
On Sat, 18 Feb 2006, Brendan Duddridge wrote:
> Hi,
>
> I have a query that returns 569 rows in FrontBase, but only 30 rows
> in Postgres. The data is the same as I just finished copying my
> entire database over from FrontBase to Postgres.
>
> I've reduced my problem to the following statement and have
> discovered that FrontBase returns null rows along with the rows that
> match the query and PostgreSQL only returns the not null rows.
>
> CON.IS_SUBSCRIBED NOT IN ('X', 'P')
>
> Is that normal?
Short form from the spec as we read it:
RVC NOT IN (IPV) => NOT (RVC IN (IPV)) => NOT (RVC =ANY IPV)
The result of RVC =ANY IPV can be described with:
If the implied comparison predicate [ RVC = IPVi] is true for at least
one row IPVi in IPV then true
If the implied comparison predicate is false for every row IPVi in IPV
then false
Otherwise unknown.
NULL = 'X' returns unknown, as does NULL = 'P', so the last case is the
one that should apply. NOT (unknown) is unknown, so the result of
CON.IS_SUBSCRIBED NOT IN ('X', 'P') is unknown for NULL IS_SUBSCRIBED.
Where clauses pass rows where the result of the clause is true, so those
rows are not part of the result.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
