Ashish Karalkar wrote:
> ${PG_PATH}/psql -d qsweb --command "CREATE SCHEMA qsweb"
>
> ${PG_PATH}/psql -d qsweb --command "ALTER SCHEMA qsweb OWNER TO qsweb"
>
> ${PG_PATH}/psql --command "ALTER USER qsweb SET SEARCH_PATH='qsweb'"
>
>
> The output is right till alter schema but then while setti
Le lundi 02 juillet 2007, D. Dante Lorenso a écrit :
> I wanted to select several rows of data and have them returned in a
> single record with the rows joined by a delimiter. Turns out this is
> very easy to do in PostgreSQL:
>
>SELECT a.id, a.name,
> ARRAY_TO_STRING(ARRAY(
>SELE
Postgres User wrote:
I have a table of around 6,000 places in the world. Everytime my
server receives a ping, I'm grabbing the content of an article from an
RSS feed. Then I search the article for the presence of any the 6000
terms.
A typical article is around 1200 words.
I don't need to save
The query would become
SELECT a.id, a.name, array_acc(b.name) as b_names
FROM a LEFT JOIN b USING(id)
GROUP BY a.id, a.name;
All variants are possible. Variant with array_to_string is faster and
doesn't need own aggregate function. And you can easy remove
duplicities.
SELECT a.id, a.name,
I had a similar problem and overcame it by temporarily setting
zero_damaged_pages, then doing a full vacuum and re-index on the affected table.
Thanks, I suppose I need to reindex the table afterwards, or they can
point to non existent data ?
Poul
---(end of
and re-index on the affected table.
Sorry, was to fast
Poul
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
LOL. Thats what i get for running configure and make with PG_HOME set to my
8.1.4 install.
With that fixed i will start probing my WAL archives now.
-jason
--- Original Message ---
From: Tom Lane <[EMAIL PROTECTED]>
To: "Jason L. Buberel" <[EMAIL PROTECTED]>
Sent: 7/2/2007, 1:33:24 PM
Hi list,
i have a problem with connecting to postgresql on a remote server. basically i
want to import some sets of data into the database, using the psql command from
within a shell script.
i have a local machine where postgres is installed and a remote one where
postgres is installed as well.
Hello Stefan,
Recently I Have faced this problem and solved by .pgpass file approch
Take alook on
http://www.postgresql.org/docs/8.0/interactive/libpq-pgpass.html
With Regards
Ashish...
- Original Message -
From: "Stefan Zweig" <[EMAIL PROTECTED]>
To:
Sent: Tuesday, July 03, 2007
http://www.postgresql.org/docs/8.2/interactive/libpq-pgpass.html
or you can set PGPASSWORD environment variable (not recommended though)
Stefan Zweig wrote:
Hi list,
i have a problem with connecting to postgresql on a remote server. basically i
want to import some sets of data into the databa
On 03/07/2007 13:47, Stefan Zweig wrote:
i know already that there is a way to connect to a remote postgres
using psql with the options --host 123.123.123.1 --port 5432
--username --password
You can specify the username on the command line, but not the password:
the --password option only ens
On Tue, Jul 03, 2007 at 02:47:58PM +0200, Stefan Zweig wrote:
>
> i appreciate any help.
man psql:
It is also convenient to have a ~/.pgpass file to avoid
regularly having to type in passwords. See the documentation
for more information.
Peter
---(end of broa
Hello,
Is there a simple way/command/function to check whether two strings have
at least one overlap character please?
For example,
str1: 05baaa
str2: ooboo
Query: select str1 ??? str2 ;
Result: true
Because b is in both str1 and str2.
Thank you!
---(end of broadc
Emi Lu wrote:
> Is there a simple way/command/function to check whether two
> strings have at least one overlap character please?
>
> For example,
>
> str1: 05baaa
> str2: ooboo
>
> Query: select str1 ??? str2 ;
>
> Result: true
>
> Because b is in both str1 and str2.
SELECT str1 ~ ( '[' || s
"Jason L. Buberel" <[EMAIL PROTECTED]> writes:
> Instead, in order to achieve my goal I would have to restore to that
> backup, and rely on the contents of the archive_logs to have the
> recovery process return me to the selected xid PITR.
Correct.
> So is there any way to 'trick' or force the
On Jul 2, 2007, at 11:58 PM, Jason L. Buberel wrote:
I am now learning that fact, but recall the original scenario that
I am trying to mimic:
1. Person accidentally deletes contents of important table.
2. Admin (me) wants to roll back db state to just prior to that
deletion.
3. (Me) Assumes
Understood all around - thanks to everyone for helping me clarify (in my
head/understanding) how these pieces fit together.
One last request - can you glance over the plan below and let me know if
it sounds sane? The goal again is to be able to recover to a PITR record
in the very recent past
Is there a way to determine the order that triggers are executed? We are
converting from SQL Anywhere 9 and one of the features that they have is the
ability to add a order statement to triggers. This allows from a programmatic
way to control how triggers are fired if there is more than o
--- Mike Gould <[EMAIL PROTECTED]> wrote:
> Is there a way to determine the order that triggers are executed? We are
> converting from SQL
> Anywhere 9 and one of the features that they have is the ability to add a
> order statement to
> triggers. This allows from a programmatic way to contr
am Tue, dem 03.07.2007, um 12:11:01 -0400 mailte Mike Gould folgendes:
> Is there a way to determine the order that triggers are executed? We are
alphabetically
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0
On 7/2/07, Ron Johnson <[EMAIL PROTECTED]> wrote:
On 06/18/07 08:05, Merlin Moncure wrote:
[snip]
>
> That being said, it's pretty clear to me we are in the last days of
> the disk drive.
Oh, puhleeze. Seagate, Hitachi, Fuji and WD aren't sitting around
with their thumbs up their arses.In 3
Hello,
Can I know how to get the date of each month's last Thursday please?
For example, something like
Query: select getDateBaseOnWeekday('2007-04-01', 'Last Thursday');
Result: 2007-04-26
Thank you!
---(end of broadcast)---
TIP 1: if posting/
Hi, in a stored procedure, if I do COPY table FROM 'mypath' DELIMITERS '\t'; it
works but if my path is a text variable which contain my path, i.e.COPY table
FROM mytextvar DELIMITERS '\t'; it doesn't work, I get ERROR: syntax error at
or near "$1"SQL state: 42601Context: SQL statement in PL/PgS
Charles Pare wrote:
Hi, in a stored procedure, if I do COPY table FROM 'mypath'
DELIMITERS '\t'; it works but if my path is a text variable which
contain my path, i.e.COPY table FROM mytextvar DELIMITERS '\t'; it
doesn't work, I get ERROR: syntax error at or near "$1"SQL state:
42601Context: SQL
am Tue, dem 03.07.2007, um 14:27:24 -0400 mailte Emi Lu folgendes:
> Hello,
>
> Can I know how to get the date of each month's last Thursday please?
>
> For example, something like
>
> Query: select getDateBaseOnWeekday('2007-04-01', 'Last Thursday');
> Result: 2007-04-26
Write you own functi
Emi Lu wrote:
Hello,
Can I know how to get the date of each month's last Thursday please?
For example, something like
Query: select getDateBaseOnWeekday('2007-04-01', 'Last Thursday');
Result: 2007-04-26
Thank you!
CREATE OR REPLACE FUNCTION lastday (date,int) RETURNS DATE AS '
DECLARE
Wow, it works greatThank's for the quick answerThe Postgres community is
amazing!Charles> Date: Tue, 3 Jul 2007 20:01:03 +0100> From: [EMAIL PROTECTED]>
To: [EMAIL PROTECTED]> CC: pgsql-general@postgresql.org> Subject: Re: [GENERAL]
Stored Procedure: Copy table from; path = text variable> > Char
On Jul 3, 2007, at 13:27 , Emi Lu wrote:
Can I know how to get the date of each month's last Thursday please?
For example, something like
Query: select getDateBaseOnWeekday('2007-04-01', 'Last Thursday');
Result: 2007-04-26
Using a function for calculating the first occurrence of a particu
Thank you all for your inputs!
Based on your inputs, made it a bit change to my application:
==
DROP FUNCTION test_db.lastWeekdayDate (date, varchar) ;
CREATE OR REPLACE FUNCTION test_db.lastWeekdayDate (dat
Emi Lu wrote:
Hello,
Can I know how to get the date of each month's last Thursday please?
For example, something like
Query: select getDateBaseOnWeekday('2007-04-01', 'Last Thursday');
Result: 2007-04-26
Thank you!
It turns out my original solution was slightly (badly) wrong and was
retu
On Jul 3, 2007, at 14:54 , Emi Lu wrote:
result := (
(date_part('year', $1) || '-' || date_part('month',
$1) || '-01')::date
+ '1 month'::interval - '1 day'::interval
)::date;
I recommend not using string manipulation to handle data that i
Charles Pare wrote:
Wow, it works great
> Thank's for the quick answer
12 minutes? I've seen bug-patches turned around quicker than that by Tom
;-)
> The Postgres community is amazing!
Mostly by volunteers helping out on lists :-)
There's always someone out there you can lend a helping han
Richard Huxton wrote:
> Charles Pare wrote:
>> Wow, it works great
>> Thank's for the quick answer
>
> 12 minutes? I've seen bug-patches turned around quicker than that by Tom
> ;-)
Yeah, that's really quite disappointing Richard - you need to pull your
socks up :-)
/D
Dave Page wrote:
Richard Huxton wrote:
Charles Pare wrote:
Wow, it works great
Thank's for the quick answer
12 minutes? I've seen bug-patches turned around quicker than that by Tom
;-)
Yeah, that's really quite disappointing Richard - you need to pull your
socks up :-)
Would have been und
I want to select several rows of data and have them returned in a single
record with the rows joined by a delimiter. It would be great if this
could be done in a generic way possibly using the GROUP BY like this:
SELECT a.id, a.name, STR_SUM(b.name, ',') AS b_names
FROM a, b
WHERE a.i
Hello people,
I just installed PostgreSQL on my Mac.
I used the package available here:
http://www.entropy.ch/software/macosx/postgresql/
Here is a screendump of initdb...
$
$
$
$
$ /usr/local/bin/initdb -E UTF8 -D /usr/local/pgsql/data
The files belonging to this database system will be owned b
People,
I found the answer here:
http://wiki.rubyonrails.org/rails/pages/PostgreSQL
I fixed it with 1 (one!) command line:
gem install postgres --
--with-pgsql-include-dir=/usr/local/pgsql/include
--with-pgsql-lib-dir=/usr/local/pgsql/lib
More info:
$
$
$
gem install postgres --
--with-pgsql-
On 07/03/07 13:03, Merlin Moncure wrote:
On 7/2/07, Ron Johnson <[EMAIL PROTECTED]> wrote:
On 06/18/07 08:05, Merlin Moncure wrote:
[snip]
>
> That being said, it's pretty clear to me we are in the last days of
> the disk drive.
Oh, puhleeze. Seagate, Hitachi, Fuji and WD aren't sitting around
Hi, in a stored procedure, if I do COPY table FROM 'mypath' DELIMITERS '\t'; it
works but if my path is a text variable, i.e.COPY table FROM mytextvar
DELIMITERS '\t'; it doesn't work, I get ERROR: syntax error at or near "$1"SQL
state: 42601Context: SQL statement in PL/PgSQL function "parseinse
thnx !
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Hello all,
We have recently upgrade our postgres server from 8.0 to 8.2. I am
experiencing some difficulties in SQL queries.
Let's say I have a table NUMBERS (number (integer)) which has values
1, 5 and 8.
SELECT number FROM numbers; will return 1, 5 and 8.
In PostgreSql 8.0, SELECT number FROM
I have a user application use log.
Under pg 7.x the system performed fine.
In 8.1, the insert statements seem to take a long time sometimes, upto
several seconds or more.
Here is the table:
CREATE TABLE user_app_use_log
(
user_id int4 NOT NULL,
access_stamp timestamptz NOT NULL DEFAULT now(
HI All,
I have a dataset (90GBs worth) that contains 3 columns of unix
timestamps in seconds. Ideally, I want to use copy to load the data into
the DB (the disk i/o of trying to process this volume of data vi perl
takes a couple of days). I was hoping to convert the unix timestamps
inside the D
Hi,
I'm working on an extraction tool that should unload timestamp-ranged
data from every table of a selected schema under postgresql database,
and store its result into a file so it can be loaded into any
database. I don't know what tables I'm going to dump, but I do know
what fields should be ta
I need a design tool to design my database.
Back in past I used DBDesigner (free) but it is best suited to MySQL
and i now use PostGreSQL and SQLite.
The tool should have most of the following:
Ability to create a rather complex and arbitrarily big scheme and
produce automatically the SQL code f
I did an analyize and tried to delete 50 rows from the 1.5M table and
it took over 120 seconds.
Deleting 100 rows took nearly twice as long.
An explain of the delete query showed that indexes were being used.
I found that changing the variable enable_seqscan = off and restarting
the database, del
pg doc recommends to use
pg_dumpall -c | gzip >backup.gz
In windows it writes whole file in uncompressed form to temporary file and
only then runs gzip.
This requires large amout of free disk space in temporary file space and
thus is not reasonable.
Which is reasonable way to backup cluster
[EMAIL PROTECTED] writes:
> In PostgreSql 8.0, SELECT number FROM numbers HAVING number = 5;
> returns 5
> But in 8.2, it gives me the following error: ERROR: column
> "numbers.number" must appear in the GROUP BY clause or be used in an
> aggregate function.
> In the documentation, it says that
Hi all,
Is it still possible to get PostgreSQL merchandise? A friend of mine is
looking for some, but I can't seem to find where its available.
I have a PostgreSQL mug I bought some years ago, which sparked this
search (I know, I need a life...) :-)
Ray.
---
hi,
Although I do a lot with postgres, I am quite new to functions. So
yesterday I was playing around with a very simple one. Of course I did
something very stupid and created an "infinite loop" in the end.
My problem is, that this crashed one of my postgres server, but the
other just through an
Clemens Schwaighofer <[EMAIL PROTECTED]> writes:
> both servers are debian packages 8.1.9-1, but on the one that crashed I
> increased the stack depth to 8192. Could it be that postgres ran out of
> memory and terminated itself?
Increasing the stack depth parameter to more than what the kernel rea
D. Dante Lorenso wrote:
I want to select several rows of data and have them returned in a
single record with the rows joined by a delimiter.
Review the User Comments at
"http://www.postgresql.org/docs/8.0/interactive/sql-createaggregate.html";
for some ideas.
---(e
am Tue, dem 03.07.2007, um 23:37:57 +0100 mailte Raymond O'Donnell folgendes:
> Hi all,
>
> Is it still possible to get PostgreSQL merchandise? A friend of mine is
> looking for some, but I can't seem to find where its available.
Can you or your fried visit the pgday.it at Prato, Italy?
http://
D. Dante Lorenso wrote:
I want to select several rows of data and have them returned in a single
record with the rows joined by a delimiter.
I have something like that here:
http://www.designmagick.com/article/38
--
Postgresql & php tutorials
http://www.designmagick.com/
---
1. What is the advantage of the tsearch2() trigger? Why can't I write my
own trigger which does approximately:
UPDATE manuscript set manuscript_vector =
setweight(to_tsvector(manuscript_genre), 'A') ||
setweight(to_tsvector(manuscript_title), 'B') ||
to_tsvector(manuscript_abstra
Berend Tober wrote:
D. Dante Lorenso wrote:
I want to select several rows of data and have them returned in a
single record with the rows joined by a delimiter.
Review the User Comments at
"http://www.postgresql.org/docs/8.0/interactive/sql-createaggregate.html";
for some ideas.
I found a b
56 matches
Mail list logo
