Re: [GENERAL] array_agg crash?
Tom Lane wrote: Chris Spotts rfu...@gmail.com writes: many groups are you expecting in that query? Does the plan for the array_agg query show hash or group aggregation? GroupAggregate Huh, there's no reason it should take much memory then. Maybe you've found a memory leak. Can you put together a self-contained test case? regards, tom lane What do you want specifically as far as details for the test case? I exported just the table that that was reading from. Installed a new clean virtual machine ubuntu (jaunty) and then installed 8.4.0. Imported the table and definition. Ran the same query and the same thing happened. Table its selecting from is: Table public.trip_ids_to_customer_upload_ids Column | Type | Modifiers | Storage | Description +-+---+-+- trip_id| bigint | | plain | customer_upload_id | integer | | plain | Indexes: trips_customer_id btree (trip_id, customer_upload_id) Has OIDs: no There is 3801347 rows in the table. There are 3773039 distinct trip_id values. So you can see that the vast majority of rows here are just a single element array. -- 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] Best practices for moving UTF8 databases
On Tue, Jul 21, 2009 at 6:35 PM, Sam Masons...@samason.me.uk wrote:
On Tue, Jul 21, 2009 at 09:37:04AM +0200, Daniel Verite wrote:
I'd love to fix them. But if I do a search for
SELECT * FROM xyz WHERE col like '%0x80%'
it doesn't work. How should I search for these characters?
In 8.2, try: WHERE strpos(col, E'\x80') 0
Note that this may find valid data as well, because the error you get
is when 0x80 is the first byte of a character in UTF8; when it's at
another position, you don't want to change it.
There are various regexs around to check for valid UTF-8 encoding; one
appears to be:
http://keithdevens.com/weblog/archive/2004/Jun/29/UTF-8.regex
One translation into PG would be:
WHERE NOT col ~ ( '^('||
$$[\09\0A\0D\x20-\x7E]|$$|| -- ASCII
$$[\xC2-\xDF][\x80-\xBF]|$$|| -- non-overlong 2-byte
$$\xE0[\xA0-\xBF][\x80-\xBF]|$$|| -- excluding overlongs
$$[\xE1-\xEC\xEE\xEF][\x80-\xBF]{2}|$$|| -- straight 3-byte
$$\xED[\x80-\x9F][\x80-\xBF]|$$|| -- excluding surrogates
$$\xF0[\x90-\xBF][\x80-\xBF]{2}|$$|| -- planes 1-3
$$[\xF1-\xF3][\x80-\xBF]{3}|$$|| -- planes 4-15
$$\xF4[\x80-\x8F][\x80-\xBF]{2}$$|| -- plane 16
'*)$' );
This seems to do the right thing for me in an SQL_ASCII database.
I tried this. Get an error.
mypg=# select * from interesting WHERE NOT description ~ ( '^('||
mypg(#$$[\09\0A\0D\x20-\x7E]|$$|| -- ASCII
mypg(#$$[\xC2-\xDF][\x80-\xBF]|$$|| -- non-overlong 2-byte
mypg(# $$\xE0[\xA0-\xBF][\x80-\xBF]|$$||-- excluding overlongs
mypg(#$$[\xE1-\xEC\xEE\xEF][\x80-\xBF]{2}|$$|| -- straight 3-byte
mypg(# $$\xED[\x80-\x9F][\x80-\xBF]|$$||-- excluding surrogates
mypg(# $$\xF0[\x90-\xBF][\x80-\xBF]{2}|$$|| -- planes 1-3
mypg(#$$[\xF1-\xF3][\x80-\xBF]{3}|$$|| -- planes 4-15
mypg(# $$\xF4[\x80-\x8F][\x80-\xBF]{2}$$|| -- plane 16
mypg(# '*)$' )
mypg-#
mypg-# ;
ERROR: invalid regular expression: quantifier operand invalid
--
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] array_agg crash?
Chris Spotts rfu...@gmail.com writes: What do you want specifically as far as details for the test case? I exported just the table that that was reading from. Installed a new clean virtual machine ubuntu (jaunty) and then installed 8.4.0. Imported the table and definition. Ran the same query and the same thing happened. The table dump and the query would be enough then. Can you send it to me off-list? 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
[GENERAL] How would I get information regarding update when running for a long time?
I have an update that is comparing the id between two tables and inserting the value volume in the first table with result from second table. I think I have two questions. 1) is this update expected to take this long? 2) is there something that I did wrong with the update causing this slow time? Is there a way of determining the progress for the update? If the update is taking this long (now 1.5 hours) is there something wrong with the update statement? It seems a long time to compare and update the 20 million rows, or maybe not? Are the adds for the indexes for volume on the first table making the update slow? Here is the update and table definitions for both tables update t set volume=tchris.volume from tchris where t.id=tchris.id; pgdb001=# select count(*) from dbprc001.tunadjusted_prices; count -- 19922778 (1 row) pgdb001=# \d dbprc001.tunadjusted_prices Table dbprc001.tunadjusted_prices Column | Type | Modifiers ---+---+--- id| character varying(13) | date| date | price| numeric(18,6) | volume | numeric(18,6) | Indexes: ix_d111a btree (id) ix_d111b btree (date) ix_d111c btree (price) ix_d111d btree (volume) pgdb001=# \d dbprc001.tchris Table dbprc001.tchris Column | Type | Modifiers ---+---+--- id| character varying(13) | date| date | volume | numeric(18,6) | Indexes: ix_dchrisa btree (id) ix_dchrisb btree (date) ix_dchrisd btree (volume) Thanks for any help Chris Barnes _ More storage. Better anti-spam and antivirus protection. Hotmail makes it simple. http://go.microsoft.com/?linkid=9671357
[GENERAL] enabling join_collapse_limit for a single query only
Hi there, Is it possible to set the runtime parameter 'join_collapse_limit' for a single query only without setting/unsetting it before/after? Thanks, matt -- 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 would I get information regarding update when running for a long time?
just use: explain update ... and see what sort of plan it comes up with. -- 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] Checkpoint Tuning Question
Dan Armbrust wrote: All of my testing to date has been done with synchronous_commit=off I just tried setting full_page_writes=off - and like magic, the entire hiccup went away. Why is the full_page_write happening before the commit returns when synchronous_commit is set to off? Is there a way to fix this? Thanks, Tomer Amiaz -- View this message in context: http://www.nabble.com/Checkpoint-Tuning-Question-tp24396082p24607396.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Can LIKE under utf8 use INDEXes?
Hi. I'm confused about the behavior of LIKE under utf8 locale. Accoding to the docs ( http://www.postgresql.org/docs/8.2/interactive/locale.html - excerpted below), it seems that LIKE ignores locale and hence can't use indexes. Yet, EXPLAIN clearly shows it using indexes. The docs suggest a workaround, to allow LIKE to use indexes - but I couldn't figure it out. Although I'm stuck with locale utf8, all my data is 7-bit ascii. I'm doing a tremendous amount of WHERE x LIKE 'abc%' - what's the best way to set up a good index? (I can change the settings for this database - but the cluster must remain utf8). Thanks! (Here is the doc excerpt, from http://www.postgresql.org/docs/8.2/interactive/locale.html : The drawback of using locales other than C or POSIX in PostgreSQL is its performance impact. It slows character handling and prevents ordinary indexes from being used by LIKE. For this reason use locales only if you actually need them. As a workaround to allow PostgreSQL to use indexes with LIKE clauses under a non-C locale, several custom operator classes exist. These allow the creation of an index that performs a strict character-by-character comparison, ignoring locale comparison rules. Refer to Section 11.8 for more information.)
Re: [GENERAL] Best practices for moving UTF8 databases
Phoenix Kiula wrote:
I tried this. Get an error.
mypg=# select * from interesting WHERE NOT description ~ ( '^('||
mypg(#$$[\09\0A\0D\x20-\x7E]|$$|| -- ASCII
mypg(#$$[\xC2-\xDF][\x80-\xBF]|$$|| -- non-overlong 2-byte
mypg(# $$\xE0[\xA0-\xBF][\x80-\xBF]|$$||-- excluding overlongs
mypg(#$$[\xE1-\xEC\xEE\xEF][\x80-\xBF]{2}|$$|| -- straight 3-byte
mypg(# $$\xED[\x80-\x9F][\x80-\xBF]|$$||-- excluding surrogates
mypg(# $$\xF0[\x90-\xBF][\x80-\xBF]{2}|$$|| -- planes 1-3
mypg(#$$[\xF1-\xF3][\x80-\xBF]{3}|$$|| -- planes 4-15
mypg(# $$\xF4[\x80-\x8F][\x80-\xBF]{2}$$|| -- plane 16
mypg(# '*)$' )
mypg-#
mypg-# ;
ERROR: invalid regular expression: quantifier operand invalid
If you really don't want to go the pg_dump - iconv (remove invalid
characters) - diff the dump files route, a stored procedure that
searches for invalid characters was posted a few years back that
attempts to find the invalid characters.
http://archives.postgresql.org/pgsql-hackers/2005-12/msg00511.php
http://svana.org/kleptog/pgsql/utf8_verify.sql
--
Justin Pasher
--
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] psql \du [PATCH] extended \du with [+] - was missing
Andreas Wenk wrote: here is the patch for help.c . I think updating some translations is not neccessary because there is no change. Am I right? Not really. For example the spanish translation file has this: msgid \\du [PATTERN] list roles (users)\n msgstr \\du [PATRÓN] listar roles (usuarios)\n and it needs to read instead: msgid \\du[+] [PATTERN] list roles (users)\n msgstr \\du[+] [PATRÓN] listar roles (usuarios)\n -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Best way to import data in postgresl (not COPY)
Hello,
I have a system that must each day import lots of data from another one.
Our system is in postgresql and we connect to the other via ODBC.
Currently we do something like :
SELECT ... FROM ODBC source
foreach row {
INSERT INTO postgresql
}
The problem is that this method is very slow...
Does someone has a better suggestion ?
Thanks a lot in advance !
Denis
--
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] Can LIKE under utf8 use INDEXes?
On Wed, Jul 22, 2009 at 5:57 PM, Robert Jamessrobertja...@gmail.com wrote: Hi. I'm confused about the behavior of LIKE under utf8 locale. Accoding to the docs ( http://www.postgresql.org/docs/8.2/interactive/locale.html - excerpted below), it seems that LIKE ignores locale and hence can't use indexes. Yet, EXPLAIN clearly shows it using indexes. Are you sure you're using 8.2? -- greg http://mit.edu/~gsstark/resume.pdf -- 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] Can LIKE under utf8 use INDEXes?
On Wed, 2009-07-22 at 12:57 -0400, Robert James wrote: The docs suggest a workaround, to allow LIKE to use indexes - but I couldn't figure it out. Although I'm stuck with locale utf8, all my data is 7-bit ascii. I'm doing a tremendous amount of WHERE x LIKE 'abc%' - what's the best way to set up a good index? Create the index using text_pattern_ops, and I think it will do what you want. CREATE INDEX foo_t_idx ON foo (t text_pattern_ops); Regards, Jeff Davis -- 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] Best way to import data in postgresl (not COPY)
Denis BUCHER wrote:
Hello,
I have a system that must each day import lots of data from another one.
Our system is in postgresql and we connect to the other via ODBC.
Currently we do something like :
SELECT ... FROM ODBC source
foreach row {
INSERT INTO postgresql
}
The problem is that this method is very slow...
Does someone has a better suggestion ?
Thanks a lot in advance !
Denis
If you can prepare your statement it would run a lot faster, no idea if
odbc supports such things though.
so:
select ... from odbc...;
$q = prepare('insert into pg...')
foreach row {
$q.params[0] = ..
$q.params[1] = ..
$q.execute;
}
commit;
(* if possible, make sure you are not commitiing each insert statement,
do them all the commit once at the end *)
If you cant prepare, you should try to build multi-value insert statements:
insert into pgtable (col1, col2, col3) values ('a', 'b', 'c'), ('d',
'e', 'f'), ('g','h','i'),...;
Or, you could look into dblink, dunno if it would be faster.
-Andy
--
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] Can LIKE under utf8 use INDEXes?
Robert James srobertja...@gmail.com writes: Hi. I'm confused about the behavior of LIKE under utf8 locale. UTF8 is not a locale, it's an encoding. If you're using C locale then LIKE can use indexes, regardless of the encoding. If you're using some other locale then you need a pattern_ops index. 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] psql \du [PATCH] extended \du with [+] - was missing
Alvaro Herrera wrote: Andreas Wenk wrote: here is the patch for help.c . I think updating some translations is not neccessary because there is no change. Am I right? Not really. For example the spanish translation file has this: msgid \\du [PATTERN] list roles (users)\n msgstr \\du [PATRÓN] listar roles (usuarios)\n and it needs to read instead: msgid \\du[+] [PATTERN] list roles (users)\n msgstr \\du[+] [PATRÓN] listar roles (usuarios)\n first I want to mention, that I moved this to hackers list. And actually I realized that I have to make more changes. I have to change also the documentation. And the same change has to be made with \dg ... After your reply I understand now what Peter meant with changing the translation files also. I didn't have a look to them and did not know, that \du and \dg is also written there - my fault. I will provide a patch tomorrow. Thanks for everybody's patience with me - I am learning ... ;-) Cheers Andy -- 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] psql \du [PATCH] extended \du with [+] - was missing
Alvaro Herrera alvhe...@commandprompt.com writes: Andreas Wenk wrote: here is the patch for help.c . I think updating some translations is not neccessary because there is no change. Am I right? Not really. For example the spanish translation file has this: msgid \\du [PATTERN] list roles (users)\n msgstr \\du [PATRÓN] listar roles (usuarios)\n We've never before expected patch submitters to patch the .po files, and in fact I would have thought it would be useless to do so. The masters are not in our CVS. Why is Andreas being told to worry about this? 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] psql \du [PATCH] extended \du with [+] - was missing
Tom Lane wrote: We've never before expected patch submitters to patch the .po files, and in fact I would have thought it would be useless to do so. The masters are not in our CVS. Why is Andreas being told to worry about this? I must admit I don't know :-) -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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] psql \du [PATCH] extended \du with [+] - was missing
Alvaro Herrera wrote: Tom Lane wrote: We've never before expected patch submitters to patch the .po files, and in fact I would have thought it would be useless to do so. The masters are not in our CVS. Why is Andreas being told to worry about this? I must admit I don't know :-) hm - I don't wanna make it more complicate as it is ... Peter wrote as the first answer to this post to do so - so actually I could do it (I had a short look to the po files two minutes ago - /src/bin/psql/po/). I leave the desicion up to ;-) Cheers Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] table.column in query results?
Hi, Is it possible to get table.column in query results rather than just column? I.e. I'd like: SELECT * FROM foo, bar; foo.id | foo.name | bar.id | bar.text ---+--++- ... ...rather than: SELECT * FROM foo, bar; id | name | id | text ---+--++- ... Thanks. Andrew __ Yahoo! Canada Toolbar: Search from anywhere on the web, and bookmark your favourite sites. Download it now http://ca.toolbar.yahoo.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Select Column Auditing/Logging
Hello, I've tried searching around the net for a feature that would allow me to audit to a table or log select queries against a certain column in a table. In fact, I haven't been able to find a way to log selects to a specific table, just logging all queries. It seems that PostgreSQL can audit INSERT, UPDATE, DELETE, and TRUNCATE through the use of triggers. But SELECT triggers are unsupported (it does suggest perhaps using RULES). Has anyone attempted to implement conditional select based logging, even through RULES? I think Oracle has a feature called Fine Grained Auditing that has a ton of features that I don't really need, just what's described above in particular. P.S. The log would just need the user, time, and query. TIA, Jeremy Brown -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 8.4.0 installer for Windows from EnterpriseDB does not seem to include pgagent
The pgInstaller distribution of PostgreSQL for Windows (which is not being maintained for PostgreSQL 8.4 and above) used to include both pgadmin3 and pgagent. I cannot find pgagent.exe among the files installed by the PostgreSQL 8.4 'one-click' installer for Windows from EnterpriseDB. Am I overlooking something? It would have been nice to have an easy way of installing pgagent also for 8.4 on Windows..
Re: [GENERAL] Select Column Auditing/Logging
On Wed, 2009-07-22 at 14:41 -0700, Jeff Davis wrote: On Wed, 2009-07-22 at 12:04 -0700, Jeremy Brown wrote: It seems that PostgreSQL can audit INSERT, UPDATE, DELETE, and TRUNCATE through the use of triggers. But SELECT triggers are unsupported (it does suggest perhaps using RULES). One thing you can do is use a set-returning function that, as a side effect, records what happened. I should warn you not to record it in a transactional way: it must go out to an external service, or go back to postgresql using something like dblink. Otherwise, someone could do: BEGIN; SELECT ...; ROLLBACK; and whatever you logged would be gone. Regards, Jeff Davis -- 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] Select Column Auditing/Logging
On Wed, 2009-07-22 at 12:04 -0700, Jeremy Brown wrote: It seems that PostgreSQL can audit INSERT, UPDATE, DELETE, and TRUNCATE through the use of triggers. But SELECT triggers are unsupported (it does suggest perhaps using RULES). One thing you can do is use a set-returning function that, as a side effect, records what happened. It's not ideal because it hides a lot of information from the optimizer, but if your table is small enough it would work. Regards, Jeff Davis -- 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] Documentation Improvement suggestions
On Mon, Jul 20, 2009 at 11:37 AM, Martijn van Oosterhout klep...@svana.orgwrote: I know it's not easy, but a nice option to me would be if the 8.1 docs page linked to the equivalent page in the other versions. That would avoid the need to manually edit the URL after a google search. Oh, and +10 for the Up link at the top of the page also. +1 (!) This would solve all of the docs problems I mentioned - include a line at the top stating This is the documentation for version 8.1. Hyperlink:See documentation of current version (8.4). And, yes, an Up at the top would be super helpful - reduce about 50% of searches.
Re: [GENERAL] Can LIKE under utf8 use INDEXes?
Thank you, Tom. I guess I'm a bit confused about things here. How can I find the locale of my database? (I wasn't able to find this in the docs). If I do have the locale set to 'C', do I loose anything by using utf8 for all text fields? On Wed, Jul 22, 2009 at 4:31 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert James srobertja...@gmail.com writes: Hi. I'm confused about the behavior of LIKE under utf8 locale. UTF8 is not a locale, it's an encoding. If you're using C locale then LIKE can use indexes, regardless of the encoding. If you're using some other locale then you need a pattern_ops index. regards, tom lane
[GENERAL] plperl spi_prepare and arrays
Hi all,
Spent the last few hours searching but was unable to get a
satisfactory answer. Basically, if I do this:
my $arr = [1,2,3,4];
$_SHARED{test} = spi_prepare('update users set interest=$1 where
uid=2', 'int[]');
spi_exec_prepared($_SHARED{test}, $arr);
I get:
DBD::Pg::st execute failed: ERROR: error from Perl function view:
array value must start with { or dimension information
Okay, so I can pass the arrayref to spi_exec_prepared by making a
string out of it, with join() and string concatenation with curlies
and all that jazz, but this is an inefficient pain, especially if I
happened to have more than a 1D array. Shouldn't I be able to pass an
arrayref directly?
Also, what about going the other way - getting postgres arrays in
plperl as arrayrefs instead of as strings with curlies? In searching I
found this old message:
http://archives.postgresql.org/pgsql-general/2006-08/msg01472.php
... which suggests to me that it is indeed possible, but I would like
more details.
Thank you very much.
Nathan
--
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 would I get information regarding update when running for a long time?
On Wed, Jul 22, 2009 at 10:00:38AM -0400, Chris Barnes wrote: 1) is this update expected to take this long? 1.5 hours is pretty slow, a couple of things spring to mind as to what would make it go slow: 1) it could be waiting for a lock (the pg_locks and pg_stat_activity views may help here) 2) maintaining all those indexes is going to be slow (vmstat or better iostat will tell you what your system/disks are doing) 2) is there something that I did wrong with the update causing this slow time? None of those indexes are enforcing a UNIQUE constraint, are you sure that the id column in those tables uniquely identify the rows in the table? This isn't going to make it slow, but will cause you to get a non-deterministic (i.e. normally wrong) answer. -- Sam http://samason.me.uk/ -- 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] Can LIKE under utf8 use INDEXes?
Robert James wrote: Thank you, Tom. I guess I'm a bit confused about things here. How can I find the locale of my database? (I wasn't able to find this in the docs). If I do have the locale set to 'C', do I loose anything by using utf8 for all text fields? use psql: postgres=# \l+ List of databases Name | Owner | Encoding | Collation |Ctype| +---+--+-+-+ postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | output shortend ;-) Cheers Andy -- 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] Best way to import data in postgresl (not COPY)
On Wed, Jul 22, 2009 at 08:24:22PM +0200, Denis BUCHER wrote:
SELECT ... FROM ODBC source
foreach row {
INSERT INTO postgresql
}
The problem is that this method is very slow...
Does someone has a better suggestion ?
Using COPY[1] is normally the preferred solution to getting data into PG
fast. Some languages make this easier than others, if you can generate
SQL that looks like:
COPY table (col1,col2) FROM STDIN WITH CSV;
13,hello
42,text with,comma
\.
then you should be in luck---just bung this off to the ODBC driver
as is and all should good. If you need to copy more than will fit
in a string, arrange to put a few thousand rows in each batch, and
generate them and insert them one-at-a-time in a transaction. Using
tab-delimited mode (drop the WITH CSV) is possible, but most languages
will provide library code for generating CSV files and hence will
probably be easier to get correct.
--
Sam http://samason.me.uk/
[1] http://www.postgresql.org/docs/current/static/sql-copy.html
--
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] Can LIKE under utf8 use INDEXes?
Thanks - I don't show any locale: rbt_development= \l List of databases Name | Owner | Encoding +-+-- rbt_development | rbt | UTF8 ... On Wed, Jul 22, 2009 at 6:45 PM, Andreas Wenk a.w...@netzmeister-st-pauli.de wrote: Robert James wrote: Thank you, Tom. I guess I'm a bit confused about things here. How can I find the locale of my database? (I wasn't able to find this in the docs). If I do have the locale set to 'C', do I loose anything by using utf8 for all text fields? use psql: postgres=# \l+ List of databases Name | Owner | Encoding | Collation |Ctype| +---+--+-+-+ postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | output shortend ;-) Cheers Andy
Re: [GENERAL] Select Column Auditing/Logging
On Wed, Jul 22, 2009 at 02:41:41PM -0700, Jeff Davis wrote: On Wed, 2009-07-22 at 12:04 -0700, Jeremy Brown wrote: It seems that PostgreSQL can audit INSERT, UPDATE, DELETE, and TRUNCATE through the use of triggers. But SELECT triggers are unsupported (it does suggest perhaps using RULES). One thing you can do is use a set-returning function that, as a side effect, records what happened. It's not ideal because it hides a lot of information from the optimizer, but if your table is small enough it would work. Would something like this be more amenable to optimization: CREATE FUNCTION tbl_auditor() RETURNS BOOLEAN IMMUTABLE AS $$ logquery; RETURN TRUE; $$; CREATE VIEW tbl_view AS SELECT * FROM tbl WHERE tbl_auditor(); -- Sam http://samason.me.uk/ -- 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] Best practices for moving UTF8 databases
On Wed, Jul 22, 2009 at 05:26:37PM +0800, Phoenix Kiula wrote:
I tried this. Get an error.
mypg=# select * from interesting WHERE NOT description ~ ( '^('||
mypg(#$$[\09\0A\0D\x20-\x7E]|$$|| -- ASCII
mypg(#$$[\xC2-\xDF][\x80-\xBF]|$$|| -- non-overlong 2-byte
mypg(# $$\xE0[\xA0-\xBF][\x80-\xBF]|$$||-- excluding overlongs
mypg(#$$[\xE1-\xEC\xEE\xEF][\x80-\xBF]{2}|$$|| -- straight 3-byte
mypg(# $$\xED[\x80-\x9F][\x80-\xBF]|$$||-- excluding surrogates
mypg(# $$\xF0[\x90-\xBF][\x80-\xBF]{2}|$$|| -- planes 1-3
mypg(#$$[\xF1-\xF3][\x80-\xBF]{3}|$$|| -- planes 4-15
mypg(# $$\xF4[\x80-\x8F][\x80-\xBF]{2}$$|| -- plane 16
mypg(# '*)$' )
doh, I put the * in the wrong place! that last line should be:
')*$' )
at least that's what looks strange to me now--not sure how it got moved
though!
--
Sam http://samason.me.uk/
--
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] Can LIKE under utf8 use INDEXes?
Robert James wrote: Thanks - I don't show any locale: rbt_development= \l List of databases Name | Owner | Encoding +-+-- rbt_development | rbt | UTF8 ... ahm - you are running pg 8.2. There I think the + option is not available (\l+). So if you use a debian based system and installed it via the package manager apt or aptitude you could give this a try: /usr/lib/postgresql/8.2/bin/pg_controldata \ /var/lib/postgresql/8.2/main/ |grep LC This should output something like: LC_COLLATE:de_DE.UTF-8 LC_CTYPE: de_DE.UTF-8 I hope this helps a little ... Cheers Andy P.S.: top posting is ugly ;-) On Wed, Jul 22, 2009 at 6:45 PM, Andreas Wenk a.w...@netzmeister-st-pauli.de mailto:a.w...@netzmeister-st-pauli.de wrote: Robert James wrote: Thank you, Tom. I guess I'm a bit confused about things here. How can I find the locale of my database? (I wasn't able to find this in the docs). If I do have the locale set to 'C', do I loose anything by using utf8 for all text fields? use psql: postgres=# \l+ List of databases Name | Owner | Encoding | Collation |Ctype| +---+--+-+-+ postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | output shortend ;-) Cheers Andy -- 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] Can LIKE under utf8 use INDEXes?
Andreas Wenk wrote: Robert James wrote: Thanks - I don't show any locale: rbt_development= \l List of databases Name | Owner | Encoding +-+-- rbt_development | rbt | UTF8 ... ahm - you are running pg 8.2. There I think the + option is not available (\l+). So if you use a debian based system and installed it via the package manager apt or aptitude you could give this a try: /usr/lib/postgresql/8.2/bin/pg_controldata \ /var/lib/postgresql/8.2/main/ |grep LC This should output something like: LC_COLLATE:de_DE.UTF-8 LC_CTYPE: de_DE.UTF-8 I hope this helps a little ... Cheers Andy P.S.: top posting is ugly ;-) more correct: \l+ is also available in 8.2 but the output in 8.4 is extended ... Cheers Andy -- 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] Can LIKE under utf8 use INDEXes?
Andreas Wenk a.w...@netzmeister-st-pauli.de writes: Robert James wrote: Thanks - I don't show any locale: ahm - you are running pg 8.2. There I think the + option is not available (\l+). So if you use a debian based system and installed it via the package manager apt or aptitude you could give this a try: /usr/lib/postgresql/8.2/bin/pg_controldata \ /var/lib/postgresql/8.2/main/ |grep LC Easier way is SHOW LC_COLLATE and SHOW LC_CTYPE ... 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
[GENERAL] Help using SELECT INTO to make schema
I'd like to SELECT INTO one table into another one. However, I'd like to do two things that I don't know how to do using SELECT INTO: 1. Copy over the indexes and constraints of the first table into the second 2. Do SELECT INTO even if the second table already exists. Is there anyway to do either one of those? Failing that, is there a way to copy a table's schema - its columns, indexes, and constraints - into a new (empty) table?
Re: [GENERAL] Help using SELECT INTO to make schema
On Wed, 2009-07-22 at 18:15 -0400, Robert James wrote:
I'd like to SELECT INTO one table into another one. However, I'd like
to do two things that I don't know how to do using SELECT INTO:
1. Copy over the indexes and constraints of the first table into the
second
See: http://www.postgresql.org/docs/8.4/static/sql-createtable.html
LIKE parent_table [ { INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS |
INDEXES } ]
2. Do SELECT INTO even if the second table already exists.
INSERT INTO foo ... SELECT
Regards,
Jeff Davis
--
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 search on text arrays, using the overlaps () operator
- nha lyondi...@free.fr wrote: Another way could concern the hash join. It has been shown that this step costs a lot with respect to the overall runtime. Depending on available storage space and DBMS load, a kind of materialized view may be handled in order to cut off the overloading join. Here are some suggested statements to create this helper table: [snip] Hi nha, Sorry about the long lag after your last post. I didn't want to post back until I had something solid to report on. Using a materialized view turned out to be the best way to solve my problem. My coworker designed a new table that consists of the key columns for 3 large tables that were being joined. A trigger is used to make sure the materialized view is kept up-to-date. Since new data is added infrequently (once a month), the cost of keeping the materialized view up-to-date is cheap. The resulting query runs exceedingly fast! :) Thank you so much for your guidance. I have learned a lot from this incident! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
