Hi guys,
I have a question about outer join. For example as follow (pg 8.4.1):
--
create table t_1(a int);
create table t_3(a int);
insert into t_1 values(1);
insert into t_1 values(2);
insert into t_3 values(1);
insert into t_3 values(3);
postgres=# select version();
2010/1/5 Andy Colson a...@camavision.com
I have a function that's working for what I needed it to do, but now I need
to call it for every id in a different table... and I'm not sure what the
syntax should be.
Here is an example:
create or replace function test(uid integer, out vhrs
On Tue, 2010-01-05 at 22:29 -0800, Yan Cheng Cheok wrote:
Thanks for the information. I perform benchmarking on a very simple table, on
local database. (1 table, 2 fields with 1 is bigserial, another is text)
INSERT INTO
On Wed, 2010-01-06 at 15:30 +1300, Tim Uckun wrote:
I, for one, would loudly and firmly resist the addition of such a
feature. Almost-as-fast options such as intelligent re-checking of
Even if it was not the default behavior?
If you really want to do that, look at the manual for how
On tis, 2010-01-05 at 16:06 -0800, Andrew Lardinois wrote:
Poking around in the 8.5 Devel Documentation section 8.13.1, the XML
Type, I noticed that:
The xml type does not validate input values against a document type
declaration (DTD), even when the input value specifies a DTD
I suppose
Ahmad,
Do you have something monitoring PostgreSQL by connecting to port 5432
(or whatever you have it listening on) such as Nagios or Zenoss?
- Chris
Ahmad Rumman wrote:
I am getting WARNING at log file:
Jan 6 11:19:54 dev04 postgres[14624]: [1622-1] DEBUG: name: unnamed;
Tim Uckun timuc...@gmail.com writes:
Is there a command like COPY which will insert the data but skip all
triggers and optionally integrity checks.
pg_bulkload does that AFAIK.
http://pgbulkload.projects.postgresql.org/
Regards,
--
dim
--
Sent via pgsql-general mailing list
Dean Rasheed wrote:
So there is quite a bit of flexibility - you may choose to have the
constraint checked at any of these times:
- after each row (the default for NON DEFERRABLE constraints)
- after each statement (DEFERRABLE [INITIALLY IMMEDIATE])
- at the end of the transaction
2010/1/5 Roman Neuhauser neuhauser+pgsql-general#postgresql@sigpipe.cz:
# jayadevan.maym...@ibsplc.com / 2010-01-04 10:03:29 +0530:
This seems to work..
UPDATE x set i=i+1
from (select i as m from x order by m desc) y where x.i = y.m
Jayadevan
Thanks, that nicely achieves the
I posted this several days ago to pgsql-jdbc but have had no response. I am
posting it here (with minor changes in the wording).
I have developed some code that works, I'm just not sure I have the best
solution.
I have applications in which the user can create a read-only resultset with
hx.li fly...@126.com writes:
ERROR: FULL JOIN is only supported with merge-joinable join conditions
My question is: why on clause restrict t_1.a=1?
It's an implementation restriction. If the clauses aren't mergejoinable
there's no very practical way to keep track of which inner-side rows
have
2010/1/6 Daniel Verite dan...@manitou-mail.org:
Dean Rasheed wrote:
So there is quite a bit of flexibility - you may choose to have the
constraint checked at any of these times:
- after each row (the default for NON DEFERRABLE constraints)
- after each statement (DEFERRABLE
On 1/6/2010 2:45 AM, Filip Rembiałkowski wrote:
2010/1/5 Andy Colson a...@camavision.com mailto:a...@camavision.com
I have a function that's working for what I needed it to do, but now
I need to call it for every id in a different table... and I'm not
sure what the syntax should be.
Daniel Verite dan...@manitou-mail.org writes:
But still I wonder why there is that difference in behavior between NON
DEFERRABLE and DEFERRABLE INITIALLY IMMEDIATE, when the unique constraint
doesn't get deferred by using SET CONSTRAINTS.
In the first case, we get the after each row behavior
On Tue, 05 Jan 2010 20:20:13 -0600,
Seb splu...@gmail.com wrote:
On Wed, 30 Dec 2009 20:04:51 -0600,
Seb splu...@gmail.com wrote:
On Wed, 30 Dec 2009 19:39:15 -0600,
Seb splu...@gmail.com wrote:
CREATE RULE footwear_nothing_upd AS
ON UPDATE TO footwear DO INSTEAD NOTHING; CREATE RULE
Howdy!
I'm currently in a MySQL - PostgreSQL migration project (Go, go, go, ...
shall I cc: slashdot, too? ;-)
Part of this is in embedded context, where a (diskless) embedded computer
runs from flash. Since we don't want to stress the flash too much, the db
is actually loaded from a dump at
Hello,
Is there a way to protect psql source code? For example oracle has wrap
utility.
I want to deploy my DB on a hosting company server. But they can see my
functions code (they have root privileges) and this is what I want to avoid.
Thank you in advance,
Marius Pitigoi
Adrian von Bidder avbid...@fortytwo.ch writes:
With our test dump, the db (after import) is ca. 300M on disk, ca. half in
WAL files (pg_xlog.) If I could mostly get rid of the WAL (keep it to a
bare minimum and run pg without fsync, something like that), the remaining
160 to 180M would be
On Wed, 06 Jan 2010 09:39:45 -0600,
Seb splu...@gmail.com wrote:
Would this express the intention any better?
CREATE RULE footwear_nothing_upd AS
ON UPDATE TO footwear DO INSTEAD NOTHING;
CREATE RULE footwear_newshoelaces_upd AS
ON UPDATE TO footwear
WHERE NOT EXISTS (SELECT
On Wed, 2010-01-06 at 16:39 +0100, Adrian von Bidder wrote:
Howdy!
I'm currently in a MySQL - PostgreSQL migration project (Go, go, go, ...
shall I cc: slashdot, too? ;-)
Part of this is in embedded context, where a (diskless) embedded computer
runs from flash. Since we don't want to
On Wed, Jan 6, 2010 at 8:39 AM, Adrian von Bidder avbid...@fortytwo.ch wrote:
Howdy!
With our test dump, the db (after import) is ca. 300M on disk, ca. half in
WAL files (pg_xlog.) If I could mostly get rid of the WAL (keep it to a
bare minimum and run pg without fsync, something like that),
On 06/01/2010 16:09, Marius Pitigoi wrote:
Hello,
Is there a way to protect psql source code? For example oracle has wrap
utility.
I want to deploy my DB on a hosting company server. But they can see my
functions code (they have root privileges) and this is what I want to
avoid.
I don't
On Jan 6, 2010, at 8:09 AM, Marius Pitigoi wrote:
Hello,
Is there a way to protect psql source code? For example oracle has wrap
utility.
I want to deploy my DB on a hosting company server. But they can see my
functions code (they have root privileges) and this is what I want to avoid.
On 1/5/2010 10:54 AM, Tom Lane wrote:
Adrian Klaverakla...@comcast.net writes:
From what I could see in the source code
(src/backend/utils/adt/formatting.c) the year portion of the string is
not run through the FM modifier. A fix would mean a patch to the above
AFAIK.
Should it be? Can
On Wed, Jan 6, 2010 at 11:11 AM, Erik Jones ejo...@engineyard.com wrote:
On Jan 6, 2010, at 8:09 AM, Marius Pitigoi wrote:
Hello,
Is there a way to protect psql source code? For example oracle has wrap
utility.
I want to deploy my DB on a hosting company server. But they can see my
On Wed, 2010-01-06 at 13:11 -0700, Scott Marlowe wrote:
On Wed, Jan 6, 2010 at 11:11 AM, Erik Jones ejo...@engineyard.com wrote:
On Jan 6, 2010, at 8:09 AM, Marius Pitigoi wrote:
Hello,
Is there a way to protect psql source code? For example oracle has wrap
utility.
I want to
Adrian von Bidder wrote:
With our test dump, the db (after import) is ca. 300M on disk, ca. half in
WAL files (pg_xlog.) If I could mostly get rid of the WAL (keep it to a
bare minimum and run pg without fsync, something like that), the remaining
160 to 180M would be ok.
Drop
It appears as though the timestamp resolution is now low
enough that it cannot keep up with the speed at which
items can be inserted. That is, when ordering entries
by timestamp, it's possible that the ordering will not
reflect the actual entry order. (I assume the corollary
is that the sort
Guy Rouillier guyr-...@burntmail.com writes:
Oracle states clearly in the SQL Reference manual:
A modifier can appear in a format model more than once. In such a case,
each subsequent occurrence toggles the effects of the modifier.
*Toggles* the effect of the modifier? Egad, what drunken
Steve Wampler swamp...@noao.edu writes:
It appears as though the timestamp resolution is now low
enough that it cannot keep up with the speed at which
items can be inserted.
Your example looks like what's being called is current_timestamp(3),
or else something on the client side is rounding it
On Thu, Jan 7, 2010 at 3:13 AM, Dimitri Fontaine dfonta...@hi-media.com wrote:
Tim Uckun timuc...@gmail.com writes:
Is there a command like COPY which will insert the data but skip all
triggers and optionally integrity checks.
pg_bulkload does that AFAIK.
That's a great utility.
Tom Lane wrote:
Your example looks like what's being called is current_timestamp(3),
or else something on the client side is rounding it off to 3 digits.
The bare function will give whatever resolution the operating system
supplies, down to microseconds at best (the limit of the POSIX API for
Steve Wampler swamp...@noao.edu writes:
Tom Lane wrote:
Even so, though, I think it would be quite foolish to design an
application around the assumption that the timestamps of successive
insertions will be distinguishable. Put in a serial column.
I'll do that. I was a bit surprised to see
thedb=# create table foo (col1 text, constraint chk check (col1 in
('a','b','c',null)));
CREATE TABLE
thedb=# insert into foo (col1) values ('xxx');
INSERT 0 1
H... I would have thought that this would have violated the constraint
because 'xxx' is not null and nit one of the allowed values.
Gauthier, Dave dave.gauth...@intel.com writes:
thedb=# create table foo (col1 text, constraint chk check (col1 in
('a','b','c',null)));
CREATE TABLE
thedb=# insert into foo (col1) values ('xxx');
INSERT 0 1
H... I would have thought that this would have violated the constraint
I am writing a shell script which runs as a cron entry. The objective
is to delete older records from postgresql DB.
I have thousands of records. What is the optimum number of records to
delete in one delete command
( my script will delete records in a loop and I want to ensure that
the swap
If I run the following (in either a terminal or the PgAdmin3 Query tool) I get
the error:
ERROR: query has no destination for result data
SQL state: 42601
Hint: If you want to discard the results of a SELECT, use PERFORM instead.
Context: PL/pgSQL function anything_all_udf line 3 at SQL
Hi. Need some help getting WAL log archiving going, please.
PostgreSQL 8.4.2
archive_command = '/usr/local/bin/rsync -e /usr/bin/ssh %p
postg...@remoteserver:directory/%f /dev/null'
I am able to login to remoteserver as user postgres using key-based
authentication (trust relationship exists).
I am having the table with 1 million rows.
I know there can be multiple YanChengCHEOK. But in certain situation, I will
be only interested in 1 YanChengCHEOK.
I try to perform SELECT query.
SemiconductorInspection=# SELECT measurement_type_id FROM measurement_type
WHERE
On Wed, 6 Jan 2010 17:45:31 -0800 (PST)
Yan Cheng Cheok ycch...@yahoo.com wrote:
situation, I will be only interested in 1 YanChengCHEOK.
SELECT measurement_type_id INTO _measurement_type_id FROM
measurement_type WHERE measurement_type_name='YanChengCHEOK';
LIMIT 1
Is that what you
On Wednesday 06 January 2010 5:01:39 pm Iain Barnett wrote:
If I run the following (in either a terminal or the PgAdmin3 Query tool) I
get the error:
ERROR: query has no destination for result data
SQL state: 42601
Hint: If you want to discard the results of a SELECT, use PERFORM instead.
Aleksey Tsalolikhin escribió:
I do have a cron job that cleans files older than 2 days out of the
pg_xlog directory;
Bad, bad idea. Get rid of that. Perfect way to corrupt your system.
Postgres removes pg_xlog files automatically when they are no longer
necessary. If it doesn't remove them,
On Wed, Jan 06, 2010 at 05:09:06PM +0100, Marius Pitigoi wrote:
Hello,
Is there a way to protect psql source code? For example oracle has
wrap utility. I want to deploy my DB on a hosting company server.
But they can see my functions code (they have root privileges) and
this is what I want
I came across a lot of similar example for foreign key
CREATE TABLE orderinfo
(
orderinfo_id serial ,
customer_id integer NOT NULL,
date_placed date NOT NULL,
date_shipped date ,
shipping numeric(7,2) ,
CONSTRAINT orderinfo_pk PRIMARY KEY(orderinfo_id),
CONSTRAINT orderinfo_customer_id_fk FOREIGN
On Wed, Jan 6, 2010 at 8:51 PM, Yan Cheng Cheok ycch...@yahoo.com wrote:
I came across a lot of similar example for foreign key
CREATE TABLE orderinfo
(
orderinfo_id serial ,
customer_id integer NOT NULL,
date_placed date NOT NULL,
date_shipped date ,
shipping numeric(7,2) ,
CONSTRAINT
On Wed, Jan 6, 2010 at 7:51 PM, Yan Cheng Cheok ycch...@yahoo.com wrote:
instead of let customer_id being type as integer, can i let it be serial? is
there any difference?
if the table referenced by customer_id is having primary key typed big
serial, customer_id shall be declared as
It's an implementation restriction. If the clauses aren't mergejoinable
there's no very practical way to keep track of which inner-side rows
have had a match.
If we could consider it is equivalent transformation as follow?
select * from t_1 full outer join t_3 on t_1.a=1;
and
select * from
hx.li fly...@126.com writes:
If we could consider it is equivalent transformation as follow?
select * from t_1 full outer join t_3 on t_1.a=1;
and
select * from t_1 full outer join t_3 on true where t_1.a=1;
Those are not equivalent.
regards, tom lane
--
Sent via
On Wednesday 06 January 2010 5:01:39 pm Iain Barnett wrote:
If I run the following (in either a terminal or the PgAdmin3 Query tool) I
get the error:
ERROR: query has no destination for result data
SQL state: 42601
Hint: If you want to discard the results of a SELECT, use PERFORM instead.
On Wed, Jan 6, 2010 at 9:08 PM, Richard Broersma
richard.broer...@gmail.com wrote:
On Wed, Jan 6, 2010 at 7:51 PM, Yan Cheng Cheok ycch...@yahoo.com wrote:
instead of let customer_id being type as integer, can i let it be serial? is
there any difference?
if the table referenced by
On Wed, Jan 6, 2010 at 8:36 PM, Scott Marlowe scott.marl...@gmail.com wrote:
. A
serial foreign key would be nonsensical since foreign keys should be
be generating their own values.
Pretty sure the OP was talking about referencing a bigserial from a
foreign key, which makes perfect sense for
Yan Cheng Cheok wrote:
The time taken to perform measurement per unit is in term of ~30 milliseconds.
We need to record down the measurement result for every single unit. Hence, the
time taken by record down the measurement result shall be far more less than
milliseconds, so that it will have
On 1/6/2010 3:29 PM, Tom Lane wrote:
Guy Rouillierguyr-...@burntmail.com writes:
Oracle states clearly in the SQL Reference manual:
A modifier can appear in a format model more than once. In such a case,
each subsequent occurrence toggles the effects of the modifier.
*Toggles* the effect
On Thu, Jan 7, 2010 at 11:29 AM, Alvaro Herrera
alvhe...@commandprompt.com wrote:
Aleksey Tsalolikhin escribió:
I do have a cron job that cleans files older than 2 days out of the
pg_xlog directory;
Bad, bad idea. Get rid of that. Perfect way to corrupt your system.
Postgres removes
Thanks for the valuable advice! Will take them into consideration seriously..
From my point of view, my current requirement is limited by so-called
overhead during communication with database. See the following result from
SQL Shell :
SemiconductorInspection=# \timing on
Timing is on.
On Wed, Jan 6, 2010 at 22:03, shulkae shul...@gmail.com wrote:
[...]
I have thousands of records. What is the optimum number of records to
delete in one delete command
Optimum in which way?
--
- Rikard - http://bos.hack.org/cv/
--
Sent via pgsql-general mailing list
shulkae wrote:
I am writing a shell script which runs as a cron entry. The objective
is to delete older records from postgresql DB.
I have thousands of records. What is the optimum number of records to
delete in one delete command
as many as you need to,
DELETE FROM yourtable AS t
Sorry if this question had been asked before. Although I had googled, but find
no answer.
I try to use C++, to iterate the array returned from stored procedure.
std::stringstream ss;
ss SELECT * FROM get_array_test();
res = PQexec(conn, ss.str().c_str());
int
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
59 matches
Mail list logo
