Using Mysql the query works, so I expected it to be somehow standard (I know,
Mysql sometimes allows much more than the standard...)
Does this has something to do with Support LATERAL subqueries in the to do
list?
It's a very shame it's not supported...
Thank you
--- Lun 18/5/09, Sam Mason
Marsh Ray wrote:
The central question: So if I successfully commit an update
transaction on one connection, then instantaneously issue a select on
another previously-opened connection, under what circumstances am I
guaranteed that the select will see the effects of the update?
If the select
On Mon, 2009-05-18 at 18:44 -0500, Marsh Ray wrote:
This is a connection kept open for extended periods, and used
mutithreadedly for selects only.
I have some code that will allow you to reuse snapshots from one session
to another, which could help your multiple threads see a consistent view
Hello,
I try to use the new 8.4 INTERVAL type with libpq, but get crazy with
the input formatting rules...
I use PQprepare() / PQexecPrepared() with parameter list, binding the
INTERVAL values with the 1186 pg_type and passing a string buffer with
values like:
12345 for an INTERVAL YEAR
The
Hi,
does the planner know how scattered rows are? If yes: where is this info
stored?
I'm asking because it looks to me that not only the number of rows, but also
how data is on disk (more or less sequential) is used to determine what type of
index scan (bitmap/regular) has to be used...
but
Hello,
Can someone explain this:
test1= create table t1 ( k int, i interval second );
CREATE TABLE
test1= insert into t1 values ( 1, '-67 seconds' );
INSERT 0 1
test1= insert into t1 values ( 2, '999 seconds' );
INSERT 0 1
test1= select * from t1;
k | i
---+---
1 | -00:00:07
2 |
Ops, found it:
http://www.postgresql.org/docs/8.4/static/view-pg-stats.html
(correlation column)
I guess it would be worth pointing it out in the planner docs...
Scara Maccai wrote:
Hi,
does the planner know how scattered rows are? If yes: where is this
info stored?
--
Sent via
Sebastien FLAESCH wrote:
Hello,
Can someone explain this:
test1= create table t1 ( k int, i interval second );
CREATE TABLE
test1= insert into t1 values ( 1, '-67 seconds' );
INSERT 0 1
test1= insert into t1 values ( 2, '999 seconds' );
INSERT 0 1
test1= select * from t1;
k | i
I think it should be clarified in the documentation...
Actually I would like to use this new INTERVAL type to store IBM/Informix
INTERVALs,
which can actually be used like this with DATETIME types:
create table t1 (
k int,
dt1 datetime hour to minute,
dt2 datetime hour to
On Tue, May 19, 2009 at 12:04:28AM -0700, Scara Maccai wrote:
Using Mysql the query works, so I expected it to be somehow standard
(I know, Mysql sometimes allows much more than the standard...)
No, as far as I know the standard explicitly says that the MySQL
behaviour is incorrect.
Does this
Sebastien FLAESCH wrote:
I think it should be clarified in the documentation...
Please don't top-quote. And yes, I think you're right.
Hmm a quick google for: [sql interval second] suggests that it's not
the right thing. I see some mention of 2 digit precision for a leading
field, but no
Actually it's not limited to the usage of INTERVAL SECOND, I am writing
a PostgreSQL driver for our 4GL virtual machine...
I need to store all possible Informix INTERVAL types such as:
INTERVAL MONTH(8) TO MONTH
INTERVAL DAY(8) TO MINUTE
INTERVAL SECOND TO FRACTION(5)
... etc ...
Sebastien FLAESCH wrote:
Actually it's not limited to the usage of INTERVAL SECOND, I am writing
a PostgreSQL driver for our 4GL virtual machine...
I need to store all possible Informix INTERVAL types such as:
INTERVAL MONTH(8) TO MONTH
INTERVAL DAY(8) TO MINUTE
INTERVAL SECOND TO
On Tue, May 19, 2009 at 10:08:37AM +0200, Sebastien FLAESCH wrote:
I try to use the new 8.4 INTERVAL type with libpq, but get crazy with
the input formatting rules...
I think you're giving the database conflicting instructions and it's
getting confused.
fprintf(stdout,++ Preparing INSERT
On 18/05/2009 23:28, Howard Cole wrote:
Just installed 8.3.7 on a W2K3 machine using the pginstaller. I cannot
find contrib/tsearch2.sql - I need to import an 8.2 backup with the old
tsearch2 but cannot find the new compatibility layer sql file.
Hi there,
tsearch2 was incorporated into the
Hi,
how can you get N numbers (without holes) from a sequence?
Thomas
--
Thomas Guettler, http://www.thomas-guettler.de/
E-Mail: guettli (*) thomas-guettler + de
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
Raymond O'Donnell wrote:
On 18/05/2009 23:28, Howard Cole wrote:
Just installed 8.3.7 on a W2K3 machine using the pginstaller. I cannot
find contrib/tsearch2.sql - I need to import an 8.2 backup with the old
tsearch2 but cannot find the new compatibility layer sql file.
Hi there,
On Tue, May 19, 2009 at 12:45 PM, Thomas Guettler h...@tbz-pariv.de wrote:
Hi,
how can you get N numbers (without holes) from a sequence?
generate_series(), or make sure there's only one connection, no
transactions rollback.
--
GJ
--
Sent via pgsql-general mailing list
Yes, good point.
I realize now that I would have expected libpq to give me a way to specify
the exact decoration or precision of INTERVAL parameters...
As you can do with ODBC's SQLBindParameter(), where you specify the C type,
SQL type, precision/scale or length ...
I believe this is important
Hi all,
i'm using postgresql 8.3 and i have create a very simple script for insert
data into database (because i'm testing it, i create and drop/mod frequently
the tables).
My script is this:
#!/bin/bash
echo Insert start
psql -U myself -d test -f 000_ins_users.sql
psql -U myself -d test -f
hubert depesz lubaczewski schrieb:
On Tue, May 19, 2009 at 01:45:17PM +0200, Thomas Guettler wrote:
how can you get N numbers (without holes) from a sequence?
alter sequence XXX increment by 1000;
select nextval('XXX');
alter sequence XXX increment by 1;
If other processes run nextval()
In response to kelvin273 :
Hi all,
i'm using postgresql 8.3 and i have create a very simple script for insert
data into database (because i'm testing it, i create and drop/mod frequently
the tables).
My script is this:
#!/bin/bash
echo Insert start
psql -U myself -d test -f
On Mon, May 18, 2009 at 11:33:03PM +0430,
Zico mailz...@gmail.com wrote
a message of 74 lines which said:
No, I don`t have any data of Postgres data directory.
Next time, do not forget backups...
As far as i can remember, my postgre files were in /usr/share/postgresql/8.3
as i am using
kelvin273 wrote:
Hi all,
i'm using postgresql 8.3 and i have create a very simple script for insert
data into database (because i'm testing it, i create and drop/mod frequently
the tables).
My script is this:
#!/bin/bash
echo Insert start
psql -U myself -d test -f 000_ins_users.sql
psql -U
On Tue, May 19, 2009 at 02:17:20PM +0200, Sebastien FLAESCH wrote:
As you can do with ODBC's SQLBindParameter(), where you specify the C type,
SQL type, precision/scale or length ...
I believe this is important when it comes to data type conversion (for ex,
when you want to insert a
Solved...mine error...the script was create under windows and launched under
linux...the file format was not corrent...
Thanks :-)
kelvin273 wrote:
Hi all,
i'm using postgresql 8.3 and i have create a very simple script for insert
data into database (because i'm testing it, i create and
On Tue, May 19, 2009 at 8:17 AM, Sebastien FLAESCH s...@4js.com wrote:
Yes, good point.
I realize now that I would have expected libpq to give me a way to specify
the exact decoration or precision of INTERVAL parameters...
As you can do with ODBC's SQLBindParameter(), where you specify the C
On Tue, May 19, 2009 at 10:12 AM, Sam Mason s...@samason.me.uk wrote:
On Tue, May 19, 2009 at 02:17:20PM +0200, Sebastien FLAESCH wrote:
As you can do with ODBC's SQLBindParameter(), where you specify the C type,
SQL type, precision/scale or length ...
I believe this is important when it comes
- Original Message -
From: Tom Lane t...@sss.pgh.pa.us
To: Bayless Kirtley bk...@cox.net
Cc: John R Pierce pie...@hogranch.com; PostgreSQL
pgsql-general@postgresql.org; scottmarl...@gmail.com
Sent: Monday, May 18, 2009 3:22 PM
Subject: Re: [GENERAL] Daylight saving time question
you should use something similar to 'merge sort'
but only if your input is sorted (m_bx expects this)
In my case, order is not guaranteed, and as the result needs to match
the order of the input, it seems that using some exhaustive tail
recursive method is the way to go. (By that I mean a
Thank you guys for your input, I really appreciate.
It's a while I haven't posted on this list and be happy to get fast and
accurate answers...
As I wrote in a previous mail, I maintain a database driver for our 4GL runtime
system, allowing Informix 4gl applications to use PostgreSQL.
In this
On Tue, May 19, 2009 at 01:45:17PM +0200, Thomas Guettler wrote:
how can you get N numbers (without holes) from a sequence?
alter sequence XXX increment by 1000;
select nextval('XXX');
alter sequence XXX increment by 1;
Best regards,
depesz
--
Linkedin: http://www.linkedin.com/in/depesz /
Joshua Berry escribió:
Please forgive the lack of grace. I'd love tips on how to improve this!
Tip: follow Pavel's suggestion.
--
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
--
Sent via
Pavel Stehule escribió:
postgres=# create or replace function xx(anyarray, anyarray) returns
bool[] as $$
select array(select (select x = any(select y from unnest($2) g2(y)))
from unnest($1) g(x))
$$ language sql immutable;
CREATE FUNCTION
There ain't no unnest() function in 8.3 ...
--
Sam Mason s...@samason.me.uk writes:
I don't really know 8.4, but I believe you're saying here that you
explicitly want the values to be of basic INTERVAL type here, i.e. not
INTERVAL DAY TO HOUR for parameter 3.
Right, you can get the equivalent behavior from psql thus:
regression=# select
On Tue, May 19, 2009 at 03:32:16PM +0200, Thomas Guettler wrote:
If other processes run nextval() between increment by 1000 and increment
by 1,
they leave big holes in the sequence.
yes. I know. there is no other way.
if you want 1000 ids, but they don't have to be consequtive, you can
2009/5/19 Alvaro Herrera alvhe...@commandprompt.com:
Pavel Stehule escribió:
postgres=# create or replace function xx(anyarray, anyarray) returns
bool[] as $$
select array(select (select x = any(select y from unnest($2) g2(y)))
from unnest($1) g(x))
$$ language sql immutable;
CREATE
On Mon, May 18, 2009 at 03:13:56PM -0400, Joshua Berry wrote:
Hello all,
Is there an easy and efficient way to return a boolean false for a query
that returns no result, and true for one that does return a result?
Currently we select the result into a temp table.
SELECT INTO temp_table id
Tom Lane wrote:
Sam Mason s...@samason.me.uk writes:
I don't really know 8.4, but I believe you're saying here that you
explicitly want the values to be of basic INTERVAL type here, i.e. not
INTERVAL DAY TO HOUR for parameter 3.
Right, you can get the equivalent behavior from psql thus:
Hello,
I wrote a small
program in cpp which uses pqxx API.The file that i included are
#include pqxx/connection
#include pqxx/transaction
After compiling i got the following error :
prog29.cpp:5:21: pqxx/connection: No such file or
Thomas Guettler írta:
Hi,
how can you get N numbers (without holes) from a sequence?
Thomas
# create sequence tmp_seq cache 1000;
CREATE SEQUENCE
From the same client:
# select nextval('tmp_seq');
nextval
-
1
(1 sor)
# select nextval('tmp_seq');
nextval
-
Hi list,
I have (what I thought was) a relatively simple problem, but my
knowledge of sql is just not good enough to get this done:
I have a table which is basically a number of individuals with both
their origin and destination as columns (see Table 1). In this case,
origins and
Carson Farmer wrote:
Hi list,
I have (what I thought was) a relatively simple problem, but my
knowledge of sql is just not good enough to get this done:
I have a table which is basically a number of individuals with both
their origin and destination as columns (see Table 1). In this case,
On Tue, May 19, 2009 at 9:32 AM, Thomas Guettler h...@tbz-pariv.de wrote:
hubert depesz lubaczewski schrieb:
On Tue, May 19, 2009 at 01:45:17PM +0200, Thomas Guettler wrote:
how can you get N numbers (without holes) from a sequence?
alter sequence XXX increment by 1000;
select
On Mon, May 18, 2009 at 3:13 PM, Joshua Berry yob...@gmail.com wrote:
Hello all,
Is there an easy and efficient way to return a boolean false for a query
that returns no result, and true for one that does return a result?
Probably the best general approach is to:
select count(*) = 1 from
(
Hi,
Consider the following scenario:
CREATE FUNCTION test(name)
select into cnt count(id) from items where owner = name;
--suppose someone inserts during this point? then next check will succeed
when it should not.
if (cnt 10) then
insert into items values ('new item', name);
end;
end;
I have a requirement to select the effective exchange rate for a
number of currencies as of a specific date and time. The rates may
come from several sources for the same currency. For some
currencies the rate may be set infrequently. I have come close to
getting this to work but cannot seem to
Shakil Shaikh wrote:
Hi,
Consider the following scenario:
CREATE FUNCTION test(name)
select into cnt count(id) from items where owner = name;
--suppose someone inserts during this point? then next check will
succeed when it should not.
if (cnt 10) then
insert into items values ('new
aravind chandu wrote:
#include pqxx/connection
#include pqxx/transaction
prog29.cpp:5:21: pqxx/connection: No such file or directory
prog29.cpp:5:21: pqxx/transaction: No such file or directory
I checked the include
directory and all these files
On Tue, May 19, 2009 at 12:37, aravind chandu avin_frie...@yahoo.com wrote:
Hello,
I wrote a small program in cpp which uses pqxx API.The file that i
included are
#include pqxx/connection
#include pqxx/transaction
After compiling i got the following
Glen Parker wrote:
Richard Huxton wrote:
3. Check after an insert on the items table and raise an exception if
there are 11+ items.
I'd be tempted by #3 - assuming most of the time you won't breach this
limit.
#3 won't work unless the other transactions have all committed by the
time
Richard Huxton wrote:
Shakil Shaikh wrote:
Hi,
Consider the following scenario:
CREATE FUNCTION test(name)
select into cnt count(id) from items where owner = name;
--suppose someone inserts during this point? then next check will
succeed when it should not.
if (cnt 10) then
insert into
Richard Huxton wrote:
Glen Parker wrote:
Richard Huxton wrote:
#3 won't work unless the other transactions have all committed by the
time you do the check. It is guaranteed to fail at some point.
If it's in an AFTER INSERT/UPDATE trigger then whatever transaction
takes you beyond 10 rows
Andy Colson wrote:
Carson Farmer wrote:
Hi list,
I have (what I thought was) a relatively simple problem, but my
knowledge of sql is just not good enough to get this done:
I have a table which is basically a number of individuals with both
their origin and destination as columns (see Table
Carson Farmer wrote:
Hi list,
I have (what I thought was) a relatively simple problem, but my
knowledge of sql is just not good enough to get this done:
I have a table which is basically a number of individuals with both
their origin and destination as columns (see Table 1). In this case,
James B. Byrne wrote:
I am perplexed why I cannot select a column from the table without
having to include it in the GROUP BY clause as well.
Any help is welcomed.
Group by is saying I want only one row returned for each distinct value
in this column
so a food table like this:
name |
James B. Byrne wrote:
I have a requirement to select the effective exchange rate for a
number of currencies as of a specific date and time. The rates may
come from several sources for the same currency. For some
currencies the rate may be set infrequently. I have come close to
getting this to
On Tue, May 19, 2009 16:41, Andy Colson wrote:
If your query above is getting you mostly what you want, just use it
as a derived table.
I lack the experience to understand what this means.
If, as you suggest, I use a subquery as the expression to the main
SELECT and for it I use the syntax
On Tue, May 19, 2009 17:02, Andy Colson wrote:
so: select max(name), type from food group by type
works cuz we only get one name (the max name) back for each type.
or: select name, type from food group by type, name
which in our example is kinda pointless, but still, give us the
distinct
Hi All,
I have one question.
Which of the recent versions of postgresql support direct I/O?
Thanks,
Dipali Pal
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
James B. Byrne wrote:
On Tue, May 19, 2009 16:41, Andy Colson wrote:
If your query above is getting you mostly what you want, just use it
as a derived table.
I lack the experience to understand what this means.
If, as you suggest, I use a subquery as the expression to the main
SELECT and
James B. Byrne wrote:
On Tue, May 19, 2009 17:02, Andy Colson wrote:
so: select max(name), type from food group by type
works cuz we only get one name (the max name) back for each type.
or: select name, type from food group by type, name
which in our example is kinda pointless, but still,
Andy Colson wrote:
Carson Farmer wrote:
Hi list,
I have (what I thought was) a relatively simple problem, but my
knowledge of sql is just not good enough to get this done:
I have a table which is basically a number of individuals with both
their origin and destination as columns (see Table
Pal, Dipali (UMKC-Student) wrote:
Hi All,
I have one question.
Which of the recent versions of postgresql support direct I/O?
that would be, none.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
James B. Byrne wrote:
I have a requirement to select the effective exchange rate for a
number of currencies as of a specific date and time. The rates may
come from several sources for the same currency. For some
currencies the rate may be set infrequently. I have come close to
getting this to
Andy Colson wrote:
James B. Byrne wrote:
I have a requirement to select the effective exchange rate for a
number of currencies as of a specific date and time. The rates may
come from several sources for the same currency. For some
currencies the rate may be set infrequently. I have come
I'm attempting to install 8.2x postgresql, and configure fails to find
the realine libraries.
Here's my configure:
./configure --with-includes=/usr/local/include:/usr/local/include/
readline --with-libraries=/usr/local/lib
The readline.h file is in the include directory and the lib has the
On Tue, May 19, 2009 at 6:34 PM, Jeffrey Trimble jtrim...@cc.ysu.eduwrote:
I'm attempting to install 8.2x postgresql, and configure fails to find the
realine libraries.
Here's my configure:
./configure --with-includes=/usr/local/include:/usr/local/include/readline
Merlin Moncure mmonc...@gmail.com writes:
On Mon, May 18, 2009 at 3:13 PM, Joshua Berry yob...@gmail.com wrote:
Is there an easy and efficient way to return a boolean false for a query
that returns no result, and true for one that does return a result?
Probably the best general approach is
Thanks, but that didn't work. Here's the complete error result from
that:
checking for -lreadline... no
checking for -ledit... no
configure: error: readline library not found
If you have readline already installed, see config.log for details on
the
failure. It is possible the compiler
On Tue, May 19, 2009 at 7:08 PM, Jeffrey Trimble jtrim...@cc.ysu.eduwrote:
Thanks, but that didn't work. Here's the complete error result from that:
checking for -lreadline... no
checking for -ledit... no
configure: error: readline library not found
If you have readline already installed,
On May 19, 2009, at 11:29 PM, Andy Colson wrote:
I'm not sure what this will do:
HAVING
COUNT(fxr.currency_code_quote) = 1
The only time I have ever used HAVING is like:
select name from something group by name having count(*) 1
to find duplicate name's.
That will leave out all
Hi.. thanks.
Here's a printout from my find command:
/usr/local/include/readline
/usr/local/include/readline/readline.h
And the plot thickens. Here is where it errors out and then loops
endlessly until it quits:
(There are symbolic links from libreadline.so to libreadline.so.6.0)
On Tue, May 19, 2009 at 7:23 PM, Jeffrey Trimble jtrim...@cc.ysu.eduwrote:
Hi.. thanks.
Here's a printout from my find command:
/usr/local/include/readline
/usr/local/include/readline/readline.h
And the plot thickens. Here is where it errors out and then loops endlessly
until it quits:
Hi...Here's the output of that one:
ldd /usr/local/lib/libreadline.so
linux-gate.so.1 = (0xe000)
libc.so.6 = /lib/libc.so.6 (0xb7e9c000)
/lib/ld-linux.so.2 (0xb8055000)
ddev:/ #
There are two versions of readline. REadline 5.2 and Readline 6.0
Readline 5.2 was
On Tue, May 19, 2009 at 7:32 PM, Jeffrey Trimble jtrim...@cc.ysu.eduwrote:
Hi...Here's the output of that one:
ldd /usr/local/lib/libreadline.so
linux-gate.so.1 = (0xe000)
libc.so.6 = /lib/libc.so.6 (0xb7e9c000)
/lib/ld-linux.so.2 (0xb8055000)
ddev:/ #
Not really sure what the
On Tue, 19 May 2009, Pal, Dipali (UMKC-Student) wrote:
Which of the recent versions of postgresql support direct I/O?
As of 8.1 PostgreSQL does direct I/O for writes to the WAL if you've
configured wal_sync_method={open_datasync,open_sync} on supported
platforms. I know Linux works but
--
From: Richard Huxton d...@archonet.com
Of course, if you're going to have a separate table then you might as well
store the count in there and actually update it on every
insert/update/delete. Assuming you might find the count of some use
Merlin Moncure wrote:
On Tue, May 19, 2009 at 9:32 AM, Thomas Guettler h...@tbz-pariv.de wrote:
hubert depesz lubaczewski schrieb:
On Tue, May 19, 2009 at 01:45:17PM +0200, Thomas Guettler wrote:
how can you get N numbers (without holes) from a sequence?
alter sequence XXX increment by 1000;
Jeffrey Trimble jtrim...@cc.ysu.edu writes:
configure:6337: checking for -lreadline
configure:6364: gcc -o conftest -I/usr/local/include -I/usr/local/
include/readline -Wall -Wmissing-prototypes -Wpointer-arith -Winline -
Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -
I m new to PostgreSQL so please tell me the drawbacks of is this solution
Your Table 3 should not be a table it should be a array.
CREATE TABLE table3
(
ori_des int[][]
);
for origin area_n and destination area_m if count is k, then ori_des[n][m] = k.
--- On Tue, 19/5/09,
81 matches
Mail list logo
