[GENERAL] Backslashes in data in version 8.1.2

have found to change data with backslashes in it is something like the following: update backtest set field = replace(field,'\\','') where field ~ E'\\134'; UPDATE 1 uscf= select * from backtest; select * from backtest; field - ABCDEFG ABCDEFG (2 rows) -- Mike Nolan

Re: [GENERAL] Backslashes in data in version 8.1.2

~ ''; field --- (0 rows) select * from backtest where field like '%%'; field --- (0 rows) Could this be a locale issue? The one where it does not work uses the C locale, the others use the default locale, en_US.UTF-8. -- Mike Nolan ---(end of broadcast

Re: [GENERAL] Backslashes in data in version 8.1.2

PHP app, whatever changed is apparently affecting both Postgres and PHP. Any ideas? Some kind of library issue maybe, such as readline? -- Mike Nolan ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate

Re: [GENERAL] Number of rows of a table

Using psql how can I ask postgresql to show the actual number of rows of a table? What do you mean by 'actual number of rows'? Is there a reason you can't just do: select count(*) from this_table: -- Mike Nolan ---(end of broadcast

Re: [pgsql-advocacy] [GENERAL] Oracle buys Innobase

probably have the upper hand wielding them. -- Mike Nolan ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org

Re: [GENERAL] Oracle buys Innobase

? -- Mike Nolan ---(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] Help with inventory control

functions. -- Mike Nolan ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [GENERAL] Data Entry Tool for PostgreSQL

and in training. -- Mike Nolan ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Data Entry Tool for PostgreSQL

it's pretty robust, though it needs some major cleanup work and doesn't have a lot of documentation yet. Contact me about your specific needs, if you're willing to be a pre-beta tester, I may be able to put something together for you. -- Mike Nolan Tailored Software Service, Inc. [EMAIL PROTECTED

Re: [GENERAL] Duplicate Values or Not?!

return zero... I disagree. Someone who wants true case independence (for whatever reason) needs all aspects of uniqueness such as selects, indexes and groups treating data the same way. This needs to be something the person who creates the instance or the database can control. -- Mike Nolan

[GENERAL] Followup to week truncation thread

+ interval '1 day') - interval '1 day')::date as week, count(*) as tot from trantable group by 1 order by 1; -- Mike Nolan ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

[GENERAL] Dumb question about 8.1 beta test

release?) -- Mike Nolan ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org

[GENERAL] TG_OP and undefined OLD values

separating the code into two independent if statements, duplicating the action statements? -- Mike Nolan ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

[GENERAL] Is there such a thing as a 'background database job'?

updates. I suppose I could 'nice' the program itself, but would that have any positive impact? -- Mike Nolan ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [GENERAL] Generating random values.

,^^2v12hqIwLbvCQQQi18152 Do not write it down or save it in a password manager, as doing so creates security problems. -- Mike Nolan ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Generating random values.

there are probably a few 'worst' answers. :-) Once someone has established a password scheme, either randomly generated or user selected, it should not be that difficult to write routines to generate acceptable passwords or to enforce standards for user-generated passwords. -- Mike Nolan

Re: [GENERAL] triggers/functions across databases

If you have databases that are dependent on each others data you should probably move those databases into a new schema within one database... That's a non-trivial task, especially if some of the tables in the two databases have the same name. -- Mike Nolan ---(end

Re: [GENERAL] need trigger help

'; create trigger my_trig before insert or update on my_tablename for each row execute procedure public.my_trigger(); The 'RETURN NEW' part is very important, without it your hash field won't get updated at all. -- Mike Nolan ---(end of broadcast

Re: [GENERAL] getting the ranks of items

, you'd need some way of passing the function the ranking criteria with persistence between calls, which might have some startup issues. Wouldn't that also cause problems with multiple users calling the function simultaneously? -- Mike Nolan ---(end of broadcast

Re: [GENERAL] Tuning queries inside a function

Mike Nolan wrote: select * from foo('bar','debug') But how do I do that inside a pl/pgsql function? 'select into' doesn't seem to work properly. You would have to code it. For example: IF $2 = ''debug'' THEN: That part I get, but I cannot seem to get an 'explain select

Re: [GENERAL] Tuning queries inside a function

to redefine the database to cut back on the number of queries inside the function. -- Mike Nolan ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

[GENERAL] Tuning queries inside a function

be tuned further, but 'explain' doesn't really tell much about what's happening inside the function. Any ideas on how to tune a user function? -- Mike Nolan ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] Tuning queries inside a function

to the nearest second, which may not be a fine enough time interval. -- Mike Nolan ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

Re: [GENERAL] Days in month query

) + interval ''1 month'' - interval ''1 day''; return wk_day; END ' language 'plpgsql'; -- Mike Nolan ---(end of broadcast)--- TIP 8: explain analyze is your friend

