Hi,
in my special case I'd like to check if a XML[] is empty, because postgres
doesn't implement any equality operators, this is not a 'simple' task.
My current solution is:
SELECT * FROM sometable WHERE XMLSERIALIZE(CONTENT ((XPATH('/a:bar,
somexmlcolumn, ARRAY[ARRAY['a',
Erik Jones wrote:
On Sep 10, 2008, at 5:57 AM, Tommy Gildseth wrote:
Tom Lane wrote:
Tommy Gildseth [EMAIL PROTECTED] writes:
Richard Huxton wrote:
For what it's worth, I've run into a situation similar to this with a
client a couple time in the last week or two (I can't say identical as
In 7.4, how do I declare that a column in a table is to be
automatically set to the value of some fixed expression whenever a row
is updated?
Eg column last_modified is always set to current_timestamp
Chris
--
Chris Velevitch
Manager - Adobe Platform Users Group, Sydney
m: 0415 469 095
On Thu, Sep 11, 2008 at 3:53 AM, Shane Ambler [EMAIL PROTECTED] wrote:
Tom Lane wrote:
Darren Weber [EMAIL PROTECTED] writes:
There is a postgres user account on my OSX system. I'm not clear
about how it was created. I've installed a binary version of 8.3 in
/Library/PostgreSQL/8.3/ and
Hmm. It's a feature, but maybe a dangerous one. The expression is
being treated as text(foo), which is intentional in order to allow
use of functions as if they were virtual columns. However, then it
decides that what you've got there is a cast request. There wasn't
any ability to cast
Hello,
2008/9/11 Chris Velevitch [EMAIL PROTECTED]:
In 7.4, how do I declare that a column in a table is to be
automatically set to the value of some fixed expression whenever a row
is updated?
Eg column last_modified is always set to current_timestamp
you should to use trigger
regards
On Wed, Sep 10, 2008 at 5:43 PM, Filip Rembiałkowski
[EMAIL PROTECTED] wrote:
nothing wrong here but this can also be rewritten to pure SQL function
(can be few percent faster and optimizable by planner)
CREATE OR REPLACE FUNCTION get_user_data( INT )
RETURNS SETOF RECORD AS $$
SELECT
On Wed, Sep 10, 2008 at 5:56 PM, Alvaro Herrera
[EMAIL PROTECTED] wrote:
Ah, right, you only forgot to mention that other 99% of the
requirements.
:)
What's wrong with your first example?
It works, but those type casts TRUE::BOOLEAN AS forwards and selects
with AS (email VARCHAR, backend
Mickey Shekdar wrote:
Hello,
We are switching our web booking engine vendor. The present vendor is
using Microsoft SQL and the new vendor uses PostgreSQL.
I have three questions and will appreciate your comments:
[1] Will PostgreSQL perform better on Linux or Windows OS
The performance
Tom Lane wrote:
Jef Peeraer [EMAIL PROTECTED] writes:
i am using the xml add-ons, but the date output format seems to be wrong :
I think the conversion to xml intentionally always uses ISO date format,
because that's required by some spec somewhere.
Yes, it follows XML Schema. Which is why
On Thu, 11 Sep 2008, Peter Eisentraut wrote:
Tom Lane wrote:
Jef Peeraer [EMAIL PROTECTED] writes:
i am using the xml add-ons, but the date output format seems to be wrong :
I think the conversion to xml intentionally always uses ISO date format,
because that's required by some
Hello
2008/9/11 Joao Ferreira gmail [EMAIL PROTECTED]:
Hello all,
I'm trying to optimize the execution of a query which deletes a big
amount of records based on time
I need to remove from 100.000 to 1.000.000 records from my table once a
day, and I'dd like to make that removal as fast as
2008/9/11 Jef Peeraer [EMAIL PROTECTED]:
On Thu, 11 Sep 2008, Peter Eisentraut wrote:
Tom Lane wrote:
Jef Peeraer [EMAIL PROTECTED] writes:
i am using the xml add-ons, but the date output format seems to be wrong
:
I think the conversion to xml intentionally always uses ISO date
Jef Peeraer wrote:
On Thu, 11 Sep 2008, Peter Eisentraut wrote:
Tom Lane wrote:
Jef Peeraer [EMAIL PROTECTED] writes:
i am using the xml add-ons, but the date output format seems to be wrong :
I think the conversion to xml intentionally always uses ISO date format,
because that's required
Joao Ferreira gmail wrote:
I need to remove from 100.000 to 1.000.000 records from my table once a
day, and I'dd like to make that removal as fast as possible. This is the
idea:
DELETE FROM tt WHERE time $1;
Would it be considerably faster if I declare that query inside a user
function,
In article [EMAIL PROTECTED],
Greg Smith [EMAIL PROTECTED] writes:
On Tue, 9 Sep 2008, Artacus wrote:
Can psql access environmental variables or command line params?
$ cat test.sql
select :TEST as input;
$ psql -v TEST=16 -f test.sql
input
---
16
(1 row)
Nice trick, but when I
Dean Rasheed [EMAIL PROTECTED] writes:
foo.char and foo.varchar have similarly unexpected behavior; I think
that's probably the end of it, though, since those are the only types
that CoerceViaIO will take as targets.
... and also any user defined domains based on those, which is
what I
Hello all,
my application is coming to a point on which 'partitioning' seems to be
the solution for many problems:
- query speed up
- data elimination speed up
I'dd like to get the feeling of it by talking to people who use
partitioning, in general..
- good, bad,
- hard to manage, easy to
questions [EMAIL PROTECTED] writes:
I am getting this error with initdb while creating shared segment -
memory segment exceeded available memory or swap space. To reduce the
request size (currently 1785856 bytes), reduce PostgreSQL's shared_buffers
parameter (currently 50)) and/or its
Artacus [EMAIL PROTECTED] writes:
If I want to pass in a text[] argument to a plpgsql function, at what
array size am I asking for problems? 100? 10,000? 100,000?
Yeah, like you I was pretty worried about how it would handle using
larger arrays. But I was surprised to find that it did a
On Thu, 11 Sep 2008, Peter Eisentraut wrote:
Jef Peeraer wrote:
On Thu, 11 Sep 2008, Peter Eisentraut wrote:
Tom Lane wrote:
Jef Peeraer [EMAIL PROTECTED] writes:
i am using the xml add-ons, but the date output format seems to be
wrong :
I think the conversion to
2008/9/11 Jef Peeraer [EMAIL PROTECTED]:
On Thu, 11 Sep 2008, Peter Eisentraut wrote:
Jef Peeraer wrote:
On Thu, 11 Sep 2008, Peter Eisentraut wrote:
Tom Lane wrote:
Jef Peeraer [EMAIL PROTECTED] writes:
i am using the xml add-ons, but the date output format seems to be
Jef Peeraer wrote:
it would be a flag to indicate no conversion from the datestyle settings
in the database...i think, from a users perspective, the table_to_xml is
completely useless, if you have to reformat everything afterwards
Just write a function that does your formatting
Hi:
I have a job that loads a large table, but then has to update about
half the records for various reasons. My perception of what happens on
update for a particular recors is...
- a new record will be inserted with the updated value(s).
- The old record is marked as being obselete.
- Not
I might be able to answer my own question...
vacuum FULL (analyze is optional)
Correct?
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Gauthier, Dave
Sent: Thursday, September 11, 2008 10:01 AM
To: pgsql-general@postgresql.org
Subject:
On Thu, 2008-09-11 at 07:01 -0700, Gauthier, Dave wrote:
Hi:
I have a job that loads a large table, but then has to “update” about
half the records for various reasons. My perception of what happens
on update for a particular recors is...
- a new record will be inserted with the
Harald Fuchs escribió:
Nice trick, but when I try the following variant:
psql -v TEST=16 -c 'select :TEST as input'
I get
ERROR: syntax error at or near :
LINE 1: select :TEST as input
This seems to be contrary to the psql manual page:
These assignments are done during a
Brad Nicholson wrote:
If you want to compact the the table, you either need to use CLUSTER or
VACUUM FULL + REINDEX.
Actually those are all pretty slow. If you can do a no-op ALTER TYPE
that rewrites the entire table, it is a lot faster. Something like
ALTER TABLE tab ALTER COLUMN col TYPE
In response to Gauthier, Dave [EMAIL PROTECTED]:
I might be able to answer my own question...
vacuum FULL (analyze is optional)
CLUSTER _may_ be a better choice, but carefully read the docs regarding
it's drawbacks first. You may want to do some benchmarks to see if it's
really needed
Bill Moran wrote:
In response to Gauthier, Dave [EMAIL PROTECTED]:
I might be able to answer my own question...
vacuum FULL (analyze is optional)
CLUSTER _may_ be a better choice, but carefully read the docs regarding
it's drawbacks first. You may want to do some benchmarks to see
In response to Alvaro Herrera [EMAIL PROTECTED]:
Bill Moran wrote:
In response to Gauthier, Dave [EMAIL PROTECTED]:
I might be able to answer my own question...
vacuum FULL (analyze is optional)
CLUSTER _may_ be a better choice, but carefully read the docs regarding
it's
On Thu, Sep 11, 2008 at 6:01 PM, Pavel Stehule [EMAIL PROTECTED] wrote:
you should to use trigger
I've never used trigger before, it looks messy and error prone having
to write functions.
How is it that you can declare the default value of a column on insert
but not on update?
Chris
--
Chris
The European PGDay 2008 is now open for registration. We are working on
finalizing the English schedule, but we already have an impressive list
of speakers available at http://www.pgday.org/en/speakers. The schedule
will have at least one English talk at all times, as well as a number of
Italian
On 2008-09-10 16:46, Jack Orenstein wrote:
Application requirement. We need to do something for each row retrieved from
BIG
and the something is expensive. We do the scan slowly (30 second sleep inside
the loop) to amortize the cost.
Then do the processing in separate transactions like
Tom Lane wrote:
The ideas I had involved not considering the cast interpretation when
the actual syntax is table.column and some-set-of-other-conditions.
While this is certainly possible to implement, any variant of it will
break the existing 100% equivalence of foo.bar and bar(foo); which
seems
Harald Fuchs wrote:
In article [EMAIL PROTECTED],
Greg Smith [EMAIL PROTECTED] writes:
On Tue, 9 Sep 2008, Artacus wrote:
Can psql access environmental variables or command line params?
$ cat test.sql
select :TEST as input;
$ psql -v TEST=16 -f test.sql
input
---
16
(1 row)
Nice
On Thu, Sep 11, 2008 at 1:35 AM, Chris Velevitch
[EMAIL PROTECTED] wrote:
In 7.4, how do I declare that a column in a table is to be
automatically set to the value of some fixed expression whenever a row
is updated?
Eg column last_modified is always set to current_timestamp
A trigger as
Peter Eisentraut escribió:
psql manual page:
-c command
--command command
Specifies that psql is to execute one command string, command,
and then exit. This is useful in shell scripts.
command must be either a command string that is completely
On Thu, Sep 11, 2008 at 1:35 AM, Chris Velevitch
[EMAIL PROTECTED] wrote:
In 7.4, how do I declare that a column in a table is to be
automatically set to the value of some fixed expression whenever a row
is updated?
Oh yeah, if you can upgrade from 7.4 that would be a good idea, it's
getting a
At 7:13am -0400 on Thu, 11 Sep 2008, Harald Fuchs wrote:
Nice trick, but when I try the following variant:
psql -v TEST=16 -c 'select :TEST as input'
I get [a syntax error]
This seems to be contrary to the psql manual page:
Nope. Take a look at the -c option. Specifically Thus you
On 2008-09-11 17:21, Jack Orenstein wrote:
Then do the processing in separate transactions like this (in pseudocode):
The id last_id trick doesn't work for me -- I don't have an index that
would
support it efficiently.
Turning on autocommit seems to work, I'm just not clear on the
Tomasz Ostrowski wrote:
On 2008-09-11 17:21, Jack Orenstein wrote:
Then do the processing in separate transactions like this (in pseudocode):
The id last_id trick doesn't work for me -- I don't have an index that would
support it efficiently.
Turning on autocommit seems to work, I'm just
Hi,
I have read several of the posting on the list and I'm guessing I have a
router issue because I get disconnected from the database after some idle
time. I'm connecting remotely to a postgres 8.3.1 on openSUSE 11. My
question is how can I determine what the real cause of dropping the
On Thu, Sep 11, 2008 at 10:07 AM, johnf [EMAIL PROTECTED] wrote:
Hi,
I have read several of the posting on the list and I'm guessing I have a
router issue because I get disconnected from the database after some idle
time. I'm connecting remotely to a postgres 8.3.1 on openSUSE 11. My
Hello,
I would like to connect to Postgres from Python running on a Windows box. I
need the ODBC driver to create a windows ODBC datasource. I've been looking
for two days and have found lots of dead links, but no drivers. Can someone
please help me locate a driver so I can connect?
Thanks,
Greg,
* Greg Lindstrom ([EMAIL PROTECTED]) wrote:
I would like to connect to Postgres from Python running on a Windows box. I
need the ODBC driver to create a windows ODBC datasource. I've been looking
for two days and have found lots of dead links, but no drivers. Can someone
please help
On Thu, Sep 11, 2008 at 5:13 PM, Greg Lindstrom [EMAIL PROTECTED] wrote:
Hello,
I would like to connect to Postgres from Python running on a Windows box. I
need the ODBC driver to create a windows ODBC datasource. I've been looking
for two days and have found lots of dead links, but no
On Thu, Sep 11, 2008 at 8:56 AM, Bill Moran
[EMAIL PROTECTED] wrote:
In response to Alvaro Herrera [EMAIL PROTECTED]:
Bill Moran wrote:
In response to Gauthier, Dave [EMAIL PROTECTED]:
I might be able to answer my own question...
vacuum FULL (analyze is optional)
CLUSTER _may_
On Thursday 11 September 2008 09:13:14 am Scott Marlowe wrote:
On Thu, Sep 11, 2008 at 10:07 AM, johnf [EMAIL PROTECTED] wrote:
Hi,
I have read several of the posting on the list and I'm guessing I have a
router issue because I get disconnected from the database after some idle
time. I'm
Hi,
If I have a table like...
CREATE TABLE apa
(
objectid SERIAL PRIMARY KEY NOT NULL
,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
,somedata text
)
WITHOUT OIDS;
...where if rowX har higher objectid than rowY, it is implied that
On Thu, Sep 11, 2008 at 10:24 AM, johnf [EMAIL PROTECTED] wrote:
On Thursday 11 September 2008 09:13:14 am Scott Marlowe wrote:
On Thu, Sep 11, 2008 at 10:07 AM, johnf [EMAIL PROTECTED] wrote:
Hi,
I have read several of the posting on the list and I'm guessing I have a
router issue because
I installed PostGres Adv Server locally from enterprisedb.com and had
everything needed to create an ODBC connection for our data modeling
tools...
On Thu, Sep 11, 2008 at 12:13 PM, Greg Lindstrom [EMAIL PROTECTED]wrote:
Hello,
I would like to connect to Postgres from Python running on a
Hi:
Here's the problem...
I have a read-only DB that gets reloaded from scratch every night.
This takes several hours and I don't want any late night users to have
to wait for this process to complete, so I have 2 DBs. The first DB is
the one the users access. Call it main_db. I load a
Kevin Hunter wrote:
At 7:13am -0400 on Thu, 11 Sep 2008, Harald Fuchs wrote:
Nice trick, but when I try the following variant:
psql -v TEST=16 -c 'select :TEST as input'
I get [a syntax error]
This seems to be contrary to the psql manual page:
Nope. Take a look at the -c option.
-- Original message --
From: Roderick A. Anderson [EMAIL PROTECTED]
Kevin Hunter wrote:
At 7:13am -0400 on Thu, 11 Sep 2008, Harald Fuchs wrote:
Nice trick, but when I try the following variant:
psql -v TEST=16 -c 'select :TEST as input'
I get [a
On Thursday 11 September 2008, Gauthier, Dave [EMAIL PROTECTED]
wrote:
I have a job that loads a large table, but then has to update about
half the records for various reasons. My perception of what happens on
update for a particular recors is...
- a new record will be inserted with the
On Thu, Sep 11, 2008 at 11:15 AM, Alan Hodgson [EMAIL PROTECTED] wrote:
On Thursday 11 September 2008, Gauthier, Dave [EMAIL PROTECTED]
wrote:
I have a job that loads a large table, but then has to update about
half the records for various reasons. My perception of what happens on
update for
On Thu, Sep 11, 2008 at 10:08 AM, Marcus Engene [EMAIL PROTECTED] wrote:
select
objectid
from
apa
where
created_at now() - interval '1 day'
order by
objectid desc;
In this select, it would have been nice to dodge the full table scan without
adding an index to created_at. Is
On Thu, Sep 11, 2008 at 10:42 AM, Gauthier, Dave
[EMAIL PROTECTED] wrote:
Hi:
Here's the problem...
I have a read-only DB that gets reloaded from scratch every night. This
takes several hours and I don't want any late night users to have to wait
for this process to complete, so I have
Hi,
Is there a psql or other command that I can use to list tables and their
rows? All I found is this:
http://archives.postgresql.org/pgsql-hackers/2004-09/msg00876.php
The describe (\d) command (or \td+ ) doesn't show them (I expected
tuples count to be there too) - I can only see name,
On Thu, Sep 11, 2008 at 06:08:15PM +0200, Marcus Engene wrote:
In this select, it would have been nice to dodge the full table scan
without adding an index to created_at. Is this possible somehow?
yes.
1. drop condition on created_at
2. run the query as cursor
3. fetch rows from cursor until
On Thu, Sep 11, 2008 at 3:19 PM, Markova, Nina [EMAIL PROTECTED] wrote:
Is there a psql or other command that I can use to list tables and their
rows? All I found is this:
http://archives.postgresql.org/pgsql-hackers/2004-09/msg00876.php
select tablename,reltuples from pg_class inner join
Yes, issue a kill on the pid from the command line as either postgres
or root. note I didn't say kill -9 there.
How do I get the pids? Is there something specific I should look for in
the executable name I can see in ps?
Will I break any remote server processes which are handeling remote
Commercial Windows Unix based PostgreSQL ODBC drivers:
http://www.connx.com
http://www.openlinksw.com
For the Mac:
http://www.actualtechnologies.com/product_opensourcedatabases.php
Free:
http://pgfoundry.org/softwaremap/trove_list.php?form_cat=310
On Thu, Sep 11, 2008 at 2:27 PM, Gauthier, Dave [EMAIL PROTECTED] wrote:
How do I get the pids? Is there something specific I should look for in the
executable name I can see in ps?
You can either use a combination of ps and grep:
ps ax|grep postgres|grep dbname
or use the pg_stat_activity
David Wilson wrote:
On Thu, Sep 11, 2008 at 3:19 PM, Markova, Nina [EMAIL PROTECTED] wrote:
Is there a psql or other command that I can use to list tables and their
rows? All I found is this:
http://archives.postgresql.org/pgsql-hackers/2004-09/msg00876.php
select tablename,reltuples
Stephen Frost wrote:
Greg,
* Greg Lindstrom ([EMAIL PROTECTED]) wrote:
I would like to connect to Postgres from Python running on a Windows box. I
need the ODBC driver to create a windows ODBC datasource. I've been looking
for two days and have found lots of dead links, but no drivers.
Tom Lane wrote:
interval '1' year.
...is SQL spec syntax, but it's not fully implemented in Postgres...
Or someone could try to make it work, but given that no one has taken
the slightest interest since Tom Lockhart left the project, I wouldn't
hold my breath waiting for that.
I have
I have few other applications running on my machine but they all don't use
more than 60MB. I checked and SHMALL is in pages. I also reduced
shared_buffers and max_connections but that didn't help. Also I did ipcs
after I got the error and following is what I got -
T ID KEY
Ron Mayer wrote:
Tom Lane wrote:
Or someone could try to make it work, but given that no one has taken
the slightest interest since Tom Lockhart left the project, I wouldn't
hold my breath waiting for that.
I have interest. For 5 years I've been maintaining a patch for a client
Doh. Now
* Bill Todd ([EMAIL PROTECTED]) wrote:
FWIW I cannot get the ODBC driver to work correctly with ADO and the OLE
DB provider for ODBC. It sees TEXT fields as VARCHAR instead of
LONGVARCHAR. I do not know if the problem is at the ODBC level or the
ADO level but test carefully if you are
questions [EMAIL PROTECTED] writes:
I have few other applications running on my machine but they all don't use
more than 60MB. I checked and SHMALL is in pages. I also reduced
shared_buffers and max_connections but that didn't help. Also I did ipcs
after I got the error and following is what I
On Thu, Sep 11, 2008 at 5:38 AM, Joao Ferreira gmail
[EMAIL PROTECTED] wrote:
Hello all,
I'm trying to optimize the execution of a query which deletes a big
amount of records based on time
I need to remove from 100.000 to 1.000.000 records from my table once a
day, and I'dd like to make
OK, killing the remote users is fine. Just want ot make sure I'm not
killing some sort of shared remote server process(es) that would prevent
future remotes to connect.
-Original Message-
From: Scott Marlowe [mailto:[EMAIL PROTECTED]
Sent: Thursday, September 11, 2008 5:35 PM
To:
How is it that you can declare the default value of a column on insert
but not on update?
You can do this without triggers (at least on 8.3).
UPDATE foo
SET field1 = 2,
field2 = default
WHERE field3 = 22
I just tested it and it will set the value back to the default. The
caveat here is
On Fri, Sep 12, 2008 at 12:50 PM, Artacus [EMAIL PROTECTED] wrote:
You can do this without triggers (at least on 8.3).
UPDATE foo
SET field1 = 2,
field2 = default
WHERE field3 = 22
That means I have to then go through all my code and make sure I set
the fields value. If I forget to
On Fri, Sep 12, 2008 at 1:53 AM, Scott Marlowe [EMAIL PROTECTED] wrote:
Here's a simple example of last modified trigger using plpgsql from way back:
-- FUNCTION --
CREATE FUNCTION modtime () RETURNS opaque AS '
BEGIN
new.lm :=''now'';
RETURN new;
END;
' LANGUAGE
Moving this thread to Performance alias as it might make more sense for
folks searching on this topic:
Greg Smith wrote:
On Tue, 9 Sep 2008, Amber wrote:
I read something from
http://monetdb.cwi.nl/projects/monetdb/SQL/Benchmark/TPCH/index.html
saying that PostgreSQL can't give the
On Thu, Sep 11, 2008 at 9:10 PM, Chris Velevitch
[EMAIL PROTECTED] wrote:
On Fri, Sep 12, 2008 at 1:53 AM, Scott Marlowe [EMAIL PROTECTED] wrote:
Here's a simple example of last modified trigger using plpgsql from way back:
-- FUNCTION --
CREATE FUNCTION modtime () RETURNS opaque AS '
On Thu, Sep 11, 2008 at 8:50 PM, Artacus [EMAIL PROTECTED] wrote:
How is it that you can declare the default value of a column on insert
but not on update?
You can do this without triggers (at least on 8.3).
UPDATE foo
SET field1 = 2,
field2 = default
WHERE field3 = 22
I just tested
That means I have to then go through all my code and make sure I set
the fields value. If I forget to modify one statement, things will
break.
Right, that's why the right answer for what you want to do is to have a
trigger. I was just giving you an alternative since you didn't like the
$ TEST=16; psql -c select $TEST as \input1\;
$ TEST=16; echo select $TEST as \input1\; | psql
Yep that works. My coworker also suggested using EOF to simulate a
psql script.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
82 matches
Mail list logo
