On Thu, Nov 10, 2011 at 1:24 PM, daflmx daf...@qq.com wrote:
Hello,all.
I have installed the postgresql .
$/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data
LOG:database system was shut down at 2011-11-10 15:36:14 CST
LOG:database system is ready to accept connections
LOG:autovacuum
Tarlika Elisabeth Schmitz, 10.11.2011 00:52:
I would like to implement the equivalent of count (DISTINCT field) OVER ():
SELECT
id, name, similarity(name, 'Tooneyvara') as delta,
count (id) OVER() AS cnt
FROM vtown
WHERE
similarity(name, 'Tooneyvara') 0.1
ORDER BY delta
Hello,all.
I have installed the postgresql .
$/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data start
LOG:database system was shut down at 2011-11-10 15:36:14 CST
LOG:database system is ready to accept connections
LOG:autovacuum launcher started
when I check the status of cluster
On Thu, 10 Nov 2011 10:02:36 +0100
Thomas Kellerer spam_ea...@gmx.net wrote:
Tarlika Elisabeth Schmitz, 10.11.2011 00:52:
I would like to implement the equivalent of count (DISTINCT id)
OVER ():
[...]
produces result:
id, name, delta, cnt
1787 Toomyvara 0.5 4
1787 Toomevara
Tarlika Elisabeth Schmitz, 10.11.2011 11:24:
SELECT id,
name,
delta,
sum(case when rn = 1 then rn else null end) over() as
distinct_id_count
FROM (
SELECT
id, name, similarity(name, 'Tooneyvara') as delta,
row_number() OVER(partition by id) AS rn
Hi,
In one db that I have, there are a few columns that are blob datatype.
This db has around 200MB of data today and as it a development db yet,
so I am replicating data to another db for testing purposes using
pg_dump and pg_restore.
To export the data it is pretty fast, about 3~4 minutes,
Hi David,
On Wed, 2011-11-09 at 09:52 -0800, David Kerr wrote:
So, aside from removing the PKs do i have any other options?
Sure you have: order the inserts by primary key inside each transaction.
Then you will not get deadlocks, but inserting the same key again will
fail of course (but that's
Is there a way to list the installed pl languages for a database and/or
server cluster?
thanks
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
I'm desperately trying to get a hold of the latest RPM's for PostgreSQL 9.0..5
for SLES 11 SP1 x86_64 ... I simply can not find these anywhere !!
It seems that the good folk over at software.opensuse.org are only compiling
9.1.x now. Rather annoying to say the least for those of us who don't
Sometimes the planner can't find the most efficient way to execute your
query. Thanks to relational algebra, there may be other, logically
equivalent queries that it DOES know how to optimize.
But I don't know relational algebra. yet. (Date/Codd is a sleeping pill.)
I need more experience
On Wed, Nov 09, 2011 at 10:58:01PM -0600, Dennis Ryan wrote:
Is there a way to list the installed pl languages for a database
and/or server cluster?
\dL in psql
or
select * from pg_language;
Best regards,
depesz
--
The best thing about modern society is how easy it is to avoid contact with
I am thinking there is a better/simpler way, though this is what I have
working:
(postgres 9.1)
I would like to have the list of colors for each type of clothing to be
comma seperated in the end result.
like this:
typeorganized_by_type
pants red, blue, orange
shirt black,
On Wednesday, November 09, 2011 8:58:01 pm Dennis Ryan wrote:
Is there a way to list the installed pl languages for a database and/or
server cluster?
aklaver@tucker:~$ createlang -l -U postgres test
Procedural Languages
Name| Trusted?
---+--
plpgsql | yes
plpythonu
On 10 November 2011 02:54, Wes Cravens wcrav...@cortex-it.com wrote:
On 11/9/2011 7:34 PM, David Johnston wrote:
Use WITH RECURSIVE instead of a function.
I apologize but I don't know how that would work. An example would help.
There are fine examples in the documentation for the SELECT
On Thursday 10 November 2011 05:58:01 Dennis Ryan wrote:
Is there a way to list the installed pl languages for a database and/or
server cluster?
they are registered in the pg_language system catalog.
Rgds, Jens
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make
On 10 November 2011 08:56, Kalai R softlinne...@gmail.com wrote:
please suggest, what are the configurations should I do in postgres to avoid
these problem.
Thank You
None, it's not a Postgres problem. Most likely it is a problem with
your Windows installation.
You have files disappearing,
Henry Drexler, 10.11.2011 14:22:
I am thinking there is a better/simpler way, though this is what I have working:
(postgres 9.1)
I would like to have the list of colors for each type of clothing to be comma
seperated in the end result.
like this:
typeorganized_by_type
pants
On Thu, Nov 10, 2011 at 8:34 AM, Thomas Kellerer spam_ea...@gmx.net wrote:
SELECT type,
string_agg(color, ',') as organized_by_type
FROM clothes
GROUP BY type;
wow, yes that is cleaner.
Thank you for taking the time - obviously I need to read through the string
functions again.
On Nov 10, 2011, at 8:22, Henry Drexler alonup...@gmail.com wrote:
I am thinking there is a better/simpler way, though this is what I have
working:
(postgres 9.1)
I would like to have the list of colors for each type of clothing to be comma
seperated in the end result.
like this:
=?ISO-8859-1?B?ZGFmbG14?= daf...@qq.com writes:
[ server is running but ]
$/usr/local/pgsql/bin/createdb mydb
createdb:could nto connect to database postgres:could not connect to
server:No such file or directory
Is the server running locally and accepting connections on Unix domain
I upgraded to Fedora 16 yesterday…
I thought I might have lost my 12 year old db when the system came up
and I noticed the 9.1 had overwrote the old binaries.
Then I read about pg_upgrade stuff and it worked!
I found that postgresql would not start at boot time until
I did:
systemctl enable
--*Test SQL*
CREATE TABLE tb(id integer primary key,
name varchar(32),
parent integer);
INSERT INTO tb VALUES(1, 'iPhone',NULL);
INSERT INTO tb VALUES(2, 'HTC', NULL);
INSERT INTO tb VALUES(3, 'Motorola', NULL);
INSERT INTO tb VALUES(4, 'iPhone3GS',
Thanks the select * from pg_language works and is exactly what I was looking
for. \dL in psql does not in my version, 9.0.4.14, however \dL did work on
another install of version 9.1.1.
Thanks for the help.
-Original Message-
From: dep...@depesz.com [mailto:dep...@depesz.com]
Sent:
Hello,
I am new to postgres, but need to resolve this error:
PGError: ERROR: current transaction is aborted, commands ignored
until end of transaction block
: SELECT 1 FROM trades WHERE (trades.uuid =
'bc8d86e6-0a8d-11e1-a345-001d09203579' AND trades.id != 25) LIMIT
1
Don't know what is the
Richard,
I manage to find one comment about an implicit rollback in a section of
the developer's guide when porting from Oracle-to-Postgres: when an
exception is caught by an EXECPTION clause, all database changes since
the block's BEGIN are automatically rolled back
Do you know of any
I have 16 tables which created according to generated MD5 hash.
At the time of execution i tried to retrieve data from a table where
table name id dynamic.
table name can be expertdb.rate_AVAIL_[0 to F]
I tried to get this from ;
SELECT * FROM expertdb.rate_AVAIL_ ||
On 10 November 2011 15:43, shuaixf shua...@gmail.com wrote:
--*Test SQL*
CREATE TABLE tb(id integer primary key,
name varchar(32),
parent integer);
INSERT INTO tb VALUES(1, 'iPhone', NULL);
INSERT INTO tb VALUES(2, 'HTC', NULL);
INSERT INTO tb
On 9 November 2011 06:02, slavix mikerin.sl...@gmail.com wrote:
Hello,
I am new to postgres, but need to resolve this error:
PGError: ERROR: current transaction is aborted, commands ignored
until end of transaction block
: SELECT 1 FROM trades WHERE (trades.uuid =
On Tuesday, November 08, 2011 9:02:40 pm slavix wrote:
Hello,
I am new to postgres, but need to resolve this error:
PGError: ERROR: current transaction is aborted, commands ignored
until end of transaction block
: SELECT 1 FROM trades WHERE (trades.uuid =
your transaction had an error, and any query after the first one that
has failed will be ignored.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Hello,
I am having trouble when attempting to update an existing geometry on a spatial
table. The table stores 2D Point geometry, with the SRID 101. The update
statement i am trying to use to update the geometry with the id 110 is as
follows:
UPDATE Table Name SET Geometry Column Name =
Hello
2011/11/10 shuaixf shua...@gmail.com:
--*Test SQL*
CREATE TABLE tb(id integer primary key,
name varchar(32),
parent integer);
INSERT INTO tb VALUES(1, 'iPhone', NULL);
INSERT INTO tb VALUES(2, 'HTC', NULL);
INSERT INTO tb VALUES(3, 'Motorola',
I'm testing out various pg_dump scenarios using the -n switch and I have
a few questions:
- When using the -n switch, is the whole schema locked from all non-read
DML/DDL operations?
- If the whole schema is locked, once each table is dumped, is it then
released for non-read DML/DDL operations?
On Thursday, November 10, 2011 8:47:39 am you wrote:
not sure about version.. (latest i presume because installed recently)
same problem on my local machine running Ubuntu and on Heroku server
what command to get version?
select version();
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent
On 11/09/11 11:54 PM, daflmx wrote:
I have installed the postgresql .
$/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data
.
What version of postgres?
What installation method? (compile from source? install prebuilt binary
from where?)
What operating system version and distribution?
On Thursday, November 10, 2011 8:56:56 am you wrote:
PostgreSQL 8.4.9 on i686-pc-linux-gnu, compiled by GCC gcc-4.4.real
(Ubuntu/Linaro 4.4.4-14ubuntu5) 4.4.5, 32-bit
Please reply to the list also. That gets your issue in front of more eyes:)
The above answers my first question.
The second
Hello
2011/11/8 Lori Corbani l...@informatics.jax.org:
Richard,
I manage to find one comment about an implicit rollback in a section of the
developer's guide when porting from Oracle-to-Postgres: when an exception
is caught by an EXECPTION clause, all database changes since the block's
On 11/9/2011 7:19 PM, Wes Cravens wrote:
I have an adjacency list kind of table
CREATE TABLE thingy (
id int,
parent int
);
I'd like to be able to write a procedural function that returns a row or
rows from this table with an appended field that represents the children.
On Thursday, November 10, 2011 9:10:10 am Slava Mikerin wrote:
I am running Rails 3.1 that uses a db. I used phpmyadmin for
development and deployed to Heroku which uses postgres. When I got
this error on the Heroku server I switched to postgres locally to
investigate and got same error.
So
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Henry Drexler
Sent: Thursday, November 10, 2011 8:42 AM
To: Thomas Kellerer
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Re: plpgsql, I have a solution, want to see if there
is a
Hello
2011/11/8 Dinesh Kumara din...@rezgateway.com
**
I have 16 tables which created according to generated MD5 hash.
At the time of execution i tried to retrieve data from a table where table
name id dynamic.
table name can be expertdb.rate_AVAIL_[0 to F]
I tried to get this from ;
-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Wes Cravens
Sent: Thursday, November 10, 2011 11:54 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Returning a row from a function with an appended
array field
On 11/10/2011 12:05 PM, David Johnston wrote:
-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Wes Cravens
Sent: Thursday, November 10, 2011 11:54 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Returning
On Thu, Nov 10, 2011 at 09:09:06AM +0100, Csaba Nagy wrote:
- Hi David,
-
- On Wed, 2011-11-09 at 09:52 -0800, David Kerr wrote:
- So, aside from removing the PKs do i have any other options?
-
- Sure you have: order the inserts by primary key inside each transaction.
- Then you will not get
On 10 Nov 2011, at 17:42, Slava Mikerin wrote:
Thank you for reply Alban, I am new to postgres and don't know the
right commands to use to troubleshoot this issue.. The error message
is unclear and I don't know what caused the problem. can you tell me
what exact command to use? I don't need
On 10 Nov 2011, at 19:51, Wes Cravens wrote:
On 11/10/2011 12:05 PM, David Johnston wrote:
On 11/9/2011 7:19 PM, Wes Cravens wrote:
I have an adjacency list kind of table
CREATE TABLE thingy (
id int,
parent int
);
I'd like to be able to write a procedural function that returns
-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of slavix
Sent: Wednesday, November 09, 2011 12:03 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] troubleshooting PGError
Hello,
I am new to postgres, but need to
I have to do a lot of testing which involves uninstalling PostgreSQL
completely sometimes, but there is a lot of crud still left around after
an uninstall.
1) the installation directory with PostgreSQL droppings - I have to go
manually remove it. I do not know why the uninstaller does not
On 11/10/11 1:30 PM, J.V. wrote:
Can anyone get me started on where to checkout the postgreSQL
installer code and fix it so that it is no longer broken? I may need
to ask a few questions so if I can be put in touch with the
uninstaller team, would really like to get this fixed and go back and
Asli Akarsakarya aslia...@yahoo.com writes:
I am having trouble when attempting to update an existing geometry on a
spatial table. The table stores 2D Point geometry, with the SRID 101. The
update statement i am trying to use to update the geometry with the id 110 is
as follows:
UPDATE
Hi,
On 11 November 2011 00:04, Jay Levitt jay.lev...@gmail.com wrote:
Sometimes the planner can't find the most efficient way to execute your
query. Thanks to relational algebra, there may be other, logically
equivalent queries that it DOES know how to optimize.
But I don't know relational
yes, this is on windows.
Currently removing the data directory and the postgresql user is not
optional. It fails and does not remove those two items.
There must be 30+ registry keys still there as well.
What I am looking to do is to delete any registry entry:
1. that has a data value
I go through a series of SELECT INTO in a trigger function.
SELECT INTO country_id id
FROM vcountry WHERE [...];
I might or o might not find a result.
Next, I try to find a region within this country, if found previously,
and if not anywhere.
SELECT INTO
country_id, region_id
On 11/10/11 2:18 PM, J.V. wrote:
What I am looking to do is to delete any registry entry:
1. that has a data value matching the pattern '*postgres*'
2. that has a directory value matching the pattern '*postgres*'
3. that has a key name matching the pattern '*postgres*'
these
Respected,
All the time we see 'idle in transaction' in pg_stat_activity and dig
details with process pid from pg_logs for the query,query execution time
etc..
Instead of searching with process pid, am trying to pull the information
with shell scripting for lines prefixed with 'idle in
Thank you. I found the problem. It was with the previous query, not
the one that was showing up in the prompt.
I found the actual query in one of the log files.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
Hello, I have problem with installation, I cannot install, cause there is a password, and I dont now what it is about, what kind a password, give me the answer please
I am running Rails 3.1 that uses a db. I used phpmyadmin for
development and deployed to Heroku which uses postgres. When I got
this error on the Heroku server I switched to postgres locally to
investigate and got same error.
ruby-1.9.2-p290 and pg-0.11.0 gem used
On Thu, Nov 10, 2011 at 9:04
On Fri, Nov 11, 2011 at 4:18 AM, Raghavendra
raghavendra@enterprisedb.com wrote:
Respected,
All the time we see 'idle in transaction' in pg_stat_activity and dig
details with process pid from pg_logs for the query,query execution time
etc..
Instead of searching with process pid, am
On 14 October 2011 12:12, Thom Brown t...@linux.com wrote:
Hi,
I just noticed that the VACUUM process touches a lot of relations
(affects mtime) but for one file I looked at, it didn't change. This
doesn't always happen, and many relations aren't touched at all.
I had the following
Hey all,
I'm trying to make use of OidFunctionCall3 and am wondering how to
resolve an issue. I need to be able to pass to the function called with
OidFunctionCall3 a NULL and am having difficulty figuring out how.
{{{
/* build fcnarg */
for (i = 0; i set_count; i++) {
if
On Thursday, November 10, 2011 12:09:26 pm Kaspars Zelgis wrote:
Hello, I have problem with installation, I cannot install, cause there is a
password, and I dont now what it is about, what kind a password, give me
the answer please
Install from what to what?
Examples:
One Click installer on
On 11/10/2011 05:04 PM, daflmx wrote:
Is the server running locally and accepting connections on Unix domain
socket/var/run/postgresql/.s.PGSQL.5432?
Well?
Does the socket file exist?
Is PostgreSQL configured to use a different socket - perhaps in /tmp -
in postgresql.conf?
I'm guessing
On Thu, 10 Nov 2011 17:46:47 -0500
David Johnston pol...@yahoo.com wrote:
-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Tarlika
Elisabeth Schmitz
Sent: Thursday, November 10, 2011 5:18 PM
To:
Bborie Park bkp...@ucdavis.edu writes:
I'm trying to make use of OidFunctionCall3 and am wondering how to
resolve an issue. I need to be able to pass to the function called with
OidFunctionCall3 a NULL and am having difficulty figuring out how.
You can't. Those convenience functions are
On 11/10/2011 04:43 PM, Tom Lane wrote:
Bborie Parkbkp...@ucdavis.edu writes:
I'm trying to make use of OidFunctionCall3 and am wondering how to
resolve an issue. I need to be able to pass to the function called with
OidFunctionCall3 a NULL and am having difficulty figuring out how.
You
Thom Brown t...@linux.com writes:
On 14 October 2011 12:12, Thom Brown t...@linux.com wrote:
I just noticed that the VACUUM process touches a lot of relations
(affects mtime) but for one file I looked at, it didn't change. This
doesn't always happen, and many relations aren't touched at all.
On 11 November 2011 00:55, Tom Lane t...@sss.pgh.pa.us wrote:
Thom Brown t...@linux.com writes:
On 14 October 2011 12:12, Thom Brown t...@linux.com wrote:
I just noticed that the VACUUM process touches a lot of relations
(affects mtime) but for one file I looked at, it didn't change. This
Hi,
Considering query for binary data stored directly in tables
using libpq API, I'm trying to understand what is the difference
between specifying binary format in functions like
PQexecParams and use of BINARY CURSOR.
For example, with query like this:
SELECT large_image FROM tbl;
where
On 11/10/2011 11:10 PM, Jerry Levan wrote:
I upgraded to Fedora 16 yesterday…
I thought I might have lost my 12 year old db when the system came up
and I noticed the 9.1 had overwrote the old binaries.
... of course, you keep regular backups so you weren't too worried
anyway right?
On 11/11/2011 06:18 AM, J.V. wrote:
yes, this is on windows.
Currently removing the data directory and the postgresql user is not
optional. It fails and does not remove those two items.
There must be 30+ registry keys still there as well.
What I am looking to do is to delete any registry
On 11/11/2011 04:09 AM, Kaspars Zelgis wrote:
Hello, I have problem with installation, I cannot install, cause there
is a password, and I dont now what it is about, what kind a password,
give me the answer please
You have to make the password up. It is the password you will use to log
in to
Hello,all.
I have installed the postgresql .
$/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data start
LOG:database system was shut down at 2011-11-10 15:36:14 CST
LOG:database system is ready to accept connections
LOG:autovacuum launcher started
when I check the status of cluster
On Nov 10, 2011, at 9:56 PM, Craig Ringer wrote:
On 11/10/2011 11:10 PM, Jerry Levan wrote:
I upgraded to Fedora 16 yesterday…
I thought I might have lost my 12 year old db when the system came up
and I noticed the 9.1 had overwrote the old binaries.
... of course, you keep regular
You have sent this message at least once before.
Pleas go back and read the replies to the first one, don't just re-send
your message.
On 11/11/2011 11:36 AM, daflmx wrote:
Hello,all.
I have installed the postgresql .
$/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data start
LOG:database
Jerry Levan jerry.le...@gmail.com writes:
On Nov 10, 2011, at 9:56 PM, Craig Ringer wrote:
On 11/10/2011 11:10 PM, Jerry Levan wrote:
I found that postgresql would not start at boot time until
I did:
systemctl enable postgresql.service
That's Fedora policy: don't start a service unless the
Hi;
I have found recently that tables in certain contexts seem to have a
name pseudocolumn. I was wondering if there is any documentation as
to what this is and what it signifies.
postgres=# CREATE table TEST2 (a text, b text);
CREATE TABLE
postgres=# INSERT INTO test2 values ('', '');
Chris Travers chris.trav...@gmail.com writes:
I have found recently that tables in certain contexts seem to have a
name pseudocolumn. I was wondering if there is any documentation as
to what this is and what it signifies.
I/O conversion cast from composite type to string. You might find
this
See documentation, chapter Viii.E.2.2.2
2011/11/11, Chris Travers chris.trav...@gmail.com:
Hi;
I have found recently that tables in certain contexts seem to have a
name pseudocolumn. I was wondering if there is any documentation as
to what this is and what it signifies.
postgres=# CREATE
79 matches
Mail list logo