[GENERAL] Tablespaces and indexes

to version 8, but that may not happen for a couple of months. Are there any plans to have a separate 'default index tablespace' parameter? -- Mike Nolan ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send

Re: [GENERAL] checking SQL statement/subexpression validity

I need to check whether a SQL subexpression (to be used in WHERE clause), e.g.: I've never tested it from Perl, but could you use 'explain select' to see if it parses? It won't actually execute it if it does. -- Mike Nolan ---(end of broadcast

Re: [GENERAL] Is there a peer-to-peer server solution with PG?

office plus exixting outside traffic from our public website. That's one of those things you just don't know will work until you have it built, so I'm looking for other options now while I have time to consider them. Once I get on-site in two weeks it'll a lot more hectic. -- Mike Nolan

[GENERAL] Is there a peer-to-peer server solution with PG?

data connections between the two offices starting next week, but I was wonding if there is a good peer-to-peer option for PostgreSQL at this time. As I understand Slony, it is master-slave only. -- Mike Nolan ---(end of broadcast)--- TIP 1: subscribe

Re: [GENERAL] Is there a peer-to-peer server solution with PG?

/Slave is probably an acceptable solution, I was just wondering if there was a multi-master one available yet. -- Mike Nolan ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command

Re: [GENERAL] table name restiction

at the syntax level or the data level seems to be one that brings out nearly religous ferver when 'discussed'. -- Mike Nolan ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command

Re: [GENERAL] Data entry - forms design or other APIs etc. - what is there?

source community. While it was written (in PHP) with PostgreSQL in mind, I've already used it with limited sucess with other database back ends, specifically MySQL and Oracle. I think it should be possible to make it work with any database for which there is a PEAR implementation in PHP. -- Mike

Re: [GENERAL] Postgresql website issues.

questionable IMHO. When combined with the reduced type size some things are so faint they're unreadable. -- Mike Nolan ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [GENERAL] Important Info on comp.databases.postgresql.general

be interesting. If it isn't already in mailman, it would be an interesting option to add to mailman's web interface to give subscribers the option to include or exclude posts being gatewayed from USENET. (I could use that feature on some lists I run.) -- Mike Nolan ---(end

Re: [GENERAL] OID's

all wraparound issues would probably mean going to a 64 bit field, which would certainly be a non-trivial task. -- Mike Nolan ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [GENERAL] How do I recover from pg_xlog/0000000000000000 (log

contained no data (a guy with OpenACS or something). Also, if you don't routinely test your backups every now and then, how can you be sure they'll work when you NEED them to? -- Mike Nolan ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] dealing with invalid date

want to fix before converting it to a date. You can also do that in perl or PHP or whatever it is you're writing the user interface in. Making the decision of when and how to do that is a large part what I consider my 'value added' role in designing a database system for a client. -- Mike Nolan

Re: [GENERAL] two digit years in inserts

/2004. In general, I think that date conversion decisions are an application design issue, not a database system issue, and the fewer such decisions that are made by the database, the better. -- Mike Nolan ---(end of broadcast)--- TIP 7: don't

Re: [GENERAL] SSL connection between PHP4 PostgreSQL ???

of one that works for me: DB::connect(pgsql://foobar:[EMAIL PROTECTED]/dbnm?requiressl=true); -- Mike Nolan ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do

Re: [GENERAL] guaranteeing that a sequence never skips (fwd)

not much chance of anything else causing a rollback. -- Mike Nolan ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [GENERAL] guaranteeing that a sequence never skips (fwd)

, so a sequence works fine. One and only one program assigns the actual check numbers--in a separate column. That's the sort of thing that most commercial packages do, even though it seems clumsy and adds an extra step, and that's why they do it that way, too. -- Mike Nolan

[GENERAL] Accessing an array element from a function

how it's supposed to work, is that documented somewhere? -- Mike Nolan ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])

Re: [GENERAL] Import an Excel table to a Postgresql one

How to import an Excel table into a Postgresql table in a simple way? Another way is to save the Excel table as a dBase file and import it using the dbf2pg utility in the contrib/dbase directory. -- Mike Nolan ---(end of broadcast)--- TIP 9

[GENERAL] 'order by' in an insert into command

by clause in the insert is being ignored or because the sequence is incrememted before the sort takes place. Is there a way to do this insert? -- Mike Nolan ---(end of broadcast)--- TIP 6: Have you searched our list archives? http

Re: [GENERAL] postgres on in the internet

to punch a hole through your firewall? Can you do a a combination of A and B? (Does that make much sense?) You should also consider blocking all IP addresses other than the client nodes at the firewall. That won't help much if the client node gets compromised. -- Mike Nolan

Re: [GENERAL] Single Row Table?

is doing an insert when it doesn't need to, then the application is flawed as well. -- Mike Nolan ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so

Re: [GENERAL] Single Row Table?

But should you also prevent DELETE's from that table? Otherwise you could wind up with no rows at all. I guess that would have to be done using a rule... Why not just revoke the delete privilege? -- Mike Nolan ---(end of broadcast)--- TIP 1

[GENERAL] restoring a .dmp file to another table name

in it that would get messed up if I were to try to extract the DDL leading up to the COPY statement using head and tail statements to change the table name. -- Mike Nolan ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] Before/After trigger sequencing questiont

a pretty sophisticated example of what PG can do. -- Mike Nolan ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get

Re: [GENERAL] Trigger on Postgres for tables syncronization

I want if my program inserted, updated, deleted the record of appointment than the postgres does the syncronization to the corresponded tables (appointment0 or appointment1 or both). Is there a reason you aren't doing this with views? -- Mike Nolan ---(end

Re: [GENERAL] Run a unix or perl command in a trigger

it uses 'untrusted' perl. It creates a security hole in that anyone who has write access to the postgres user home directory can run ANYTHING. -- Mike Nolan create or replace function submit_batch(varchar, varchar) returns varchar security invoker as ' # perl body goes here # parameters: user

[GENERAL] Queries not always using index on timestamp search

though 'current_date-3' produces the same date as the hard-coded one in the first search. Is there a way to get the second query to use the index? -- Mike Nolan ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] Running Totals and other stuff....

column. The best long term solution, IMHO, would be to change postgres so that it has a unique system column for each record, like Oracle does. -- Mike Nolan ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] [PERFORM] Trigger Function

