On Thu, 2007-09-06 at 14:13 +0800, Ow Mun Heng wrote:
> I have a table in PG, Pulled from SQL Server using Perl DBI (w/o using
> chopblanks) and have ended up with a column where the "space" is being
> interpreted as a value.
>
> eg:
>
> "ABC " when it should be "ABC"
>
> this is being defined
I have a table in PG, Pulled from SQL Server using Perl DBI (w/o using
chopblanks) and have ended up with a column where the "space" is being
interpreted as a value.
eg:
"ABC " when it should be "ABC"
this is being defined as varchar(4)
I've already pull the relevent columns with
create foo
Hi,
I've read several solutions to get the sequence name for a field defined as
serial by create/alter table, but when I
define a default expression (e.g. like next_val('foo_seq') ) for an integer
column pg_depend will not contain
information to describe this relation.
Is there a backward compa
Table is like
create table foo (
number int,
subset int,
value int
)
select * from foo;
number | subset | value
111
122
1310
143
current query is like
select number,
avg(case when subset = 1 then value else null end) as v1,
avg(ca
On Tue, 2007-09-04 at 00:51 -0400, Tom Lane wrote:
> Ow Mun Heng <[EMAIL PROTECTED]> writes:
> > I just browsed to my $PGDATA location and noticed that there are some
> > tables which has ending of .1
>
> TFM has some useful background knowledge for that sort of thing:
> http://www.postgresql.org/
Josh Trutwin wrote:
> > SELECT nspname as schema, c2.oid as tableoid, c2.relname as
> > table, substring(pg_catalog.pg_get_indexdef(i.indexrelid, 0, true)
> > from E'\\((.*)\\)')
> 2.) Can you explain that substring line? What in the world is "from
> E'\\((.*)\\)')" doing? Somehow it gets t
I don't know how I could have missed that - that sure enough that did the
trick! Thanks to all that answered! :)
liam
On 9/5/07 4:34 PM, "Chris" <[EMAIL PROTECTED]> wrote:
You need to analyze, not vacuum full. pg_dump doesn't include any
analyze statements, you need to do that manually.
--
On Tue, 2007-09-04 at 07:49 +0300, Mikko Partio wrote:
>
> No (changing tablespaces does not change your logical schema).
I just tested this "feature" with a temp table and it works as
advertised. (In progress of moving a table there now actually)
2nd question.. reading the docs, it says that
On Wed, 5 Sep 2007 19:08:33 -0400
"Merlin Moncure" <[EMAIL PROTECTED]> wrote:
> On 9/5/07, Josh Trutwin <[EMAIL PROTECTED]> wrote:
> > I have a php application that needs to query the PK of a table -
> > I'm currently using this from the information_schema views:
>
> try this:
> CREATE OR REPLACE
2007/9/5, Martin Langhoff <[EMAIL PROTECTED]>:
> Hi!
>
> I am having a bit of trouble with indexes, locales and LIKE queries.
>
> Background
> --
>
> Using Pg8.1/8.2 on a utf-8 database, my left-anchored LIKE clauses were
> forcing a full table scan instead of using the index. After a bit o
On 9/5/07, Martijn van Oosterhout <[EMAIL PROTECTED]> wrote:
> On Wed, Sep 05, 2007 at 10:44:20AM -0700, Trevor Talbot wrote:
> > Unless psql is turning on keepalive or similar, or the OS is forcing
> > it on by default, there are no timeouts for idle TCP connections. If
> > the command was transp
On 9/5/07, Håkan Jacobsson <[EMAIL PROTECTED]> wrote:
> Hi,
>
> I want to create a DELETE statement which deletes duplicates
> in a table.
>
> That is, I want to remove all rows - but one - having three
> columns with the same data (more columns exist and there the
> data varies).
> For example:
>
Liam Slusser wrote:
I've been trying to replicate a database but each time I replication it the
performance of the copy is about 100 times slower (~100ms to ~8 seconds for
the same query). The only way I have found to replicate it and keep the
same performance is doing a hotcopy of the database.
On Sep 5, 2007, at 18:57 , Liam Slusser wrote:
I've been trying to replicate a database but each time I
replication it the
performance of the copy is about 100 times slower (~100ms to ~8
seconds for
the same query). The only way I have found to replicate it and
keep the
same performanc
On 9/5/07, Josh Trutwin <[EMAIL PROTECTED]> wrote:
> I have a php application that needs to query the PK of a table - I'm
> currently using this from the information_schema views:
try this:
CREATE OR REPLACE VIEW PKEYS AS
SELECT nspname as schema, c2.oid as tableoid, c2.relname as table,
I've been trying to replicate a database but each time I replication it the
performance of the copy is about 100 times slower (~100ms to ~8 seconds for
the same query). The only way I have found to replicate it and keep the
same performance is doing a hotcopy of the database.
Please note I didn'
Hello all,
Has anyone had any luck compiling the Pl/Perl language on Mac OSX
(10.4)? I get the following error:
*** Cannot build PL/Perl because libperl is not a shared library.
*** You might have to rebuild your Perl installation. Refer to
*** the documentation for details.
If I modify Ma
Sorry, just realized that I misread the query's requirements, but you
can play with PG's system catalogs to complete it.
See:
http://www.postgresql.org/docs/8.1/static/catalogs.html
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
On 9/5/07, Josh Trutwin <[EMAIL PROTECTED]> wrote:
> Curious if there is a better/cheaper way to get the data I'm looking
> for though?
SELECT conname
FROM pg_constraint c JOIN pg_class l ON c.conrelid = l.relfilenode
JOIN pg_namespace n ON n.OID = l.relnamespace
WHERE contype = 'p'
A
I have a php application that needs to query the PK of a table - I'm
currently using this from the information_schema views:
SELECT column_name
FROM information_schema.table_constraints tc
INNER JOIN information_schema.constraint_column_usage ccu
ON tc.constraint_name = ccu.constraint_n
On 9/5/07, Martijn van Oosterhout <[EMAIL PROTECTED]> wrote:
> On Wed, Sep 05, 2007 at 10:44:20AM -0700, Trevor Talbot wrote:
> > Unless psql is turning on keepalive or similar, or the OS is forcing
> > it on by default, there are no timeouts for idle TCP connections. If
> > the command was transp
On 9/5/07, Ottó Havasvölgyi <[EMAIL PROTECTED]> wrote:
> What is the cause that "now()" works but "now" does not and
> "current_user" works but "current_user()" does not.
From http://www.postgresql.org/docs/8.2/static/functions-info.html :
"Note: current_user, session_user, and user have special
On Sep 5, 2007, at 15:31 , Ottó Havasvölgyi wrote:
What is the cause that "now()" works but "now" does not and
now() is a PostgreSQL extension and not required by the SQL spec. For
the most part, PostgreSQL extensions are functions and look like
them. CURRENT_TIMESTAMP and CURRENT_DATE ar
Hi,
What is the cause that "now()" works but "now" does not and
"current_user" works but "current_user()" does not. They are both
functions, and are present in pg_proc. How can I decide if a function
needs parentheses or not if it has no parameters?
Thanks in advance,
Otto
--
On Wed, Sep 05, 2007 at 10:44:20AM -0700, Trevor Talbot wrote:
> Unless psql is turning on keepalive or similar, or the OS is forcing
> it on by default, there are no timeouts for idle TCP connections. If
> the command was transported to the server successfully and psql was
> just waiting for a re
On 9/5/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> Thanks all for the quick replies.
>
> SET client_encoding = 'UTF8';
>
> Results, same error. Now I'm really concerned.
How / where are you setting the client_encoding? I'm wonder if it's
really set when the pg_dump / pg_restore commands ar
On 9/5/07, Scott Marlowe <[EMAIL PROTECTED]> wrote:
> begin;
> delete from table where uid in (select * from table t1 join table t2
> on (t1.field1=t2.field1 AND t1.field2=t2.field2 AND
> t1.field3=t2.field3 AND t1.uid>t2.uid) );
> (check for dups / lost data)
> commit;
There's a bug up there ^^^
Thanks all for the quick replies.
Here is the latest issue, to verify that the pg_dump works, I'm going
to do dump and restore on the same host/cluster.
Source:
DB_source:
Red Hat Enterprise Linux AS release 4 (Nahant Update 4)
psql 8.2.4
Destination:
same machine different db name
echo $LAN
On 9/5/07, Richard Huxton <[EMAIL PROTECTED]> wrote:
> Steve Crawford wrote:
> > A while back I had to restart the server and today discovered that some
> > of the client machines have psql processes dating back several months.
> >
> > Obviously no TCP connection on the server end but client-side
At 12:41 PM -0400 9/5/07, Owen Hartnett wrote:
At 11:32 AM -0400 9/5/07, Tom Lane wrote:
Alvaro Herrera <[EMAIL PROTECTED]> writes:
Owen Hartnett wrote:
I've been able to turn on statement logging (I've set log_statement to
'all'), but it doesn't seem to show the begin transaction - commit
On Sep 5, 2007, at 9:59 AM, Steve Crawford wrote:
I've dug through the docs and peeked at the source and found no way to
specify a connect_timeout so:
1. Did I, in fact, just overlook something?
PGCONNECT_TIMEOUT=5 psql ?
There are a lot of useful environment variables that libpq and
anythi
I've dug through the docs and peeked at the source and found no way to
specify a connect_timeout so:
1. Did I, in fact, just overlook something?
2. We would find it extremely useful to have this option. Would anyone else?
3. Alternately, what about adding a "raw connection string" feature to
psq
At 11:32 AM -0400 9/5/07, Tom Lane wrote:
Alvaro Herrera <[EMAIL PROTECTED]> writes:
Owen Hartnett wrote:
I've been able to turn on statement logging (I've set log_statement to
'all'), but it doesn't seem to show the begin transaction - commit -
rollback statements. Is there another way to
Richard Huxton wrote:
> Steve Crawford wrote:
>> What would cause psql to hang indefinitely when the backend disappears?
>>
>> We have a script that uses psql to insert a record (TCP connection to DB
>> on different machine). The command is basically
>> psql -c "insert into..."
>>
>> A while back
Alvaro Herrera <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> In PG 8.2 I'd agree, but older versions are not so good about logging
>> execution of prepared statements. What's the server version exactly,
>> and is there any indication of use of prepared statements in the log?
> Humm, but can yo
Tom Lane wrote:
> Alvaro Herrera <[EMAIL PROTECTED]> writes:
> > Owen Hartnett wrote:
> >> I've been able to turn on statement logging (I've set log_statement to
> >> 'all'), but it doesn't seem to show the begin transaction - commit -
> >> rollback statements. Is there another way to have them
Alvaro Herrera <[EMAIL PROTECTED]> writes:
> Owen Hartnett wrote:
>> I've been able to turn on statement logging (I've set log_statement to
>> 'all'), but it doesn't seem to show the begin transaction - commit -
>> rollback statements. Is there another way to have them show up in the log?
> If
Owen Hartnett wrote:
> At 10:14 AM -0400 8/28/07, Owen Hartnett wrote:
>> At 7:05 PM -0400 8/27/07, Tom Lane wrote:
>>> Owen Hartnett <[EMAIL PROTECTED]> writes:
I assign the transaction object to each of the commands, but it seems
that some tables will get updated, even when I call rol
On 9/5/07, Håkan Jacobsson <[EMAIL PROTECTED]> wrote:
> Hi,
>
> I want to create a DELETE statement which deletes duplicates
> in a table.
>
> That is, I want to remove all rows - but one - having three
> columns with the same data (more columns exist and there the
> data varies).
Assuming you've
On 9/5/07, Stefan Schwarzer <[EMAIL PROTECTED]> wrote:
> Hi there,
>
> I want to calculate per Capita values on-the-fly, taking for example
> the "Total GDP" data set and divide it by "Total Population". Now,
> each of these data sets have a couple of "0" or "-" values (the
> latter being the i
On 9/4/07, Nik <[EMAIL PROTECTED]> wrote:
> This is on PostgreSQL 8.1.3 on Windows 2003 Advanced Server.
Only 8.2 or newer has CREATE INDEX CONCURRENTLY.
Maybe you could schedule a maintenance window for this.
---(end of broadcast)---
TIP 5: don't
Hi,
I want to create a DELETE statement which deletes duplicates
in a table.
That is, I want to remove all rows - but one - having three
columns with the same data (more columns exist and there the
data varies).
For example:
column1
column2
column3
column4
column5
column2 = 'test', column3 =
At 10:14 AM -0400 8/28/07, Owen Hartnett wrote:
At 7:05 PM -0400 8/27/07, Tom Lane wrote:
Owen Hartnett <[EMAIL PROTECTED]> writes:
I assign the transaction object to each of the commands, but it seems
that some tables will get updated, even when I call rollback. Is
something I'm calling se
Hi,
I have a master table 'Master' with 3 partition tables 'child1', 'child2','
child3' which inherits the master table 'Master'. I have check constraints in
the child tables to insert the appropriate values and also there are functions
and triggers defined to do this.
My question is, if I inser
am Wed, dem 05.09.2007, um 6:58:30 -0700 mailte Rob folgendes:
> What is the proper why to deal with temp tables and sequences? Why aren't they
> being dropped after the function ends? Why do I get OID errors if I delete the
> temp table/sequence at the end of the function and then try to rerun t
Postgres v8.2
I'm having a problem with using temporary tables and sequences in a function.
It seems that the temp tables are not being removed once the function has
completed. I'm also running into OID conflicts when I run the function in
another session. I've had to do things like this to be
On Wed, Sep 05, 2007 at 11:15:43AM +0200, Stefan Schwarzer wrote:
> SELECT DISTINCT (tpes_total.y_2004 / pop_total.y_2004) AS
> y_2004, countries_view.name AS name
> FROM pop_total, countries_view
> LEFT JOIN tpes_total ON tpes_total.id = countries_view.id
> WHERE pop_total.y_2004
Hi there,
I want to calculate per Capita values on-the-fly, taking for example
the "Total GDP" data set and divide it by "Total Population". Now,
each of these data sets have a couple of "0" or "-" values (the
latter being the indicator for : "no data available").
Until now I have it
[EMAIL PROTECTED] írta:
Hi All.
I've the necessity to use Crystal Report in my C++ project to report
(in PDF) some PostgreSQL table.
Any idea how to implement this functionality in my C++ project, or
where I can find some useful CR documentation?
All the Crystal Report documentation I found is
Steve Crawford wrote:
What would cause psql to hang indefinitely when the backend disappears?
We have a script that uses psql to insert a record (TCP connection to DB
on different machine). The command is basically
psql -c "insert into..."
A while back I had to restart the server and today dis
blay bloo wrote:
I am defining some functions using plpythonu, through the standard means.
Here I have one function (test1) which calls another (testfunc). When
I excute this I get the following error:
ERROR: plpython: function "test1" failed
DETAIL: : global name 'testfunc' is not defined
On 05/09/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
>
> Hi All.
> I've the necessity to use Crystal Report in my C++ project to report (in
> PDF) some PostgreSQL table.
> Any idea how to implement this functionality in my C++ project, or where I
> can find some useful CR documentation?
> All
http://adv.email.it/cgi-bin/foclick.cgi?mid=6918&d=20070905
53 matches
Mail list logo
