Re: [GENERAL] SQL equivallent to "\ds" in psql
On 17 Oct 2001, Arcady Genkin wrote: > What I want is an SQL equivallent of "\ds" command, which will return > a list of all sequences in the current database. I need this for a > periodically run script so that I could keep an eye on all sequences > in the database. \ds *is* the psql command for listing sequences: arc=> create sequence testme; CREATE arc=> \ds List of relations Name | Type | Owner +--+--- testme | sequence | arc_admin (1 row) -- Brett http://www.chapelperilous.net/ Thrashing is just virtual crashing. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] SQL equivallent to "\ds" in psql
On 17 Oct 2001, Arcady Genkin wrote:
> Where does Postgres store information about the sequences? I tried
> looking in the tables produced by "\dS", but could find no references
> to the sequences. :(
Oops, I thought you had made a typo, but I made a thinko. Use the -E
option to generate the SQL to pull out sequences:
arc=> create sequence testme;
CREATE
arc=> \ds
* QUERY *
SELECT c.relname as "Name",
(CASE WHEN relkind = 'S' THEN 'sequence'::text ELSE 'index'::text END)
as "Type",
u.usename as "Owner"
FROM pg_class c, pg_user u
WHERE c.relowner = u.usesysid AND relkind in ('S')
AND c.relname !~ '^pg_'
UNION
SELECT c.relname as "Name",
(CASE WHEN relkind = 'S' THEN 'sequence'::text ELSE 'index'::text END)
as "Type",
NULL as "Owner"
FROM pg_class c
WHERE not exists (select 1 from pg_user where usesysid = c.relowner) AND
relkind in ('S')
AND c.relname !~ '^pg_'
ORDER BY "Name"
*
List of relations
Name | Type | Owner
+--+---
testme | sequence | arc_admin
(1 row)
-- Brett
http://www.chapelperilous.net/
Sometime when you least expect it, Love will tap you on the shoulder...
and ask you to move out of the way because it still isn't your turn.
-- N.V. Plyter
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] TEXT field's length limit in PostgreSQL
On Thu, 18 Oct 2001, [iso-8859-2] Micha³ 'Samuel' Modestowicz wrote: > I know that my question may seem trivial, but unfortunately I haven't found > any understandable info on this topic. It's about length limit of the TEXT > field in PostgreSQL. I've heard that the TEXT fields in psql are limited to > 4096 bytes but it's just unbelievable for me. So if I've got text of more > than 4096 bytes size, for example article - do I really have to put it in > the LOB field and use these terrific lo-functions to access it ?? Please, if > anyone can help me - write to [EMAIL PROTECTED] . The limitation is gone in 7.1. According to the documentation, the length is unlimited. Realistically, I think sizes of around 1G are the discrete limit. -- Brett http://www.chapelperilous.net/ Even moderation ought not to be practiced to excess. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] Column names
On Fri, 19 Oct 2001, Mark Muffett wrote: > Is there a simple query to return the column names used in a table? If you start up psql with the -E option, and then do \dt , it will spit out the SQL used to retrieve the metadata on your table. You can use that as your basis. -- Brett http://www.chapelperilous.net/ After a number of decimal places, nobody gives a damn. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Possible bug in postgresql-7.1.3-1PGDG
On Thu, 18 Oct 2001, Rich Shepard wrote: > Just in case I mis-typed at the command line, I just tried the above. Same > error: cannot find database system (in /var/lib/pgsql/data, where it is) > and failed looking for /var/lib/pgsql/data/global/pg_control. > > The problem is that the file, pg_control is in /var/lib/pgsql/data. There > is no global/ directory. There should be! Are you sure you are using 7.1.3? It sounds like the data directory didn't get upgraded properly. -- Brett http://www.chapelperilous.net/ The Beatles: Paul McCartney's old back-up band. ---(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] RPM postmaster -i ?
On Thu, 18 Oct 2001, Michael Coughlin wrote: > The docs say to startup, using postmaster -i (Internet?) switch - for TCP/IP > use. They don't elaborate on where to properly implement postmaster -i. Have you read the administrator's manual? It gives tips on what to put into start up scripts. > Some of the places, I've uncovered where one *might* invoke postmaster -i > are: > /etc/tc.d/init.d/postgresql If this script is there, this is the one to use. You may even have it syumlinked in one of the rc#.d directories, and it should be automatically starting at boot time. This was probably installed by the RPMs. -- Brett http://www.chapelperilous.net/ All the troubles you have will pass away very quickly. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Backup Postgre Windows to Linux
On Fri, 19 Oct 2001, Flávio Brito wrote: > I am using "pg_dumpall -h localhost -p 5432 > db.out" > When I try it on Windows its turns my printer on and freeze Ouch! What version of Windows are you using? I seem to recall that DOS & Windows used to remap an interrupt (divide by zero? overflow?) to be a printer interrupt, so certain fatal exceptions trapped by the hardware would just start printing crap out on the printer inadvertently. I wonder if this is still the case in newer versions of Windows? -- Brett http://www.chapelperilous.net/ "All we are given is possibilities -- to make ourselves one thing or another." -- Ortega y Gasset ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Cross-database queries
On Fri, 19 Oct 2001, Andy Hallam wrote: > I am porting our applications from SQL SERVER, ORACLE and DB2 to PostgreSQL. > > I have just read that PostgreSQL does NOT support cross-database queries. Nope, it doesn't. However, you can simulate this in your client application by having two database connections open and making two queries and then processing both sets of data. There was talk about having schema support in PostgreSQL at time. In fact, I think it is listed as 'Urgent' in the TODO list as part of the replication support (http://postgresql.cokernet.com/docs/todo.html) -- Brett http://www.chapelperilous.net/ You can fool all the people all of the time if the advertising is right and the budget is big enough. -- Joseph E. Levine ---(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 through to the mailing list cleanly
Re: [GENERAL] Possible bug in postgresql-7.1.3-1PGDG
On Fri, 19 Oct 2001, Rich Shepard wrote: > Should I move configuration files (such as pg_hba.conf) to a safe area, > then manually delete all references to postgres that locate and find reveal, > before starting from scratch? A good idea. I usually back up all of my databases (if required) and configuration data before running an installation (I build PostgreSQL from source only), then replace after I am done upgrading. -- Brett http://www.chapelperilous.net/ Why does a ship carry cargo and a truck carry shipments? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Possible bug in postgresql-7.1.3-1PGDG
On Fri, 19 Oct 2001, Rich Shepard wrote: > Thanks. Other than pg_hdb.conf, what files do I save? Save all of your conf files, and probably pg_control & pgpasswd also. If you have data, you should also do a database dump so you can restore it. Take a look at the administrator's manual also. It has more details on what config stuff is there and in what files. -- Brett http://www.chapelperilous.net/ All is well that ends well. -- John Heywood ---(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 through to the mailing list cleanly
Re: [GENERAL] Postgresql usage clip.
On Mon, 29 May 2000, The Hermit Hacker wrote: > > This has "fire" written all over it > > > > But as somebody who uses both, in large scale (er.. global) enterprise > > level data management, each has it's place. MySQL has much faster > > simple table scans, but it cannot handle the complex structures that > > Pgsql can. Pgsql has scads of additional features, but is limited > > in platform support compared to mysql. > > Huh? You caught my eye on this one ... what platform are we missing? :( Actually, I daresay PostgreSQL runs on more platforms, than other RDBMSes. Brett W. McCoy http://www.chapelperilous.net --- "The Lord gave us farmers two strong hands so we could grab as much as we could with both of them." -- Joseph Heller, "Catch-22"
Re: [GENERAL] Postgresql usage clip.
On Mon, 29 May 2000, Ron Chmara wrote: > > Huh? You caught my eye on this one ... what platform are we missing? :( > > Well, you have binaries for NT, but what about home users/developers on 95? 98? > mySQL even does OS/2. Really. But you have to pay money to run it on those platforms (except for OS/2). Brett W. McCoy http://www.chapelperilous.net --- Hello. Jim Rockford's machine, this is Larry Doheny's machine. Will you please have your master call my master at his convenience? Thank you. Thank you. Thank you. Thank you. Thank you. Thank you. -- "The Rockford Files"
Re: [GENERAL] Postgresql usage clip.
On Mon, 29 May 2000, Lamar Owen wrote: > > Well, you have binaries for NT, but what about home users/developers on 95? > 98? > mySQL even does OS/2. Really. > > For home use/development, run either Linux or FreeBSD in another partition on > your Win9x machine. Or, even use one of the 'WinLinux' style distributions > that cooexist with Windows very well. You'll (and PostgreSQL will) be much > happier with a unix-like environment (which Cygwin duplicates anyway for the > Win32 PostgreSQL server) for running the PostgreSQL server. Get VMWare and run > Win9x in a window on your Linux partition to test Win9x apps if you must run > Win9x. > > Or, port Cygwin to Win9x so that PostgreSQL can run on Win9x. CygWin does run on Windows 9x, doesn't it? Or at least it used to! Brett W. McCoy http://www.chapelperilous.net --- Hello. Jim Rockford's machine, this is Larry Doheny's machine. Will you please have your master call my master at his convenience? Thank you. Thank you. Thank you. Thank you. Thank you. Thank you. -- "The Rockford Files"
Re: [GENERAL] Postgresql usage clip.
On Mon, 29 May 2000, Ron Chmara wrote: > > > Well, you have binaries for NT, but what about home users/developers on 95? 98? > > > mySQL even does OS/2. Really. > > But you have to pay money to run it on those platforms (except for OS/2). > > Brett W. McCoy > > Yes. How much money has to be paid to run postgreSQL on Win95? Is it > comparable in cost to get support for that platform? Can PostgreSQL run on Win9x with CygWin32? Brett W. McCoy http://www.chapelperilous.net --- Hello. Jim Rockford's machine, this is Larry Doheny's machine. Will you please have your master call my master at his convenience? Thank you. Thank you. Thank you. Thank you. Thank you. Thank you. -- "The Rockford Files"
Re: [GENERAL] Postgresql usage clip.
On Tue, 30 May 2000, The Hermit Hacker wrote: > On Mon, 29 May 2000, Brett W. McCoy wrote: > > > On Mon, 29 May 2000, Ron Chmara wrote: > > > > > > Huh? You caught my eye on this one ... what platform are we missing? :( > > > > > > Well, you have binaries for NT, but what about home users/developers on 95? 98? > > > mySQL even does OS/2. Really. > > > > But you have to pay money to run it on those platforms (except for OS/2). > > And, I seem to recall someone did an OS/2 binary for PostgreSQL ... Gooing by the language in the User Manual, PostgreSQL does seem to work on Win9x: "...The only part of the library to really be installed is the libpq.dll library. This file should in most cases be placed in the WINNT\SYSTEM32 directory (or in WINDOWS\SYSTEM on a Windows 95/98 system)..." The documentation references using MVC++. Brett W. McCoy http://www.chapelperilous.net --- Hello. Jim Rockford's machine, this is Larry Doheny's machine. Will you please have your master call my master at his convenience? Thank you. Thank you. Thank you. Thank you. Thank you. Thank you. -- "The Rockford Files"
Re: [GENERAL] Postgresql usage clip.
On Tue, 30 May 2000, Bruce Momjian wrote: > > "...The only part of the library to really be installed is the libpq.dll > > library. This file should in most cases be placed in the WINNT\SYSTEM32 > > directory (or in WINDOWS\SYSTEM on a Windows 95/98 system)..." > > > > The documentation references using MVC++. > > We support WIn95/98 clients, not servers. My mistake. Brett W. McCoy http://www.chapelperilous.net --- For every credibility gap, there is a gullibility fill. -- R. Clopton
Re: [GENERAL] Auto-increment Numeric Primary keys
On Sun, 18 Jun 2000, Vipin Samtani wrote:
> How can I auto-increment numeric primary keys? So on a table called
> "Test1" with fields "ID" and "Name" when I do an INSERT, I only type
>
> INSERT INTO Test1 values ('Bob');
>
> instead of
>
> INSERT INTO Test1 values (1, 'Bob');
>
> Is this implemented in PostgreSQL 7?
You'll want to use the serial type:
CREATE TABLE Test1 (mykey SERIAL, Name varchar(10));
You will need to specify the fields when you insert:
INSERT INTO Test1(Name) Values('Bob');
However, take a look at using sequences (which is what the serial type
uses underneath). They can give you a good bit of flxibility in terms of
starting and ending numbers, amount of increment, and so on.
Brett W. McCoy
http://www.chapelperilous.net
---
Everything I like is either illegal, immoral or fattening.
-- Alexander Woollcott
RE: [GENERAL] Re: Re: Why PostgreSQL is not that popular as MySQL?
On Sun, 10 Dec 2000, Matthew wrote: > > I think MySQL got a big start by migrating mSQL users years ago and > > having a compatibility module for mSQL. > > > [Matthew] Would it make sense for postgre to have a mysql > compatibility module? An add on package (perhaps in contrib) that would add > many of the functions that mysql has that postgre does not. I know this > couldn't cover everything, but it could probably make it much easier to port > an app written against mysql to postgre. I have seen several posts on these > lists about someone attempting to port some app to postgre, and now knowing > how to find the postgre equivelant of some mysql function. I think it would be wasted effort. I would rather the developers focus on PostgreSQL, not MySQL, or Access, or whatever. We have things like DBI & ODBC (not to mention SQL, which is fairly standard) to make data access generic. I think trying to do this at a lower-level is a waste of time. Just my $.02, of course. http://www.chapelperilous.net/~bmccoy/ --- You mean you didn't *know* she was off making lots of little phone companies?
RE: [GENERAL] Re: Re: Why PostgreSQL is not that popular as MySQL ?
On Mon, 11 Dec 2000, Matthew wrote: > > I agree that the key developers shouldn't spend much time on such a > > thing, but on the other hand this isn't a project that requires a key > > developer to get done. If Matthew or someone else feels like spending > > time on it, I wouldn't object... > > > [Matthew] I agree also, I didn't mean to imply that a core > developer work on it. I was just asking if this was a project that would > interest people. If it has enough demand I would get started on it. I > don't know a whole lot about mysql, but getting a function list and > comparing it to postgre shouldn't be too hard. Ah, ok, I misunderstood your intention. You know what also would be good? A guide to porting from MySQL to PostgreSQL, like a mini-HOWTO or something along those lines. I've done some MySQL development (although I was using PostgreSQL even before I knew MySQL existed, believe it or not), so I would be willing to help in this endeavour. -- Brett http://www.chapelperilous.net/~bmccoy/ --- Get forgiveness now -- tomorrow you may no longer feel guilty.
Re: [GENERAL] Sql query with partial matching
On Thu, 7 Dec 2000, Travis Bauer wrote: > How would I write an sql statement which would select all the > records from a table where some string field contains > some substring? Example: find all the records where f1 contains > the world "cool." SELECT * FROM mytable WHERE f1 LIKE '%cool%'; or use a regular expression: SELECT * FROM mytable WHERE f1 ~* 'cool'; http://www.chapelperilous.net/~bmccoy/ --- "Pascal is Pascal is Pascal is dog meat." -- M. Devine and P. Larson, Computer Science 340
Re: [GENERAL] extra spaces
On Thu, 14 Dec 2000, Soma Interesting wrote: > I'm using PHP & PostgreSQL. > > All values called from the database are still padded with extra spaces from > the column size in the database. Is this normal - I don't remember this > happening when I was using MySQL. I thought usually the database stripped > the extra spaces when it retrieved the values. > > How do you recommend dealing with this, using a PHP command to strip every > value from the database - or can I change something in the database to have > it strip the spaces auto magic like? Sounds like you are using fixed length strings (char) rather than variable length strings (varchar). -- Brett http://www.chapelperilous.net/~bmccoy/ --- A fool-proof method for sculpting an elephant: first, get a huge block of marble; then you chip away everything that doesn't look like an elephant.
Re: [GENERAL] Carrage Returns \ Line Breaks!
On Mon, 18 Dec 2000, Brian C. Doyle wrote: > I have a file that I am trying to import/copy into a table. The problem > that I am having is that there are carriage returns through out the > document. These carriage returns should be copied into the column it > belongs to yet during the > copy table from '/home/location/file.ext' using delimiters '|'; > > I get errors when it hits the carriage return. Can i get around this? You should convert the carriage returns into the literal text '\n'. Are they ^J or ^M? -- Brett http://www.chapelperilous.net/~bmccoy/ --- A soft answer turneth away wrath; but grievous words stir up anger. -- Proverbs 15:1
Re: [GENERAL] Carrage Returns \ Line Breaks!
On Mon, 18 Dec 2000, Brian C. Doyle wrote: > Okay now I am an Idoit. How do I tell what is used for the carriage > return? I know how to make the replacement I just can not tell they > characters used. Are you on Windows, Unix or Mac? -- Brett http://www.chapelperilous.net/~bmccoy/ --- Knock, knock! Who's there? Sam and Janet. Sam and Janet who? Sam and Janet Evening...
Re: [GENERAL] Auto incrementing fields. How?
On Tue, 19 Dec 2000, Harry Wood wrote:
> Anyone know how to create auto incrementing fields?
create sequence my_seq;
create table my_table (
my_id integer primary key default nextval('my_seq'),
another_field varchar(10),
...
);
OR, you can create an implicit sequence:
create table my_table (
my_id SERIAL primary key,
...
);
If you drop the table later on, though, you will need to drop the created
sequence manually.
When you insert data into the table, do not specify the autoincrement
field in the insert statement:
insert into my_table(another_field) values('some data');
Refer to the Postgres documentation for full details.
-- Brett
http://www.chapelperilous.net/~bmccoy/
---
Politics, as a practice, whatever its professions, has always been the
systematic organisation of hatreds.
-- Henry Adams, "The Education of Henry Adams"
Re: [GENERAL] Auto Increment
On Tue, 26 Dec 2000, Bruce Momjian wrote: > > See the documentation on CREATE SEQUENCE, and also on CREATE TABLE and the > > SERIAL type. > > > > [An aside: this is something definitely that qualifies as a frequently > > asked question, as this is like the 4th time in a week this question has > > been asked. Would posting a FAQ list on a regular basis to the list be > > helpful for things like this, like is done on the Perl newsgroups?] > > > > I don't think so. Doesn't everyone know the location of the FAQ? Perhaps not -- this is what I am wondering. Is it sent to subscribers as part of a welcome message? At any rate, this particular question I think has now qualified for the FAQ, at the very least! Or maybe even a section in the FAQ for people coming over from MySQL... -- Brett PS. Got your book, Bruce. Nice job! http://www.chapelperilous.net/~bmccoy/ --- "The pyramid is opening!" "Which one?" "The one with the ever-widening hole in it!" -- The Firesign Theatre
[GENERAL] The FAQ
On Wed, 27 Dec 2000, Bruce Momjian wrote: > > Perhaps not -- this is what I am wondering. Is it sent to subscribers as > > part of a welcome message? At any rate, this particular question I think > > has now qualified for the FAQ, at the very least! Or maybe even a section > > in the FAQ for people coming over from MySQL... > > It is already in the FAQ, right? Doh! OK, now that my foot is out of my mouth... perhaps a gentle reminder to some of the newer folks that a lot of questions asked are answered in the FAQ already, and if you don't know where the FAQ is, go to http://www.postgresql.org/docs/faq-english.html (there are also FAQs in German, Japanese and Korean). And don't mind me... been a long evening of coding... -- Brett http://www.chapelperilous.net/~bmccoy/ --- An aphorism is never exactly true; it is either a half-truth or one-and-a-half truths. -- Karl Kraus
Re: [GENERAL] Auto Increment
On Wed, 27 Dec 2000, Tom Lane wrote: > At one time Marc was arranging for the list bot to append automatic > tags to all postings. I'd be in favor of an automatic tag that read > something like > > list administrivia: [EMAIL PROTECTED] > Postgres FAQs: http://... > > Suggestions anyone? (Keep in mind that brevity is the soul of wit > here...) I think that's a good idea... one line in a tag may save many packets of questions that don't need to be asked on the list if they have already been answered. :-) -- Brett http://www.chapelperilous.net/~bmccoy/ --- Premature optimization is the root of all evil. -- D.E. Knuth
Re: [GENERAL] How to drop a NOT NULL column constraint?
On Tue, 2 Jan 2001, Ed Loehr wrote: > I have a table created like so: > > CREATE TABLE foo ( > id INTEGER NOT NULL > ... > ); > > I need to alter the table to get rid of the "NOT NULL" constraint on the > 'id' column. Can I do this using ALTER TABLE? Or do I have to > dump/recreate/reload the table? > > I'm looking for something like "ALTER TABLE foo ALTER COLUMN id DROP NOT > NULL", but the docs don't hint at it... I don't think Postgres supports DROP CONSTRAINT in the ALTER TABLE statement yet. You'll need to create a temp table with the same structure but without the constaint and do a SELECT INTO to copy the data over, then delete the old table and rename the new table to the old name. -- Brett http://www.chapelperilous.net/~bmccoy/ --- Shah, shah! Ayatollah you so!
Re: [GENERAL] RE: RE: Re: MySQL and PostgreSQL speed compare
On Wed, 3 Jan 2001, Peter Eisentraut wrote: > > I'm baffled by what seems to be a lack of detailed documentation for > > something so fundamental as the built-in functions. > > Just to add a particular point, in addition to Vince's excellent general > points, as the one who was last to update the function documentation: > The reason that there's no "detailed" documentation is that there are no > details. How much can you write about round(), avg(), or current_user? Actually, I like the 'grid' layout that the functions are listed in -- it gives the syntax, the return value and an example of usage, all in a single glance. -- Brett http://www.chapelperilous.net/~bmccoy/ --- You will be the victim of a bizarre joke.
Re: [GENERAL] backend corruption
On Sat, 6 Jan 2001, George Johnson wrote: > So basically, I've wiped all my databases, it looks like, and no, there is > no directory/structure which atomically can be called "your database", other > than the ENTIRE data/base directory. Sure there is -- under my $PGHOME/data/base, each separate database on my system is a sub-directory, which contain the system dictionaries, tables, indexes, etc. -- Brett http://www.chapelperilous.net/~bmccoy/ --- "Just think of a computer as hardware you can program." -- Nigel de la Tierre
Re: [GENERAL] backend corruption
On Sat, 6 Jan 2001, Tom Lane wrote: > Yeah, but those files are only half the truth. The other half lives in > pg_log and the installation-wide tables (pg_database, etc). George is > correct: you cannot recover using only the contents of $PGDATA/base/foo. > You really need all of $PGDATA. Really? I stand corrected. How does MySQL do things? -- Brett http://www.chapelperilous.net/~bmccoy/ --- There is not much to choose between a woman who deceives us for another, and a woman who deceives another for ourselves. -- Augier
Re: [GENERAL] Outer Joins
On Sat, 6 Jan 2001, Robert B. Easter wrote: > What is the syntax for this? Is there an example I can see/run? Should follow standard SQL92 syntax (which, BTW, Oralce doesn't): SELECT * FROM table1 LEFT OUTER JOIN table2 ON (table1.field = table2.field) This will return all rows from table1 even if no corresponding row exists in table2. A RIGHT OUTER JOIN would do the opposite. -- Brett http://www.chapelperilous.net/~bmccoy/ --- It is better to have loved a short man than never to have loved a tall.
Re: [GENERAL] Dangling large objects
Dangling large objects? I thought this was a family list. :-) -- Brett PS. Sorry, couldn't resist... http://www.chapelperilous.net/~bmccoy/ --- Unnamed Law: If it happens, it must be possible.
Re: [GENERAL] Can't compare decimal columns???
On Sun, 14 Jan 2001, Stephan Szabo wrote: > I believe there was discussion about these things on -hackers a while > back. The type promotion and which type a constant is seen as is kind of > wierd right now. The query will probably work with explicit typecast to > numeric for the constants. Yes, I got it to work with CAST(? as NUMERIC). Was just curious about the operator issue. -- Brett http://www.chapelperilous.net/~bmccoy/ --- Success is in the minds of Fools. -- William Wrenshaw, 1578
Re: [GENERAL] MySQL file system
On Tue, 16 Jan 2001, Ned Lilly wrote: > Anyone heard about this? > > http://no.spam.ee/~tonu/mysqlfs.html That only confirms the criticisms that some have leveled at MySQL of being an SQL interface to a filesystem and not a true relational database management system. -- Brett http://www.chapelperilous.net/~bmccoy/ --- Boys, you have ALL been selected to LEAVE th' PLANET in 15 minutes!!
Re: [GENERAL] pg_hba.conf edit
On Wed, 17 Jan 2001, Uro Gruber wrote: > I want to know how to edit this file. Because is only for reading. Is > there any program to edit this. For now i chmod it to 600 > end edit with my editor. That's the way you are supposed to do it! Just make sure you make it read-only after you are done. -- Brett http://www.chapelperilous.net/~bmccoy/ --- While most peoples' opinions change, the conviction of their correctness never does.
Re: [GENERAL] Why does the JDBC driver not support prepareCall?
On Fri, 19 Jan 2001, mg wrote: > I have troubles with jsp-based websites that have been generated by > Macromedia DreamWeaver UltraDev 1.0. My analysis revealed that the > problem lies in the JDBC driver not supporting prepareCall (prepareCall > throws the PSQLException "postgresql.con.call" instead). > Is there any workaround or intermediate solution? Is there anybody > working to fill this gap, and when can a solution be expected? Is there > anybody working on this subject? prepareCall is reportedly going to be implemented in 7.1. -- Brett http://www.chapelperilous.net/~bmccoy/ --- "Mach was the greatest intellectual fraud in the last ten years." "What about X?" "I said `intellectual'." ;login, 9/1990
Re: [GENERAL] data dictionary
On Fri, 19 Jan 2001, Matthew Taylor wrote: > Umm I must have missed it in the manual, (read it 3-4 times tho) but what is > the equivalent data dictionary structure in Postgres to the following in > Oracle. > > Select table_name from user_tables; > > (gives a list of the table names in the database(table space) used at the > time) > > etc various special tables defining the data dictionary Take a look at pg_class and pg_tables (which is a view based on pg_class), which are available for each schema: select * from pg_tables where tableowner=CURRENT_USER; -- Brett http://www.chapelperilous.net/~bmccoy/ --- A woman without a man is like a fish without a bicycle. -- Gloria Steinem
Re: [GENERAL] Re: Re: is PG able to handle a >500 GB Database?
On Sat, 20 Jan 2001, Tom Lane wrote: > > currval returns error unless nextval has been called at least once in the > > session. > > > I use .last_value > > > Perhaps I'm fooling myself > > Yes, you are, unless you never have more than one client attached to > your database. last_value will return whatever value was last assigned > by any backend, therefore you might not get the value that was inserted > into your tuple, but someone else's. In that case you would call next_val *before* you insert and use that value in the INSERT statement. -- Brett http://www.chapelperilous.net/~bmccoy/ --- Military intelligence is a contradiction in terms. -- Groucho Marx
Re: [GENERAL] MySQL has transactions
On Wed, 24 Jan 2001, Tom Lane wrote: > Something I'm curious about: does MySQL support user-defined datatypes? No, it does not. > If so, how hard is it to make a new datatype? I think datatype > extensibility is one of the cooler features of Postgres ... Check this out: http://www.mysql.com/documentation/mysql/bychapter/manual_Comparisons.html#Compare_PostgreSQL http://www.chapelperilous.net/~bmccoy/ --- If you give a man enough rope, he'll claim he's tied up at the office.
Re: [GENERAL] MySQL has transactions
On Wed, 24 Jan 2001, Peter Eisentraut wrote: > David Wall writes: > > > Now that MySQL has transaction support through Berkeley DB lib, and it's > > always had way more data types, > > I count 25 documented and distinct data types for MySQL, and 30 for > PostgreSQL. Not to mention that Postgres has an extensible type system whereas MySQL does not. -- Brett http://www.chapelperilous.net/~bmccoy/ --- When a man knows he is to be hanged in a fortnight, it concentrates his mind wonderfully. -- Samuel Johnson
Re: [GENERAL] Postgres Client other than Linux?
On Wed, 24 Jan 2001, Raymond Chui wrote: > I also have a HP machine with OS HP-UX 10.20, I have installed > Perl 5.6 and DBI. But when I install DBD (DBD-Pg-0.95) it requires > me install all those *.h files and libpg.* files first. I don't want to > install PostgreSQL server in the HP machine, I only want to install > the software development package and the client for my HP. But > I only find the whole source postgresql-7.0.3 package from PostgreSQL > Web site. Besides, I also had problem to compile the postgresql-7.0.3 > in HP-UX 10.20. Any help? Thanks! There is a build option to do just the clients with the source package. The basic procedure is: make -C src/bin install make -C src/interfaces install make -C doc install See the admin documentation for more details on installation. -- Brett http://www.chapelperilous.net/~bmccoy/ --- Wake up and smell the coffee. -- Ann Landers
Re: [GENERAL] LO to text conversion
On Wed, 24 Jan 2001, Alexander Klimov wrote: > I need to store XML documents in database, so I use large objects for > them. Now I have a problem: I want to search for patterns in documents, > and it looks like there is no standard way to do it, so I write > C function to do it, but it has limitation: it is not support regular > expressions. Although it will be easy to use some library to handel > them, but I know that PostgreSQL already has it. > > So, my question is: is there any function to search in lobjs? Another > thing which would be enought is convertion of lo to text. > > BTW: Somebody can say, that I could use text field for documents as > well, but it is inefficient to embed document in sql query and then > extract it back You might want to use the full-text search facility, whihc you can find under src/contrib. With the new TOADT features of 7.1, storing large chunks of text is now quite feasible. -- Brett http://www.chapelperilous.net/~bmccoy/ --- I would like to electrocute everyone who uses the word 'fair' in connection with income tax policies. -- William F. Buckley
Re: [GENERAL] Connection pooling
On Thu, 25 Jan 2001, Gilles DAROLD wrote: > Don't forget to use Apache::DBI on top of DBI.pm. See mod_perl > documentation > for a complete setting. Apache::DBI is supposed to be supporting connection pooling in the near future. -- Brett http://www.chapelperilous.net/~bmccoy/ --- The universe seems neither benign nor hostile, merely indifferent. -- Sagan
Re: [GENERAL] design
On Tue, 30 Jan 2001, Jeff wrote: > I have a design question. Lets say we want to keep track of users and > their respective snail mail addresses. Each user can have up to 4 > different mailing address. Is it better to have all this information in > one table. Or is it better to have a user table and an address table, > and have the user id as a foreign key in the address table? I would put the addresses in a separate table and use the foreign key. That way each user can have as many addresses as you want. A year from now you might change your requirement to 5 addresses. Or perhaps you want to keep historical information. In general, if you find yourself designing a table where duplicate information is showing up (in this case, if you had only used one table, user names would have been entered 4 times, once for each address), you need to apply normalization and break it into two (or more) tables. -- Brett http://www.chapelperilous.net/~bmccoy/ --- Health is merely the slowest possible rate at which one can die.
Re: [GENERAL] php as stored procedures
On Wed, 31 Jan 2001, Dan Wilson wrote: > I wouldn't call PHP a subset of Perl at all! I'd call them sibling > languages with different strengths. I think Perl does certain things better > than PHP but PHP has strengths that Perl probably can't compete with. But > for the most part, AFAIK, anything you can do in Perl, you can also do in > PHP. Err... I would say it's more the other way around... anything you can do in PHP you can do in Perl. -- Brett http://www.chapelperilous.net/~bmccoy/ --- Learn to pause -- or nothing worthwhile can catch up to you.
Re: [GENERAL] permissions on databases
On Tue, 6 Feb 2001, Tressens Lionel wrote: > I am the super user of my DBMS and if create a database for a simple user, > I do : > > => create database formyuser; > > But If this user connects to the database and tries to create a table, he > has no rights. I must (the super user) create the table for him and then > give him all the permissions with GRANT. > > So my question is : How can I create a database for a simple user (ie not > super user) and give him all the rigths to create and drop tables in it ? Create the user with database create permission and then let the user create the database directly, so they are the default owner for it. Otherwise, yes, you need to grant all of the permission to the user. -- Brett http://www.chapelperilous.net/~bmccoy/ --- Whenever people agree with me, I always think I must be wrong. - Oscar Wilde
Re: [GENERAL] monitoring running queries?
On Thu, 8 Feb 2001, Brice Ruth wrote: > Its not working ... I looked at the admin docs and edited the pg_options > file appropriately ... the following is what appears in /var/log/messages: > > postgres[23686]: read_pg_options: verbose=2,query=4,syslog=2 > > But no queries ... I sent SIG_HUP to postmaster, even restarted > postmaster. The file /var/log/postgresql appears to be empty as well. I don't know how you are starting postgreSQL, but I just have it redirect everything to a log file under the postgres super user's directory: nohup postmaster [options] >>server.log 2>&1 & Make sure you don't use the -S option -- it detaches from the tty and you will get no output! -- Brett http://www.chapelperilous.net/~bmccoy/ --- Above all else -- sky.
Re: [GENERAL] illegal characters
On Fri, 9 Feb 2001, Oleg Lebedev wrote: > I am using postgresql to store data passed from a web page. A user may > enter whatever text she wants on that web page. Do I have to prepend all > the illegal characters in the text with backslashes before storing the > text in the database? Is there any way to make postgresql prepend these > illegal characters for me? > Example: > I have an entry 'foo/bar' in a database table (it was stored as > 'foo/bar' NOT as 'foo\/bar', when I try to search for all rows that > contain entry 'foo/bar', I get no results. > Any help will be greatly appreciated. What programming interface are you using for the form? Most usually provide some sort of escaping mechanism before you insert data into the database. Otherwise, you can write your own validation functions (which you should do any way, to make sure users aren't doing bad things) to escape funncy characters (single quotes, slashes, etc.). -- Brett http://www.chapelperilous.net/~bmccoy/ --- This is National Non-Dairy Creamer Week.
Re: [GENERAL] Newbie question :-)
On Sat, 24 Feb 2001, Bela Lantos wrote: > I just started learning Database Design, our programs have to work on > Oracle, but at home I run Linux. > > Can any of you tell me how portable are the programs written for Progresql? > Would they run on Oracle without problem? That all depends on the programming interface you are writing your programs with. If you are using Oracle's or PostgreSQL's native libraries, they are not going to be portable at all. However, if you are building your code with a data abstraction layer (i.e., Java's JDBC, Perl's DBI, ODBC), you will have a lot more portability between database systems. -- Brett http://www.chapelperilous.net/~bmccoy/ --- Please come home with me ... I have Tylenol!!
Re: [GENERAL] Re: RPMs for PHP accessing PostgreSQL via ODBC overRedHat
On Tue, 6 Mar 2001, Chris wrote: > >Is it really necessary to use PHP and ODBC? PHP has a native postgresql > >interface I believe... > > It sounds like the postgres server is a different machine to the web > server, so ODBC is needed to communicate between the 2.. No it isn't. Postgres should be accessible via PHP whether it's on the same server or on a remote server (and Postgres is configured to allow remote access). The only reason I can guess as to why ODBC is being used is to perhaps maintain code compatibility with the use of a different databse platform that the web server may have been using previously. -- Brett http://www.chapelperilous.net/~bmccoy/ I didn't get sophisticated -- I just got tired. But maybe that's what sophisticated is -- being tired. -- Rita Gain ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] PHP and PostgresSQL beta
On Mon, 5 Mar 2001 [EMAIL PROTECTED] wrote: > I know this issue has been discussed, currently I can't find the > resolution. What needs to be done to the latest php release to get it > to successfully compile with the latest beta version of pgsql? It can't find the postgresql headers. Did you install from RPM and not install the devel RPM, or perhaps the headers aren't in a standard place? -- Brett http://www.chapelperilous.net/~bmccoy/ Good leaders being scarce, following yourself is allowed. ---(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] Re: Prompt question
On Sun, 11 Mar 2001, Russell Hires wrote: > I have a question. I'm > currently running version 7.0.3 (compiled myself using debuild) on > debian-m68k potato. When I start psql, my prompt doesn't look like every > example I've seen. The examples are like this: "mydb=>" My prompt looks like > this: "test=#" (no quotes, of course) I don't know if this is significant or > not, obviously it is in Linux, but in Postgres I don't know. Usually you get that prompt when you are logged in as the postgres superuser (usually postgres). Is this the case here? -- Brett http://www.chapelperilous.net/~bmccoy/ If voting could change the system, it would be illegal. If not voting could change the system, it would be illegal. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] Views...
On Sun, 25 Mar 2001, Christian Marschalek wrote: > I can't find the desciption of "views" in the PostgreSQL docs. > Could somebody please point me out or explain database views to me? http://www.postgresql.org/users-lounge/docs/7.0/user/sql-createview.htm This is a good start to see how they are used in PostgreSQL. Briefly, a view is a 'virtual' table -- you create a view with a select statement that can relate data between different tables or use a subset of data from a specific table. You can think of them as a 'saved query'. Once you create a view, you can then query them just like a normal table. You cannot, as of yet, update views in PostgreSQL, they are read only. -- Brett http://www.chapelperilous.net/btfwk/ As the poet said, "Only God can make a tree" -- probably because it's so hard to figure out how to get the bark on. -- Woody Allen ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] Starting Postmaster
On Mon, 26 Mar 2001, Scott Gritton wrote: > I've installed postgresql on a Linux-Mandrake 7.0.2 box with everything > installing correctly. > But when I try to start postmaster I get the following: > > DEBUG: Data Base System is in production state at Mon Mar 26 (and so forth) > > I know that there is a simple answer but I haven't been able to find it in > documentation or faqs. Your postmaster if functioning perfectly, you just need to direct its output to a logfile and put it into the background. Here is how I usually startup postmaster: nohup postmaster [options] > logfile 2>&1 & -- Brett http://www.chapelperilous.net/btfwk/ Humor in the Court: Q: Now, you have investigated other murders, have you not, where there was a victim? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] anti Christian bias?
On Sat, 14 Apr 2001, Jan Wieck wrote: > Is it allowed to borrow the Cristian rules even if I don't > believe in God and don't pray? Do they fall under the GPCL > (General Public Christian License) or are they distributed > under a BSDish style license? What if I link myself to them - > does all I'm doing then become property of the pope or some > church? I think the Artistic License would apply here. Larry Wall (another geeky Christian) would probably approve. :-) -- Brett http://www.chapelperilous.net/btfwk/ The way to a man's heart is through the left ventricle. ---(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] Re: anti Christian bias?
On Sat, 14 Apr 2001, Stefan Waidele jun. wrote: > How specific is BCE? > 1973 Before the Current Era my birth year, > but _only_ under that very pro-christian > assumption that BC = BCE ! This brings up another question nto related to religion but just time keeping in PostgreSQL: can PostgreSQL handle completely different time systems, like say that of the Muslims or the Jews? They don't use BC, CE, BCE, etc. How would PostgreSQL handle somehting like that? -- Brett http://www.chapelperilous.net/btfwk/ It is not doing the thing we like to do, but liking the thing we have to do, that makes life blessed. -- Goethe ---(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] i want to start automaticly postgres when a reboot my server...help please.....
On Tue, 9 Mar 1999, amarof wrote: > please give me the way to do that > all thanx It would help a little if you mentioned what you are running PostgreSQL on. Different systems have different kinds of startup scripts. Brett W. McCoy http://www.lan2wan.com/~bmccoy --- You're being followed. Cut out the hanky-panky for a few days. - BEGIN GEEK CODE BLOCK - Version: 3.12 GAT dpu s:-- a C UL$ P+ L+++ E W++ N- o K- w--- O@ M-@ !V PS+++ PE Y+ PGP- t++ 5- X+ R+@ tv b+++ DI+++ D+ e>++ h+ r++ y -- END GEEK CODE BLOCK --
RE: [GENERAL] question on converting M$ Access tables
On Wed, 10 Mar 1999, William J. Stotts wrote: > I have a VB application that uses a M$ Access 97 table to store it's data > in. I wish to change to postgres to store the data, and use ODBC to link > the postgres tables back to the M$ Access mdb. Are there any utilities > that allow me some way of automating the conversion of the mdb table > structures into postgres short of recreating the tables from scratch in > postgres. Have you lokked at pgAdmin? it has utitilities for migrating Access files to PostgreSQL. Brett W. McCoy http://www.lan2wan.com/~bmccoy/ --- I have made this letter longer than usual because I lack the time to make it shorter. -- Blaise Pascal -BEGIN GEEK CODE BLOCK- Version: 3.12 GAT dpu s:-- a C UL$ P+ L+++ E W++ N+ o K- w--- O@ M@ !V PS+++ PE Y+ PGP- t++ 5- X+ R+@ tv b+++ DI+++ D+ G++ e>++ h+(---) r++ y --END GEEK CODE BLOCK--
Re: [GENERAL] ODBC driver
On Tue, 2 Jun 1998, Kevin Heflin wrote: > > Did you create a Postgres user with the 'createuser' tool (usually run by > > the postgres superuser (but not root))? This allows designated users to > > use postgresql databases, and in some cases, create and destroy and even > > set database permissions for other users. > > Also, don't you have to set up Postgres to accept users from another > machine.. I forget exactly where you do that. That'd be the pg_hba.conf file, and you can set up everything from IP based authentication to password authentication. Brett W. McCoy http://www.lan2wan.com/~bmccoy/ --- "The number of UNIX installations has grown to 10, with more expected." -- The UNIX Programmer's Manual, 2nd Edition, June, 1972
Re: [GENERAL] Re: [HACKERS] NEW POSTGRESQL LOGOS
On Wed, 3 Jun 1998, Ken McGlothlen wrote: > Already taken by Apple's Quicktime product. However, a cross between the BSD > daemon 'toon and a penguin would be fairly funny; a red and white penguin with > horns and a long, barbed tail? :) Actually, I've seen something along those lines before also, a spoof on the Linux penguin, Tux. Brett W. McCoy http://www.lan2wan.com/~bmccoy/ --- "The number of UNIX installations has grown to 10, with more expected." -- The UNIX Programmer's Manual, 2nd Edition, June, 1972
Re: [GENERAL] NEW POSTGRESQL LOGOS
On Thu, 4 Jun 1998, Tom Good wrote: > What about *the* Penguin? He's available at www.dccomics.com... > Sorry, couldn't resist. I like the logos and will borrow them > shortly for the UGD PostgreSQL page...thanks Steve. Maybe we should present the idea of the logo to the keepers of the Gimp, and have them run it as one of their monthly contests. The GNOME project did this, and there were some fabulous logos entered by some very talented artists. Brett W. McCoy http://www.lan2wan.com/~bmccoy/ --- "The number of UNIX installations has grown to 10, with more expected." -- The UNIX Programmer's Manual, 2nd Edition, June, 1972
Re: [GENERAL] of penguins, daemons and logos
On Thu, 4 Jun 1998, Stephan Doliov wrote: > a peace offering to those who would argue into the night about BSD v. > SVR4: > > how about having the penguin and the daemon barbequeing larry ellison, > sysbase and informix? With Duke (the little Java dude) looking on with sinister delight...? Brett W. McCoy http://www.lan2wan.com/~bmccoy/ --- "The number of UNIX installations has grown to 10, with more expected." -- The UNIX Programmer's Manual, 2nd Edition, June, 1972
Re: [GENERAL] Problem with apostrophe
On Wed, 17 Jun 1998, Amos Hayes wrote: > > Is there any way of having an apostrophe in a data field? > > It is a problem because the apostrophe is the same character as the single > > quote, so the database gets a parser error when a name such as "O'Brian" > > is entered. > > Have you tried doubling up? 'O''Brian' > > That is what I have to do in Oracle anyway. Isn't there an escape character, like the slash? Brett W. McCoy http://www.lan2wan.com/~bmccoy --- "The Number of UNIX installations has grown to 10, with more expected." -- The UNIX Programmer's Manual, 2nd Edition, June, 1972
Re: [GENERAL] Re: [HACKERS] [Fwd: SGVLLUG Oracle and Informix on Linux]
On Wed, 22 Jul 1998, The Hermit Hacker wrote: > Oracle now comes along and says that it is going to have a > Linux-binary distribution available. So? How much is that binary going > to cost? And what sort of licensing is provided? I think PostgreSQL will continue on as much as before, just as Linux is continuing to put some competition to NT, because of its low cost and flexibility. Certainly many people will flock to Oracle, perhaps by corporate pressure, perhaps for the support or interoperability with other Oracle servers. But it's not going to kill off PostgreSQL. I'm happy that Oracle is being ported to Linux. I'll probably never use Oracle on Linux, but I think it will help get Linux wider recognition in the enterprise environment. And, how many 'supported platforms' of Oracle also support PostgreSQL? PostgreSQL isn't a Linux only server. > Continue our trend...continuing listening to the ppl asking for > various "reasonable" features and working towards providing them. I > support free/open software because, IMHO, the software is generally better > written, and more featured, because those that are developing it are doing > so because they *enjoy* what they are doing, they have a passion for > it...not because some large company is paying them to do it. A good point. Brett W. McCoy http://www.lan2wan.com/~bmccoy --- "The Number of UNIX installations has grown to 10, with more expected." -- The UNIX Programmer's Manual, 2nd Edition, June, 1972
Re: [GENERAL] Postgres vs commercial products
On Wed, 22 Jul 1998, Amos Hayes wrote: > I almost hate to ask, and really, it's not a big deal to me personally, > but why is it called "PostgreSQL"? Originally it was just Postgres, and didn't support any SQL. When it was redesigned with SQL support, that's when the SQL postfix came about. Actually, it was known as Postgres95 originally to differentiate it from the original Postgres, but as it moved towards SQL compliancy, the SQL postfix becamse the norm. The full story is in the PostgreSQL documentation. Brett W. McCoy http://www.lan2wan.com/~bmccoy --- "The Number of UNIX installations has grown to 10, with more expected." -- The UNIX Programmer's Manual, 2nd Edition, June, 1972
Re: [GENERAL] Postgres vs commercial products
On Wed, 22 Jul 1998, Amos Hayes wrote: > Has there ever been any discussion about a new name? It doesn't matter to > me but it would seem that with the current discussions about promotion and > competition, it might help to have a more "public friendly" name. It is > not obvious to me what a "postgres" is nor what it would do if I were to > install it. Granted that "oracle", "informix", and "sybase" are all a > little strange too, but they give hints (oracle, inform, base) about > containing knowledge. They also seem to roll off the tongue a little > easier. Actually, I think Postgres originally came out of the Ingres family, which is still around. It is an odd name, but sometimes odd names get remembered. My company has a commercial database that is called "Diogenes", and it gets remembered because it is so different from other databases that are similar (like Medline or other healthcare related online databases). Brett W. McCoy http://www.lan2wan.com/~bmccoy --- "The Number of UNIX installations has grown to 10, with more expected." -- The UNIX Programmer's Manual, 2nd Edition, June, 1972
Re: [GENERAL] Postgres vs commercial products
On Wed, 22 Jul 1998, Bruce Momjian wrote: > University Ingres was first, then commercial Ingres was a split-off. > PostgreSQL is based on University Ingres. That's what I thought... Brett W. McCoy http://www.lan2wan.com/~bmccoy --- "The Number of UNIX installations has grown to 10, with more expected." -- The UNIX Programmer's Manual, 2nd Edition, June, 1972
Re: [GENERAL] How about this LOGO?
On Fri, 24 Jul 1998, The Hermit Hacker wrote: > Hey, my preference is an elephant. Big, strong, reliable and > never forgets... Too many connotations with the GOP... Brett W. McCoy http://www.lan2wan.com/~bmccoy --- "The Number of UNIX installations has grown to 10, with more expected." -- The UNIX Programmer's Manual, 2nd Edition, June, 1972
Re: [GENERAL] How about this LOGO?
On Fri, 24 Jul 1998, The Hermit Hacker wrote: > > Too many connotations with the GOP... > > Enlighten me...what is "the GOP"? Sorry. The US Republican Party, which uses an elephant for their mascot. Brett W. McCoy http://www.lan2wan.com/~bmccoy --- "The Number of UNIX installations has grown to 10, with more expected." -- The UNIX Programmer's Manual, 2nd Edition, June, 1972
Re: [GENERAL] Sufficient Primary Key?
On Fri, 24 Jul 1998, Dan Delaney wrote: >I'm working on a library catalog and trying to decide what to use > for the primary key for the authors. Do you think that the first > three letters of first and last name with the birth year would be > sufficient (e.g., Alan Watts would be ALAWAT1915). So, essentially, > do you think there there would ever be two authors with the same > first and last name AND the same birth year? I really don't want to > inject the middle name into there because I can't find the middle > name (or even middle initial) of most authors! You can do multi-column keys in PostgreSQL, I believe, which is essentially what you would be doing by combining that data into a single column. BTW, Alan Watts is a favourite of mine. Brett W. McCoy http://www.lan2wan.com/~bmccoy --- "The Number of UNIX installations has grown to 10, with more expected." -- The UNIX Programmer's Manual, 2nd Edition, June, 1972
Re: [GENERAL] Sufficient Primary Key?
On Fri, 24 Jul 1998, Dan Delaney wrote: > I don't like doing that because I don't want to store the entire > name in every other table which needs to be linked with this one. > So, for instance, in the table that keeps track of what authors go > to what book, I want it to just have two fields, the Book's primary > key and the Author's primary key, and I'd prefer those primary keys > to be nice and small instead of, say, the entire title and copyright > year for the book and the entire first and last name and date of > birth for the author. See what I mean? I see your point, and didn't realize that you had a bunch of other tables linked. I have actually done something similar to what you want to do, but with drug numbers and product codes, mainly because using the drug numbers alone would involve duplicates, and matching approval records to patent records. There's only so much one can normalize. Brett W. McCoy http://www.lan2wan.com/~bmccoy --- "The Number of UNIX installations has grown to 10, with more expected." -- The UNIX Programmer's Manual, 2nd Edition, June, 1972
Re: [GENERAL] Re: [HACKERS] [Fwd: SGVLLUG Oracle and Informix on Linux]
On Fri, 24 Jul 1998, The Web Administrator wrote: > The boys at Postgres have so far done a good job of creating an effecient > Database, let's keep it that way, rather than created a Clone of some > commercial software that might have years of compatibility issues that > forced implementation of methods that may not be the most effecient.. > So let's stop comparing Postgres to the rest.. If there is something we > need to do with our Databases that we can't, then these are the issues we > should address only.. PostgreSQL is the first real database server I've ever used. I've not used Oracle or Sybase or anything like that, so the only thing I can compare it with is desktop database systems like MS-Access, and all I can say is that there' s no comparison. :-) I don't want PostgreSQL to be like Access, nor anything else for that matter, but just be what it is. Brett W. McCoy http://www.lan2wan.com/~bmccoy --- "The Number of UNIX installations has grown to 10, with more expected." -- The UNIX Programmer's Manual, 2nd Edition, June, 1972
Re: [GENERAL] COPY with default values won't work?
On Wed, 24 Mar 1999, Charles Tassell wrote: > @#$#!! Any way to make COPY use default, or shove a lot of data in with a > single INSERT query? According to older messages in the mailing list, it > is *possible*, but I can't get it to work. > > My problem is that I might be using this to put a few thousand entries in > the db every night, and when I last attempted this using a few thousand > insert statements, it was awfully slow (on the order of taking HOURS) You could write a perl script to read the data column by column frm the text file and insert it into the databse using the Pg module. Brett W. McCoy http://www.lan2wan.com/~bmccoy/ --- Quantum Mechanics is God's version of "Trust me." -BEGIN GEEK CODE BLOCK- Version: 3.12 GAT dpu s:-- a C UL$ P+ L+++ E W++ N+ o K- w--- O@ M@ !V PS+++ PE Y+ PGP- t++ 5- X+ R+@ tv b+++ DI+++ D+ G++ e>++ h+(---) r++ y --END GEEK CODE BLOCK--
Re: [GENERAL] Getting total records in result set
On Sat, 10 Apr 1999, Mike Frisch wrote: > What is the easiest (and least system intensive) method of getting the > total number of records in the result set? I am presently opening the > cursor, doing a FETCH ALL, reading PQntuples(), closing the cursor, and > reopening it for the 'real' work. > > Background: I am writing a CGI script that paginates the output from an > SQL query and need to know a total number of records to calculate the > number of pages to display. > > Is what I am going correct or is there a better way? If you create a view with SQL, then do 'select count(*) from ', you can just read the first (and only) row from THAT query. Can you then create your cursor from the view? Brett W. McCoy http://www.lan2wan.com/~bmccoy/ --- What garlic is to food, insanity is to art.
Re: [GENERAL] Oops...
On Sat, 10 Apr 1999, Mike Frisch wrote: > With respect to my previous query, I'd like to clarify that the FETCHes I > am doing are subsets of the full results (hence I cannot use PQntuples). > For instance, I am FETCHing 25 records out of a possible 1000, so > PQntuples() is returning 25. I need some way of knowing there's a 1000 > total. Why can't you get the number of the initial results before fetching them page by page? If you create an initial query and save it as a view, why can't you grab the number out of that first result set (via a ntuples function call or by executing an SQL statement), then page through it with your cursor? Or can cursors not be created from views? Brett W. McCoy http://www.lan2wan.com/~bmccoy/ --- If the human brain were so simple that we could understand it, we would be so simple we couldn't.
Re: [GENERAL] what is the MAX postgres db size & table size? NOBODY KNOWS?
On Thu, 15 Apr 1999, valter wrote: > The Hermit Hacker wrote: > > > there is a max? *raised eyebrow* > > > > i've not understand! > > in Sysbase the limit is 8 Tera Byte, in Mysql a table cannot be much than 2 or 4 > Gb ... > what are the postgresql LIMITS? I think it's dependent on the operating system, isn't it? Brett W. McCoy http://www.lan2wan.com/~bmccoy --- President Thieu says he'll quit if he doesn't get more than 50% of the vote. In a democracy, that's not called quitting. -- The Washington Post
Re: [GENERAL] Any ideas why this doesn't work or how to rewrite it?
On Thu, 29 Apr 1999, Herouth Maoz wrote: > At 18:50 +0300 on 28/04/1999, Aaron Holtz wrote: > > > > db=> select count(distinct customer_username) from customerdata; > > ERROR: parser: parse error at or near "distinct" > > > > How do you get a count of distinct data output via postgres? I can always > > just count the number of tuples returned but this seemed to be a valid > > query. > > Valid it is, but not yet supported in PostgreSQL. > > An (ugly) workaround would be something along the lines of: > > SELECT count(customer_username) > FROM customerdata c1 > WHERE int( oid ) = ( > SELECT min( int( c2.oid ) ) > FROM customerdata c2 > WHERE c1.customer_username = c2.customer_username > ); I think, Aaron, you could get a count of distinct customer names like this: SELECT DISTINCT customer_username, COUNT(*) FROM customerdata GROUP BY customer_username; This will give you 2 columns, one with the distinct customer_usernames and the second with the count of each. The GROUP BY caluse is important here. This looks like what you wanted in your original query. Brett W. McCoy http://www.lan2wan.com/~bmccoy --- Schapiro's Explanation: The grass is always greener on the other side -- but that's because they use more manure.
Re: [GENERAL] Optimizations for busy DB??
On Thu, 13 May 1999, Brian wrote: > > I am assuming here, of course, that this didn't change betwen 6.3 and 6.4 > > (which is what I am using). > > ok, so then I am assuming: > > /usr/bin/postmaster -B 256 -i -S -D/var/lib/pgsql -o -F -S 1024 > > is what I would want... Yep. You can also put the -B as a backend option, but under postmaster, it still does the shared buffer allocation. Brett W. McCoy http://www.lan2wan.com/~bmccoy --- "When are you BUTTHEADS gonna learn that you can't oppose Gestapo tactics *with* Gestapo tactics?" -- Reuben Flagg
Re: [GENERAL] Optimizations for busy DB??
On Thu, 13 May 1999, Brian wrote: > > Actually, you're right -- you only want to pass -B to the backend if you > > are running it standalone, otherwise use the postmaster option. Note, > > standalone as opposed to what? I run it with -i and have clients all over > connecting to it? You can run postgres directly without the postmaster, for debugging purposes. This isn't recommended for regular usage, of course. Brett W. McCoy http://www.lan2wan.com/~bmccoy --- Brook's Law: Adding manpower to a late software project makes it later
Re: [GENERAL] Full Text Searches
On Sun, 23 May 1999, Bruce Momjian wrote: > We have a fulltext stuff in the contrib directory. What's it called? I only see some tcl frontend stuff. Despite my pessimism form the prior message, I am interested in a full text retrieval engine. Brett W. McCoy http://www.lan2wan.com/~bmccoy/ --- Lonely is a man without love. -- Englebert Humperdinck
Re: [GENERAL] Full Text Searches
On Mon, 24 May 1999, Bruce Momjian wrote: > > What's it called? I only see some tcl frontend stuff. Despite my > > pessimism form the prior message, I am interested in a full text retrieval > > engine. > > It is called contrib/fulltextindex. Does someone want to suggest a > better name? I didn't see it on the ftp site. I only saw pgv and tcldb in the contrib directory. Brett W. McCoy http://www.lan2wan.com/~bmccoy --- Cabbage, n.: A familiar kitchen-garden vegetable about as large and wise as a man's head. -- Ambrose Bierce, "The Devil's Dictionary"
Re: [GENERAL] Full Text Searches
On Mon, 24 May 1999, Bruce Momjian wrote: > Sorry, I meant in the distribution's contrib directory, not the ftp > site. I didn't even know we had a contrib directory on the ftp site. Wel, you do now! Thanks! I'll check it out! Brett W. McCoy http://www.lan2wan.com/~bmccoy --- Once, adv.: Enough. -- Ambrose Bierce, "The Devil's Dictionary"
Re: [GENERAL] Full Text Searches
Hey, found the module. Looks pretty interesting -- even has the capability of ignoring stopwords. This is just what I am looking for! Brett W. McCoy http://www.lan2wan.com/~bmccoy --- "What's the use of a good quotation if you can't change it?" -- Dr. Who
Re: [GENERAL] "group, by", problem, when, combined, with, "insert, into"
On Wed, 4 Aug 1999, sam smith wrote: > select loser,count(*) from moves group by loser; > > but when i combine it with an insert into - > insert into losses select loser,count(*) from moves group by loser; > > I get > ERROR: Illegal use of aggregates or non-group column in target list I think you want 'select loser, count(*) into losses from moves group by loser' Brett W. McCoy http://www.lan2wan.com/~bmccoy --- Keep Cool, but Don't Freeze - Hellman's Mayonnaise
Re: [GENERAL] Convert MS access database into PostgreSQL
On Fri, 15 Oct 1999, Duncan Kinder wrote: > How do you import text data into Postgres? > > You should be able to export Access data into text format and then - if it > is possible to import that text - import that text into Postgres. > > This would be generally interesting to someone working in, for example, > computer assisted reporting (CAR), where they spend a lot of time > downloading - say, census or Bureau of Labor Statistics data - into their > databases and then manipulating that data. You can do it with the SQL COPY statement (you must be the postgres superuser to do it), or the psql command \copy (and you don't need to be the superuser, but it has some limitations). Brett W. McCoy http://www.lan2wan.com/~bmccoy --- "I stayed up all night playing poker with tarot cards. I got a full house and four people died." -- Steven Wright
Re: [GENERAL] New PostgreSQL book on web site
On Tue, 26 Oct 1999, Bruce Momjian wrote: > I am in the process of writing a book about PostgreSQL for Addison, > Wesley to be published in 2000. Cool! Brett W. McCoy http://www.lan2wan.com/~bmccoy --- The Heineken Uncertainty Principle: You can never be sure how many beers you had last night.
Re: [GENERAL] Stored Procedures
On Fri, 5 Nov 1999, Jeff MacDonald wrote: > 1: does postgres support stored procedures Yes, quite extensively, and far beyond what SQL 7 offers. You may want to take a look at the programmer's manual for the documentation. > 2: say a user has a microsoft sql server 7 database > with ~120 stored procedures, and alot of data, is their > a script or tool to convert that to a postgres database > or does it have to be done by hand. > You can convert the data over with ODBC, but not the stored procedures. Under SQL 7, stored procedures are essentially SQL batch files and cannot be used as a term in an expression, whereas under PostgreSQL, you can create true functions in PL/PgSQL (a procedural language akin to Oracle's), or as loadable executable modules written in C, C++, Tcl, etc., that returns values and objects, and can be used in an expression. I was shocked recently when we put in SQL 7 in our office (we've been using PostgreSQL for a while now, but we needed SQL 7 to use with a commercial retrieval system) and needed to start writing functions as I was used to under PostgreSQL, and couldn't. A big win for PostgreSQL! Brett W. McCoy http://www.lan2wan.com/~bmccoy --- There are people so addicted to exaggeration that they can't tell the truth without lying.
Re: [GENERAL] full-text indexing
On Tue, 18 Apr 2000, Bruce Momjian wrote: > I have one word for you: CLUSTER. Without it, index lookups are too > slow. With it, they are rapid. I have done some work like this > commerically with Ingres, which has an ISAM type that keeps the matching > rows pretty close on a newly-created ISAM index. In PostgreSQL, and > regular CLUSTER will keep you good. I agree! The last bit of advice given in the full text README. As I said, I'd built full-text stuff for experimentation (I had maybe 30k of raw text, which amounted to several 100,000 indexed entries), and I had clustered it, and it was pretty darn fast, even on a Pentium 233 with only 48 megs of RAM. I have significantly better hardware to run it on now. The original project called MySQL, but it just didn't have what we needed to put something like this together. > If you find it slow, let me know. I have done some benchmarking with > the author and he found it pretty fast, usually a few seconds. See the > section in my book on CLUSTER for information on _why_ it helps. Thanks, Bruce. Brett W. McCoy http://www.chapelperilous.net --- Twenty two thousand days. Twenty two thousand days. It's not a lot. It's all you've got. Twenty two thousand days. -- Moody Blues, "Twenty Two Thousand Days"
Re: [GENERAL] full-text indexing
On Tue, 18 Apr 2000, Bruce Momjian wrote: > > I agree! The last bit of advice given in the full text README. As I > > said, I'd built full-text stuff for experimentation (I had maybe 30k of > > raw text, which amounted to several 100,000 indexed entries), and I had > > clustered it, and it was pretty darn fast, even on a Pentium 233 with only > > 48 megs of RAM. I have significantly better hardware to run it on now. > > The original project called MySQL, but it just didn't have what we needed > > to put something like this together. > > With the original author, testing was fast, but when he loaded all the > data, it got very slow. The problem was that as soon as his data > exceeded the buffer cache, performance became terrible. How much data are we talking here? How can one get around this buffer cache problem? Brett W. McCoy http://www.chapelperilous.net --- Twenty two thousand days. Twenty two thousand days. It's not a lot. It's all you've got. Twenty two thousand days. -- Moody Blues, "Twenty Two Thousand Days"
Re: [GENERAL] full-text indexing
On Tue, 18 Apr 2000, Bruce Momjian wrote: > Let me be specific. The problem is that without cluster, your fragment > rows are together in the index, but are all over the heap table, so you > have to read in all those disk buffers, and that is slow. With cluster, > most of your matching fragments are on the same disk page, so one access > gets them all. > > The nightly CLUSTER is a pain, but the only way I ever got it working > quickly. This is pretty painless compared to what I've had to do getting big full-text systems like Excalibur to work. I can deal with a nightly cluster. Brett W. McCoy http://www.chapelperilous.net --- Twenty two thousand days. Twenty two thousand days. It's not a lot. It's all you've got. Twenty two thousand days. -- Moody Blues, "Twenty Two Thousand Days"
Re: [GENERAL] storing large amounts of text
On Sat, 22 Apr 2000, Paul Dlug wrote: > I thought TEXT had a limit of 8K or something around that size, I would > need something larger. You'd be better off breaking the text up into smaller fields. Another option is to store a pathname to a file that contains the text of your article. Brett W. McCoy http://www.chapelperilous.net --- QOTD: "He's on the same bus, but he's sure as hell got a different ticket."
Re: [GENERAL] USMARC and postgresql?
On Tue, 9 May 2000, Andrew Sullivan wrote: > BLOBs. It's a pain, though, because building the indices against the > catalogue is difficult. I think, actually, that the PICK-based spproach is > better, but PICK is going away. Anyway, you likely need to talk to someone > about BLOBs. I can't help, sorry. The MARC format is really intended to be used in a full-text search environment, such as the old online systems like Dialog or Lexix-Nexis use (although they don't use MARC specifically). They all predate SQL and mapping one onto the other is not an easy task. It's easy to go from a SQl database to one of the variable-length (or 80 column card image) text-formats, but not the other way around unless you are into doing some text-processing programming. Brett W. McCoy http://www.chapelperilous.net --- If the very old will remember, the very young will listen. -- Chief Dan George
Re: [GENERAL] Postgres Tool Question
On Tue, 9 May 2000, Samuel A. Mullen wrote: > Are there any tools for generating reports for Postgres? I searched > RedHat, Enterprise Linux, the HOWTO, and haven't found anything. http://www.perl.com :-) I think pgAccess has some report printing faciliites as well. You can always connect to PostgreSQL via MS-Access and generate reports out of that also. Brett W. McCoy http://www.chapelperilous.net --- If the very old will remember, the very young will listen. -- Chief Dan George