that converts columns to something you can store in a common log table. (I've not found a way to do this without inserting one row for each column being logged, though.) -- Mike Nolan ---(end of broadcast)--- TIP 7: don't forget to increase

Re: [GENERAL] Data change logs

is that because this is done at the trigger level, the user does NOT have to have any access to the log table, the trigger can use SECURITY DEFINER. That way you get full control over who can even look at the log. -- Mike Nolan ---(end of broadcast)--- TIP 7

Re: [GENERAL] Data change logs

insert into _log select * from where keyfield = NEW.keyfield; Oops, that should be OLD.keyfield. -- Mike Nolan ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister

Re: [GENERAL] security question

privileges for both local and remote (networked) users, as do most major database platforms. If you are a DBA, you need to be aware of these, most of them are fairly well documented in the online manual on postgresql.org. Specifically read the sections on GRANT and on hba_conf. -- Mike Nolan

Re: [GENERAL] Listing databases

psql. -- Mike Nolan ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

Re: [GENERAL] What is wrong here?

a bit of detective work to nail down. Are you using schemas? If so, is that table in the public schema or in a user schema? Is the function in the public schema or in a user schema? -- Mike Nolan ---(end of broadcast)--- TIP 2: you can get off all

Re: [GENERAL] PL/SQL question

your 'return null' is being handled, and that can be related to when the trigger fires. -- Mike Nolan ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [GENERAL] Data Encryption in PostgreSQL, and a Tutorial.

for credit card data IMHO, but I don't think pgcrypto includes one.) -- Mike Nolan ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message

Re: [GENERAL] Select/Group by/Order by question

reports with data labeled 'ignore this column'. :-) With Tom's help, I found a solution. -- Mike Nolan ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL

Re: [GENERAL] Data Encryption in PostgreSQL, and a Tutorial.6

encrypt/decrypt, because I don't think it supports public/private key encryption. -- Mike Nolan ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] Can we have time based triggers in Postgresql??

file or to use a 'trusted' username that could also be a major security hole. Also, a script-based job can be changed or deleted by someone with the right file permissions even though they may not have database permissions, and vice versa. -- Mike Nolan ---(end

Re: [GENERAL] PG vs MySQL

, if not from ours. -- Mike Nolan ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] PG vs MySQL

command should only list users authorized for the current database (and perhaps only superusers should get even that much information), etc. Perhaps it is possible to set PG to do this, but that should probably be the default. -- Mike Nolan ---(end of broadcast

Re: [GENERAL] PG vs MySQL

circumstances under which postmaster might not be running when that information is needed. -- Mike Nolan ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED

Re: [GENERAL] PG vs MySQL

the pg_hba.conf method will scale. What happens if there are hundreds of client databases or thousands of entries in pg_hba.conf? -- Mike Nolan ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

[GENERAL] case statement as inline function?

the specification phase, which may happen every few days. However, the columns referred to in the case statement can change too. Is there a way to pass the entire set of columns in a table to a function? -- Mike Nolan ---(end of broadcast)--- TIP

Re: [GENERAL] case statement as inline function?

as mailtype, memname from master where mailtype is not null; I think it may inline it as an optimization step, I don't know of any way to shortcut how to write it. (I'm running 7.4.1.) -- Mike Nolan ---(end of broadcast)--- TIP 1: subscribe

