On Wed, Jan 03, 2007 at 10:31:46PM -0600, mike wrote:
How are you attempting to restore the table after using psql? Psql
insert statements? Pgdump? COPY FROM?
Actually, right now I feed the dump file into a program which mangles
it and analyzes it in various ways. It will eventually be fed
On 1/3/07, Richard Huxton dev@archonet.com wrote:
hubert depesz lubaczewski wrote:
On 1/3/07, Richard Huxton dev@archonet.com wrote:
If you do that separately at the start of the process, (one query per
custom column in the old table) then it becomes straightforward.
no, because meaning
hubert depesz lubaczewski wrote:
On 1/3/07, Richard Huxton dev@archonet.com wrote:
hubert depesz lubaczewski wrote:
On 1/3/07, Richard Huxton dev@archonet.com wrote:
If you do that separately at the start of the process, (one query per
custom column in the old table) then it becomes
Bob Pawley wrote:
Found it in template 1. This seems strange as both servers and pgadmins
are the same version and I haven't opened the template until today.
The only thing I can think of is that you accidentally restored into
template1. Probably easier to do with pgadmin than from the
hubert depesz lubaczewski wrote:
On 1/4/07, Richard Huxton dev@archonet.com wrote:
Show me the table definitions and some sample data and I'll see if the
SQL is do-able.
technically - i can, but please - belive me it is not possible.
advert_custom_fields table has approx. 1200 columns (for
Hi,
I have a custom database (a very fast select/slow insert db) written
in c/c++.
I can access it with mysql writing a Custom Engine. That is pretty
cool because now all my custom db tables can be joined with tables in
mysql's format.
I only need read access to my custom table format db.
Hi.
I'm developing an application using PostgreSQL and it happened table
inheritance is THE solution to some design problems I have.
Unfortunately the feature doesn't exactly work as true class/object
inheritance would. Main problems are well recognized and documented:
child table doesn't
Tomas Lanczos [EMAIL PROTECTED] wrote:
Hello,
Using PostgreSQL 8.1.4, pgadmin III 1.6.0 in WinXP I tried to export a table
using COPY (first time in postgresql, did many times in Sybase):
COPY ml50jtsk_datum_v TO 'c:/postgresql/ml50jtsk.out';
I got the following errormessage:
ERROR:
On Thu, Jan 04, 2007 at 12:16:17AM -0800, [EMAIL PROTECTED] wrote:
On Wed, Jan 03, 2007 at 10:31:46PM -0600, mike wrote:
How are you attempting to restore the table after using psql? Psql
insert statements? Pgdump? COPY FROM?
Actually, right now I feed the dump file into a program which
My pg_largeobject table filenode is 16404:
(select relfilenode from pg_class where relname='pg_largeobject')
Pages (record) count in table pg_largeobject: 73574 - 144 MB (73574 * 2Kb)
(select count(loid) from pg_largeobject)
Relation pages count (in table pg_class) for table pg_largeobject:
Last week, I tried to arrange for you to receive a slightly
damaged copy of Derek Gehl's Internet Millionaire's Protégé
Bootcamp videos... but I don't think you got them?
Since these copies are available for a DEEP discount, I
didn't think you'd care about a couple of scuff marks on
the
Following up on recent experiences I had with unit tests, I've written
an article on unit testing in PostgreSQL. If anyone is interested, it
can be found at http://www.bigsmoke.us/postgresql-unit-testing/
Of course, comments and criticisms are ever welcome.
---(end of
Hi.
Is it possible to use the SQL parser embedded inside the postgres code via
some public API?
I wish to get a parsed SQL query in any format which lends itself to easy
analysis (structural, for example) so as to be able to find similarities
between queries.
I tried peeking around a bit, and
On Thu, Jan 04, 2007 at 08:01:17PM +0530, Ravindra Jaju wrote:
I tried peeking around a bit, and tried pulling out src/backend/parser - but
that depends on other parts of the code, making it a bit non-trivial. Plus,
any changes in the base code upstream would not be available to this
Hi.
Is it possible to use the SQL parser embedded inside the postgres code via
some public API?
I wish to get a parsed SQL query in any format which lends itself to easy
analysis (structural, for example) so as to be able to find similarities
between queries.
I tried peeking around a
Hi.
That was informative. Thanks.
The reason I started pursuing this path was that this file (parser/parser.h)
happens
to be distributed as part of some 'devel' package, making me think that it
might
be possible to re-use the SQL parsing functionality that exists within
postgres.
Even if it is
Hello !
I am trying to run this query in psql:
update clin.episode
set fk_patient = clin.health_issue.fk_patient
from clin.health_issue
where
clin.episode.fk_patient is NULL and
clin.episode.fk_health_issue = clin.health_issue.pk;
It returns UPDATE 2 which is what I expect from
On 1/4/07, Tatsuo Ishii [EMAIL PROTECTED] wrote:
3] If not, what would be a better mechanism to re-use all the work
already
done in postgres for SQL analysis?
One idea would be writing a PostgreSQL function which calls
raw_parser() and returns the parse tree as a texual representaion.
Martijn van Oosterhout kleptog@svana.org writes:
On Thu, Jan 04, 2007 at 08:01:17PM +0530, Ravindra Jaju wrote:
2] If yes, I found that none of the shared library files expose this
function - I could not link a simple C program with this function
successfully. (I tried all the .so files
Hello,
I work with a product which uses postgresql internally. I build
postgresql from source for Solaris SPARC and Linux. So far I have been
using 7.2.1 but lately I realized that it has long been obsolete and I
chose to move to 8.1.5.
In my earlier build system, I was patching a couple of
Scara Maccai [EMAIL PROTECTED] writes:
I only need read access to my custom table format db. Would read access
be possible with the C-Language Functions - Returning Sets API?
Probably. Take a look at contrib/dblink for ideas.
regards, tom lane
Grzegorz Nowakowski [EMAIL PROTECTED] writes:
But I have another question: why can't be
inheritance implemented as implicit JOIN?
Interesting thought, but joins are expensive --- this would be quite a
lot slower than the current way, I fear, especially when you consider
more than one level of
Karsten Hilbert [EMAIL PROTECTED] writes:
I am trying to run this query in psql:
update clin.episode
set fk_patient = clin.health_issue.fk_patient
from clin.health_issue
where
clin.episode.fk_patient is NULL and
clin.episode.fk_health_issue = clin.health_issue.pk;
It returns
Arindam [EMAIL PROTECTED] writes:
I am in a fix. Do I need to separately download the jdbc drivers and
build them into a jar file?
The jdbc driver is distributed separately now, yes. See
http://jdbc.postgresql.org/
regards, tom lane
---(end of
Speaking of partitioning, I see there some improvements planed for
this feature in 8.3 - any info on what exactly users can expect? Any
possibility to improve it so we don't have to add insert trigger that
selects the right table for operation? Also, propagation of Alter
table on inherited tables
On 1/4/07, Tom Lane [EMAIL PROTECTED] wrote:
Arindam [EMAIL PROTECTED] writes:
I am in a fix. Do I need to separately download the jdbc drivers and
build them into a jar file?
The jdbc driver is distributed separately now, yes. See
http://jdbc.postgresql.org/
regards,
ops. alter table seems to be propagating OK in 8.2...
On 1/4/07, Vlad [EMAIL PROTECTED] wrote:
Speaking of partitioning, I see there some improvements planed for
this feature in 8.3 - any info on what exactly users can expect? Any
possibility to improve it so we don't have to add insert trigger
Arindam [EMAIL PROTECTED] writes:
Thank you. I was actually looking for a more extensive list of changes
which have happened between these two versions. Is there a place that
can help?
Read the release notes ...
http://www.postgresql.org/docs/8.2/static/release.html
Hi,
On Thu, 2007-01-04 at 22:10 +0530, Arindam wrote:
I was actually looking for a more extensive list of changes
which have happened between these two versions. Is there a place that
can help?
See the release notes:
http://www.postgresql.org/docs/8.1/static/release.html
You will need to
Hi all,
I've created a database (pgsql 8.1 on Debian Etch) that uses
triggers/functions to keep all changes for various tables in a history
schema. This is the first time I've done this (captured and stored
changes in a different schema) so I was hoping for some backup/restore
advice.
Hi,
I need to be able to determine the last time (and date) that a database was
written to. I know it could be possible just to check the last modified
dates in the PGDATA directory, but i need to compare the last write time of
3 databases (connecting via JDBC). Hopefully the last write date
On Thu, 2007-01-04 at 11:11, Andy Dale wrote:
Hi,
I need to be able to determine the last time (and date) that a
database was written to. I know it could be possible just to check
the last modified dates in the PGDATA directory, but i need to compare
the last write time of 3 databases
Madison Kelly wrote:
Hi all,
I've created a database (pgsql 8.1 on Debian Etch) that uses
triggers/functions to keep all changes for various tables in a history
schema. This is the first time I've done this (captured and stored
changes in a different schema) so I was hoping for some
On Thu, Jan 04, 2007 at 11:36:35AM -0500, Tom Lane wrote:
update clin.episode
set fk_patient = clin.health_issue.fk_patient
from clin.health_issue
where
clin.episode.fk_patient is NULL and
clin.episode.fk_health_issue = clin.health_issue.pk;
It returns UPDATE 2 which is
Hi,
On Thu, 2007-01-04 at 11:20 -0500, Madison Kelly wrote:
As far as I can tell, you can only dump one schema at a time. Is
this true?
You can dump multiple schemas and multiple tables at a time with 8.2.
Regards,
--
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
On Thu, Jan 04, 2007 at 06:37:23PM +0100, Karsten Hilbert wrote:
It returns UPDATE 2 which is what I expect from the data.
However, the rows in question are not actually updated.
That seems very strange. Could you perhaps have a BEFORE UPDATE trigger
that's changing the values back
Richard Huxton wrote:
As far as I can tell, you can only dump one schema at a time. Is
this true?
No, pg_dump dumps a whole database by default. You can dump just a
single schema or table though.
Hmm, I wonder why I thought this... Was this true in older versions or
did I just imagine
Madison Kelly wrote:
Richard Huxton wrote:
As far as I can tell, you can only dump one schema at a time. Is
this true?
No, pg_dump dumps a whole database by default. You can dump just a
single schema or table though.
Hmm, I wonder why I thought this... Was this true in older versions or
Richard Huxton wrote:
JTyrrell wrote:
pg_dump -a -d testrig testrig.data
on a machine running postgresql 7.4.13 with database testirg i have no
problem.
Then, I want to do the same but on a different machine running postgresql
7.4.6 with database root. So...
pg_dump -a -d root
Hi,
I am doing a project in Database Monitoring tool. I am planning to add
pgsql also. So i need the table details, where i can get details like
Request Rate, Bytes Received Rate, Bytes Send Rate, Open Connections,
Aborted Connections, Aborted Clients, Threads Used, Threads in Cache,
Thread
Hi!
I installed PostgreSQL on CentOS 4.4 using the packages provided at:
ftp://ftp.postgresql.org/pub/binary/v8.2.0/linux/rpms/redhat/rhel-es-4/
I installed using:
#yum localinstall compat-postgresql-libs-4-2PGDG.rhel4.i686.rpm
#yum localinstall postgresql-8.2.0-2PGDG.i686.rpm
Hola a todos.
Como sabemos openSUSE viene con muchos paquetes para instalar o
preinstalados. El punto es que viene con la versión de PostgreSQL
8.1... y cuando descargo la 8.2 desde el sitio de PostgreSQL me lo
instala en en /usr/local/pgsl; la instalación inicial del motor que
viene dentro del
I have multi-company database.
Each company has its own chart of accounts table which are stored in each
company schema.
Some account numbers are used in a common table which is stored in public
schema.
So I need to create duplicate foreign keys like
create temp table company1.chartoffaccounts
Hi,
On Thu, 2007-01-04 at 16:57 +0100, Gunnar Wagenknecht wrote:
Do I need to look for a different 'perl-DBD-Pg' that doesn't depend on
'libpq.so.3' or should 'libpq.so.3' be provided by
'postgresql-libs-8.2.0-2PGDG.i686.rpm'?
Install this package:
JTyrrell [EMAIL PROTECTED] writes:
1. pg_dump -s doesn't work. I've tried using a load of other options and
none at all but no luck.
2. I'm running as root so wont be a problem there, and the file im dumping
to has full permissions for everyone.
What makes this even more confusing is I
Le jeudi 4 janvier 2007 06:51, sbaskar a écrit :
I am doing a project in Database Monitoring tool. I am planning to add
pgsql also. So i need the table details, where i can get details like
Request Rate, Bytes Received Rate, Bytes Send Rate, Open Connections,
[...]
Some of the SQL requests
Gunnar Wagenknecht wrote:
Do I need to look for a different 'perl-DBD-Pg' that doesn't depend on
'libpq.so.3' or should 'libpq.so.3' be provided by
'postgresql-libs-8.2.0-2PGDG.i686.rpm'?
Just make a symbolic link from the shipped libpq.so.5 to libpq.so.3 and
it should work.
Yum kind
This is revisiting a problem I posed to this group a month or so ago
regarding separating different users' data through schema views. The
solution we're using is based on a suggestion we received here:
http://archives.postgresql.org/pgsql-general/2006-12/msg00037.php
Everything is working
On 1/4/07, Tony Caduto [EMAIL PROTECTED] wrote:
Gunnar Wagenknecht wrote:
Do I need to look for a different 'perl-DBD-Pg' that doesn't depend on
'libpq.so.3' or should 'libpq.so.3' be provided by
'postgresql-libs-8.2.0-2PGDG.i686.rpm'?
Just make a symbolic link from the shipped
I think I got it:
CREATE FUNCTION new_get_emp_id() RETURNS INTEGER AS $$ select emp_id
from secureview.tbl_employee where username = (SELECT current_user) $$
LANGUAGE SQL IMMUTABLE;
I made the function immutable so it only calls it once, therefore no
longer requiring a call per-row.
On Jan 3, 2007, at 5:24 , Luca Ferrari wrote:
Running the database, the users
decided to place numbers as strings, so values like 00110002 and so
on.
Note that '00110002' is not a number (i.e., it's not equal to
110002): it's a string of digits.
is there a tool or a
way to easily do
Andrus wrote:
I have multi-company database.
Each company has its own chart of accounts table which are stored in each
company schema.
Some account numbers are used in a common table which is stored in public
schema.
So I need to create duplicate foreign keys like
create temp table
Michael Glaesemann wrote:
On Jan 3, 2007, at 5:24 , Luca Ferrari wrote:
And moreover a database design question: is a better idea to choose
always
(when possible) numeric keys?
Depends on your requirements. This is an oft-discussed topic about
which you can find many more opinions by
John McCawley [EMAIL PROTECTED] writes:
I think I got it:
CREATE FUNCTION new_get_emp_id() RETURNS INTEGER AS $$ select emp_id
from secureview.tbl_employee where username = (SELECT current_user) $$
LANGUAGE SQL IMMUTABLE;
I made the function immutable so it only calls it once, therefore no
Tom Lane wrote:
John McCawley [EMAIL PROTECTED] writes:
I think I got it:
CREATE FUNCTION new_get_emp_id() RETURNS INTEGER AS $$ select emp_id
from secureview.tbl_employee where username = (SELECT current_user) $$
LANGUAGE SQL IMMUTABLE;
I made the function immutable so it only calls it
I tried stable, and that didn't help at all. How long does PostgreSQL
maintain the state of the function when using immutable? Until a
restart? Until the end of a session? Until the function is dropped and
re-added?
While this value isn't 100% absolutely positively guaranteed to never
create database test;
\c test
create table base (foo int not null);
create table derived () inherits (base);
alter table derived alter foo drop not null;
insert into derived values(null);
Dump it, and the dump will not include any command to drop the not null
constraint on derived.foo, so restore
John McCawley [EMAIL PROTECTED] writes:
While this value isn't 100% absolutely positively guaranteed to never
change, it is pretty dang close.
Counterexample: SET ROLE or SET SESSION AUTHORIZATION.
regards, tom lane
---(end of
Scott Ribe [EMAIL PROTECTED] writes:
create database test;
\c test
create table base (foo int not null);
create table derived () inherits (base);
alter table derived alter foo drop not null;
insert into derived values(null);
Dump it, and the dump will not include any command to drop the
I'm not trying to be argumentative, but I honestly don't know what you
mean here...and the only reason I ask for clarification is that you are
probably trying to tell me something important :)
Tom Lane wrote:
John McCawley [EMAIL PROTECTED] writes:
While this value isn't 100% absolutely
I just became involved in a scenario wherein a migration between
releases (8.1.x - 8.2) using pg_dumpall piped to psql (per section
23.5 of the 8.2 docs) was interrupted based on duration of the
procedure. The interruption was green lit because it was determined
that the data had been
Thomas F. O'Connell [EMAIL PROTECTED] writes:
My big question is: Is there anything that happens late in the game
in a pg_dumpall that affects system catalogs or other non-data
internals in any critical ways that would make an interrupted
pg_dumpall | psql sequence unstable?
There's
What about:
create function set_emp_id() returns void as $$
begin
drop table if exists emp_1_id;
select emp_id into temp emp_1_id from secureview.tbl_employee where
username = current_user;
end;
$$ language plpgsql;
create function get_emp_id() returns int as $$
return select
Scott Ribe wrote:
What about:
create function set_emp_id() returns void as $$
begin
drop table if exists emp_1_id;
select emp_id into temp emp_1_id from secureview.tbl_employee where
username = current_user;
end;
$$ language plpgsql;
create function get_emp_id() returns int as
Had some database corruption problems today. Since they came on the
heels of making some minor database changes yesterday, they may or may
not be related to that. Centos 4.x, Postgresql 8.1.4
I modified the following settings and then issued a reload.I hadn't
turned up the kernel.shmmax
Devrim GUNDUZ wrote:
Install this package:
http://developer.postgresql.org/~devrim/rpms/compat/compat-postgresql-libs-3-3PGDG.i686.rpm
Thanks, it worked. But I had to remove
compat-postgresql-libs-4-2PGDG.rhel4.i686.rpm for this one to work. What
if some client needs libpq.so.4?
Generally, I
Michael Best wrote:
8.1.4 is also in CentosPlus
They are apparently following a RH RPM that is at revision 8.1.4
http://mirror.centos.org/centos/4/centosplus/Readme.txt
Thanks for the tip!
Cu, Gunnar
--
Gunnar Wagenknecht
[EMAIL PROTECTED]
http://wagenknecht.org/
67 matches
Mail list logo