[SQL] pg_dump "feature"
In 7.1.2 you can launch this command without any problems: pg_dump -u -s database < file.txt where file.txt contains 2 lines: username password ~ ~ ~ And you'll get the dump of the . In 7.1.3 this command work, the only incovenience is that pg_dump shows "User name: Password:" but gets the password from file. In 7.3.x the pg_dump accepts the password only from stdin. The user must enter his password. It's a feature or a bug, because i can't see any logic in this behaviour. I hope i'll receive an answer, or else i'll post this topic on developers list :) Thanks.
Re: [SQL] problem with temporary table.
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Thursday 10 July 2003 03:03, Vijay Kumar wrote: > Hi, > We are using postgresql7.3.3, we are encountering some problems by > using temporary tables. > > Actually our requirement was, > 1. create temporary table. > 2. insert some values on that table by using some quries. > 3. select the inserted values from the temporary table. > < snip> > > The above function is working fine for the first call, from next > call onwards it is throwing the below error. Error: relation 'temp_table' > already exists. > So drop the table when you are done with it. You may also want to investigate returning a table of data, rather than a cursor or a single row. I am not too sure on how this would work exactly because I have never done it. Someone else may be willing to fill in the details. - -- Jonathan Gardner <[EMAIL PROTECTED]> (was [EMAIL PROTECTED]) Live Free, Use Linux! -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQE/FVY4WgwF3QvpWNwRAjIXAJ47L28D29zv91JGXQnA1rQ79wqRlwCeLB56 /+FhmG0tosyNXyH61po2myY= =UWtn -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] numerical sort on mixed alpha/numeric data
Hi folks, I've got a table holding loco numbers and an id which references the locos table. How can I sort this table, so that numeric values appear first in numerical order followed by alpha in alpha order.? nymr=# \d lnumbers Table "lnumbers" Column | Type | Modifiers ---+---+--- lnid | integer | not null lnumber | character varying(10) | not null lncurrent | boolean | Primary key: lnumbers_pkey Triggers: RI_ConstraintTrigger_7121182 nymr=# select * from lnumbers order by lnumber; lnid | lnumber | lncurrent --+-+--- 26 | 08556 | t 13 | 08850 | f 2 | 2392| f 15 | 24 061 | t 12 | 25 278 | f 1 | 29 | t 5 | 30926 | t 3 | 4277| t 7 | 44767 | t 21 | 45157 | t 13 | 4518| t 6 | 45212 | t 16 | 45337 | t 23 | 4771| f 19 | 5 | t 24 | 55019 | t 27 | 59 | f 11 | 60007 | t 8 | 60532 | t 23 | 60800 | t 14 | 62005 | t 14 | 62012 | f 18 | 64360 | f 2 | 65894 | t 17 | 6619| t 27 | 69023 | t 9 | 75014 | t 10 | 75029 | t 22 | 76079 | t 4 | 80135 | t 20 | 825 | t 18 | 901 | t 5 | 926 | f 26 | D3723 | f 15 | D5061 | t 12 | D7628 | t 25 | D9009 | t 24 | D9019 | f (38 rows) nymr=# -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Data Warehousing
Hi, I'd like to learn about Data Warehousing - using PostgreSQL of course. I've been looking around for some good starting info. on this subject without a lot of joy so I'd like to ask if anyone could point me to a good starting off doco. or tutorial. I have found some data out there but what I have found has been pretty abstract and refers to propriety software of which I'm not interested. Any help here would be sensational. Many thanks. Best regards Rudi. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] pg_dump "feature"
"Viorel Dragomir" <[EMAIL PROTECTED]> writes: > In 7.3.x the pg_dump accepts the password only from stdin. The user must en= > ter his password. > It's a feature or a bug, because i can't see any logic in this behaviour. It's a feature ... or at least an intentional change. If you want to keep your password in a file, see the ~/.pgpass feature. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] numerical sort on mixed alpha/numeric data
Gary Stainburn wrote: Hi folks, I've got a table holding loco numbers and an id which references the locos table. How can I sort this table, so that numeric values appear first in numerical order followed by alpha in alpha order.? What about select lnid,lnumber,lncurrent from (select *, case when lnumber ~ '^[0-9]+' then lnumber::int else null end as number from lnumber) order by number, lnumber I hope, it helps... Dima nymr=# \d lnumbers Table "lnumbers" Column | Type | Modifiers ---+---+--- lnid | integer | not null lnumber | character varying(10) | not null lncurrent | boolean | Primary key: lnumbers_pkey Triggers: RI_ConstraintTrigger_7121182 nymr=# select * from lnumbers order by lnumber; lnid | lnumber | lncurrent --+-+--- 26 | 08556 | t 13 | 08850 | f 2 | 2392| f 15 | 24 061 | t 12 | 25 278 | f 1 | 29 | t 5 | 30926 | t 3 | 4277| t 7 | 44767 | t 21 | 45157 | t 13 | 4518| t 6 | 45212 | t 16 | 45337 | t 23 | 4771| f 19 | 5 | t 24 | 55019 | t 27 | 59 | f 11 | 60007 | t 8 | 60532 | t 23 | 60800 | t 14 | 62005 | t 14 | 62012 | f 18 | 64360 | f 2 | 65894 | t 17 | 6619| t 27 | 69023 | t 9 | 75014 | t 10 | 75029 | t 22 | 76079 | t 4 | 80135 | t 20 | 825 | t 18 | 901 | t 5 | 926 | f 26 | D3723 | f 15 | D5061 | t 12 | D7628 | t 25 | D9009 | t 24 | D9019 | f (38 rows) nymr=# ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] numerical sort on mixed alpha/numeric data
On Wednesday 16 July 2003 3:27 pm, Dmitry Tkach wrote: > Gary Stainburn wrote: > >Hi folks, > > > >I've got a table holding loco numbers and an id which references the locos > >table. How can I sort this table, so that numeric values appear first in > >numerical order followed by alpha in alpha order.? > > What about > > select lnid,lnumber,lncurrent from > (select *, case when lnumber ~ '^[0-9]+' then lnumber::int else null > end as number from lnumber) > order by number, lnumber > > > I hope, it helps... > > Dima Hi, thanks for this. I had to alias the sub-select, and the cast from varchar to int didn't work, below is the working version. select lnid,lnumber,lncurrent from (select *, case when lnumber ~ '^[0-9]+' then lnumber::text::int else null end as number from lnumbers) foo order by number, lnumber; Gary > > >nymr=# \d lnumbers > > Table "lnumbers" > > Column | Type | Modifiers > >---+---+--- > > lnid | integer | not null > > lnumber | character varying(10) | not null > > lncurrent | boolean | > >Primary key: lnumbers_pkey > >Triggers: RI_ConstraintTrigger_7121182 > > > >nymr=# select * from lnumbers order by lnumber; > > lnid | lnumber | lncurrent > >--+-+--- > > 26 | 08556 | t > > 13 | 08850 | f > >2 | 2392| f > > 15 | 24 061 | t > > 12 | 25 278 | f > >1 | 29 | t > >5 | 30926 | t > >3 | 4277| t > >7 | 44767 | t > > 21 | 45157 | t > > 13 | 4518| t > >6 | 45212 | t > > 16 | 45337 | t > > 23 | 4771| f > > 19 | 5 | t > > 24 | 55019 | t > > 27 | 59 | f > > 11 | 60007 | t > >8 | 60532 | t > > 23 | 60800 | t > > 14 | 62005 | t > > 14 | 62012 | f > > 18 | 64360 | f > >2 | 65894 | t > > 17 | 6619| t > > 27 | 69023 | t > >9 | 75014 | t > > 10 | 75029 | t > > 22 | 76079 | t > >4 | 80135 | t > > 20 | 825 | t > > 18 | 901 | t > >5 | 926 | f > > 26 | D3723 | f > > 15 | D5061 | t > > 12 | D7628 | t > > 25 | D9009 | t > > 24 | D9019 | f > >(38 rows) > > > >nymr=# -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] pg_dump "feature"
Thanks for the answer[s].
Interesting that when i launch the command from php through exec() it works
fine even on pg_dump 7.3.x
I guess this feature works only on [shell] command line.
This is the php code:
$tmp_fname = tempnam("/tmp", "file-");
$f = fopen($tmp_fname, "w");
fwrite($f, "$username\n".USER_DB_PASSWORD."\n");
fclose($f);
$cmd = "pg_dump $add_cmd $export $add_table < $tmp_fname";
$result = system($cmd);
unlink($tmp_fname);
- Original Message -
From: "Tom Lane" <[EMAIL PROTECTED]>
To: "Viorel Dragomir" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Wednesday, July 16, 2003 5:29 PM
Subject: Re: [SQL] pg_dump "feature"
> "Viorel Dragomir" <[EMAIL PROTECTED]> writes:
> > In 7.3.x the pg_dump accepts the password only from stdin. The user must
en=
> > ter his password.
> > It's a feature or a bug, because i can't see any logic in this
behaviour.
>
> It's a feature ... or at least an intentional change.
>
> If you want to keep your password in a file, see the ~/.pgpass feature.
>
> regards, tom lane
>
> ---(end of broadcast)---
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] Data Warehousing
Rudi Starcevic wrote: Hi, I'd like to learn about Data Warehousing - using PostgreSQL of course. I've been looking around for some good starting info. on this subject without a lot of joy so I'd like to ask if anyone could point me to a good starting off doco. or tutorial. I have found some data out there but what I have found has been pretty abstract and refers to propriety software of which I'm not interested. Well, I really recommend reading the classic from Kimball first: The Data Warehouse Toolkit (http://www.amazon.com/exec/obidos/tg/detail/-/0471200247/qid=1058369223/sr=1-1/ref=sr_1_1/104-9522807-8010345?v=glance&s=books) As I come from an Oracle Background, I do not yet know where to get good Postgres-specific DWH knowledge. Regards, Dani ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] numerical sort on mixed alpha/numeric data
On Wed, Jul 16, 2003 at 12:48:26 +0100, Gary Stainburn <[EMAIL PROTECTED]> wrote: > Hi folks, > > I've got a table holding loco numbers and an id which references the locos > table. How can I sort this table, so that numeric values appear first in > numerical order followed by alpha in alpha order.? You can first sort by whether or not a pattern match succeeds. You haven't said whether or not the empty string is a number or an alpha and that will affect the choice of pattern. For example: select * from lumbers order by lumber !~ '^[0-9]+$', lumber; ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] numerical sort on mixed alpha/numeric data
On Wed, Jul 16, 2003 at 11:41:06 -0500, Bruno Wolff III <[EMAIL PROTECTED]> wrote: > On Wed, Jul 16, 2003 at 12:48:26 +0100, > Gary Stainburn <[EMAIL PROTECTED]> wrote: > > Hi folks, > > > > I've got a table holding loco numbers and an id which references the locos > > table. How can I sort this table, so that numeric values appear first in > > numerical order followed by alpha in alpha order.? > > You can first sort by whether or not a pattern match succeeds. You haven't > said whether or not the empty string is a number or an alpha and that will > affect the choice of pattern. > > For example: > select * from lumbers order by lumber !~ '^[0-9]+$', lumber; I missed the need for numeric ordering for the numbers. The CASE solution someone else suggested seems to answer your question though. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] relevance
I'm trying to build a really basic search engine for a site I'm using - say I'm going to simplify this as much as I can. Say I have a table with 2 columns: id, message Person wants to search for the term 'sql'. So, I'd do a simple search like: select id from tablename where message like '%sql%'; If there any way to determine exactly how many times 'sql' is matched in that search in each particular row, and then sort by the most matches, or am I going to have to write a script to do the sorting for me? Thanks! ---(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: [SQL] relevance
Have you seen contrib/tsearch ? http://www.sai.msu.su/~megera/postgres/gist/tsearch/ Oleg On Wed, 16 Jul 2003, jtx wrote: > I'm trying to build a really basic search engine for a site I'm using - > say I'm going to simplify this as much as I can. > > Say I have a table with 2 columns: id, message > > Person wants to search for the term 'sql'. So, I'd do a simple search > like: > > select id from tablename where message like '%sql%'; > > If there any way to determine exactly how many times 'sql' is matched in > that search in each particular row, and then sort by the most matches, > or am I going to have to write a script to do the sorting for me? > > Thanks! > > > ---(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 > Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] relevance
select id from tablename where message like '%sql%'; If there any way to determine exactly how many times 'sql' is matched in that search in each particular row, and then sort by the most matches, or am I going to have to write a script to do the sorting for me? You could probably write a function in postgres (say, "matchcount()") which returns the match count (possibly using perl and a regex). SELECT matchcount(message,'sql') AS matchcount, id FROM tablename WHERE message LIKE '%sql%' ORDER BY matchcount(message,'sql') DESC The ORDER BY will probably fail, but you can try :) -- Terence Kearns ~ ph: +61 2 6201 5516 IT Database/Applications Developer Enterprise Information Systems Client Services Division University of Canberra www.canberra.edu.au ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Recursive request ...
I see connect by in Oracle ??? is there an equivalent in PostgreSQL or not ?? Rajesh Kumar Mallah a écrit: Dear Bournon, There are already good implementation of Tree structures in databases ranging from using pure SQL to PostgreSQL specfic methods , less point in revinting wheel unless u really need. Some Pointers: "Tree-structure functions" http://www.brasileiro.net:8080/postgres/cookbook/ Gist Based: contrib/ltree Joe Celko's Article on "Nested Sets & Adjacency Lists" http://www.ibase.ru/devinfo/DBMSTrees/sqltrees.html http://users.starpower.net/rjhalljr/Serve/MySQL/traer.html http://www.google.com/search?hl=en&ie=UTF-8&oe=UTF-8&q=adjacency+list+%2B+tree+%2B+joe On Wednesday 09 Jul 2003 8:57 pm, Benoît Bournon wrote: I have to make a function that returns a tree with title and link of a table. Recursively, a information depends on a parent information. It is to organise a menu with parent dependance. How is it possible and faster ? in C ? pl/pgsql or other ? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
