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
> 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 integer, out phrs
>
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 IN
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
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
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;
> blo
Tim Uckun 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 (pgsql-general@postgresql.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 [INITIALLY IMMEDIATE])
> - at the end of the transac
2010/1/5 Roman Neuhauser :
> # 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 illusion of atomic immediate checking.
>
> --
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
mult
"hx.li" 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 had a ma
2010/1/6 Daniel Verite :
> 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])
On 1/6/2010 2:45 AM, Filip Rembiałkowski wrote:
2010/1/5 Andy Colson 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.
Here is an ex
"Daniel Verite" 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 with the pk=pk+1
On Tue, 05 Jan 2010 20:20:13 -0600,
Seb wrote:
> On Wed, 30 Dec 2009 20:04:51 -0600,
> Seb wrote:
> On Wed, 30 Dec 2009 19:39:15 -0600,
>> Seb wrote:
> CREATE RULE footwear_nothing_upd AS
>>> ON UPDATE TO footwear DO INSTEAD NOTHING; CREATE RULE
>>> footwear_newshoelaces_upd AS ON UPDATE TO f
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 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 ok.
checkpoint_s
On Wed, 06 Jan 2010 09:39:45 -0600,
Seb 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 sh_id FROM sho
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 wa
On Wed, Jan 6, 2010 at 8:39 AM, Adrian von Bidder 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), the remaining
> 1
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 d
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 av
On 1/5/2010 10:54 AM, Tom Lane wrote:
Adrian Klaver 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 anyone check how this w
On Wed, Jan 6, 2010 at 11:11 AM, Erik Jones 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
>> functions c
On Wed, 2010-01-06 at 13:11 -0700, Scott Marlowe wrote:
> On Wed, Jan 6, 2010 at 11:11 AM, Erik Jones 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 dep
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 checkpoint_seg
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 us
Guy Rouillier 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 idiot chose
that spe
Steve Wampler 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 off to 3 digit
On Thu, Jan 7, 2010 at 3:13 AM, Dimitri Fontaine wrote:
> Tim Uckun 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. Unfortunately since it bypasses the WAL I
c
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
thi
Steve Wampler 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 that the
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" 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
> because 'xxx' is not
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 file
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 stateme
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 http://www.postgresql.org/mailpref/pgsql-general
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 measurement_type_na
On Wed, 6 Jan 2010 17:45:31 -0800 (PST)
Yan Cheng Cheok 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 were looking for
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 instea
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
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 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 orderinfo
On Wed, Jan 6, 2010 at 7:51 PM, Yan Cheng Cheok 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 bigint ?
This is
> 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 * fr
"hx.li" 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 pgsql-gene
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 instea
On Wed, Jan 6, 2010 at 9:08 PM, Richard Broersma
wrote:
> On Wed, Jan 6, 2010 at 7:51 PM, Yan Cheng Cheok 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 b
On Wed, Jan 6, 2010 at 8:36 PM, Scott Marlowe 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 certain types o
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 Rouillier 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? Ega
On Thu, Jan 7, 2010 at 11:29 AM, Alvaro Herrera
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 pg_xlog files automatic
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.
Semi
On Wed, Jan 6, 2010 at 22:03, shulkae 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 (pgsql-general@postgresql.org)
To make
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());
in
--
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
