Re: [GENERAL] How to find freak UTF-8 character?
On Saturday 1. October 2011 07.55.01 Leif Biberg Kristensen wrote: > I've somehow introduced a spurious UTF-8 character in my database. When I > try to export to an application that requires LATIN1 encoding, my export > script bombs out with this message: > > psycopg2.DataError: character 0xe2808e of encoding "UTF8" has no equivalent > in "LATIN1" I finally figured it out, with a little help from maatb's unicode database (http://vazor.com/unicode/c200E.html): SELECT * FROM foo WHERE bar LIKE E'%\xe2\x80\x8e%'; regards, Leif -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to find freak UTF-8 character?
I've somehow introduced a spurious UTF-8 character in my database. When I try to export to an application that requires LATIN1 encoding, my export script bombs out with this message: psycopg2.DataError: character 0xe2808e of encoding "UTF8" has no equivalent in "LATIN1" I figure that it should be easy to find the offending character in the database by doing a SELECT * FROM foo WHERE bar like '%\0xe2808e%' or something like that, but I can't find the correct syntax, I can't find a relevant section in the manual, and I can't figure out how to google this problem. regards, Leif -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] stored procs
On 09/30/2011 11:41 PM, John R Pierce wrote: On 09/30/11 2:09 AM, J.V. wrote: Some tables have millions of rows, well, something like UPDATE tablename SET id=generate_series(1,numberofrows); will update every row to a sequential value. However, I have no idea how you would match the foreign key references in other tables to these new sequence values. There are two ways to do that. You can add a *new* column for the new keys and generate them. Then you add a matching empty column to each referencing table and fill it using a JOIN against the old key and ALTER each referencing table to add the FOREIGN KEY before dropping the old key column. Finally, you drop the old key column in the main table. Alternately, you can ALTER all the foreign key references to be CASCADE, then UPDATE the main table to set new keys. PostgreSQL will cascade the changes to the referencing tables. The second method is simpler and you might think it'd be faster, but it probably won't be. The first method requires one sequential re-write of each table when the UPDATE to fill the new key columns runs, but is otherwise just a series of JOINs on key columns. On the other hand, the second method requires *lots* of *random* writes all over the place on the referencing tables, and is likely to be a lot slower even if you have indexes on your foreign key columns. If you *don't* have indexes on your foreign key columns the second method is going to be spectacularly, amazingly, stunningly slow. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Connection refused (0x0000274D/10061)
On 09/30/2011 06:17 PM, Pietro Laranci wrote: On the machines are not active firewalls. Most likely there _is_ a firewall involved, maybe just not one you're aware of. As Adrian said, check the settings in the standard Windows Firewall if you're on Windows. For more troubleshooting: Check netstat to see if PostgreSQL is actually listening on the port you've configured. Use Wireshark to capture incoming tcp/ip traffic and see if (a) SYN packets to open the connection actually reach the server, and (b) whether the server actually replies with an RST to reject the connection or if something else happens. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Change server encoding after the fact
On Fri, Sep 30, 2011 at 2:57 PM, Cody Caughlan wrote: > Please see below. > > On Fri, Sep 30, 2011 at 1:12 PM, Scott Marlowe > wrote: >> >> On Fri, Sep 30, 2011 at 1:45 PM, Cody Caughlan wrote: >> > That worked, but "file" shows no difference: >> > $ iconv -f utf-8 -t utf-8 -c foo.sql > utf.sql >> > $ file -i foo.sql >> > foo.sql: text/plain; charset=us-ascii >> > $file -i utf.sql >> > utf.sql: text/plain; charset=us-ascii >> > So iconv didnt actually convert the file OR does is the "file" command >> > just >> > ignorant? >> >> Not sure. try loading the dump into the UTF-8 DB in postgres and see >> what happens I guess? > > > Uh oh. > On the remote machine: > $ pg_dump -Fc -E UTF8 foo > foo.sql > Then I've created a new local DB with UTF8 encoding and I try to restore > this dump into it: > pg_restore: [archiver (db)] Error while PROCESSING TOC: > pg_restore: [archiver (db)] Error from TOC entry 2342; 0 17086 TABLE DATA > wine_books vinosmith > pg_restore: [archiver (db)] COPY failed for table "wine_books": ERROR: > invalid byte sequence for encoding "UTF8": 0xc309 > CONTEXT: COPY wine_books, line 1147 > WARNING: errors ignored on restore: 1 > And sure enough the table "wine_books" is empty. Not good. You may have to hunt down that one bad line (1147) and chop it out / edit it so it works. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Change server encoding after the fact
Please see below. On Fri, Sep 30, 2011 at 1:12 PM, Scott Marlowe wrote: > On Fri, Sep 30, 2011 at 1:45 PM, Cody Caughlan wrote: > > That worked, but "file" shows no difference: > > $ iconv -f utf-8 -t utf-8 -c foo.sql > utf.sql > > $ file -i foo.sql > > foo.sql: text/plain; charset=us-ascii > > $file -i utf.sql > > utf.sql: text/plain; charset=us-ascii > > So iconv didnt actually convert the file OR does is the "file" command > just > > ignorant? > > Not sure. try loading the dump into the UTF-8 DB in postgres and see > what happens I guess? > Uh oh. On the remote machine: $ pg_dump -Fc -E UTF8 foo > foo.sql Then I've created a new local DB with UTF8 encoding and I try to restore this dump into it: pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 2342; 0 17086 TABLE DATA wine_books vinosmith pg_restore: [archiver (db)] COPY failed for table "wine_books": ERROR: invalid byte sequence for encoding "UTF8": 0xc309 CONTEXT: COPY wine_books, line 1147 WARNING: errors ignored on restore: 1 And sure enough the table "wine_books" is empty. Not good.
Re: [GENERAL] Change server encoding after the fact
On Fri, Sep 30, 2011 at 1:45 PM, Cody Caughlan wrote: > That worked, but "file" shows no difference: > $ iconv -f utf-8 -t utf-8 -c foo.sql > utf.sql > $ file -i foo.sql > foo.sql: text/plain; charset=us-ascii > $file -i utf.sql > utf.sql: text/plain; charset=us-ascii > So iconv didnt actually convert the file OR does is the "file" command just > ignorant? Not sure. try loading the dump into the UTF-8 DB in postgres and see what happens I guess? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgres for OLAP & data mining
On 30 September 2011 18:41, Henry Drexler wrote: > Are you looking for stuff like this? > > http://www.postgresql.org/docs/9.0/static/functions-window.html > http://www.postgresql.org/docs/9.0/static/functions-string.html > > Thanks for reply. Yes, I knew these functions. In fact, I was rather wondering whether there are dedicated packages for OLAP and some guidelines for configuring postgres (and whether postgres is a good option for OLAP). Best Dario > On Fri, Sep 30, 2011 at 10:12 AM, Dario Beraldi > wrote: > >> Hello, >> >> I'm looking for some information (guidelines, docs, tutorials, etc...) >> about using postgres for OLAP, data mining, data warehousing. The sort of >> questions I'm trying to answer are on the lines of >> - What tools/SQL commands are available? >> - How should postgres be configured? >> - How suitable is postgres for these tasks (compared to other databases)? >> >> I have done some Google search but I would appreciate any advice/hint from >> more experienced users. >> >> Many thanks in advance! >> >> Dario > > >
Re: [GENERAL] Change server encoding after the fact
On Fri, Sep 30, 2011 at 1:41 PM, Scott Marlowe wrote: > On Fri, Sep 30, 2011 at 1:39 PM, Cody Caughlan wrote: >> Its a Rails app and I do have: >> encoding: utf8 > > > Hmmm, if you try this does it work (mostly)? > > iconv -f utf-8 -t utf-8 -c < infile > outfile If that doesn't work try: iconv -f utf-8 -t utf-8//IGNORE -c < infile > outfile -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Change server encoding after the fact
That worked, but "file" shows no difference: $ iconv -f utf-8 -t utf-8 -c foo.sql > utf.sql $ file -i foo.sql foo.sql: text/plain; charset=us-ascii $file -i utf.sql utf.sql: text/plain; charset=us-ascii So iconv didnt actually convert the file OR does is the "file" command just ignorant? On Fri, Sep 30, 2011 at 12:41 PM, Scott Marlowe wrote: > On Fri, Sep 30, 2011 at 1:39 PM, Cody Caughlan wrote: > > Its a Rails app and I do have: > > encoding: utf8 > > > Hmmm, if you try this does it work (mostly)? > > iconv -f utf-8 -t utf-8 -c < infile > outfile >
Re: [GENERAL] Change server encoding after the fact
On Fri, Sep 30, 2011 at 1:39 PM, Cody Caughlan wrote: > Its a Rails app and I do have: > encoding: utf8 Hmmm, if you try this does it work (mostly)? iconv -f utf-8 -t utf-8 -c < infile > outfile -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Change server encoding after the fact
Its a Rails app and I do have: encoding: utf8 Set in my DB configuration. On Fri, Sep 30, 2011 at 12:38 PM, Scott Marlowe wrote: > On Fri, Sep 30, 2011 at 1:35 PM, Cody Caughlan wrote: > > > > > > On Fri, Sep 30, 2011 at 12:31 PM, Scott Marlowe > > > wrote: > >> > >> On Fri, Sep 30, 2011 at 12:46 PM, Cody Caughlan > wrote: > >> > Please see below. > >> > > >> > On Fri, Sep 30, 2011 at 11:38 AM, Scott Marlowe > >> > > >> > wrote: > >> >> > >> >> On Fri, Sep 30, 2011 at 12:26 PM, Cody Caughlan > >> >> wrote: > >> >> > Thanks Scott. See below: > >> >> > > >> >> > On Fri, Sep 30, 2011 at 11:05 AM, Scott Marlowe > >> >> > > >> >> > wrote: > >> >> >> > >> >> >> On Fri, Sep 30, 2011 at 11:25 AM, Cody Caughlan < > tool...@gmail.com> > >> >> >> wrote: > >> >> >> > I would like to change my server_encoding which is currently > >> >> >> > SQL_ASCII > >> >> >> > to UTF8. > >> >> >> > > >> >> >> > I have existing data that I would like to keep. > >> >> >> > > >> >> >> > From my understanding of the steps I need to: > >> >> >> > > >> >> >> > 1) alter the template1 database encoding via > >> >> >> > > >> >> >> > UPDATE pg_database SET encoding = 6 where datname IN > ('template0', > >> >> >> > 'template1'); > >> >> >> > >> >> >> Just create database using template0 as template and you can skip > >> >> >> this > >> >> >> step ^^ > >> >> > > >> >> > > >> >> > Wouldn't this only work if my template0 was UTF8 itself? > >> >> > => select datname, pg_encoding_to_char(encoding) from pg_database; > >> >> >datname| pg_encoding_to_char > >> >> > --+- > >> >> > template1| SQL_ASCII > >> >> > template0| SQL_ASCII > >> >> > postgres | SQL_ASCII > >> >> > > >> >> > So it appears both template0 & template1 are SQL_ASCII, so how > would > >> >> > creating from a new DB from template0 be any different than > >> >> > template1? > >> >> > >> >> Well, let's try, shall we? From a freshly created cluster on my > >> >> laptop, running 8.4: > >> >> > >> >> smarlowe=# select datname, pg_encoding_to_char(encoding) from > >> >> pg_database; > >> >> datname | pg_encoding_to_char > >> >> ---+- > >> >> template1 | SQL_ASCII > >> >> template0 | SQL_ASCII > >> >> postgres | SQL_ASCII > >> >> smarlowe | SQL_ASCII > >> >> (4 rows) > >> >> > >> >> smarlowe=# create database j template template0 encoding 'UTF8'; > >> >> CREATE DATABASE > >> >> > >> >> Seems to work. > >> >> > >> >> P.s. I'm not sure why it works, I just know that it does. :) > >> >> > >> > > >> > Ok, I see what you mean. This would create a new DB with the proper > >> > encoding. Which is "fine", and probably what I will do. I guess I see > an > >> > ideal scenario being one where we permanently convert the template > >> > encoding > >> > to UTF8 so going forward I dont have to worry about forgetting to > adding > >> > the > >> > encoding= 'UTF8' for every new DB I create. > >> > >> Ah ok. The way I fix that is this: > >> > >> update pg_database set datistemplate = false where datname='template1'; > >> drop database template1; > >> create database template1 template template0 encoding 'UTF8'; > >> > >> But your way would likely work too. > >> > >> >> I think you got it backwards, the -f should be somthing other than > >> >> utf-8 right? That's what the -t should be right? Try iconv without > a > >> >> -f switch and a -t of utf-8 and see what happens... > >> > > >> > You're right, I had -f when I needed -t. I tried it again with the > same > >> > error: > >> > $ iconv -t utf-8 foo.sql > utf.sql > >> > iconv: illegal input sequence at position 2512661 > >> > >> Any idea waht the actual encoding of your source database is? > >> SQL_ASCII is basically not really ascii, more like anything goes. > > > > > > How would I find this? pg_database says my DB is SQL_ASCII. > > "show all" says > > client_encoding = SQL_ASCII > > server_encoding = SQL_ASCII > > It would have been set by the application accessing postgresql and > inserting the data. I.e. was it a windows app using a typical windows > encoding? etc. >
Re: [GENERAL] Change server encoding after the fact
On Fri, Sep 30, 2011 at 1:35 PM, Cody Caughlan wrote: > > > On Fri, Sep 30, 2011 at 12:31 PM, Scott Marlowe > wrote: >> >> On Fri, Sep 30, 2011 at 12:46 PM, Cody Caughlan wrote: >> > Please see below. >> > >> > On Fri, Sep 30, 2011 at 11:38 AM, Scott Marlowe >> > >> > wrote: >> >> >> >> On Fri, Sep 30, 2011 at 12:26 PM, Cody Caughlan >> >> wrote: >> >> > Thanks Scott. See below: >> >> > >> >> > On Fri, Sep 30, 2011 at 11:05 AM, Scott Marlowe >> >> > >> >> > wrote: >> >> >> >> >> >> On Fri, Sep 30, 2011 at 11:25 AM, Cody Caughlan >> >> >> wrote: >> >> >> > I would like to change my server_encoding which is currently >> >> >> > SQL_ASCII >> >> >> > to UTF8. >> >> >> > >> >> >> > I have existing data that I would like to keep. >> >> >> > >> >> >> > From my understanding of the steps I need to: >> >> >> > >> >> >> > 1) alter the template1 database encoding via >> >> >> > >> >> >> > UPDATE pg_database SET encoding = 6 where datname IN ('template0', >> >> >> > 'template1'); >> >> >> >> >> >> Just create database using template0 as template and you can skip >> >> >> this >> >> >> step ^^ >> >> > >> >> > >> >> > Wouldn't this only work if my template0 was UTF8 itself? >> >> > => select datname, pg_encoding_to_char(encoding) from pg_database; >> >> > datname | pg_encoding_to_char >> >> > --+- >> >> > template1 | SQL_ASCII >> >> > template0 | SQL_ASCII >> >> > postgres | SQL_ASCII >> >> > >> >> > So it appears both template0 & template1 are SQL_ASCII, so how would >> >> > creating from a new DB from template0 be any different than >> >> > template1? >> >> >> >> Well, let's try, shall we? From a freshly created cluster on my >> >> laptop, running 8.4: >> >> >> >> smarlowe=# select datname, pg_encoding_to_char(encoding) from >> >> pg_database; >> >> datname | pg_encoding_to_char >> >> ---+- >> >> template1 | SQL_ASCII >> >> template0 | SQL_ASCII >> >> postgres | SQL_ASCII >> >> smarlowe | SQL_ASCII >> >> (4 rows) >> >> >> >> smarlowe=# create database j template template0 encoding 'UTF8'; >> >> CREATE DATABASE >> >> >> >> Seems to work. >> >> >> >> P.s. I'm not sure why it works, I just know that it does. :) >> >> >> > >> > Ok, I see what you mean. This would create a new DB with the proper >> > encoding. Which is "fine", and probably what I will do. I guess I see an >> > ideal scenario being one where we permanently convert the template >> > encoding >> > to UTF8 so going forward I dont have to worry about forgetting to adding >> > the >> > encoding= 'UTF8' for every new DB I create. >> >> Ah ok. The way I fix that is this: >> >> update pg_database set datistemplate = false where datname='template1'; >> drop database template1; >> create database template1 template template0 encoding 'UTF8'; >> >> But your way would likely work too. >> >> >> I think you got it backwards, the -f should be somthing other than >> >> utf-8 right? That's what the -t should be right? Try iconv without a >> >> -f switch and a -t of utf-8 and see what happens... >> > >> > You're right, I had -f when I needed -t. I tried it again with the same >> > error: >> > $ iconv -t utf-8 foo.sql > utf.sql >> > iconv: illegal input sequence at position 2512661 >> >> Any idea waht the actual encoding of your source database is? >> SQL_ASCII is basically not really ascii, more like anything goes. > > > How would I find this? pg_database says my DB is SQL_ASCII. > "show all" says > client_encoding = SQL_ASCII > server_encoding = SQL_ASCII It would have been set by the application accessing postgresql and inserting the data. I.e. was it a windows app using a typical windows encoding? etc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Change server encoding after the fact
On Fri, Sep 30, 2011 at 12:31 PM, Scott Marlowe wrote: > On Fri, Sep 30, 2011 at 12:46 PM, Cody Caughlan wrote: > > Please see below. > > > > On Fri, Sep 30, 2011 at 11:38 AM, Scott Marlowe > > > wrote: > >> > >> On Fri, Sep 30, 2011 at 12:26 PM, Cody Caughlan > wrote: > >> > Thanks Scott. See below: > >> > > >> > On Fri, Sep 30, 2011 at 11:05 AM, Scott Marlowe > >> > > >> > wrote: > >> >> > >> >> On Fri, Sep 30, 2011 at 11:25 AM, Cody Caughlan > >> >> wrote: > >> >> > I would like to change my server_encoding which is currently > >> >> > SQL_ASCII > >> >> > to UTF8. > >> >> > > >> >> > I have existing data that I would like to keep. > >> >> > > >> >> > From my understanding of the steps I need to: > >> >> > > >> >> > 1) alter the template1 database encoding via > >> >> > > >> >> > UPDATE pg_database SET encoding = 6 where datname IN ('template0', > >> >> > 'template1'); > >> >> > >> >> Just create database using template0 as template and you can skip > this > >> >> step ^^ > >> > > >> > > >> > Wouldn't this only work if my template0 was UTF8 itself? > >> > => select datname, pg_encoding_to_char(encoding) from pg_database; > >> >datname| pg_encoding_to_char > >> > --+- > >> > template1| SQL_ASCII > >> > template0| SQL_ASCII > >> > postgres | SQL_ASCII > >> > > >> > So it appears both template0 & template1 are SQL_ASCII, so how would > >> > creating from a new DB from template0 be any different than template1? > >> > >> Well, let's try, shall we? From a freshly created cluster on my > >> laptop, running 8.4: > >> > >> smarlowe=# select datname, pg_encoding_to_char(encoding) from > pg_database; > >> datname | pg_encoding_to_char > >> ---+- > >> template1 | SQL_ASCII > >> template0 | SQL_ASCII > >> postgres | SQL_ASCII > >> smarlowe | SQL_ASCII > >> (4 rows) > >> > >> smarlowe=# create database j template template0 encoding 'UTF8'; > >> CREATE DATABASE > >> > >> Seems to work. > >> > >> P.s. I'm not sure why it works, I just know that it does. :) > >> > > > > Ok, I see what you mean. This would create a new DB with the proper > > encoding. Which is "fine", and probably what I will do. I guess I see an > > ideal scenario being one where we permanently convert the template > encoding > > to UTF8 so going forward I dont have to worry about forgetting to adding > the > > encoding= 'UTF8' for every new DB I create. > > Ah ok. The way I fix that is this: > > update pg_database set datistemplate = false where datname='template1'; > drop database template1; > create database template1 template template0 encoding 'UTF8'; > > But your way would likely work too. > > >> I think you got it backwards, the -f should be somthing other than > >> utf-8 right? That's what the -t should be right? Try iconv without a > >> -f switch and a -t of utf-8 and see what happens... > > > > You're right, I had -f when I needed -t. I tried it again with the same > > error: > > $ iconv -t utf-8 foo.sql > utf.sql > > iconv: illegal input sequence at position 2512661 > > Any idea waht the actual encoding of your source database is? > SQL_ASCII is basically not really ascii, more like anything goes. > How would I find this? pg_database says my DB is SQL_ASCII. "show all" says client_encoding = SQL_ASCII server_encoding = SQL_ASCII
Re: [GENERAL] Change server encoding after the fact
Cody Caughlan writes: > Ok, I see what you mean. This would create a new DB with the proper > encoding. Which is "fine", and probably what I will do. I guess I see an > ideal scenario being one where we permanently convert the template encoding > to UTF8 so going forward I dont have to worry about forgetting to adding the > encoding= 'UTF8' for every new DB I create. Well, if you're feeling brave you can mark template0 as having utf8 encoding via a manual update to pg_database. In theory that should be safe enough. If you know template1 doesn't, and never has, contained any non-ASCII data, you could do the same to it ... but it would be a lot safer to drop it and recreate from template0. See http://wiki.postgresql.org/wiki/Adventures_in_PostgreSQL,_Episode_1 for some context. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Change server encoding after the fact
On Fri, Sep 30, 2011 at 12:46 PM, Cody Caughlan wrote: > Please see below. > > On Fri, Sep 30, 2011 at 11:38 AM, Scott Marlowe > wrote: >> >> On Fri, Sep 30, 2011 at 12:26 PM, Cody Caughlan wrote: >> > Thanks Scott. See below: >> > >> > On Fri, Sep 30, 2011 at 11:05 AM, Scott Marlowe >> > >> > wrote: >> >> >> >> On Fri, Sep 30, 2011 at 11:25 AM, Cody Caughlan >> >> wrote: >> >> > I would like to change my server_encoding which is currently >> >> > SQL_ASCII >> >> > to UTF8. >> >> > >> >> > I have existing data that I would like to keep. >> >> > >> >> > From my understanding of the steps I need to: >> >> > >> >> > 1) alter the template1 database encoding via >> >> > >> >> > UPDATE pg_database SET encoding = 6 where datname IN ('template0', >> >> > 'template1'); >> >> >> >> Just create database using template0 as template and you can skip this >> >> step ^^ >> > >> > >> > Wouldn't this only work if my template0 was UTF8 itself? >> > => select datname, pg_encoding_to_char(encoding) from pg_database; >> > datname | pg_encoding_to_char >> > --+- >> > template1 | SQL_ASCII >> > template0 | SQL_ASCII >> > postgres | SQL_ASCII >> > >> > So it appears both template0 & template1 are SQL_ASCII, so how would >> > creating from a new DB from template0 be any different than template1? >> >> Well, let's try, shall we? From a freshly created cluster on my >> laptop, running 8.4: >> >> smarlowe=# select datname, pg_encoding_to_char(encoding) from pg_database; >> datname | pg_encoding_to_char >> ---+- >> template1 | SQL_ASCII >> template0 | SQL_ASCII >> postgres | SQL_ASCII >> smarlowe | SQL_ASCII >> (4 rows) >> >> smarlowe=# create database j template template0 encoding 'UTF8'; >> CREATE DATABASE >> >> Seems to work. >> >> P.s. I'm not sure why it works, I just know that it does. :) >> > > Ok, I see what you mean. This would create a new DB with the proper > encoding. Which is "fine", and probably what I will do. I guess I see an > ideal scenario being one where we permanently convert the template encoding > to UTF8 so going forward I dont have to worry about forgetting to adding the > encoding= 'UTF8' for every new DB I create. Ah ok. The way I fix that is this: update pg_database set datistemplate = false where datname='template1'; drop database template1; create database template1 template template0 encoding 'UTF8'; But your way would likely work too. >> I think you got it backwards, the -f should be somthing other than >> utf-8 right? That's what the -t should be right? Try iconv without a >> -f switch and a -t of utf-8 and see what happens... > > You're right, I had -f when I needed -t. I tried it again with the same > error: > $ iconv -t utf-8 foo.sql > utf.sql > iconv: illegal input sequence at position 2512661 Any idea waht the actual encoding of your source database is? SQL_ASCII is basically not really ascii, more like anything goes. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Change server encoding after the fact
Please see below. On Fri, Sep 30, 2011 at 11:38 AM, Scott Marlowe wrote: > On Fri, Sep 30, 2011 at 12:26 PM, Cody Caughlan wrote: > > Thanks Scott. See below: > > > > On Fri, Sep 30, 2011 at 11:05 AM, Scott Marlowe > > > wrote: > >> > >> On Fri, Sep 30, 2011 at 11:25 AM, Cody Caughlan > wrote: > >> > I would like to change my server_encoding which is currently SQL_ASCII > >> > to UTF8. > >> > > >> > I have existing data that I would like to keep. > >> > > >> > From my understanding of the steps I need to: > >> > > >> > 1) alter the template1 database encoding via > >> > > >> > UPDATE pg_database SET encoding = 6 where datname IN ('template0', > >> > 'template1'); > >> > >> Just create database using template0 as template and you can skip this > >> step ^^ > > > > > > Wouldn't this only work if my template0 was UTF8 itself? > > => select datname, pg_encoding_to_char(encoding) from pg_database; > >datname| pg_encoding_to_char > > --+- > > template1| SQL_ASCII > > template0| SQL_ASCII > > postgres | SQL_ASCII > > > > So it appears both template0 & template1 are SQL_ASCII, so how would > > creating from a new DB from template0 be any different than template1? > > Well, let's try, shall we? From a freshly created cluster on my > laptop, running 8.4: > > smarlowe=# select datname, pg_encoding_to_char(encoding) from pg_database; > datname | pg_encoding_to_char > ---+- > template1 | SQL_ASCII > template0 | SQL_ASCII > postgres | SQL_ASCII > smarlowe | SQL_ASCII > (4 rows) > > smarlowe=# create database j template template0 encoding 'UTF8'; > CREATE DATABASE > > Seems to work. > > P.s. I'm not sure why it works, I just know that it does. :) > > Ok, I see what you mean. This would create a new DB with the proper encoding. Which is "fine", and probably what I will do. I guess I see an ideal scenario being one where we permanently convert the template encoding to UTF8 so going forward I dont have to worry about forgetting to adding the encoding= 'UTF8' for every new DB I create. > >> > Are these the correct steps to perform or is there an easier / > in-place > >> > way? > >> > >> > Also, when I dump my old DB and restore it, will it be converted > >> > appropriately (e.g. it came from am SQL_ASCII encoding and its going > into a > >> > UTF-8 database)? > >> > >> You might need to set client encoding when restoring. Or use iconv to > >> convert from one encoding to another, which is what I usually do. > >> Note that it's VERY likely you'll have data in a SQL_ASCII db that > >> won't go into a UTF8 database without some lossiness. > > > > > > Yes, I see this might be the case. From my playing around with iconv I > > cannot even properly do the conversion: > > $ pg_dump -Fp foo > foo.sql > > $ file -i foo.sql > > foo.sql: text/plain; charset=us-ascii > > $ iconv -f utf-8 foo.sql > utf8.sql > > iconv: illegal input sequence at position 2512661 > > I think you got it backwards, the -f should be somthing other than > utf-8 right? That's what the -t should be right? Try iconv without a > -f switch and a -t of utf-8 and see what happens... > You're right, I had -f when I needed -t. I tried it again with the same error: $ iconv -t utf-8 foo.sql > utf.sql iconv: illegal input sequence at position 2512661
Re: [GENERAL] Change server encoding after the fact
Scott Marlowe writes: > On Fri, Sep 30, 2011 at 12:26 PM, Cody Caughlan wrote: >> So it appears both template0 & template1 are SQL_ASCII, so how would >> creating from a new DB from template0 be any different than template1? > P.s. I'm not sure why it works, I just know that it does. :) CREATE DATABASE assumes that template0 cannot contain any non-ASCII data, so it's okay to clone it and then pretend that the result has some other encoding. The same assumption cannot be made for template1, since that's user-modifiable. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Change server encoding after the fact
On Fri, Sep 30, 2011 at 12:26 PM, Cody Caughlan wrote: > Thanks Scott. See below: > > On Fri, Sep 30, 2011 at 11:05 AM, Scott Marlowe > wrote: >> >> On Fri, Sep 30, 2011 at 11:25 AM, Cody Caughlan wrote: >> > I would like to change my server_encoding which is currently SQL_ASCII >> > to UTF8. >> > >> > I have existing data that I would like to keep. >> > >> > From my understanding of the steps I need to: >> > >> > 1) alter the template1 database encoding via >> > >> > UPDATE pg_database SET encoding = 6 where datname IN ('template0', >> > 'template1'); >> >> Just create database using template0 as template and you can skip this >> step ^^ > > > Wouldn't this only work if my template0 was UTF8 itself? > => select datname, pg_encoding_to_char(encoding) from pg_database; > datname | pg_encoding_to_char > --+- > template1 | SQL_ASCII > template0 | SQL_ASCII > postgres | SQL_ASCII > > So it appears both template0 & template1 are SQL_ASCII, so how would > creating from a new DB from template0 be any different than template1? Well, let's try, shall we? From a freshly created cluster on my laptop, running 8.4: smarlowe=# select datname, pg_encoding_to_char(encoding) from pg_database; datname | pg_encoding_to_char ---+- template1 | SQL_ASCII template0 | SQL_ASCII postgres | SQL_ASCII smarlowe | SQL_ASCII (4 rows) smarlowe=# create database j template template0 encoding 'UTF8'; CREATE DATABASE Seems to work. P.s. I'm not sure why it works, I just know that it does. :) >> > Are these the correct steps to perform or is there an easier / in-place >> > way? >> >> > Also, when I dump my old DB and restore it, will it be converted >> > appropriately (e.g. it came from am SQL_ASCII encoding and its going into a >> > UTF-8 database)? >> >> You might need to set client encoding when restoring. Or use iconv to >> convert from one encoding to another, which is what I usually do. >> Note that it's VERY likely you'll have data in a SQL_ASCII db that >> won't go into a UTF8 database without some lossiness. > > > Yes, I see this might be the case. From my playing around with iconv I > cannot even properly do the conversion: > $ pg_dump -Fp foo > foo.sql > $ file -i foo.sql > foo.sql: text/plain; charset=us-ascii > $ iconv -f utf-8 foo.sql > utf8.sql > iconv: illegal input sequence at position 2512661 I think you got it backwards, the -f should be somthing other than utf-8 right? That's what the -t should be right? Try iconv without a -f switch and a -t of utf-8 and see what happens... -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgres for OLAP & data mining
At 16:12 30/09/2011, Dario Beraldi wrote: Hello, I'm looking for some information (guidelines, docs, tutorials, etc...) about using postgres for OLAP, data mining, data warehousing. The sort of questions I'm trying to answer are on the lines of - What tools/SQL commands are available? - How should postgres be configured? - How suitable is postgres for these tasks (compared to other databases)? I have done some Google search but I would appreciate any advice/hint from more experienced users. Check these tutorials: http://pgexperts.com/document.html?id=49 http://www.pgexperts.com/document.html?id=40 There are more at http://www.pgexperts.com/presentations.html YOu can check the presentations made on several pgcons and similar events http://www.pgcon.org/2011/schedule/index.en.html http://www.postgresopen.org/2011/home/ HTH -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Change server encoding after the fact
Thanks Scott. See below: On Fri, Sep 30, 2011 at 11:05 AM, Scott Marlowe wrote: > On Fri, Sep 30, 2011 at 11:25 AM, Cody Caughlan wrote: > > I would like to change my server_encoding which is currently SQL_ASCII to > UTF8. > > > > I have existing data that I would like to keep. > > > > From my understanding of the steps I need to: > > > > 1) alter the template1 database encoding via > > > > UPDATE pg_database SET encoding = 6 where datname IN ('template0', > 'template1'); > > Just create database using template0 as template and you can skip this step > ^^ > Wouldn't this only work if my template0 was UTF8 itself? => select datname, pg_encoding_to_char(encoding) from pg_database; datname| pg_encoding_to_char --+- template1| SQL_ASCII template0| SQL_ASCII postgres | SQL_ASCII So it appears both template0 & template1 are SQL_ASCII, so how would creating from a new DB from template0 be any different than template1? > > Are these the correct steps to perform or is there an easier / in-place > way? > > > Also, when I dump my old DB and restore it, will it be converted > appropriately (e.g. it came from am SQL_ASCII encoding and its going into a > UTF-8 database)? > > You might need to set client encoding when restoring. Or use iconv to > convert from one encoding to another, which is what I usually do. > Note that it's VERY likely you'll have data in a SQL_ASCII db that > won't go into a UTF8 database without some lossiness. > Yes, I see this might be the case. From my playing around with iconv I cannot even properly do the conversion: $ pg_dump -Fp foo > foo.sql $ file -i foo.sql foo.sql: text/plain; charset=us-ascii $ iconv -f utf-8 foo.sql > utf8.sql iconv: illegal input sequence at position 2512661 Uh oh... I cannot event convert it? Whats my next step at this point if I cannot even convert my data? I'd be OK with some lossiness. Thanks again /Cody
Re: [GENERAL] Change server encoding after the fact
On Fri, Sep 30, 2011 at 11:25 AM, Cody Caughlan wrote: > I would like to change my server_encoding which is currently SQL_ASCII to > UTF8. > > I have existing data that I would like to keep. > > From my understanding of the steps I need to: > > 1) alter the template1 database encoding via > > UPDATE pg_database SET encoding = 6 where datname IN ('template0', > 'template1'); Just create database using template0 as template and you can skip this step ^^ > Are these the correct steps to perform or is there an easier / in-place way? > Also, when I dump my old DB and restore it, will it be converted > appropriately (e.g. it came from am SQL_ASCII encoding and its going into a > UTF-8 database)? You might need to set client encoding when restoring. Or use iconv to convert from one encoding to another, which is what I usually do. Note that it's VERY likely you'll have data in a SQL_ASCII db that won't go into a UTF8 database without some lossiness. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgres for OLAP & data mining
Are you looking for stuff like this? http://www.postgresql.org/docs/9.0/static/functions-window.html http://www.postgresql.org/docs/9.0/static/functions-string.html On Fri, Sep 30, 2011 at 10:12 AM, Dario Beraldi wrote: > Hello, > > I'm looking for some information (guidelines, docs, tutorials, etc...) > about using postgres for OLAP, data mining, data warehousing. The sort of > questions I'm trying to answer are on the lines of > - What tools/SQL commands are available? > - How should postgres be configured? > - How suitable is postgres for these tasks (compared to other databases)? > > I have done some Google search but I would appreciate any advice/hint from > more experienced users. > > Many thanks in advance! > > Dario
[GENERAL] Change server encoding after the fact
I would like to change my server_encoding which is currently SQL_ASCII to UTF8. I have existing data that I would like to keep. From my understanding of the steps I need to: 1) alter the template1 database encoding via UPDATE pg_database SET encoding = 6 where datname IN ('template0', 'template1'); 2) Dump my current database pg_dump -Fc foo > foo.db 3) Drop my current database drop database foo; 4) recreate it with the proper encoding create database foo with template = template1 encoding = 'UTF-8'; 5) restore from backup pg_restore -d foo foo.db Are these the correct steps to perform or is there an easier / in-place way? Also, when I dump my old DB and restore it, will it be converted appropriately (e.g. it came from am SQL_ASCII encoding and its going into a UTF-8 database)? Thank you /Cody -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to improve this similarity query?
Look at doc chapter II.12 2011/9/30, haman...@t-online.de : > Hi, > > I have a table of names, and searches are usually performed on prefix match. > This could nicely translate into an index search > Suppose first name is stored as either 'Jim' or 'Jimmy', searching > ... where firstname ~* '^jim'; > gets proper result. I had hoped that creating a functional > index on lower(firstname) and using a query like > where lower(firstname) ~ '^jim' > would improve the search, but it does not. > I ended up with adding a lowercased column for matching > > Now a few names (a few percent of the entire lot) have alternates, like > 'James'. > These could be nicknames, informal variants, language variants, alternate > spellings > > I have already split off these few percent into a separate table and can > query that like > ... where 'jim' ~* firstname_pattern;'jim' ~* firstname_pattern;'jim' ~* > firstname_pattern; > > There are two problems with this approach: when I use 'Jimbo' for the plain > query, > I do not get any 'Jim' or 'Jimmy', as expected. To achieve the same effect > on the pattern > query, I need to "decorate" the pattern somewhat. Actually, when I specify > 'J(im|ames)' for > the pattern, it gets preprocessed - and is stored in the database as - > 'J(im|am($|e($|s)))$' > Unfortunately there are regex patterns which the preprocessing script cannot > handle, so > I might have to try a different regex. > The other, bigger, problem: the search cannot make use of an index, and it > has to compile > a regex for every entry in the table. I am considering a change to that > part: in the Jim/James > case it is obvious that I could speed up the query with > where firstname_pattern ~* '^j' and 'jim' ~* firstname_pattern; > If the pattern was 'Bob|Robert' instead, I would have to change the > preprocessing so the > 'B' and 'R' parts would be separate. > > So, I wonder whether there is any better way of doing these. I have looked > into tsquery > resp. fulltext, but they do not seem to support prefix matches, only exact > ones. > > Regards > Wolfgang Hamann > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- pasman -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] stored procs
On 09/30/11 2:09 AM, J.V. wrote: Some tables have millions of rows, well, something like UPDATE tablename SET id=generate_series(1,numberofrows); will update every row to a sequential value. However, I have no idea how you would match the foreign key references in other tables to these new sequence values. anything that updates a field on a million rows will be causing every row to be updated... postgres never updates anything in place, it will be copying the whole row to a new one (this is how it implements MVCC, and its fundamental to the architecture, there's nothing you can do to override this behavior). -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Any was to prevent a join if no columns are selected from a view?
On Thu, 2011-09-29 at 22:54 -0600, Ben Chobot wrote: > On Sep 29, 2011, at 4:57 PM, Jason Long wrote: > > > > > I thought I had read somewhere that Postges could ignore a join if > > it > > was not necessary because there were no columns from the table or > > view > > selected in the query. Is this possible? > > > > This sounds like incorrect logic to me, so I would be surprised if it > was possible. That is the way it is looking. I just modified my application to generate the join manually.
[GENERAL] One-click installer, Windows 7 32-bit, and icacls.exe
I saw a thread where somebody saw icacls.exe being called by the one-click installer. I'm having the same thing - the installer has been running for 45 minutes now and is basically going to have to be stopped because I'm out of time waiting for it. Looking at process monitor, it is clear that icacls.exe is going through every file on the entire system and changing its permissions. The process tree indicates that it is a child of the installer, and that it is running the command: icacls C:\ /grant "kawright":RX Clearly this won't do at all and should be considered a severe installer bug. Thanks, Karl -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgres for OLAP & data mining
On Fri, Sep 30, 2011 at 9:12 AM, Dario Beraldi wrote: > Hello, > > I'm looking for some information (guidelines, docs, tutorials, etc...) > about using postgres for OLAP, data mining, data warehousing. The sort of > questions I'm trying to answer are on the lines of > - What tools/SQL commands are available? > - How should postgres be configured? > - How suitable is postgres for these tasks (compared to other databases)? > > I have done some Google search but I would appreciate any advice/hint from > more experienced users. > IMHO what you need is in logic layer software is not in data layer. Regards, > > Many thanks in advance! > > Dario -- Cristian Salamea @ovnicraft
Re: [GENERAL] Quick-and-Dirty Data Entry with LibreOffice3?
On Fri, 30 Sep 2011, Vincent Veyron wrote: You get a lot more : this gives you an interface to Postgresql inside an Emacs buffer. Thank you. Rich -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] postgres for OLAP & data mining
Hello, I'm looking for some information (guidelines, docs, tutorials, etc...) about using postgres for OLAP, data mining, data warehousing. The sort of questions I'm trying to answer are on the lines of - What tools/SQL commands are available? - How should postgres be configured? - How suitable is postgres for these tasks (compared to other databases)? I have done some Google search but I would appreciate any advice/hint from more experienced users. Many thanks in advance! Dario
Re: [GENERAL] Quick-and-Dirty Data Entry with LibreOffice3?
Le mercredi 28 septembre 2011 à 06:07 -0700, Rich Shepard a écrit : > On Wed, 28 Sep 2011, Vincent Veyron wrote: > > > I'm not sure what 'INSTALL INTO ... statements' are, but are you aware > > of the very convenient 'M-x sql-postgres' in emacs? > > Vincent, > >I have a SQL major mode for emacs. Don't know that it's specific to > postgres but it is automatically invoked when I open a buffer with a .sql > file name. I get syntax-related colors; perhaps it does more of which I am > unaware. You get a lot more : this gives you an interface to Postgresql inside an Emacs buffer. -- Vincent Veyron http://marica.fr/ Logiciel de gestion des sinistres et des contentieux pour le service juridique -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Connection refused (0x0000274D/10061)
On Friday, September 30, 2011 3:17:51 am Pietro Laranci wrote: > Good morning to everybody > > I have a pg9 db to wich i can connect to by local but not from remote. In > the properties of the postgres db , the port is set to 5300 (not the > default 5432). I tried to connect in remote with pgadmin3 and quantum gis, > both give the error Connection refused (0x274D/10061). This is the > complet message in quantum gis (but is silimar in pgadmin3) > > "could not connect to server: Connection refused (0x274D/10061) > > Is the server running on host "95.110.201.74" and accepting > > TCP/IP connections on port 5300?" > > > > Whit both the programs installed on the same pc of the db, i can connect > without problem to the db. > > > > I already did these settings: > > > > - in postgresql.conf : listen_addresses = '*' and port = 5300 (in the place > of the default 5432) > > > > - in pg_hba.conf: > > host [dbname] all 0.0.0.0/0 md5 > > host [dbname] all 127.0.0.1/32 md5 > > > > - restarted postgres > > > > > > I also tried to substitute on the db properties "host=localhost" with > "host=95.110.201.74" > > > > On the machines are not active firewalls. > > > > Have you any clue of what's wrong? You did not say what OS you are running Postgres on, at a guess I am going to go with Windows. Are you sure the Windows firewall is not blocking port 5300? > > > > Grazie > > > > Thanks -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Deadlock of REINDEX and SELECT queries in PostgresSQL 7.4
On Thursday, September 29, 2011 8:49:07 am Tendulker, Shivanand G Prabhu (SSTL) wrote: > Hello > > We are facing a deadlock kind of issue in PostgresSQL 7.4 > > We have 2 databases with 3 tables each. DB contains about 250 records. We > observed deadlock when 2 different clients are performing REINDEX and > SELECT start their operations near simultaneously. > > Client 1 performs following operations in a tight loop:- > VACCUM, ANALYZE of each table, REINDEX of each table' > > Client 2 performs SELECT in one of the table in a tight loop. > > Upon looking at the postgres locks, it seems like all the locks are granted > to 'REINDEX' operation and SELECT is waiting. REINDEX never returns in > this scenario. This problem occurs when Client1 and Client 2 are running > simultaneously. Both in a tight loop. Once this deadlock is reached all > the subsequent SELECT, RENDEX operations go into 'waiting' mode. > > Is this a known issue? Is the REINDEX and SELECT transactions directed at > postgres at same time not a valid combination? Yes, see here: http://www.postgresql.org/docs/7.4/static/explicit-locking.html > > Please provide help in resolving this issue. > > Thanks and Regards > Shiv -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Rules going away
You're right, rules are perfect for very limited and narrow cases. And make it very hard to write complicated queries against. (i.e., updates that only touch few columns, likewise with inserts). I'm guessing the upside is that rules are faster then triggers. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Rules going away
> -Original Message- > From: Gregg Jaskiewicz [mailto:gryz...@gmail.com] > Sent: Friday, September 30, 2011 5:18 AM > To: Igor Neyman > Cc: Ondrej Ivanič; pgsql-general@postgresql.org > Subject: Re: [GENERAL] Rules going away > > speaking of DO INSTEAD, for insert/update case. Try using RETURNING > with that and rules ;) Good luck Well, I designed it for specific case. And in my application I had no need for RETURNING clause. That's why I'm saying, it works perfectly for me, but I'm not trying to generalize, like those who say "Rules are evil and don't ever use them". Regards, Igor Neyman -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] how to improve this similarity query?
Hi, I have a table of names, and searches are usually performed on prefix match. This could nicely translate into an index search Suppose first name is stored as either 'Jim' or 'Jimmy', searching ... where firstname ~* '^jim'; gets proper result. I had hoped that creating a functional index on lower(firstname) and using a query like where lower(firstname) ~ '^jim' would improve the search, but it does not. I ended up with adding a lowercased column for matching Now a few names (a few percent of the entire lot) have alternates, like 'James'. These could be nicknames, informal variants, language variants, alternate spellings I have already split off these few percent into a separate table and can query that like ... where 'jim' ~* firstname_pattern;'jim' ~* firstname_pattern;'jim' ~* firstname_pattern; There are two problems with this approach: when I use 'Jimbo' for the plain query, I do not get any 'Jim' or 'Jimmy', as expected. To achieve the same effect on the pattern query, I need to "decorate" the pattern somewhat. Actually, when I specify 'J(im|ames)' for the pattern, it gets preprocessed - and is stored in the database as - 'J(im|am($|e($|s)))$' Unfortunately there are regex patterns which the preprocessing script cannot handle, so I might have to try a different regex. The other, bigger, problem: the search cannot make use of an index, and it has to compile a regex for every entry in the table. I am considering a change to that part: in the Jim/James case it is obvious that I could speed up the query with where firstname_pattern ~* '^j' and 'jim' ~* firstname_pattern; If the pattern was 'Bob|Robert' instead, I would have to change the preprocessing so the 'B' and 'R' parts would be separate. So, I wonder whether there is any better way of doing these. I have looked into tsquery resp. fulltext, but they do not seem to support prefix matches, only exact ones. Regards Wolfgang Hamann -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problem dbi_link with postgresql 9.04
Emanuel Araújo wrote: > found that the problem occurs when the dbi_link makes parsing of a field > float / double to a text > field, because when it creates the tables already created with this type of > data. I do not understand that. > Another thing we see is that the problem is not with null fields or zero > bytes but with a value of > 1.5, no problem in NULLs or Zero Bytes. > > Based on that there is any solution? What I would do is check how the data look in Perl. Build a simple Perl script that selects the problem data and display them byte for byte. That should help understand the problem. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problem dbi_link with postgresql 9.04
hi, news! found that the problem occurs when the dbi_link makes parsing of a field float / double to a text field, because when it creates the tables already created with this type of data. Another thing we see is that the problem is not with null fields or zero bytes but with a value of 1.5, no problem in NULLs or Zero Bytes. Based on that there is any solution?
[GENERAL] Connection refused (0x0000274D/10061)
Good morning to everybody I have a pg9 db to wich i can connect to by local but not from remote. In the properties of the postgres db , the port is set to 5300 (not the default 5432). I tried to connect in remote with pgadmin3 and quantum gis, both give the error Connection refused (0x274D/10061). This is the complet message in quantum gis (but is silimar in pgadmin3) "could not connect to server: Connection refused (0x274D/10061) Is the server running on host "95.110.201.74" and accepting TCP/IP connections on port 5300?" Whit both the programs installed on the same pc of the db, i can connect without problem to the db. I already did these settings: - in postgresql.conf : listen_addresses = '*' and port = 5300 (in the place of the default 5432) - in pg_hba.conf: host [dbname] all 0.0.0.0/0 md5 host [dbname] all 127.0.0.1/32 md5 - restarted postgres I also tried to substitute on the db properties "host=localhost" with "host=95.110.201.74" On the machines are not active firewalls. Have you any clue of what's wrong? Grazie Thanks
Re: [GENERAL] stored procedures (packages)
J.V. wrote: > I need to run a series of stored procedures, what is the best way to > organize and run. > > Ideally would like something like Oracle PL/SQL where I can put all > methods in one file and create a main() method > > then just : select main() to have them all run. > > If there is a way to do this, please let me know. Don't try to do PostgreSQL things the Oracle way. To group functions that logically belong together, you can put them into a separate schema. You can grant users execute rights on only one of them if you like (or rather, because by default PUBLIC has execute rights, revoke that from all others). > Also is it possible to call a stored function or procedure from another > stored proc/function? Sure, use them in an SQL statement inside a function. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] stored procs
On 30 September 2011 11:20, J.V. wrote: > For tables that already exist and have a foreign key relationship, is there > an equivalent alter statement for the statement below? > > Does this mean that if table xxx.id primary key value changes, the foreign > key value will change as well? They do if you tell them to CASCADE on UPDATE. -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] stored procs
For tables that already exist and have a foreign key relationship, is there an equivalent alter statement for the statement below? Does this mean that if table xxx.id primary key value changes, the foreign key value will change as well? If this is the case, then would not have to match up all the foreign keys to the new re-sequenced id. Also another question: if the "id serial primary key", do I need to create a sequence at all? Or is that managed by the database? Typically you have to link the id to the database sequence object, but it looks as in this case no "CREATE SEQUENCE" would be needed. thanks J.V. On 9/30/2011 2:45 AM, John R Pierce wrote: create table yyy (id serial primary key, xxx_id int references xxx(id)); -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Rules going away
speaking of DO INSTEAD, for insert/update case. Try using RETURNING with that and rules ;) Good luck -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] stored procs
On 09/30/11 1:28 AM, John R Pierce wrote: On 09/30/11 1:23 AM, John R Pierce wrote: convert the fields to bigint which are 64 bit and not likely to wrap around any time in this century indeed, this is as simple as ... alter table tblname alter column id type bigint; of course, you'll need to alter all the FK fields that refer to it too, and I don't know if that entangles this. yes, quite easy. test=# create table xxx (id serial primary key, val text); NOTICE: CREATE TABLE will create implicit sequence "xxx_id_seq" for serial column "xxx.id" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "xxx_pkey" for table "xxx" CREATE TABLE test=# create table yyy (id serial primary key, xxx_id int references xxx(id)); NOTICE: CREATE TABLE will create implicit sequence "yyy_id_seq" for serial column "yyy.id" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "yyy_pkey" for table "yyy" CREATE TABLE test=# alter table xxx alter column id type bigint; ALTER TABLE test=# alter table yyy alter column xxx_id type bigint; ALTER TABLE voila! -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] stored procs
On 09/30/11 1:23 AM, John R Pierce wrote: convert the fields to bigint which are 64 bit and not likely to wrap around any time in this century indeed, this is as simple as ... alter table tblname alter column id type bigint; of course, you'll need to alter all the FK fields that refer to it too, and I don't know if that entangles this. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] stored procs
On 09/30/11 1:13 AM, J.V. wrote: thanks for the help, we have a production schema with 80 tables and a few of the tables have consumed the max limit for the id field, so I have to write a program (stored functions would be the fastest way to do this), that will go and drop the sequence, create & start at 1 again, and re-sequence every table and then make sure all the foreign keys in every table point to where they are supposed to. you shouldn't have to drop the sequence, just setval('sequencename',max(idfield)+1); after resequencing the ID's with a generate_series... ... or, convert the fields to bigint which are 64 bit and not likely to wrap around any time in this century (sequences already use bigint) -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] stored procs / data types
On 09/30/11 12:59 AM, J.V. wrote: What data types do I have access to in a stored proc? I cannot seem to find the stored procedure manual. http://www.postgresql.org/docs/current/static/plpgsql.html I am not speaking of database field/column data types, but rather stored proc data types. in pl/pgsql, they are the same. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] stored procedures (packages)
On 09/30/11 12:57 AM, J.V. wrote: I need to run a series of stored procedures, what is the best way to organize and run. Ideally would like something like Oracle PL/SQL where I can put all methods in one file and create a main() method then just : select main() to have them all run. in function main, have select function1(); select function2(); ... Also is it possible to call a stored function or procedure from another stored proc/function? see above. note that postgres doesn't acctually have stored procedures, it only has functions. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] stored procs / data types
What data types do I have access to in a stored proc? I cannot seem to find the stored procedure manual. I am not speaking of database field/column data types, but rather stored proc data types. Regards, J.V. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] stored procedures (packages)
I need to run a series of stored procedures, what is the best way to organize and run. Ideally would like something like Oracle PL/SQL where I can put all methods in one file and create a main() method then just : select main() to have them all run. If there is a way to do this, please let me know. Also is it possible to call a stored function or procedure from another stored proc/function? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] stored procs
On 09/30/11 12:24 AM, J.V. wrote: What I want to do however is within a stored procedure make a call and store all tables in a given schema in a list. so procedure1 calls procedure2 (not sure why this extra level of procedures, but I'm playing along) and procedure2 does SELECT table_name from information_schema.tables where table_schema='someschemaname'; and returns this data as a 'list' (whatever you mean by that) ? -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] could not access file "$libdir/pg_buffercache": No such file or directory
Royce Ausburn wrote: > I'm in the process of testing out Postgres 9.0 for production use. I've been using it for development > on my mac, a build from EnterpriseDB. We've just installed a 9.0.5 on an Ubuntu (Ubuntu 10.04.3 LTS) > machine from a backport from lucid. There's an existing 8.4.8 postgres install also on that machine, > but as far as I'm aware the debs are set up so you can happily have two installs side by side. > > I've dumped a test DB from my laptop and attempted to restore it on to the ubuntu machine, but I see > errors: > > royce@fishy:~$ createdb test > royce@fishy:~$ pg_restore -d test --no-owner test_RTR.pgc > pg_restore: [archiver (db)] Error while PROCESSING TOC: > pg_restore: [archiver (db)] Error from TOC entry 65; 1255 2194102 FUNCTION pg_buffercache_pages() > persona > pg_restore: [archiver (db)] could not execute query: ERROR: could not access file > "$libdir/pg_buffercache": No such file or directory > Command was: CREATE FUNCTION pg_buffercache_pages() RETURNS SETOF record > LANGUAGE c > AS '$libdir/pg_buffercache', 'pg_buffercache_... > > > After looking around a bit, my theory on these errors is that my mac has some contrib module installed > that adds views to my test database, and those views have been included in the dump. The contrib > module is apparently not installed on the ubuntu machine and hence the restore can't create the views. > Is this theory correct? If so: > > - Can these errors be safely ignored? (not ideal, because I'll miss other, real errors) > - Is there some simple way I can prevent these views from being emitted as part of the dump? > - Is there some simple way I can prevent these views from being restored from a dump that contains > them? > - Otherwise, how can I purge these contrib modules from my laptop? > > Otherwise, if my theory is incorrect, any hints as to what it might be? Your theory sounds correct since there is a contrib "pg_buffercache" and the error message indicates that the shared object cannot be found. The best solution would be to drop FUNCTION pg_buffercache_pages() and VIEW pg_buffercache in the original database before dumping. That way you can be sure that nothing else in the database depends on these objects. You can always reinstall the contrib if you need it. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] stored procs
Yes, I know that one. What I want to do however is within a stored procedure make a call and store all tables in a given schema in a list. Is this something you can answer? thanks J.V. On 9/29/2011 3:25 AM, Richard Huxton wrote: On 29/09/11 02:33, J.V. wrote: Is is possible within a stored procedure to read all the tables in a schema into a list? [snip] I need to extract this meta-data for a project. Apart from information_schema mentioned elsewhere, start psql with -E and then try \dt and similar - it will show you the queries it uses. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general