Re: [GENERAL] Trigger loop question

Mike Nolan [EMAIL PROTECTED] writes: If I set up an on update trigger for table 'A' that updates the corresponding column in table 'B', and one for table 'B' that updates the corresponding column in table 'A', does that create an endless loop? Yes. You could break the loop perhaps

Re: [GENERAL] Trigger loop question

weird question. If in the trigger for table A I have more than one statement that updates table B, or if more than one trigger procedure updates table B, does that cause multiple firings of either before or after update triggers on table B? -- Mike Nolan ---(end

Re: [GENERAL] Differences between postgres and mysql

so it goes away immediately upon exit and some of the ways \o works, I'm used to using both features in Oracle to provide a historical trail of my work), mysql can't even repeat a command (\g) without first re-editing it. -- Mike Nolan ---(end of broadcast

Re: [GENERAL] psql, 7.4, and the \d command

need or want that information. I would only display the host information if psql was called with -h. -- Mike Nolan ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

[GENERAL] CRM Academic Research Request

I received the following note on another database-oriented list. This may be something that pg users could help in, though I'll also pass on the caveat that the other list had, that I have no direct knowledge about the institution or the researchers. -- Mike Nolan

[GENERAL] Parsing bug?

In the following query the field 'memid' is varchar(8). Is the error message below a bug? select substr(memid,1,1) as memtp, substr(memid,2,4) as newx from memmast group by memtp, newx ERROR: column memmast.memid must appear in the GROUP BY clause or be used in an aggregate function -- Mike

Re: [GENERAL] Parsing bug?

group by newx FAIL: select substr(memid,1,1) as memtp, substr(memid,2,4) as newx, count(*) from memmast group by memtp, newx -- Mike Nolan ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http

Re: [GENERAL] Parsing bug?

Works for me in every branch back to 7.1 ... what version are you using? 7.4.1, but I figured out what I did wrong. The alias for the first column turns out to be the same as the name of another column in the table. -- Mike Nolan ---(end of broadcast

Re: [GENERAL] Schemas not created on restore

use pg_dump and pg_dumpall from it? (I get the same 'undefined symbol: get_progname' message when I try that.) -- Mike Nolan ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

[GENERAL] 7.4, 'group by' default ordering?

unrelated topic, does the SQL standard indicate whether NULL should sort to the front or the back? Is there a way to force it to one or the other independent of whether the order by clause uses ascending or descending order? -- Mike Nolan ---(end of broadcast

Re: [GENERAL] order by is ambiguous

column heading with what the back end uses as a default column name. '?column?' would probably not meet SQL standards. -- Mike Nolan ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister

Re: [GENERAL] GetLastInsertID ?

was doing some testing of a PHP module that is called from a web form a while back and got inconsistent results with currval, I probably had a script error of some kind. -- Mike Nolan ---(end of broadcast)--- TIP 9: the planner will ignore your desire

Re: [GENERAL] 'like' refuses to use an index???

Mike Nolan [EMAIL PROTECTED] writes: However, I think RH always sets the LANG environmental variable, so that's going to be picked up by initdb, which means that the C locale will NOT be used unless specifically asked for. Other OS packages may also force the choice of a default LANG

Re: [GENERAL] between

Manual, but I cheated by looking it up in the index. There are index entries for 'BETWEEN' in Practical PosgreSQL and Managing Using MySQL, too. Sometimes books are still better than online docs. :-) -- Mike Nolan ---(end of broadcast)--- TIP 6: Have

Re: [GENERAL] between

and add suitable entries ... I may look into it after the first of the year, though I'm likely to propose something more sweeping than that. -- Mike Nolan ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan

Re: [GENERAL] hardware requirements under Redhat

(including budget). Were you told why that was the wrong choice? -- Mike Nolan ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])

Re: [GENERAL] hardware requirements under Redhat (fwd)

it to use the full 1GB. That was under RH 8, though. - Mike Nolan ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] tablespaces in 7.5?

. I've lost touch with the rest of the members in that group, though, since the computer I was using for PG development purposes got zapped by lightning in August. -- Mike Nolan ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go

[GENERAL] Any commercial shopping cart packages using postgresql?

under PostgreSQL? I found one web store package in the pgsql project archives, but it looks like it may need a lot of tinkering to get it working. -- Mike Nolan ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index