Re: [GENERAL] Call a program
am Tue, dem 22.05.2007, um 16:19:55 -0400 mailte Pablo Hume folgendes: > Hi, sorry for my english, i?m from Paraguay. > > I need to know if exists a way to call a program like CMD (command prompt) > from > a procedure or a trigger, and execute an external application. Yes, this is possible. You need to write a procedure in an untrusted language like pl/perlU oder pl/sh. (the latter works only in unix-like environments, iirc) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Adding auto-increment / sequence
am Mon, dem 21.05.2007, um 18:29:46 -0700 mailte camb folgendes:
> Hey all,
> Is there any way to add some kind of sequence of auto-incrementing
> column to the result set returned by a SELECT?
Yes.
You can use an sequence similar this:
test=# select * from foo;
col1 | col2 | col3
--+--+--
1 |2 |
2 | |
(2 rows)
test=*# create sequence foo_seq;
CREATE SEQUENCE
test=*# select nextval('foo_seq'), * from foo;
nextval | col1 | col2 | col3
-+--+--+--
1 |1 |2 |
2 |2 | |
(2 rows)
Don't forget to reset this sequence, the next select nextval() starts
with the current value and returns 3.
Other way: write an set-returning function and count the rows there.
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] Will a DELETE violate an FK?
am Tue, dem 29.05.2007, um 10:48:21 -0400 mailte Robert James folgendes: > I'd like to be able to detect if a record has associations. I don't want to > actually delete it, just know if it could be deleted. (This is to build an > intelligent menu on a GUI) Set a savepoint, try to delete the record, catch an error if an error occurs and rolled back. > > On 5/29/07, Albe Laurenz <[EMAIL PROTECTED]> wrote: And please, no top-posting. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] general features
am Sun, dem 03.06.2007, um 10:34:47 +0200 mailte Badawy, Mohamed folgendes: > Hi all > > am very new to postgres & actually am just collecting information about but I > having a problem with time so I was asking if someone could help me with quick > anserws about these features in postgres. > > > > 1-max amount of available storage data. http://www.postgresql.org/docs/faqs.FAQ.html#item4.4 > 2-Clustering support. What do you mean exactly? > 3-price. http://www.postgresql.org/docs/faqs.FAQ.html#item1.3 > 4-speed. very high ;-) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Foreign keys and indexes
am Tue, dem 05.06.2007, um 11:49:20 +0200 mailte Marc Compte folgendes: > Dear list, > > This might be too basic for a question but I just couldn't find the > answer so far. > > Does PostgreSQL create an implicit index also for foreign keys? No, only for primary keys to enforce the uniqueness. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] How to count pairs?
am Sun, dem 10.06.2007, um 13:41:27 +0200 mailte Andrej Kastrin folgendes: > Dear all, > > I need to count all pairs of instances which occure under given ID > number, e.g.: > > ID word > - > 1 car > 1 fruit > 2 car > 2 fruit > 2 vegetable > 3 car > 3 vegetable > > And the output should be simillar to: > > car fruit 2 > car vegetable 2 > fruit vegetable 1 > test=*# select * from foo; id | word +--- 1 | car 1 | fruit 2 | car 2 | fruit 3 | car 3 | vegetable 2 | vegetable (7 rows) test=*# select word_a || ',' || word_b as pair, count(1) from ( select a.word as word_a, b.word as word_b from foo a join foo b on a.id=b.id where a.word != b.word and a.word > b.word) as bla group by pair; pair | count -+--- vegetable,car | 2 vegetable,fruit | 1 fruit,car | 2 (3 rows) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Create a table B with data coming from table A
am Mon, dem 11.06.2007, um 21:23:59 - mailte [EMAIL PROTECTED] folgendes: > My original table is like that: > > IDA1 A2 A3 cnt > 1234 1 0 0 4 > 1234 1 0 1 8 > 1234 1 1 1 5 > 1235 1 0 0 6 > 1235 1 0 1 7 > 1235 1 1 1 12 > > I have to create a new table B: > > IDB1 B2 B3 S > 1234 4 8 5 17 > 1235 6 7 12 25 > > The combination (A1=1,A2=0,A3=0) gives B1 > The combination (A1=1,A2=0,A3=0) gives B2 > The combination (A1=1,A2=1,A3=1) gives B3 > > S = B1+B2+B3 > > I think it's a classical problem, but i can't see to problem key test=*# select * from tab_a; id | a1 | a2 | a3 | cnt --++++- 1234 | 1 | 0 | 0 | 4 1234 | 1 | 0 | 1 | 8 1234 | 1 | 1 | 1 | 5 1235 | 1 | 0 | 0 | 6 1235 | 1 | 0 | 1 | 7 1235 | 1 | 1 | 1 | 12 (6 rows) test=*# select id, b1,b2,b3, sum(b1+b2+b3) as s from (select id, sum(case when a1=1 and a2=0 and a3=0 then cnt else 0 end) as b1, sum(case when a1=1 and a2=0 and a3=1 then cnt else 0 end) as b2, sum(case when a1=1 and a2=1 and a3=1 then cnt else 0 end) as b3 from tab_a group by id order by id) foo group by id, b1, b2, b3 order by id; id | b1 | b2 | b3 | s --++++ 1234 | 4 | 8 | 5 | 17 1235 | 6 | 7 | 12 | 25 (2 rows) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Historical Data Question
am Thu, dem 14.06.2007, um 10:57:43 -0700 mailte Lza folgendes: > Can anyone help me with this problem? > > I have a table in my database that holds information on policies and > this table is linked to a number of other tables. I need to be able to > keep a history of all changes to a policy over time. The other tables > that are linked to policy also need to store historical data. When I > run a query on the policy table for a certain period, I also need to > be able to pull the correct related rows (i.e. the information that > would have been in the table at that time) from the tables linked to > it. > > Does anyone have any suggestions on how to store historical > information in databases? Any good resources (books, etc..) that cover > this information? Maybe this one: http://www.rueping.info/doc/Andreas%20R&ping%20--%202D%20History.pdf Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] "Constant" fields in a table
am Fri, dem 15.06.2007, um 10:24:26 +0200 mailte Adrian von Bidder folgendes: > Hi, > > I want to tighten down my db schema as much as possible against accidential > corruption. For this, I'd like to have fields that can only inserted and > not later changed (think some sort of "id", account names, etc., which are > often not only stored in the database but also in external places so > changing is just a definitive DON'T). > > Obviously I can check this with triggers, but it occured me that some kind > of "CONSTANT" field attribute would be nice. Perhaps such a construct > already exists? Or perhaps somebody has come up with a generic way to do > this without having to write 30 or 50 small triggers all over the place? You can use RULE for this: http://www.postgresql.org/docs/techdocs.7 Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Historical Data Question
am Fri, dem 15.06.2007, um 8:21:45 -0400 mailte Francisco Reyes folgendes: > Lza writes: > > >I have a table in my database that holds information on policies and > >this table is linked to a number of other tables. I need to be able to > >keep a history of all changes to a policy over time. The other tables > > There is a postgresql project which I just saw last night. > Don't recall the name, but it was announced in the monthly announcement > email. > > It allows you to save all the changes made to a file and to revert back to > a given date. > I believe it was in the annoucement email for june. Maybe tablelog. http://pgfoundry.org/projects/tablelog/ Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Exec a text variable as select
am Mon, dem 18.06.2007, um 10:14:32 -0300 mailte Ranieri Mazili folgendes: > Hello, > > I'm creating a function that will create a select statement into a > while, this select will be stored into a text variable, after while ends > I need to execute this query stored into variable, on SQLSERVER I can do: > EXEC(text_variable) > How can I do this on Postgres? With EXECUTE. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Loop through all views with PHP
am Mon, dem 18.06.2007, um 14:59:34 +0200 mailte Stefan Schwarzer folgendes: > Hi there, > > my app is creating views for a certain task; now, I would like to run > on a regular basis a script which deletes these views. As they are > named with the date/hour/min/sec-appendix to make each view unique, I > don't know the names myself (Ok, I could stock the names in a > separate table as well). > > Is there any way via PHP to loop through the whole set of views to > delete those with a specific name? You can scripting this, http://people.planetpostgresql.org/greg/index.php?/archives/38-Scripting-with-psql.html#extended. Modify the query there, change "WHERE relkind = 'r'" and compare with 'v' (VIEW). Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] How to prevent modifications in a tree of rows, based on a condition?
am Tue, dem 19.06.2007, um 12:23:51 +0200 mailte Philippe Lang folgendes: > > I'd like to prevent any kind of modification (insert, update, delete) in > a order (and its lines, and steps) if all the steps in the lines of the > order are "checked". If that condition is not true, a modification is > accepted. > > > Does anyone have an idea maybe? The rule system? Thanks for your ideas. Yes, with RULEs. A simple example to prevent UPDATE for subset rows: test=# select * from foo; id | t +- 5 | 1,2 6 | 2,2 7 | 3,2 1 | 1 1 | 1 0 | 10 0 | 10 (7 rows) test=*# select * from looked ; id 1 (1 row) -- i want to prevent update for all id's listed in table looked test=*# create rule r1 as on update to foo where old.id in (select id from looked) do instead nothing; CREATE RULE test=*# update foo set t = 100 where id=0; UPDATE 2 test=*# update foo set t = 100 where id=1; UPDATE 0 test=*# select * from foo; id | t +- 5 | 1,2 6 | 2,2 7 | 3,2 1 | 1 1 | 1 0 | 100 0 | 100 (7 rows) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] date time function
am Thu, dem 28.06.2007, um 16:04:48 -0400 mailte Jasbinder Singh Bali folgendes: > Hi, > > I have a timestamp field in my talbe. > I need to check its difference in days with the current date. > > field name is time_stamp and I did it as follows: > > select age(timestamp '2000-06-28 15:39:47.272045 ') > > it gives me something like > > 6 years 11 mons 29 days 08:20:12.727955 > > How can i convert this result into absolute number of days. test=*# select current_date - '2000-06-28 15:39:47.272045 '::date; ?column? -- 2557 (1 row) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Create user
am Fri, dem 29.06.2007, um 13:31:03 +0530 mailte Ashish Karalkar folgendes: > Hello All, > > I am trying to create a user and i dont understand why it is showing me any > massage even after giving parameter -e to the command. Maybe you should use -q: -q --quiet Do not display a response. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Tables not created in proper schema
am Mon, dem 02.07.2007, um 11:13:54 +0530 mailte Ashish Karalkar folgendes: > Hello All, > I am trying to create databse with script. > I run this script from root prompt with command > > > $ su - postgres -c 'path to script.sql' > > > In the script I follow following steps > > > 1) create user xyz > 2) create database xyz -O xyz > 3) create schema xyz ALTER USER xyz SET SEARCH_PATH = ' ... '; or set the search_path at the beginning of your sql-file. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Trigger Priority
am Tue, dem 03.07.2007, um 12:11:01 -0400 mailte Mike Gould folgendes: > Is there a way to determine the order that triggers are executed? We are alphabetically Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Date for a week day of a month
am Tue, dem 03.07.2007, um 14:27:24 -0400 mailte Emi Lu folgendes:
> Hello,
>
> Can I know how to get the date of each month's last Thursday please?
>
> For example, something like
>
> Query: select getDateBaseOnWeekday('2007-04-01', 'Last Thursday');
> Result: 2007-04-26
Write you own function. You can get the last date for a month like this:
select '2007-04-01'::date+'1month'::interval-'1day'::interval;
You can get the weekday with:
select extract('dow' from
'2007-04-01'::date+'1month'::interval-'1day'::interval);
Now you know the weekday. If this value = 4, its okay. If not, subtract
days until the date is a Thursday.
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Mugs 'n stuff
am Tue, dem 03.07.2007, um 23:37:57 +0100 mailte Raymond O'Donnell folgendes: > Hi all, > > Is it still possible to get PostgreSQL merchandise? A friend of mine is > looking for some, but I can't seem to find where its available. Can you or your fried visit the pgday.it at Prato, Italy? http://www.pgday.it/en/ , maybe there. We, the german postgresql user group, have some stuff like blue plush elephants, shirts and coffee-cups, see also http://ads.wars-nicht.de/blog/ (scroll a little bit down), this will be available in Prato. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Database Insertion commitment
am Mon, dem 09.07.2007, um 2:53:48 -0400 mailte Jasbinder Singh Bali folgendes: > Hi, > > If I have a series of Insert statements within a loop in a function on the > same > table. > Would an Insert be able to see the values of previous insert in that table ? Inside this function, yes. Outside the function no. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] free scheduled import utility
am Tue, dem 10.07.2007, um 14:32:58 +0200 mailte Zlatko Matic folgendes: > Hello. > Is there any free program/utility for batch imports from .csv files, that > can be easily scheduled for daily inserts of data to PostgreSQL tables? > Regards, You can use the scheduler from your OS. For Unix-like systems is this the CRON, with windows i'm not familiar. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] odbc parameters
am Wed, dem 11.07.2007, um 14:15:02 +0200 mailte Zlatko Matic folgendes: > Hello, please don't hijack other threads. If you only change the subject for a new question, your mail will be sorted completely wrong. (within modern email-clients such thunderbird or mutt) > > I use MS Access for data import. Access imports csv file, make some > calculation and transffers data to PostgreSQL. Import the csv-file directly in postgresql and make the calculations within PG? Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] odbc parameters
am Wed, dem 11.07.2007, um 14:55:28 +0200 mailte Zlatko Matic folgendes:
> I have already tried COPY.
> But, it has problems with type castings.
> For example, COPY operation fails because PostgreSQL can't copy value
> 7.844,000 into NUMERIC field...
Either copy such values into a temp. table with text-columns and work
with arbitrary sql-funktions (you can convert it to numeric with, for
instance, regexp_replace('7.844,000',',.*$', '')::numeric) to fill the
destination table with the values or work before the COPY with
text-lools like sed, awk, perl, ...
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] multirow insert
am Fri, dem 13.07.2007, um 18:50:26 +0200 mailte Zlatko Mati? folgendes: > When using multirow INSERT INTO...VALUES command, are all rows inserted in a > batch, or row by row? Within one transaction, yes. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] redirecting output of pg_dump
am Wed, dem 18.07.2007, um 15:39:01 +0530 mailte Ashish Karalkar folgendes: > Hello all, > > I want to take backup from one server and save it to another machine hard > drive. > The backup will be taken through a shell script attached to a cron job. > > something like: > > pg_dump -d postgres -U postgres -f save the file on that machine > > > is there any way? Yes, any. For instance: - you are on the remote machine (which should store the backup) ssh remote "pg_dump ..." > backup.sql - you have the pg_dump installed on the backup-machine: pg_dump -h remote ... > backup.sql - you are on the server: pg_dump ... | ssh backup_server "cat - > backup.sql" (all untested, but should work) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Using COALESCE nside function
am Mon, dem 23.07.2007, um 13:58:22 -0400 mailte Robert Fitzpatrick folgendes: > Is it possible to use COALESCE function inside a function as a cursor > variable? Yes, why not? > > test cursor (myvar varchar) for > (coalesce(SELECT...,0)); I guess: wrong syntax. Try instead select coalesce(coll,0) from ... Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] regexp_replace
am Mon, dem 23.07.2007, um 7:50:35 -0700 mailte [EMAIL PROTECTED] folgendes:
> Hi all,
>
> I would like to change a sub-string in a text-field by using
>
> UPDATE tablename SET
> fieldname=regexp_replace(fieldname,old_sub_string,new_sub_string)
> WHERE (fieldname like '%old_sub_string%')
>
> In priniciple, it works. However, only one occurence of old_sub_string
> is replaced and further not. Which syntax has to be used to replace
> all old_sub_strings by new_sub_string in a text-field at once? I have
> seen something like '\&' in the docs and tried it, but I failed.
>
> Any Idea? Thanks a lot!
No problem, add a 'g' as extra parameter to your regexp_replace() -
function. See:
test=> select regexp_replace('xxaaxxxa','a','A');
regexp_replace
xxAaxxxa
(1 row)
test=*> select regexp_replace('xxaaxxxa','a','A','g');
regexp_replace
xxAAxxxA
(1 row)
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Database Select Slow
am Fri, dem 10.08.2007, um 17:46:11 +0800 mailte carter ck folgendes: > Hi all, > > I am facing a performance issue here. Whenever I do a count(*) on a table > that contains about 300K records, it takes few minutes to complete. Whereas > my other application which is counting > 500K records just take less than > 10 seconds to complete. > > I have indexed all the essential columns and still it does not improve the > speed. Indexes don't help in this case, a 'select count(*)' forces a seq. scan. Do you realy need this information? An estimate for the number of rows can you find in the system catalog (reltuples in pg_class, see http://www.postgresql.org/docs/current/interactive/catalog-pg-class.html) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] INSERT before UPDATE?
am Thu, dem 16.08.2007, um 10:30:01 +0200 mailte Ji?í N?mec folgendes: > Hello, > > I would like to ask you for an advice. > > There are two tables in my PostgreSQL database - main table with datas and > second with translations for all languages of these records. > > When I try to UPDATE a record in the language table and this record > doesn't exists there I need to INSERT into the language table (from > default language version) and execute UPDATE statement again. > > Could you tell me if is this possible and if so - how to do it? Okay, i try to ask our doc-bot on IRC: 11:15 < akretschmer> ??upsert 11:15 < rtfm_please> For information about upsert 11:15 < rtfm_please> see http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] INSERT before UPDATE?
am Thu, dem 16.08.2007, um 14:11:07 +0200 mailte Ji??í N??mec folgendes: > > see > > http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE > > Yes I see, but I'll try to describe it in more detail: > > I could write plpgsql trigger function which will UPDATE a record (or > INSERT new record) when the record is not found. But as far as I know > a trigger is not executed when a updated record is not found, so this > solution doesn't work for me. I don't want to change my application - > I would like to set this behaviour directly in PostgreSQL database so > I couldn't use a method from your example (...which requires that all > SQL UPDATE statements need to be modified). Maybe you can achieve this with a RULE. I'm not sure, but i would try ist. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Help creating a function
am Wed, dem 15.08.2007, um 17:29:17 -0400 mailte Madison Kelly folgendes: > What I would like to do is create a function that would do the same > thing so I could read out the IP addresses as standard dotted-decimal > format. Could anyone help me with this? I am quite the n00b when it > comes to functions. :) create or replace function bigint2inet(IN i bigint, OUT n inet) as $$ declare a int; b int; c int; d int; begin a := i/(256^3)::int; b := ((i-(256^3)*a)/(256^2))::int; c := ((i-(256^3)*a-(256^2)*b)/256)::int; d := (i-(256^3)*a-(256^2)*b-256*c)::int; n := (a||'.'||b||'.'||c||'.'||d)::inet; return; end; $$ language plpgsql immutable strict; hope thats helps, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] history table
am Tue, dem 21.08.2007, um 20:20:38 +0200 mailte Robin Helgelin folgendes: > Hi, > > I want to save history for a few tables using triggers on update and > creation. What's the best approach to do this in a webapp environment > where I want to save which webapp user that is doing the change, not > the postgresql user? Maybe tablelog. 20:49 < akretschmer> ??tablelog 20:49 < rtfm_please> For information about tablelog 20:49 < rtfm_please> see http://ads.wars-nicht.de/blog/archives/100-Log-Table-Changes-in-PostgreSQL-with-tablelog.html 20:49 < rtfm_please> or http://pgfoundry.org/projects/tablelog/ Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] [ERROR] syntax error at end of input
am Mon, dem 27.08.2007, um 9:40:45 -0300 mailte Marcelo de Moraes Serpa folgendes: > Hello list, > > I'm trying to execute the following sentences in a pl/pgsql function. > aNomeProcAudita and pTabAudit are both variables. > >DROP FUNCTION IF EXISTS aNomeProcAudita; Which version? DROP object IF EXISTS is a new feature since 8.2. Do you have 8.2? >DROP TRIGGER IF EXISTS 'Audita_' || pTabAudit || '_trigger'; I guess you need to rewrite this to use EXECUTE for dynamic querys like this. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Read Access to database
am Tue, dem 28.08.2007, um 10:56:38 +0530 mailte Ashish Karalkar folgendes: > Hello all, > > I have a database abc with owner c . > I want to grant only read access on this DB abc to user d. > More specificaly to a schema abcs in the databse abc. > Is ther any way to do so? > > I have more than 1000 table so dont want to list all the table name in the > grant command. Okay: http://www.archonet.com/pgdocs/grant-all.html Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] One database vs. hundreds?
am Tue, dem 28.08.2007, um 8:08:36 -0400 mailte Kynn Jones folgendes: > I'm hoping to get some advice on a design question I'm grappling with. > I have a database now that in many respects may be regarded as an > collection of a few hundred much smaller "parallel databases", all > having the same schema. What I mean by this is that, as far as the > intended use of this particular system there are no meaningful queries > whose results would include information from more than one of these > parallel component databases. Furthermore, one could delete all the Maybe different schemas, one schema for every "parallel databases", can help you. And different rights for the users. Why one database with many schemas? I suppose, you have objects to share with all users, for instance: - programming languages - stored procedures - maybe shared data Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] One database vs. hundreds?
am Tue, dem 28.08.2007, um 14:23:00 +0200 mailte Kamil Srot folgendes: > > Kynn Jones wrote: > >I'm hoping to get some advice on a design question I'm grappling with. > > I have a database now that in many respects may be regarded as an > >collection of a few hundred much smaller "parallel databases", all > >having the same schema. What I mean by this is that, as far as the > >intended use of this particular system there are no meaningful queries > >whose results would include information from more than one of these > > I don't have experience in this type of application, but we use pgsql > partitioning for other reasons > and it has some of the features you want (data separation, query > performance, ...). > It can be worth reading: > http://www.postgresql.org/docs/8.2/interactive/ddl-partitioning.html He don't need table partitioning, this is a different thing. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] How to avoid "Seq Scans"?
am Wed, dem 29.08.2007, um 11:15:21 +0200 mailte Vincenzo Romano folgendes: > This loop is increadibly slow. Infact the friendly explain tells me > that: > > test=# explain select * from t order by f2,f3; >QUERY PLAN > - > Sort (cost=958786.20..970734.55 rows=4779338 width=28) >Sort Key: f2,f3 >-> Seq Scan on t (cost=0.00..85501.38 rows=4779338 width=28) > > I'd like to know a hint about a technicque to avoid the sequential > scan! A 'select foo from bar' without a WHERE-condition forces a seq-scan because _YOU_ want the whole table. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] \copy only select rows
am Thu, dem 30.08.2007, um 14:59:06 +0800 mailte Ow Mun Heng folgendes: > Is there a way to do a dump of a database using a select statement? A complete database or just a simple table? > > eg: \copy trd to 'file' select * from table limit 10 Since 8.2 you can use COPY (select * from table) TO 'file'. Other solution, use a regular UNIX-Shell and psql, a la echo "select * from table limit 10;" | psql > file Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] temp tables and sequences in functions
am Wed, dem 05.09.2007, um 6:58:30 -0700 mailte Rob folgendes: > What is the proper why to deal with temp tables and sequences? Why aren't they > being dropped after the function ends? Why do I get OID errors if I delete the > temp table/sequence at the end of the function and then try to rerun the > function? > > ERROR: could not open relation with OID 58341 > SQL state: XX000 normal behavior. Read more about this problem here: http://merlinmoncure.blogspot.com/2007/09/as-previously-stated-postgresql-8.html Solution: use EXECUTE for DDL-commands inside functions. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Tutorial EXPLAIN for idiots?
am Fri, dem 07.09.2007, um 12:30:06 +0200 mailte Erwin Moller folgendes: > Hi group, > > Does anybody know of an tutorial for EXPLAIN for idiots like me? > (I am fairly confortable with Postgres, but never used EXPLAIN before.) > > I need to optimize a few slow running queries, but I am not really > getting it when reading: > http://www.postgresql.org/docs/8.1/interactive/performance-tips.html > > A bit hard for starters. :-/ > > Any tips/sites? http://redivi.com/~bob/oscon2005_pgsql_pdf/OSCON_Explaining_Explain_Public.pdf Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Database reverse engineering
am Sat, dem 08.09.2007, um 11:44:17 +0200 mailte Thorsten Kraus folgendes: > Hello, > > I am looking for a tool which is able to generate a database diagramm > including the relationships from an existing database schema. The only > tool I know for this purpose is the Clay database plugin for eclipse. > Are there any other tools which can help me? Maybe this one: postgresql-autodoc - utility to create system tables overview in HTML, DOT and XML ,[ apt-cache show postgresql-autodoc ] | Description: utility to create system tables overview in HTML, DOT and XML | This is a utility which will run through PostgreSQL system tables and returns | HTML, DOT, and 2 styles of XML which describes the database. | . | The HTML is human readable (via webbrowser). The first style of XML is actually | the fileformat of Dia, a UML diagram tool. The second type of XML is similar to | the HTML but in the Docbook 4 format. It enables you to mix in other docbook | documentation via the XREFs, generating PDFs, HTML, RTF, or other formatted | documents. Between these tools and JavaDoc with the appropriate XREFs, | documentation about a project can be generated quickly and be easily updatable | yet have a very professional look with some DSSSL work. ` Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Timestamp from an OID?
am Wed, dem 12.09.2007, um 7:32:45 -0600 mailte Dennis Muhlestein folgendes: > I've been passively collecting data for a few months. I realized, after > a while, that I never added a timestamp column to the table the data is > being stored in. I've since added that. > > Is there a way to find out when the previous rows were inserted? There No. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Timestamp from an OID?
am Wed, dem 12.09.2007, um 15:41:44 +0200 mailte A. Kretschmer folgendes: > am Wed, dem 12.09.2007, um 7:32:45 -0600 mailte Dennis Muhlestein folgendes: > > I've been passively collecting data for a few months. I realized, after > > a while, that I never added a timestamp column to the table the data is > > being stored in. I've since added that. > > > > Is there a way to find out when the previous rows were inserted? There > > No. How many records per day? Maybe you can guess the approximate timestamps. Thank to depesz on IRC for the proposal. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] get a list of table modifications in a day?
am Thu, dem 13.09.2007, um 10:44:41 +0200 mailte Ottavio Campana folgendes: > > http://ads.wars-nicht.de/blog/archives/100-Log-Table-Changes-in-PostgreSQL-with-tablelog.html > > since I already use triggers on that table, can I use table_log? > > I mean, can I have two triggers for the same event on the same table? Yes. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] UPDATE pg_catalog.pg_class as NO Superuser??
am Thu, dem 13.09.2007, um 11:25:39 +0200 mailte Kai Behncke folgendes: > But always if I sent as user xy the > "UPDATE pg_catalog.pg_class SET reltriggers = 0;"-command I get: > > "SQL error: > > ERROR: permission denied for relation pg_class" > > Why is that? MUST I be a superuser for that? Write a function with SECURITY DEFINER for that. *untested* Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] UPDATE pg_catalog.pg_class as NO Superuser??
am Thu, dem 13.09.2007, um 13:06:11 +0200 mailte Kai Behncke folgendes: > > why dont you simply alter table disable trigger? > > > > depesz > > > Could you give me an example for that please? > Thank you very much :-), Kai Open psql and type: \h alter table test=*# \h alter table Command: ALTER TABLE Description: change the definition of a table Syntax: ALTER TABLE [ ONLY ] name [ * ] Okay: ALTER TABLE foo DISABLE TRIGGER ALL; Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] ON INSERT => execute AWK/SH/EXE?
am Mon, dem 17.09.2007, um 18:50:46 +0200 mailte Bima Djaloeis folgendes: > Hi there, > > I am new to PostgreSQL, is it possible to create something so that > > 1) If I insert / update / delete an item from my DB... > 2) ... an awk / shell / external program is executed in my UNIX System? > > If yes, how do I do this and if no, thanks for telling. You can do this, you need a untrusted language like plperlU or plsh. Then you can write a TRIGGER and call external programs. Hope that helps, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] help w/ SRF function
am Mon, dem 17.09.2007, um 9:21:22 +0800 mailte Ow Mun Heng folgendes: > CREATE OR REPLACE FUNCTION foo_func(fromdate timestamp, todate > timestamp, code text) > RETURNS SETOF foo AS > $BODY$ > SELECT > TRH.ID, > TRH.data1, > TRH.data2, > FROM D > INNER JOIN TS >ON TS.id = D.id > inner join TRH >on ts.id = trh.id > WHERE D.start_timestamp BETWEEN fromdate AND todate > And D.code IN (code) > $BODY$ > LANGUAGE 'sql' IMMUTABLE STRICT; > > How can I go about this this? The above will fail due to missing columns > fromdate/todate/code. Use $1, 2 and $3 within the function-body instead fromdate, todate and code. Example: test=# select * from n; feld1 | feld2 +--- Frank |23 Frank |31 Stefan |32 Stefan |22 Jochen |29 (5 rows) test=*# create or replace function nn(int) returns setof n as $$ select * from n where feld2=$1; $$ language sql; CREATE FUNCTION test=*# select * from nn(22); feld1 | feld2 +--- Stefan |22 (1 row) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] how to know the current size of a database
am Wed, dem 19.09.2007, um 22:36:02 +1200 mailte [EMAIL PROTECTED] folgendes: > Hello > > I want to know about the size of my database. For example, I want to know > how many Mb of data for current myDatabase database in a postgres server. http://www.postgresql.org/docs/8.1/interactive/functions-admin.html Table 9-47. Database Object Size Functions Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Stuck on Foreign Keys
am Thu, dem 20.09.2007, um 14:13:40 +1000 mailte Chester folgendes: > Hi > > I have a question regarding foreign keys, I just cannot get it to create > them for meI must be doing something wrong but I have no idea what > that might be :) > > I have a table "clients" > > clientID (primary) > ticode > Firstname > SecondName > > I have a second table "titles" > > ticode (primary) > Title > > I am trying to create a foreign key on TIcode "clients" table as in below, > > ALTER TABLE clients ADD CONSTRAINT the_title FOREIGN KEY (ticode) > REFERENCES titles (ticode) ; > > I keep getting this error > > ERROR: insert or update on table "clients" violates foreign key > constraint "the_title" > DETAIL: Key (ticode)=( ) is not present in table "titles". > > Sorry, I have no idea where I am going wrong...Any help would be great my guess: Table clients, column ticode isn't a INT. It it a TEXT-type and contains an entry ' '. test=> create table clients (clientid serial primary key, ticode text); NOTICE: CREATE TABLE will create implicit sequence "clients_clientid_seq" for serial column "clients.clientid" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "clients_pkey" for table "clients" CREATE TABLE test=*> create table titles(ticode text unique, title text); NOTICE: CREATE TABLE / UNIQUE will create implicit index "titles_ticode_key" for table "titles" CREATE TABLE test=*> insert into clients values (1, ' '); INSERT 0 1 test=*> alter table clients add constraint the_title FOREIGN KEY (ticode) REFERENCES titles (ticode); ERROR: insert or update on table "clients" violates foreign key constraint "the_title" DETAIL: Key (ticode)=( ) is not present in table "titles". Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] more problems with count(*) on large table
am Fri, dem 28.09.2007, um 11:56:46 -0400 mailte Mike Charnoky folgendes: > Hi, > > I am still having problems performing a count(*) on a large table. This > > Now, certain count(*) queries are failing to complete for certain time > ranges (I killed the query after about 24 hours). The table is indexed > on a timestamp field. Here is one query that hangs: Again: an index can't help! Because of MVCC: 'select count(*)' without WHERE-condition forces an seq. table-scan. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] more problems with count(*) on large table
am Fri, dem 28.09.2007, um 12:50:34 -0400 mailte Alvaro Herrera folgendes: > A. Kretschmer wrote: > > am Fri, dem 28.09.2007, um 11:56:46 -0400 mailte Mike Charnoky folgendes: > > > Hi, > > > > > > I am still having problems performing a count(*) on a large table. This > > > > > > Now, certain count(*) queries are failing to complete for certain time > > > ranges (I killed the query after about 24 hours). The table is indexed > > > on a timestamp field. Here is one query that hangs: > > > > Again: an index can't help! Because of MVCC: 'select count(*)' without > > WHERE-condition forces an seq. table-scan. > > But he does have a WHERE condition. THe problem is, probably, that the > condition is not selective enough so the planner chooses to do a > seqscan. Yes, i'm sorry, i havn't recognize this. Maybe wrong statistics. As Andrew suggested, a 'ALTER TABLE...SET STATISTICS' can help. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] multiple row insertion
am Thu, dem 04.10.2007, um 18:47:01 +0500 mailte test tester folgendes: > > > On 10/4/07, test tester <[EMAIL PROTECTED]> wrote: > > i have version 8.1 and i want to know how to insert multiple rows in this > version. Please no silly top post. You can insert multiple values with one insert with multiple select and UNION like this example: test=*# truncate foo; TRUNCATE TABLE test=*# select * from foo; w --- (0 rows) test=*# insert into foo select 'foo1' union select 'foo2' union select 'foo3'; INSERT 0 3 test=*# select * from foo; w -- foo1 foo2 foo3 (3 rows) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Error while database creation
am Fri, dem 05.10.2007, um 11:43:09 +0530 mailte Ashish Karalkar folgendes: > Hello All, > > I am getting following error when trying to create new database. > > > createdb: database creation failed: ERROR: could not create directory "base/ > 1923827": No space left on device > can anybody please tell me what is going wrong. Please read the message again and check, if you have space on your hard disk device. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] How to convert rows into HTML columns?
am Fri, dem 05.10.2007, um 8:20:32 +0200 mailte Stefan Schwarzer folgendes: > Before I was used that the yearly values were all to be found in a > single SQL row; now for each year of each country I have a separate > row. How do I convert that into a single (HTML) row again? You can use conditionals like in my simple example: test=*# select * from vals; year | val --+ 1998 | 10.00 1998 | 20.00 1998 | 25.00 1999 | 30.00 1999 | 39.00 2000 | 100.00 2000 | 99.00 2000 | 98.00 (8 rows) test=*# select sum(case when year=1998 then val else 0 end) as "1998", sum(case when year=1999 then val else 0 end) as "1999", sum(case when year=2000 then val else 0 end) as "2000" from vals; 1998 | 1999 | 2000 ---+---+ 55.00 | 69.00 | 297.00 (1 row) > > And the next question coming up is: How should my query look like so > that I can sort the (HTML) table by a specific year in ascending or > descending order? So, that it doesn't display it by the country names > alphabetical order, but by, say 1998? If you have only one row, how would you sort this result? ;-) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] How to convert rows into HTML columns?
am Fri, dem 05.10.2007, um 9:13:10 +0200 mailte Stefan Schwarzer folgendes: > >> > >>And the next question coming up is: How should my query look like so > >>that I can sort the (HTML) table by a specific year in ascending or > >>descending order? So, that it doesn't display it by the country names > >>alphabetical order, but by, say 1998? > > > >If you have only one row, how would you sort this result? ;-) > > With the "old" design I had up to 240 rows - for each country/ > territory one row. Now, I have 240*num_years rows. Maybe i don't understand you. I extend the table: test=# select * from vals ; year | val | country --++- 1998 | 10.00 | a 1999 | 30.00 | a 2000 | 100.00 | a 1998 | 20.00 | b 1999 | 39.00 | b 2000 | 99.00 | b 1998 | 25.00 | c 2000 | 98.00 | c (8 rows) Now, select for every country and every year all in one row (in the example only for 1998 and country a and b, i'm to lazy): test=*# select sum(case when year=1998 and country='a' then val else 0 end) as "1998_a", sum(case when year=1998 and country='b' then val else 0 end) as "1998_b", sum(case when year=1999 then val else 0 end) as "1999", sum(case when year=2000 then val else 0 end) as "2000" from vals; 1998_a | 1998_b | 1999 | 2000 ++---+ 10.00 | 20.00 | 69.00 | 297.00 (1 row) you can see: sort by year and country, and all in one line ;-) Or, one row for every country, ordered by the country's name: test=*# select country, sum(case when year=1998 then val else 0 end) as "1998", sum(case when year=1999 then val else 0 end) as "1999", sum(case when year=2000 then val else 0 end) as "2000" from vals group by country order by country; country | 1998 | 1999 | 2000 -+---+---+ a | 10.00 | 30.00 | 100.00 b | 20.00 | 39.00 | 99.00 c | 25.00 | 0 | 98.00 (3 rows) Btw.: i'm subscribed on the list, please no extra CC to me. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Request: Anyone using bogus / "humorous" X-Message-Flag headers, could we please turn them off
am Fri, dem 05.10.2007, um 10:05:32 -0400 mailte Bill Bartlett folgendes: > Quick request to the group: we have several members who include bogus or > "humorous" X-Message-Flag headers in their email messages. Could I > request that you _please_ turn them off? Because they come through as Do you mean me? > flagged messages in Outlook, it throws off my email rules processing > and the messages end up into the wrong groups. (With the volume of This header is a special Outlook-Feature. If this header realy make problems in _this_ software, then i think, _this_ software is broken. But hey, tell news ;-) And yes: there are any email-software available, without problems. Including Windows. > (Yes, I understand people's personal preferences for not liking Windows > or Outlook or Microsoft, but that isn't going to change the applications > that I need to use for my day-to-day work. Feel free to continue Okay, i like this list and i like the people here. I will try to disable this special header line for this and only this list (okay, for all postgresql-lists). Let me try, i'm not sure how to disable this header-line only for [EMAIL PROTECTED] Please, don't beat me if this header are still in this message, the problem is work in progress... i'm not sure if i change the config properly. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Problem with SELECT
am Mon, dem 08.10.2007, um 3:28:04 -0700 mailte marwis1978 folgendes: > I have a following table > -+ > day | quantity > -+ > > where day is a date and quantity is an integer value. Now I need to > make a SELECT statement on this table which returns me a full > information on a whole month, it means day>='2007-10-01' and day > <'2007-11-01' but if there is no any quantity for some days a need to > have 0 as a result. Assume that I have following records: No problem, generate_series() can do the job. Example: test=*# select * from t1; d | val +- 2007-10-08 | 1 2007-10-13 | 5 (2 rows) test=*# select current_date+s, coalesce(val,0) from generate_series(0,5)s left outer join t1 on(current_date+s = d); ?column? | coalesce +-- 2007-10-08 |1 2007-10-09 |0 2007-10-10 |0 2007-10-11 |0 2007-10-12 |0 2007-10-13 |5 (6 rows) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Request: Anyone using bogus / "humorous" X-Message-Flag headers, could we please turn them off
am Mon, dem 08.10.2007, um 13:05:50 -0400 mailte Bill Bartlett folgendes: > > > (Makes me have to think twice about raising > > > any _real_ issues though, like why my VACUUMs periodically keep > getting > > > into lock contentions with my JDBC connections and ultimately > causing me > > > to have to shut down Postgres w/ "pg_ctl stop -m immediate", or how > to > > > properly handle postmaster errors that don't appear to be documented > > > anywhere except in the source code... [No, it's not the absolutely > > > > most recent version of Postgres; No, I can't upgrade it.]) > > > > This isn't fair, Bill. Your original question concerned posts your > > email client has trouble processing, which isn't the primary > > topic of the list. You also knew that it was somewhat contentious, > given that > > you've made comments such as "Believe me, I'm not defending > > Outlook", so you probably weren't overly surprised at some of the > > responses you got. Also note that the responses you got were attempts > to > > solve your problem: I don't see any that only belittled your choice of > software. > > > > Most people didn't completely read my email and thus unfortunately > completely missed the point, in many cases seemingly because they were > too quick to jump on my use of Outlook as an email client (thus assuming > I was just one of "those" terrible horrible know-nothing Windows users). I can speak only for myself, but when i see damaged mails (with, for instance, broken quoting-line as you can see above, then i think: 'apparently a windoze-user'. I don't need to know the headers. > It's obvious from 3 years of reading these forums that there is a "Linux > Postgres users good, Windows Postgres users bad" bias by many (but not > all) of the members rather than them realizing that " 'we' are all > Postgres users, 'those' other horrible people are MySQL / Oracle / etc No, sorry. We are all PG-users. Nothing else. > Rereading my original email, you can see that the problem I was trying > to address was not my choice of email software but rather that several > people in these Postgres listservs (like other people in other > listservs) were intentionally misusing a specific header flag that is > used by specific email programs (only the various "Outlook"-related Outlook isn't a email-programm, it's a PITA. Nothing else. Okay, maybe a PIM. But not an email-software, for this case it can't work. At least in a useful way. > normally just grit my teeth and ignore it.) After researching this flag > further (after seeing the feedback I got on this forum), I've discovered > that this type of misuse is frequently used, and even recommended on > many Linux-oriented web sites as a means, to annoy Outlook-based users > (as a means to annoy Windows users). As I mentioned above, I think in a > forum such as this, where "we" need to all be Postgres users, I don't You should realize: Postings/Mailings generated with OjE are 'Broken by Design'. I don't need to see the headers to recognize, that the posting/email was generated with this pice of broken software, i can see it in the body. Let me say the same with other words: not the linux-users smells, the posting/emails, generated with M$-software, smells! Thats the point. > (Just for the record, not that I should have to justify my background > and biases [or hopefully lack thereof] to the group: I gave up fighting > platform wars a LOOONG time ago, back when I used to try to get the Again: i don't want a fight (or war) on OS or email-client, but, please, realise: for mailings-list there are good and not-so-good software available. And all M$-shit isn't suitable. Rule of thumb. > Windows servers). I may be using Windows on my desktop, but I don't > think I'd put myself into the category of being merely one of "those > terrible horrible know-nothing Windows users".) Noboday said that. But again, realise, Outlook and OE isn't a good email-software. It's shit, straightforward. > > at least post them and see what kind of response you get, > > rather than judge the list as a whole due to the response you got to > an > > off-topic post. Many of the people on the lists have been here for > years and > > have gotten lots of helpful advice, which is why they've stuck Can you please see this shit of broken lines? This is from YOU! Again: i've cut off the X- - header. But i wish, all users here are using functioning email-software. It makes more easily for all. And again: Outlook and OE isn't a functioning email-software. Obviously. And sorry for my broken english... Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not
Re: [GENERAL] Request: Anyone using bogus / "humorous" X-Message-Flag headers, could we please turn them off
am Mon, dem 08.10.2007, um 12:00:30 -0700 mailte Richard Broersma Jr folgendes: > --- "A. Kretschmer" <[EMAIL PROTECTED]> wrote: > > > > at least post them and see what kind of response you get, > > > > rather than judge the list as a whole due to the response you got to > > > an > > > > off-topic post. Many of the people on the lists have been here for > > > years and > > > > have gotten lots of helpful advice, which is why they've stuck > > > > Can you please see this shit of broken lines? This is from YOU! > > I use yahoo. I guess that yahoo's web-based client must be broken also, since > I've seem email I've > posted has produce broken lines. I am not sure if this was already > mentioned, does anyone know of > non-html windows email clients that work well for this mailing list? As far i can see your mails are correct. But one exclusion: please no CC: to the sender, i'm reading the list. But yes: thats all isn't realy a problem and that has nothing to do with our topic. Please, no war about email-software. We are all PG-users and the idea behind the list is to help together and not to fight against. Peace and end of discussion about this, okay? Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Request: Anyone using bogus / "humorous" X-Message-Flag headers, could we please turn them off
am Mon, dem 08.10.2007, um 15:46:04 -0500 mailte Scott Marlowe folgendes: > Actually CC to the sender is the norm on this list. I believe there Okay, no problem. I'm knowing other lists like the german debian user list and there is this CC to the sender unwanted. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] not work in IE
am Fri, dem 12.10.2007, um 5:06:38 +0100 mailte manju arumugam folgendes: > Hi, > > > In my site when am update the edit user info page its > prperly works in firefox .But not works in IE...But > the updation takes place in the database but the page > is not displayed...Wats the reason? IE is 'broken by design', more i can't say without more informations. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] DROP VIEW lasts forever
am Wed, dem 17.10.2007, um 13:01:25 +0200 mailte Christian Rengstl folgendes: > Hi list, > > Whenever I try dropping a view using DROP VIEW myschema.myview the > query runs forever. The last time I tried was ten minutes ago and the > query is still running even though the view queries just data of one > single table with around 5 fields. > What could be the reason? maybe a look because an other transaction? Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Suggestions for Remote Procedure Calls from PG, please?
am Wed, dem 17.10.2007, um 22:30:26 -0400 mailte Bret Schuhmacher folgendes: > Does anyone else invoke a process on a remote server? How do you do it? You can use any untrusted programming language like pl/perlU or plsh. Other solution: use LISTEN/NOTIFY, see http://www.postgresql.org/docs/8.2/interactive/sql-notify.html Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] PG/Tcl and Expect?
am Tue, dem 16.10.2007, um 21:55:38 -0700 mailte Bret Schuhmacher folgendes: > Does the PG/Tcl interface allow expect scripts? I want to create a stored > procedure that ssh's to another server and runs a Perl script. Expect could > do > it, but can I load the expect module from pgtcl? I think, you need the untrusted version pl/tclU for such tasks. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] "Concatenate" two queries - how?
am Wed, dem 24.10.2007, um 15:08:51 +0200 mailte Stefan Schwarzer folgendes: > Now, I want to enable queries which display national as well as > regional values. I could probably work with independent queries, but > I think it would be "cleaner" and more efficient to get everything > into a single query. > > Can someone give me a hint how this would work? > > Thanks a lot! select ... UNION select ... Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Delete/Update with ORDER BY
am Thu, dem 25.10.2007, um 5:25:14 -0700 mailte Evandro Andersen folgendes: > In Oracle you can use this: > > > > DELETE FROM A WHERE A1 = 10 ORDER BY A2 > > > > There is something in the Postgresql ? Can you explain this a little bit more? I can't see any sense. Either i delete rows with A1=10 or not, but i don't need an order for this. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Query_time SQL as a function w/o creating a new type
am Fri, dem 26.10.2007, um 14:51:52 +0800 mailte Ow Mun Heng folgendes: > > On Fri, 2007-10-26 at 08:35 +0200, Reg Me Please wrote: > > You could try this: > > > > > > CREATE OR REPLACE FUNCTION foo( out procpid integer, out client_addr inet, > > out > > query_time interval, out current_query text ) > > RETURNS SETOF RECORD AS $BODY$ > > ... > > $BODY$ LANGUAGE PLPGSQL VOLATILE; > > > Somehow it doesn't work.. > > CREATE OR REPLACE FUNCTION query_time2( out procpid integer, out > client_addr inet, out > query_time interval, out current_query text ) AS > --RETURNS SETOF RECORD AS > $BODY$ > > BEGIN > SELECT procpid, client_addr, (now() - query_start), > current_query > FROM pg_stat_activity > ORDER BY (now() - query_start) DESC; > RETURN; > END; > $BODY$ > LANGUAGE 'plpgsql' VOLATILE; > > > ERROR: query has no destination for result data > HINT: If you want to discard the results of a SELECT, use PERFORM > instead. > CONTEXT: PL/pgSQL function "query_time2" line 3 at SQL statement Change the SELECT procpid, ... to SELECT into procpid, ... Thats all (i hope)... Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] active connections
am Tue, dem 30.10.2007, um 14:51:33 -0300 mailte João Paulo Zavanela folgendes: > Hi, > > I would like to know how many active connections exist. > Is necessary show the number ip of client. ask pg_stat_activity (select * from pg_stat_activity;) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] how can I shut off "more"?
am Wed, dem 31.10.2007, um 17:14:02 -0400 mailte Gauthier, Dave folgendes: > When I run a query, and the number of lines exceeds what the screen can hold, > the results seem to get piped into ?more? (or ?less?). How can I turn that > off > and just have everything stream out without stopping? I tried ?echo-all, but > that doesn?t seem to do the trick. Read this: http://merlinmoncure.blogspot.com/2007/10/better-psql-with-less.html Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] day of week
am Wed, dem 31.10.2007, um 16:34:44 +0200 mailte Anton Andreev folgendes: > Hi, > > I have records with date column. Is there a way I can get which day of > week this date is? Yes, no problem. select extract (dow from date). Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Would an index benefit select ... order by?
am Sat, dem 03.11.2007, um 11:09:05 +0400 mailte rihad folgendes: > Hi, > > Should an index be used on a created_at timestamp column if you know you > will be using "ORDER BY created_at ASC|DESC" from time to time? Yes. And you should use EXPLAIN. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] reverse strpos?
am Mon, dem 12.11.2007, um 10:54:53 -0500 mailte Gauthier, Dave folgendes: > Is there a function that?ll return the position of the last occurance of a > char > in a string? > > > > For Example, in the string ?abc/def/ghi? I want the position of the 2^nd ?/?. write a function to revert the string and use strpos(). create or replace function rev(varchar) returns varchar as $$ declare _temp varchar; _count int; begin _temp := ''; for _count in reverse length($1)..1 loop _temp := _temp || substring($1 from _count for 1); end loop; return _temp; end; $$ language plpgsql immutable; Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] reverse strpos?
am Mon, dem 12.11.2007, um 8:48:29 -0800 mailte David Fetter folgendes: > > > Is there a function that?ll return the position of the last > > > occurance of a char in a string? > > > > > > > write a function to revert the string and use strpos(). > > > > create or replace function rev(varchar) returns varchar as $$ > > declare > > ... > > $$ language plpgsql immutable; > > > > > > Andreas > > PL/Perl might be easier: > > CREATE OR REPLACE FUNCTION rev(TEXT) > RETURNS TEXT > IMMUTABLE > LANGUAGE plperl > AS $$ > return reverse($_[0]); > $$; heh, the PERL-Guru ;-) > > CREATE OR REPLACE FUNCTION rev(TEXT) > RETURNS TEXT > IMMUTABLE > LANGUAGE SQL > AS $$ > SELECT array_to_string( > ARRAY( > SELECT substr($1,i,1) > FROM generate_series(length($1),1,-1) AS i > ), > '' > ); > $$; Nice. The generate_series()-function are really great. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] how should I do to disable the foreign key in postgres?
am Wed, dem 14.11.2007, um 15:16:48 +0800 mailte froast folgendes: > in mysql, I used :"set foreign_key_check = 0;" to disable the foreign key > check, now I'm trying to migrate from mysql to postgres, how should I do > to disable it? You can define the constraints as deferrable. Later you can say: set constraints all deferred ; Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Trigger problem
am Tue, dem 27.11.2007, um 10:38:09 +0100 mailte Christian Rengstl folgendes: > Hi list, > > act = 'DELETION of row with id: ' || OLD.id; > act = 'UPDATE OF ' || field || ' with id: ' || > ... > INSERT INTO history(aennam, action, table_name) VALUES(current_user, > act, tab); > > Now the problem is that a tuple gets added to the table history, but > the field "action" (whatever the user did) is 99% empty, whereas the > others are filled and I don't see why... > > Any hint is greatly appreciated Maybe sometime the concateneted fields (e.g. field, OLD.id) contains NULL-values. If so, the complete string 'act' will be NULL. Solution: use coalesce(field,''). Hope that helps. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Making a query from 2 tables at same time
am Thu, dem 29.11.2007, um 15:48:45 +0100 mailte Pau Marc Munoz Torres folgendes: > Hi everybody > > I'm doing a two table query as follow > > mhc2db=> select t1.sp, t1.pos,t2.p1, t2.p4, t2.p6, t2.p7, t2.p9 from local as > t1, precalc as t2 where t1.ce='ACIAD' and t2.idr(p1, p4, p6, p7, p9, > 'HLA-DRB5*0101')>2; > > and i get the following error > > ERROR: schema "t2" does not exist > > but those tables exists!! and are as follow!! The error-message means the function-call t2.idr(...), this is wrong. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Moving lock file (/tmp/.s.PGSQL.)
am Fri, dem 30.11.2007, um 5:22:34 -0500 mailte Madison Kelly folgendes: > Hi all, > > If there a ./configure switch (or config file/command line switch) to > tell postgresql to put the lock file '.s.PGSQL..lock' and socket > '.s.PGSQL.' in a different directory? > > Thanks all! Option unix_socket_directory in file postgresql.conf. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: Fwd: Re: [GENERAL] Archiving problem on Windows
am Tue, dem 04.12.2007, um 11:31:41 +0100 mailte Christian Rengstl folgendes: > When I login as user postgres, I can copy the files without any > problem. > Here is the error message (translated as it appears in German in my log > files): > >>copy pg_xlog\myfile C:\Archive\DBArchive\myfile<< failed: error code > 1 > >>copy pg_xlog\myfile C:\Archive\DBArchive\myfile<< failed: error code > 1 > >>copy pg_xlog\myfile C:\Archive\DBArchive\myfile<< failed: error code > 1 > WARNING: transaction log file could not be archived: too many errors 2 questions: 1. really 'myfile', or is 'myfile' only a placeholder for the real logfile? 2. please try to specify the full path for the source file, maybe the copy-command can't found the file without the full path. Hope that helps, and please no top-posting. (in german: TOFU) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Archiving problem on Windows
am Mon, dem 03.12.2007, um 12:29:39 +0100 mailte Christian Rengstl folgendes: > Hi list, > > a have a problem using the following archiving command on windows: > 'copy %p C:\\Archive\\DBArchive\\%f' According the doc, the command should be: archive_command = 'copy "%p" /mnt/server/archivedir/"%f"' # Windows Perhaps missing "" around the parameters %p and %f, AND/OR wrong path for the destination (\\ instead /), i'm not sure, i'm not familiar with PG under Windows. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Archiving problem on Windows
am Tue, dem 04.12.2007, um 12:05:41 +0100 mailte Christian Rengstl folgendes: > >> a have a problem using the following archiving command on windows: > >> 'copy %p C:\\Archive\\DBArchive\\%f' > > > > According the doc, the command should be: > > > > archive_command = 'copy "%p" /mnt/server/archivedir/"%f"' # Windows > > > > > > Perhaps missing "" around the parameters %p and %f, AND/OR wrong path > for > > the destination (\\ instead /), i'm not sure, i'm not familiar with > PG > > under Windows. > > I read that part of the docs, too, and changed it accordingly, but > without any success. The propper config-file? (compare with the output from the command "show config_file;" within psql), reload/restart the server? Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] SQL Query
am Wed, dem 05.12.2007, um 10:24:04 + mailte Ashish Karalkar folgendes: > Hello List member, > > Iha a table containing two columns x and y . for single value of x there are > multiple values in y e.g > > X Y > > 1 ABC > 2 PQR > 3 XYZ > 4 LMN > 1 LMN > 2 XYZ > > I want a query that will give me following output > > 1 ABC:LMN > 2 PQR:XYZ > 3 XYZ > 4 LMN > > Any help will be really helpful You need a new aggregate-function. A solution for a similar problem (but with comma instead :) can you find here: http://www.zigo.dhs.org/postgresql/#comma_aggregate Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Problem with joining two tables
am Wed, dem 05.12.2007, um 14:42:32 +0100 mailte Przemyslaw Bojczuk folgendes: > Hello! > > I have a problem joining two tables. I tried various types of join and > none seems to work as I expect > > Table 1: > > id | stuff > --- > 1 | sth1 > 2 | sth2 > 3 | sth3 > 4 | sth4 > 5 | sth5 > .. | ... > > Table 2: > > id | desc | etc > -- > 1 | desc1 | etc1 > 2 | desc2 | etc2 > 2 | desc3 | etc3 > 2 | desc4 | etc4 > 3 | desc5 | etc5 > | desc6 | etc6 > 5 | desc7 | etc7 > .. | ... | ... > > I need something like: > > id | stuff | desc | etc > - > 1 | sth1 | desc1 | etc1 > 2 | sth2 | desc2 | etc2 > 2 | sth2 | desc3 | etc3 > 2 | sth2 | desc4 | etc4 > 3 | sth3 | desc5 | etc5 > 5 | sth5 | desc7 | etc7 > Okay, let's try: first i create your tables like above: test=*# select * from t1; id | stuff +--- 1 | sth1 2 | sth2 3 | sth3 4 | sth4 5 | sth5 (5 rows) test=*# select * from t2; id | des | etc +---+-- 1 | desc1 | etc1 2 | desc2 | etc2 2 | desc3 | etc3 2 | desc4 | etc4 3 | desc5 | etc5 | desc6 | etc6 5 | desc7 | etc7 (7 rows) And now: test=*# select t1.id, t1.stuff, t2.des, t2.etc from t1, t2 where t1.id=t2.id; id | stuff | des | etc +---+---+-- 1 | sth1 | desc1 | etc1 2 | sth2 | desc2 | etc2 2 | sth2 | desc3 | etc3 2 | sth2 | desc4 | etc4 3 | sth3 | desc5 | etc5 5 | sth5 | desc7 | etc7 (6 rows) is this your expected result? Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] SQL Query
am Wed, dem 05.12.2007, um 3:46:26 -0800 mailte David Fetter folgendes: > Use the array_accum aggregate from the docs as follows: > > SELECT x, array_to_string(array_accum(y),':') > FROM your_table > GROUP BY x; Yes, no noubt a better solution as my new aggregat... Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] SQL Query
am Wed, dem 05.12.2007, um 10:47:44 + mailte Ashish Karalkar folgendes: > > X Y > > > > 1 ABC > > 2 PQR > > 3 XYZ > > 4 LMN > > 1 LMN > > 2 XYZ > > > > I want a query that will give me following output > > > > 1 ABC:LMN > > 2 PQR:XYZ > > 3 XYZ > > 4 LMN > > > > Any help will be really helpful > > You need a new aggregate-function. A solution for a similar problem (but > with comma instead :) can you find here: > http://www.zigo.dhs.org/postgresql/#comma_aggregate > > Thanks Andreas for your replay. > But i don't have an option two send argument to the store proc nither do i > know how many multiple records are there for a single X. I want result for > all rows of table. > > I dont thnink that function will give desired output. test=# create table Ashish ( x int, y text); CREATE TABLE test=*# copy ashish from stdin; Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself. >> 1abc >> 2pqr >> 3yxz >> 4lmn >> 1lmn >> 2xyz >> \. test=*# CREATE FUNCTION my_aggregate(text,text) RETURNS text AS ' SELECT CASE WHEN $1 <> THEN $1 || '':'' || $2 ELSE $2 END; ' LANGUAGE sql IMMUTABLE STRICT; CREATE AGGREGATE my_comma (basetype=text, sfunc=my_aggregate , stype=text, initcond='' ); CREATE FUNCTION CREATE AGGREGATE test=*# select x, my_comma(y) from ashish group by x; x | my_comma ---+-- 4 | lmn 3 | yxz 2 | pqr:xyz 1 | abc:lmn (4 rows) Okay, i forgot to sort and the chars are in lower case... Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] simple update on boolean
am Thu, dem 06.12.2007, um 10:25:26 +0100 mailte Cedric Boudin folgendes: > Most honourable members of the list, > > this is a simple one, but I can't find the solution ( probably a > forest/tree problem). > > update table set bolean_column = set_it_to_its_inverse where fk = > some_value; test=# create table forrest (id int, b bool); CREATE TABLE test=*# insert into forrest values (1,'f'::bool); INSERT 0 1 test=*# insert into forrest values (2,'t'::bool); INSERT 0 1 test=*# update forrest set b = case when b then 'f'::bool else 't'::bool end; UPDATE 2 test=*# select * from forrest ; id | b +--- 1 | t 2 | f (2 rows) Okay? Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] how to redirect output to a file
am Tue, dem 04.12.2007, um 20:19:29 -0800 mailte pc folgendes: > Hi, > > How to redirect the output of an sql command to a file? > Thanks in advance within psql you can use \o , from the shell you can use this: [EMAIL PROTECTED]:~$ echo "select now()" | psql test > now.txt [EMAIL PROTECTED]:~$ cat now.txt now --- 2007-12-06 14:21:58.963405+01 (1 row) Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Error in creating function
am Thu, dem 06.12.2007, um 4:16:14 -0800 mailte Yancho folgendes: > I am trying to create a function, which takes the nearest 3 hospitals > to a point making use of a PostGIS function), and then check each > hospital for the exact distance on roads (by making use of a pgRouting > function). > > Below please find a copy of my function, and u can also find it > highlighted here : http://yancho.pastebin.com/f13cc045e > > CREATE OR REPLACE FUNCTION near_hospital(text, integer, integer) > RETURNS integer AS > $BODY$ > ... > END; > > ' language 'plpgsql'; > > > The error being given by pgAdminIII is : unterminated dollar-quoted > string at or near "$BODY$" [then some garbled text] for $1; > > Any help will be extremely appreciated! change the last line into: $BODY$ language 'plpgsql'; Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Restore problem
am Mon, dem 10.12.2007, um 12:30:14 -0800 mailte Keith Turner folgendes: > Hi first post here, I hope you can help. > > We are running 8.1 on Windows 2003 server and have had a server crash > over the weekend. A virus is suspected - we maintain an app server on Please don't hijack other threads, the original thread was 'TIMESTAMP difference'. (don't answer to an arbitrary other mail and change the subject. Every mail contains references-header) > Are there step by step instructions on restoring from the folder itself > instead of a backup file? Is it even possible? not really... Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Hijack!
am Tue, dem 11.12.2007, um 14:57:57 + mailte Richard Huxton folgendes: > Keith Turner wrote: > >Someone scolding wrote: I wrote this ;-) > > > >Please don't hijack other threads, the original thread was 'TIMESTAMP > >difference'. > > I think it was probably intended as a *gentle* scolding. We try to be as Yes, of course. It was not my intention to displease someone. > polite as possible on the PG lists. Particularly important given their > international nature of course. I'd like this PG lists. I know, my english is very ugly because it isn't my native language. But PG is a really great Open Source Project and it has a really large and userfriendly communitity. And, of course, i can learn more about english and PG and i wish to help others if i can. > It's one of those "common knowledge" things that are obvious to everyone > who's done it once themselves. It's just part of the nature of how email > works. Google around "mailing list etiquette" and you should see plenty > of guidelines. Right. There are other hints, for instance all about top-posting style. If i search the archive and read answers and i see (i read normally from top to bottom) first the answer and later the question, so this is hard to understand. The rules for mailing lists etiquette are useful and i wish, more people would follow this rules. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] very slow query
am Wed, dem 12.12.2007, um 11:44:58 + mailte Ashish Karalkar folgendes:
> Hi,
> I am having PostgreSQL 8.2.4 on Suse 10.3
>
> Server is not using the index insted it chooses to take seq scan path. table
> is
> having @ 120 million rows
>
> here is the output from planner:
> Nested Loop IN Join (cost=0.00..5030217.97 rows=2 width=106)
>-> Seq Scan on sms_new (cost=0.00..5027902.00 rows=384 width=106)
> Filter: (mobile = 919820920858::bigint)
>-> Index Scan using deliveryid_pkey on delivery (cost=0.00..6.02 rows=1
> width=8)
> Index Cond: ("outer".deliveryid = delivery.deliveryid)
> Filter: ((createddate > '2007-12-11 00:00:00'::timestamp without time
> zone) AND (createddate < '2007-12-11 21:00:00'::timestamp without time zone)
> AND ((keyword)::text = 'CRI'::text))
>
> column deliveryid has an index over it in sms_new table.
>
> can anybody please guide to force the usage of index
It's using an index on "deliveryid":
Index Scan using deliveryid_pkey
Do you have an index on "mobile"? Can you show us the table definition
and the output from EXPLAIN ANALYSE?
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] very slow query
am Wed, dem 12.12.2007, um 12:25:20 + mailte Ashish Karalkar folgendes: > > here is the output from planner: > > Nested Loop IN Join (cost=0.00..5030217.97 rows=2 width=106) > > -> Seq Scan on sms_new (cost=0.00..5027902.00 rows=384 width=106) > > Filter: (mobile = 919820920858::bigint) > > Do you have an index on "mobile"? Can you show us the table definition > and the output from EXPLAIN ANALYSE? > > no index on mobile Seq Scan on sms_new ... Filter: (mobile = 919820920858::bigint) (cost=0.00..5027902.00 Can you see the problem? Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] very slow query
am Wed, dem 12.12.2007, um 12:44:09 + mailte Ashish Karalkar folgendes: > > Do you have an index on "mobile"? Can you show us the table definition > > and the output from EXPLAIN ANALYSE? > > > > no index on mobile > > Seq Scan on sms_new ... Filter: (mobile = 919820920858::bigint) > (cost=0.00..5027902.00 > > > Can you see the problem? > > So i will have to create index on mobile is taht so? Try it. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] executing a procedure with delay
am Wed, dem 12.12.2007, um 14:43:55 +0100 mailte Pavel Stehule folgendes: > Hello > > you can use pg_sleep function. But using it in trigger is ugly, > because transaction stay in open state. Look to orafce on intersession > communication. Maybe you can use it. other solution (vaguely): LISTEN/NOFIFY and an external prozess. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] data type change on a view
Hello @all, i have a question (rot really for myself, a member of ther german forum asks): i have two tables, contains a varchar(N)-column. Now i create a VIEW based on this tables. The resulting view contains now a varchar without length. How can i prevent this? How can i force that the column in the view contains the *exact* typ? Example: test=# create table h1 (t varchar(8)); CREATE TABLE test=*# create table h2 (t varchar(8)); CREATE TABLE test=*# create view h as select t from h1 union all select t from h2; CREATE VIEW test=*# \d h View "public.h" Column | Type| Modifiers +---+--- t | character varying | thx, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Password as a command line argument to createuser
am Tue, dem 18.12.2007, um 22:04:13 -0800 mailte Jane Ren folgendes: > Hi, > > I need to write a script that creates a new user with a password > automatically. > > Is there a way I can specify the password as a command line argument to > createuser? >From a unix shell? You can call psql with -c "your command". Try this: psql -U ... database -c "create user foo password 'secret';" Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Quick Regex Question
am Thu, dem 20.12.2007, um 10:36:08 + mailte Howard Cole folgendes: > Your expression works fine Richard, as does '(^| )ho', but can you tell > me why '[ ^]ho' doesn't work? With ^ you means an anchor, but within the brackets it's a simple char. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Quick Regex Question
am Thu, dem 20.12.2007, um 12:03:57 +0100 mailte Martijn van Oosterhout folgendes: > On Thu, Dec 20, 2007 at 11:51:34AM +0100, A. Kretschmer wrote: > > am Thu, dem 20.12.2007, um 10:36:08 + mailte Howard Cole folgendes: > > > Your expression works fine Richard, as does '(^| )ho', but can you tell > > > me why '[ ^]ho' doesn't work? > > > > With ^ you means an anchor, but within the brackets it's a simple char. > > Err no, it inverts the test. [^ ] means any character *except* a space. I know, but only if the ^ at the beginning, or no? Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] postgresql long text column
am Wed, dem 26.12.2007, um 10:08:13 -0500 mailte Josh Harrison folgendes: > Hi > I have a question about postgres long text column values. > How does it handles these long text column values? Does it put all the long > text columns values from all the tables in one single place or separately? PG use a TOAST-called technique. http://www.postgresql.org/docs/8.2/interactive/storage-toast.html Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] quick question abt pg_dump and restore
am Wed, dem 09.01.2008, um 9:02:23 -0500 mailte Josh Harrison folgendes: > Hi, > When restoring the pg_dumped data thro psql does the rows of the table are > restored in the same order? ie for example if > Table A has rows r1,r2,r3,r4,r5 in this order, then if I pg_dump and restore > it > to another database, will it have the rows in the same order r1,r2,r3,r4,r5? No. If you need an order than you need an ORDER BY in the SELECT-Statement. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 6: explain analyze is your friend
