Re: [GENERAL] Backslashes in data in version 8.1.2

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

Re: [GENERAL] Backslashes in data in version 8.1.2

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

[GENERAL] Backslashes in data in version 8.1.2

backtest where field ~ E'\\134'; select * from backtest where field ~ E'\\134'; field -- ABC\DEFG (1 row) So far the only way I have found to change data with backslashes in it is something like the following: update backtest set field = replace(field,'\

Re: [GENERAL] update trigger not working

infinite loop? You need to do this in a 'before-update' trigger and set NEW.timestamp, then return NEW. -- Mike Nolan ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

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 ---

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

me very dubious software patents, and a deep pockets patent holder would 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

essons to be learned from this with regards to PostgreSQL? -- 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

nactivity timeout on the web form, as I recall. You should be able to do most of this with trigger 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

a BUNCH of time both in development 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

a client's office since March of 2004, so 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

Re: [GENERAL] Duplicate Values or Not?!

that strcoll should return 0 only if strcmp > would also 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 cre

[GENERAL] Followup to week truncation thread

week',mtrantime + 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

d the beta 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

is false or when the earlier if statement is true? Is there a way around this other than 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'?

that does 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.

choices, though 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

Re: [GENERAL] Generating random values.

jiwkjIJiwkjikmkq,^^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.

rds then let the user pick one. I often screen the output so that I don't get passwords like this one: pissbum560 -- Mike Nolan ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

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

Re: [GENERAL] table synonyms

ed on user-specific criteria (eg, restricting salesman 'X' to only his accounts in the customer master table), yes, synonyms would be nice. But if you really want them, become part of the development effort. -- Mike Nolan ---(end of broadcast)--

Re: [GENERAL] need trigger help

' language 'plpgsql'; 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 ---

Re: [GENERAL] getting the ranks of items

uld use a plPerl function. To do it with ties, 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

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 exa

Re: [GENERAL] Tuning queries inside a function

I can't seem to achieve inside a function. I think in the long run I may have 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

Re: [GENERAL] Tuning queries inside a function

nfig file, but it only gives the time 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

[GENERAL] Tuning queries inside a function

the function can 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] Days in month query

$1; declare wk_day date; BEGIN wk_day := date_trunc(''month'', this_day) + 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

4.5 system will be upgraded 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 on

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 --

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

th to support it plus the traffic from the remote 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

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

al transfer plan unfolds. Master/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 appropriat

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

ecent 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: subs

Re: [GENERAL] table name restiction

ssue of case-sensitivity either 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 a

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

a PEAR implementation in PHP. -- Mike Nolan ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [GENERAL] Postgresql website issues.

f font colors is also 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

hat > might 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 li

Re: [GENERAL] OID's

y large to avoid virtually 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

e last two weeks of backups 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)--

Re: [GENERAL] dealing with invalid date

n in pl/pgsql (among other choices) to take a string and fix whatever you 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

Re: [GENERAL] two digit years in inserts

ates, like 05/32/2004 or 11/31/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)-

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

o() if you're not sure. Second, what are you using for a connect statement? Here's a sanitized version of one that works for me: DB::connect("pgsql://foobar:[EMAIL PROTECTED]/dbnm?requiressl=true"); -- Mike Nolan ---(end of broadcast)--

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

request has a unique document number of some kind, 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

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

postpone the check-numbering phase until the number of checks is finalized, so that there's 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

[GENERAL] Accessing an array element from a function

hat's 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)---

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

because the order 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?

Re: [GENERAL] postgres "on in the internet"

. Have you considered using VPN routers 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

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)--

Re: [GENERAL] Single Row Table?

y ignored. If the application 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 comm

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

data 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

ly in October/November), I really need to write it up for the website. IMHO it's 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

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 ---

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

his function needs to be created as a superuser since 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

[GENERAL] Queries not always using index on timestamp search

second one will not, even 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] [PERFORM] Trigger & Function

at match the structure of the tables they are logging. 2. Write a 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 ---

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

e I'd use a serial 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] 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 "

Re: [GENERAL] Data change logs

aspects 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)

Re: [GENERAL] security question

reSQL has a number of security features in it to control access 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. Specific

Re: [GENERAL] Listing databases

e \l command within 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?

ions problems can take 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

Re: [GENERAL] PL/SQL question

ement look like? Part of the problem may be how 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.

better 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] s

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

ard data using 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] Select/Group by/Order by question

ort_field column? I usually don't like to send managers 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 wit

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

ut there in plaintext in the .pgpass 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 Nol

Re: [GENERAL] PG vs MySQL

ISP's (or his clients') point of view, 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

g_ident.conf are commonplace. I also wonder how well 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

Re: [GENERAL] PG vs MySQL

out having to actually log into the database. I can envision 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

Re: [GENERAL] PG vs MySQL

at the current user is authorized for, the \du 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 -

Re: [GENERAL] case statement as inline function?

when B=1 then 2 else null end > >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 --

[GENERAL] case statement as inline function?

uring 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)--

Re: [GENERAL] Trigger loop question

an infinite loop. Here's a really 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 --

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', d

Re: [GENERAL] Differences between postgres and mysql

t always does the edit to a temporary file 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 Nola

Re: [GENERAL] Differences between postgres and mysql

ta better than pg.) MySQL's poor implementation of the 'alter table' process. -- Mike Nolan ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[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

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

k end for those who 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

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 broadc

Re: [GENERAL] Parsing bug?

x, count(*) from memmast 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 F

[GENERAL] Parsing bug?

n aggregate function -- 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] Schemas not created on restore

or can I just 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

Re: [GENERAL] order by is ambiguous

the front end uses as a default 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 th

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

of the SQL standard? On a mostly 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 -

Re: [GENERAL] GetLastInsertID ?

I stand corrected. I 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 plan

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 ma

Re: [GENERAL] between

ust need > someone willing to go through the files 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

Re: [GENERAL] between

age Reference 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

Re: [GENERAL] hardware requirements under Redhat

dual circumstances (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)

;High Memory Support' setting to get 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?

t the latter word might be proprietary to Oracle. 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)--

[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