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

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

From
user5234b@yahoo.com (JoshuaF)
Date:
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?

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

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?

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

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?

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?

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?


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?

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.

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

From
Richard Huxton
Date:
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

Re: how can i use SELECT to find a substring of a

From
Scott Ribe
Date:
>> 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.

Restrictions in the Mac OS X implementation of shmget limit Postgres to 2MB
of shared buffers. This could be a problem for large databases and/or heavy
activity.


--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 665-7007 voice


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

From
Dennis Gearon
Date:
Richard Huxton wrote:

>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.
>
>
What's this about battery backud up write caches? In the disk sub system
or in the main system memory architecture? Can it be added to the PC
platform? How does the OS know when it reboots to not wipe that cache
and how to assign it to the correct processes in Postgres?


Re: how can i use SELECT to find a substring of a

From
Scott Ribe
Date:
> What's this about battery backud up write caches? In the disk sub system
> or in the main system memory architecture? Can it be added to the PC
> platform? How does the OS know when it reboots to not wipe that cache
> and how to assign it to the correct processes in Postgres?

In the disk controller. Independent of the platform. OS does not even know
it's there.


--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 665-7007 voice


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

From
Dennis Gearon
Date:
Scott Ribe wrote:

>>What's this about battery backud up write caches?
>>
>In the disk controller. Independent of the platform. OS does not even know
>it's there.
>
>
>
>
Thanks


Re: how can i use SELECT to find a substring of a

From
Jim Crate
Date:
>Restrictions in the Mac OS X implementation of shmget limit Postgres to 2MB
>of shared buffers. This could be a problem for large databases and/or heavy
>activity.

http://www.postgresql.org/docs/7.3/interactive/kernel-resources.html

These directions are sufficient to increase shared buffers significantly beyond
2MB on MacOS 10.2.6.

--
Jim Crate
Deep Sky Technologies, Inc.