Re: how can i use SELECT to find a substring of a column's data?

On Friday 12 September 2003 03:34, JoshuaF wrote:
> hello everyone i am very new to postgresql 7.3.3 and am using it on my
> mac G4 OS X and i am having some trouble which i hope you can help me
> out with some answers to my questions:
>
> 1.) How can I get a list of users currently logged into the database?
> Is it possible?

Check the table pg_stat_activity - you'll need to turn statistics on I think.
See the manual for plenty of details.

> 2) I plan on using postgresql  for a posting board database, how can I
> search for a certain word that is in the post's topic subject header?
> For example if the post subject was "Flying to the moon" and the user
> wants to find that post he/she enters 'moon' in the search field I
> made, I've used the : SELECT * FROM mytable WHERE (subject) LIKE
> 'moon');  command to find a one word exact match but don't know how to
> find just one word in a string of words using postgresql.  (  web/http
> is not an option for my posting board. )

Like '%moon%' will work, but won't use an index. There is a full-text search
system in contrib that sounds like exactly what you need though.

> 3.) Is having the default user 'Postgres' a security risk? wouldn't a
> hacker look for that default user name and thus make it easier for him
> to break in?

Not really - you can disable logins for the user anyway, it just makes admin a
little more fiddly at times. If you compile from source, there's nothing to
stop you making up your own username though.

> 4.) Is there a database table limit? (can I have 700 tables in my
> database?)

Yep. I think limits are discussed in the FAQ. From memory, 1600 columns per
table, terabytes for the database if you have enough disk.

> 5.) is there a text length limit? as I said I plan on using postgresql
> and your plugin for a Posting board and also an email system for my
> server so I am wondering if there is a limit to the posts and email
> lengths?

The TOAST system lets you store MB of data in a text field. No problem there.

> 6.) How can I backup my postgresql database and tables? is it possible
> to backup only certain tables and not the whole database?
> Is it possible to use your plugin for regular scheduled backups?

Use pg_dump for your backups - you can backup a single table or a whole
database in a variety of formats. Backups are guaranteed to be a consistent
snapshot of your database. You can schedule regular backups from cron (man
crontab). Look in the "client applications" section of the manual for info on
pg_dump.

> 7.) how many users can a mac OS X system support at one time while
> maintaining decent/good performance? does postgreSQL have a limit for
> mac servers? i've read that windows has a limit of only about 50
> simultaneous users. i have a fast G4 and if i have to will buy a G5 i
> suspect i'll need to support 200 simultaneous users that are actively
> file swapping. can a G4/G5 handle that?

You'll really need to run some tests. If there are lots of updates your
biggest problem will be disk I/O - plenty of fast SCSI disks and a raid
controller with battery-backed write cache will help there. The performance
list is the place to get more details.
If you do lots of reading but few updates, then plenty of RAM is good.

> 8.) can anyone recommend a really good book for Mac OS X and
> postgreSQL? it's very hard for me to get good help/information online,
> it's slow and complicated to me and it also hurts my eyes. has anyone
> read O'reily's book 'practicle postgresql'? is it good?

Well, Bruce's book is probably a little old now, but is a good guide. I think
there are a list of books on http://techdocs.postgresql.org/
I also seem to remember someone producing a PDF version of the manuals, so you
could print them off.

> I know these are alot of questions but for some reason i am having a
> really hard time trying to figure it out on my own.
> thank you very much everyone.

That's what the list is for. All the lists are archived at
http://archives.postgresql.org with a search form too - that can be useful.
--
  Richard Huxton
  Archonet Ltd