Re: [GENERAL] Oracle and Postgresql
* [EMAIL PROTECTED] ("Richard Broersma") wrote:
>
> On Sun, Aug 31, 2008 at 1:50 PM, Kevin Hunter <[EMAIL PROTECTED]> wrote:
>
> > 7. Though I don't personally buy it, I have heard others complain
> > loudly that there is no print-version of Postgres documentation.
>
>
> This one should be taken off the list. The postgresql online
> reference manual is in print( volumes 1 - 3)
> http://www.amazon.com/PostgreSQL-Reference-Manual-SQL-Language/dp/0954612027/ref=pd_sim_b_1
Though I mostly use the online version, I'm considering buying these.
However, I'm probably going to kill myself if I find a new edition for
sale only a short while after I've bought them. Are there any plans to
release an updated set in the near future?
--
Lars Haugseth
"If anyone disagrees with anything I say, I am quite prepared not only to
retract it, but also to deny under oath that I ever said it." -Tom Lehrer
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Duplicating a table row while honouring key constraints
* Gordon <[EMAIL PROTECTED]> wrote:
>
> I'm developing a web application in PHP and Postgres that will
> basically serve as a CMS. I want to implement a feature to allow
> users to make copies of documents or folders, so this will require the
> appropriate rows to be duplicated.
>
> If possible I'd like to do this with SQL queries and avoid SELECTing
> the row, munging it in PHP and INSERTING it back. I suspect that this
> is probably the way I'll have to go, but if it could be done entirely
> in SQL that would be nice.
>
> At first I thought INSERT INTO table_name SELECT * from table_name
> where primary_key = unique_value would do it, but that would obviously
> violate the primary key uniqueness constraint. I'm wondering if
> there's a way to do this where I only grab the data to be copied and
> let the database work out the new primary key itself.
If your primary key is a column named 'id' of type 'serial', you can
copy a record like this:
INSERT INTO my_table
SELECT nextval('table_name_id_seq'), foo, bar, baz, ...
FROM my_table
WHERE id =
--
Lars Haugseth
"If anyone disagrees with anything I say, I am quite prepared not only to
retract it, but also to deny under oath that I ever said it." -Tom Lehrer
---(end of broadcast)---
TIP 1: 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] passing passords to pgsql/pg_create/pg_dump programmatically
* "filippo" <[EMAIL PROTECTED]> wrote: > > Hello, > > I have written a program perl/Tkprogram, based on postgres. For > maintenance reasons in my program I use these commands: > > `dropdb -U postgres -h $BACKUP_SERVER $BACKUP_DATABASE_NAME`; > `createdb -U postgres -h $BACKUP_SERVER $BACKUP_DATABASE_NAME`; > `pg_dump -U postgres -h $DATABASE_SERVER $DATABASE_NAME | psql - > U postgres -h $BACKUP_SERVER $BACKUP_DATABASE_NAME`; > > my @psqlOutput = `psql -l -U postgres -h $_`; > > > my program has a graphic interface but whenever I use these command, > postgres ask for passord in the command line. How can I give these > commands the right passowrd programmatically or how can I interact > with these to give the passwords by a graphic box? $ dropdb --help $ createdb --help $ pg_dump --help As you can see, all of these command line utilities accept the -W parameter followed by the password. Alternatively you can configure PostgreSQL to allow connections from certain clients without password authentication: http://www.postgresql.org/docs/8.2/static/client-authentication.html -- Lars Haugseth "If anyone disagrees with anything I say, I am quite prepared not only to retract it, but also to deny under oath that I ever said it." -Tom Lehrer ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Compound words giving undesirable results with tsearch2
* oleg@sai.msu.su (Oleg Bartunov) wrote:
|
| On Tue, 30 May 2006, Lars Haugseth wrote:
|
| > I've setup a database using tsearch2, configured with support for compound
| > words according to the excellent guide found here:
| >
| > http://www.sai.msu.su/~megera/oddmuse/index.cgi/Tsearch_V2_compound_words
| >
| > This works fine. There is however one drawback that I'd like to know
| > whether can be remedied. Let's say I want to search for records containing
| > the word 'fritekst', which is a compound Norwegian word meaning
| > 'free text'.
| >
| > testdb=# select to_tsquery('default_norwegian', 'fritekst');
| > to_tsquery
| > --
| > 'fritekst' | 'fri' & 'tekst'
| > (1 row)
| >
| > Now, this will indeed match those records, but it will also match any
| > records containing both of the words 'fri' and 'tekst', without regard
| > to whether they are next to each other or in completely different parts
| > of the text being indexed. In many situations, this will lead to a lot
| > of 'false' matches, seen from a user perspective.
| >
| > Ideas on how to handle this problem will be much appreciated.
|
| this is where order by relevance should helps.
Thank you for pointing me to this, I hadn't thought about that.
However, my first try with the rank_cd() function does not quite
produce the results I had expected:
SELECT set_curcfg('default_norwegian');
SELECT id, rank_cd(n, mytscol, to_tsquery('fritekst')) AS rank
FROM mytable
WHERE mytscol @@ to_tsquery('fritekst')
ORDER BY rank DESC;
No matter what value I use for n here, a record where the compound word
'fritekst' appears gets a rank of 0, where as records where the words
'fri' and 'tekst' appears separately all gets a rank > 0, the closer
together, the higher the rank.
If I try to set the value of n to 0, I still get a rank of 0 for a
record containing 'fritekst', and 1 for all records containing 'fri'
and 'tekst'.
When using the rank() function instead of rank_cd() in the query above,
records with the word 'fritekst' seem to score better, but I still get
higher ranks for some records containing the separate words and not the
compound word.
--
Lars Haugseth
"If anyone disagrees with anything I say, I am quite prepared not only to
retract it, but also to deny under oath that I ever said it." -Tom Lehrer
---(end of broadcast)---
TIP 1: 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
[GENERAL] Compound words giving undesirable results with tsearch2
I've setup a database using tsearch2, configured with support for compound
words according to the excellent guide found here:
http://www.sai.msu.su/~megera/oddmuse/index.cgi/Tsearch_V2_compound_words
This works fine. There is however one drawback that I'd like to know
whether can be remedied. Let's say I want to search for records containing
the word 'fritekst', which is a compound Norwegian word meaning
'free text'.
testdb=# select to_tsquery('default_norwegian', 'fritekst');
to_tsquery
--
'fritekst' | 'fri' & 'tekst'
(1 row)
Now, this will indeed match those records, but it will also match any
records containing both of the words 'fri' and 'tekst', without regard
to whether they are next to each other or in completely different parts
of the text being indexed. In many situations, this will lead to a lot
of 'false' matches, seen from a user perspective.
Ideas on how to handle this problem will be much appreciated.
--
Lars Haugseth
"If anyone disagrees with anything I say, I am quite prepared not only to
retract it, but also to deny under oath that I ever said it." -Tom Lehrer
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] psql + autocommit
* Michael Paesold: | Peter Eisentraut wrote: | > Lars Haugseth wrote: | > > Version 8.0.0beta2 supports a global configuration file. It's should | > > be located in '~postgres/etc/pgsql'. | > | > That would be pretty useless, since normal users often don't have read | > access to another user's home directory. | | Further up in this thread I read $PGDATA/etc/psqlrc, which seems like | nonsense, too. First, $PGDATA is not known at compile time, second no user | should have access to that directory. | | Perhaps it should be (and really is) $PREFIX/etc/psqlrc? | (where $PREFIX is of course the installation prefix set during configure) | | I am not at work, so I can have a look at the code or test it myself right | now. There's a file $PREFIX/share/psqlrc.sample that reads: -- -- psql configuration file -- -- This file is read before the .psqlrc file in the user's home directory. -- -- Copy this to your sysconf directory (typically /usr/local/pgsql/etc) and -- rename it psqlrc. So $PREFIX/etc/psqlrc is the right place. (In my setup, ~postgres equals $PREFIX). This directory is not created by "gmake install", though. Perhaps it should be? -- Lars Haugseth Tinde ASA mob 92087323 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] pg_dump in cycle
* Najib Abi Fadel:
| This seems to be more interesting for shell scripting:
|
| psql -d DatabaseName -c 'select datname from pg_database where not
| datistemplate' ;
|
| datname
| -
| fgm_eval
| hotline
| usj
| dragon_devel
| dragon_joujou
| dragon_devel_v2
| dragon_prod
| fgm
| (8 rows)
$ psql -ltA | cut -d'|' -f1 | grep -v '^template' | xargs -i pg_dump -f {}.pg_dump {}
--
-- PostgreSQL database dump complete
--
--
-- PostgreSQL database dump complete
--
$ ls -l *.pg_dump
-rw-r--r--1 postgres users 493 2004-09-17 14:00 foo.pg_dump
-rw-r--r--1 postgres users3326 2004-09-17 14:00 test.pg_dump
--
Lars Haugseth
---(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] psql + autocommit
* Peter Eisentraut: | John Sidney-Woollett wrote: | > Any ideas why the global file doesn't work? | | There is no support for a global configuration file at this time. I | suggested that you *implement* it. Version 8.0.0beta2 supports a global configuration file. It's should be located in '~postgres/etc/pgsql'. -- Lars Haugseth ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
