On Thu, May 03, 2007 at 10:58:38AM +1200, Brent Wood wrote:
As oid is unique across all tables (in fact all database objects), but
serial is unique within a table, there are odd cases like this where
using an oid in each table ensures an automatic unique key in the view.
So oids can be
Naz Gassiep wrote:
Hi,
I'm trying to do an update on a table that has a unique constraint
on the field, I need to update the table by setting field = field+1
however if this does not perform the updates on the table in a proper
order (from last to first) then the update will cause a
Hello all,
I'm trying to inherit a table from the public schema as a table with the
same name in a user-specific schema, but I can't get it to work.
Say I have:
CREATE TABLE test (test_id serial PRIMARY KEY, name text NOT NULL);
CREATE SCHEMA alban AUTHORIZATION alban;
ALTER USER alban SET
Dan Weber wrote:
I made an expression index specifically for that where clause:
CREATE INDEX special_testing_idx on my_table (((bool_1 or
int_1 = 0) AND (int_2 IS NULL) AND (int_3 IS
NULL) AND (protocol = 2)));
No, you haven't. What you've done here is create an index *for that
expression*.
Stephen Harris wrote:
On Wed, May 02, 2007 at 12:45:08PM -0700, Dann Corbit wrote:
Have you done a vacuum on the table recently?
We vacuum daily and cluster weekly after the nightly activities have been
performed.
IN list, then the IN list might benefit from a bit of analysis for
The IN
Alban Hertroys wrote:
Hello all,
I'm trying to inherit a table from the public schema as a table with the
same name in a user-specific schema, but I can't get it to work.
Say I have:
CREATE TABLE test (test_id serial PRIMARY KEY, name text NOT NULL);
CREATE SCHEMA alban AUTHORIZATION
Alban Hertroys wrote:
Naz Gassiep wrote:
Hi,
I'm trying to do an update on a table that has a unique constraint
on the field, I need to update the table by setting field = field+1
I think you're looking for deferrable constraints; see:
On May 2, 2007, at 23:36 , Naz Gassiep wrote:
I don't
know why you'd ever use your second option ever, as it virtually
guarantees problems at a random point in your DB's growth.
There may be cases where the values are not all positive so you can't
use the -1 * technique, but the offset
Islam Hegazy wrote:
Hi all
I have postgresql server installed on a windows machine and I want to
retrieve data using C functions. I followed the steps in the
documentation but it didn't work for windows. I created a .dll
projects for my functions but postgres.h calls .h files that I can't
Thanks to you and Tom. The partial index solution is working splendidly.
On 5/3/07, Richard Huxton [EMAIL PROTECTED] wrote:
Dan Weber wrote:
I made an expression index specifically for that where clause:
CREATE INDEX special_testing_idx on my_table (((bool_1 or
int_1 = 0) AND (int_2 IS
I need to use the value of an environment variable as part of an SQL
query within psql.
I can do the following withing psql:
\set local_site `echo $FXA_LOCAL_SITE
\echo local site = :local_site
The result is local_site = xxx which is correct.
What I really want to do is the following:
On Wed, May 02, 2007 at 05:59:49PM -0400, Tom Lane wrote:
Stephen Harris [EMAIL PROTECTED] writes:
select stuff from table where index_key in ( .
join(,,keys %hash) . ) AND non_index_row in ('xyz','abc','def')
In what, a seq scan?
Yeah, if the number of comparisons exceeds 156
update foo
set field = -1 * (field + 1);
update foo
set field = -1 * field
where field 0;
Yes, in fact I actually use option one already in the handling of sql
trees, so I'm annoyed with myself for not figuring that out. I don't
know why you'd ever use your second option ever, as
If you are updating a large portion of your tree, you will probably want to
throw in a vacuum in
between the two updates. This should reduce the bloat caused by dead tuples
in both your index
and table.
... but that will only work if you can commit the first set of changes
before you get
Paul Tilles [EMAIL PROTECTED] writes:
What I really want to do is the following:
\set local_site `echo $FXA_LOCAL_SITE
UPDATE table_name SET office_id = :local_site;
This results in the message
column xxx does not exist
Yes, because you have no quotes in the value of
Hi NG,
I want to write a stored procedure which creates a table in my
PostgreSQL database. The procedure has one input parameter: the table name.
Here is my first try, but that does not work:
--
Richard Huxton wrote:
Alban Hertroys wrote:
Naz Gassiep wrote:
Hi,
I'm trying to do an update on a table that has a unique constraint
on the field, I need to update the table by setting field = field+1
I think you're looking for deferrable constraints; see:
Tom,
Thank you. That works.
My psql does not allow me to leave off the trailing `.
It is my typing that is the problem.
Paul
Tom Lane wrote:
Paul Tilles [EMAIL PROTECTED] writes:
What I really want to do is the following:
\set local_site `echo $FXA_LOCAL_SITE
UPDATE
Hi,
Try EXECUTE
http://www.postgresql.org/docs/8.2/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
Best Regards
Hakan Kocaman
Software-Development
digame.de GmbH
Richard-Byrd-Str. 4-8
50829 Köln
Tel.: +49 (0) 221 59 68 88 31
Fax: +49 (0) 221 59 68 88 98
Email:
On Thursday 26. April 2007 20:12, Jon Sime wrote:
I run 8.2.x on a Gentoo/x86_64 development box (just did the upgrade
to 8.2.4 yesterday) using the postgresql-experimental overlay (via
layman) and have run into no problems. Everything has compiled,
installed/upgraded and been run with no
Andrei-
*If you're developing under nix* then I would use windows cygwin (bash
shell) e.g.
\cygwin\cygwin.bat
cd /
find . -name strings.h
you will see /usr/include
but this version of strings.h only includes string.h
to bring into environment make sure you include the /usr/include in .profile
Alban Hertroys wrote:
Richard Huxton wrote:
Alban Hertroys wrote:
Naz Gassiep wrote:
Hi,
I'm trying to do an update on a table that has a unique constraint
on the field, I need to update the table by setting field = field+1
I think you're looking for deferrable constraints;
Hi,
thanks for your answer, but I don't get the point. Perhaps you can give
me a small example how to get the EXECUTE into a stored procedure.
Regards
Hakan Kocaman schrieb:
Hi,
Try EXECUTE
I'm investigating the usage of a UUID primary key generator using
Hibernate and Postgres. The reason for using a UUID is that we will
have an application hosted at different sites in different
databases. We will need to aggregate the data back into a single
database from time to time and
Hi,
your example should look like this:
CREATE OR REPLACE FUNCTION create_geom_table(table_name text) RETURNS void AS
$BODY$
DECLARE
func_text text;
BEGIN
func_text:='DROP TABLE ' || table_name ||';
CREATE TABLE ' || table_name ||'
(
I have added the path to the include directory of postgresql but it hasn't
already some of the files that are in linux include path like strings.h.
It doesn't object about postgres.h which it can find now but it objects
about strings.h
I use MSVC6 to make my dll file.
Regards
Islam
-
Try creating a temporary table, populating with the list and joining
against it. That's probably your best bet for a long list of target
values.
Check :
forum_bench= CREATE TABLE test (value INTEGER NOT NULL);
CREATE TABLE
forum_bench= INSERT INTO test SELECT * FROM
Followup to my previous test, with an index this time
EXPLAIN ANALYZE SELECT * FROM test WHERE value IN ( 1000 integers )
Bitmap Heap Scan on test (cost=3519.09..7156.83 rows=1000 width=4)
(actual time=5.843..8.897 rows=999 loops=1)
Recheck Cond: (value = ANY
Islam Hegazy wrote:
I have added the path to the include directory of postgresql but it
hasn't already some of the files that are in linux include path like
strings.h. It doesn't object about postgres.h which it can find now
but it objects about strings.h
I use MSVC6 to make my dll file.
I don't recommend it. There are better ways to store UUIDs:
char(32)-- Easy to work with, fixed length, inefficient
varchar(32) -- 4 bytes larger due to variable size
bytea() -- 20 bytes, variable length
bit(128)-- 16 bytes, optimal
I don't like char() or varchar() because of
William Garrison wrote:
I don't recommend it. There are better ways to store UUIDs:
char(32)-- Easy to work with, fixed length, inefficient
varchar(32) -- 4 bytes larger due to variable size
bytea() -- 20 bytes, variable length
bit(128)-- 16 bytes, optimal
I don't like
On Thu, 2007-05-03 at 08:58 -0700, Matthew Hixson wrote:
I'm investigating the usage of a UUID primary key generator using
Hibernate and Postgres. The reason for using a UUID is that we will
have an application hosted at different sites in different
databases. We will need to aggregate
On 5/3/07, Matthew Hixson [EMAIL PROTECTED] wrote:
Is there a significant performance difference between using int
primary keys and string primary keys in Postgres?
PostgreSQL uses B-trees for its indexes, insertion time is logarithmic
regardless of the type of the key, but strings have a
As the title of this message suggests, I've got a couple of questions about
indexing that I'm not sure about. I've tried to take a look at the docs, but I
can't remember seeing anything on these; it's quite possible, I admit, that I'm
simply not remembering all of what I saw, but I would
On 5/3/07, Jeff Davis [EMAIL PROTECTED] wrote:
On Thu, 2007-05-03 at 08:58 -0700, Matthew Hixson wrote:
I'm investigating the usage of a UUID primary key generator using
Hibernate and Postgres. The reason for using a UUID is that we will
have an application hosted at different sites in
On Thu, May 03, 2007 at 01:42:44PM -0700, Andrew Edson wrote:
As the title of this message suggests, I've got a couple of questions about
indexing that I'm not sure about. I've tried to take a look at the docs, but
I can't remember seeing anything on these; it's quite possible, I admit, that
1. Does an indexed column on a table have to be a potential primary
key?
Nope, create as many index as you need/must/should.
I've been working with a couple of rather large tables where a common
select is on a foreign key called 'cntrct_id' (Varchar(9) in format).
However, the
On Thu, 2007-05-03 at 22:32 +0200, Alexander Staubo wrote:
On 5/3/07, Matthew Hixson [EMAIL PROTECTED] wrote:
Is there a significant performance difference between using int
primary keys and string primary keys in Postgres?
PostgreSQL uses B-trees for its indexes, insertion time is
Rich,
I would think that as an ecologist, you would have a better sense than most
here of the kinds of things I'd be doing. After all, I am a mathematical
ecologist by training and the majority of applications I have developed have
been either for agricultural consultants or
Thanks for the redirect... After profiling my client memory usage and using
the built-in cursor functionality I discovered that another part of my
program was causing the memory overflow and that the ResultSet iteration was
doing exactly what it should have all along.
On 4/21/07, Kris Jurka
Hi,
is there any way to get comment meta-information for columns in a
view? I mean, suppose I have several tables and some of their columns
have comments, then I define a view on these tables, and what I want
is to somehow retrieve comments (if any) for those columns which are
selected in a view.
Hello all.
It's clear from the documentation for the fsync configuration option that
turning it off may lead to unrecoverable data corruption. I'd like to
learn more about why this is possible and how likely it really is.
A quick look at xlog.h reveals that each record in the transaction
Hi!
I am a complete newbee to Postgres. Have installed Postgres on Windows 2003
server SP1 a week back. When I try to log-in to the server (by writting psql
mydb at command prompt in postgres/bin directory), I keep getting a message
psal: FATAL: password authentication failed for
Le lundi 30 avril 2007 à 20:13 +0400, Oleg Bartunov a écrit :
On Mon, 30 Apr 2007, philippe wrote:
Now if I do a query like this
select to_tsvector('default', '... something with more than 200
chars');
- result ok
it doesn't uses french snowball stemmer
Yes, it's just to show that
Hi all,
It's nice that privileges on views are separate from the privileges
on its underlying tables. For example, if view V queries tables A and
B, I only need to grant SELECT on the view to another user; tables A
and B can have that privilege revoked and the view works.
Are there plans
thankyou very much.
but the method, you said, is adding a alias name, so it can not work.
and as i need to add many functions likes this, so the best way is to
compile the whole postgresql. eventhough, i did, it didnot work, so i am
puzzled, can add a function directly in the source file, and
John D. Burger wrote:
There was a brief discussion of this just last week, with a few solutions
suggested:
http://archives.postgresql.org/pgsql-general/2007-04/msg01098.php
Rich Shepard wrote:
That thread asked how to find business days between any two specified
dates. I would like to
I get the following error in the postgres log. I know what the error
means and how to fix it, but I don't know how to determine which
statement is causing it:
2007-03-27 09:29:04 WARNING: nonstandard use of \\ in a string literal
at character 72
2007-03-27 09:29:04 HINT: Use the escape
hi,
i had made necessary change in the postgresql.conf for enabling WAL. How can
i know that WAL is working?
The configurations done in the postgresql.conf file is as below:
#---
# WRITE AHEAD LOG
My application receives the folllowing error sometimes.
Any idea how to fix ?
Andrus.
7/XX000:Error while executing the query;ERROR: tuple concurrently updated
CONTEXT: SQL statement DROP TABLE templsabiPL/pgSQL function drop_table
line 2 at execute statement
SELECT
Hello sir,
I have already done the backup with crontab for the interval
of 3 hours. But what i need is to backup the data even in the server crash
between these intervals. Since critical transactions during these intervals
may lost. So what should i do to make the backup
Connect to PostgreSql as Postgres user (default database user):
psql yourdb -U Postgres
then you will be asked for password selected during the installation.
Hope this will help.
JB
- Original Message -
From: Suresh Nimbalkar
To: pgsql-general@postgresql.org
Sent: Tuesday,
On Wed, 2 May 2007, Lew wrote:
The best solution I've encountered so far to this type of problem is to
have a table of days with columns like isWeekday, isHoliday, julianDay,
otherTidbit, ...
Then you select or join the days within the interval of interest and
factor out weekdays, or holidays,
William Garrison [EMAIL PROTECTED] writes:
Can I get postgres to log the actualy command or stored procthat caused
the problem?
Set log_min_error_statement = error.
regards, tom lane
---(end of broadcast)---
TIP 3: Have
Andrus [EMAIL PROTECTED] writes:
My application receives the folllowing error sometimes.
7/XX000:Error while executing the query;ERROR: tuple concurrently updated
CONTEXT: SQL statement DROP TABLE templsabiPL/pgSQL function drop_table
line 2 at execute statement
That's a bit interesting
Joel Dice [EMAIL PROTECTED] writes:
It's clear from the documentation for the fsync configuration option that
turning it off may lead to unrecoverable data corruption. I'd like to
learn more about why this is possible and how likely it really is.
As you note, WAL is not particularly
Barry Brown [EMAIL PROTECTED] writes:
It's nice that privileges on views are separate from the privileges
on its underlying tables. For example, if view V queries tables A and
B, I only need to grant SELECT on the view to another user; tables A
and B can have that privilege revoked and
On 4/20/07, Tom Lane [EMAIL PROTECTED] wrote:
Hmph. It should pretty much just work ... and there is *definitely* not
any update command visible in the source code.
i dug deeper (like i should have in the first place) and the UPDATEs
are ok ... they're inside of functions which get triggered
Jeff Davis [EMAIL PROTECTED] writes:
If you're using a non-C locale, it's slower than strcmp() too.
PostgreSQL has to do an extra memcpy() in order to use strcoll(),
because strings in postgresql aren't necessarily NULL-terminated and
there's no such thing as strncoll(), unfortunately (a
[EMAIL PROTECTED] writes:
is there any way to get comment meta-information for columns in a
view? I mean, suppose I have several tables and some of their columns
have comments, then I define a view on these tables, and what I want
is to somehow retrieve comments (if any) for those columns
Hi all,
Can anybody tell me how to enable autocomplete and history in psql.
Thanks in advance.
** DISCLAIMER **
Information contained and transmitted by this E-MAIL is proprietary to
Sify Limited and is intended for use only by the individual or entity to
which it is
Mike Frysinger [EMAIL PROTECTED] writes:
On 4/20/07, Tom Lane [EMAIL PROTECTED] wrote:
Hmph. It should pretty much just work ... and there is *definitely* not
any update command visible in the source code.
i dug deeper (like i should have in the first place) and the UPDATEs
are ok ...
Can anybody tell me how to enable autocomplete and history in psql.
Make sure your platform has the readline libraries installed.
Under an RPM based Linux, try
$ rpm -qa | grep readline
You can also under Linux try $ ldd psql
and see if it finds readline.so as one of its dependencies.
I
63 matches
Mail list logo
