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 Maso
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?
>>
>>
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 vie
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
Th
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...
bu
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?
--
Se
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 mi
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
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 FRAC
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 INSE
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:
http://www.postgresql.
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,
tse
On Tue, May 19, 2009 at 12:45 PM, Thomas Guettler 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 (pgsql-general@postgresql.org)
To
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 00
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 next
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 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 the Debian di
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 m
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 numeric
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 an
On Tue, May 19, 2009 at 8:17 AM, Sebastien FLAESCH 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 type,
On Tue, May 19, 2009 at 10:12 AM, Sam Mason 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 to data typ
- Original Message -
From: "Tom Lane"
To: "Bayless Kirtley"
Cc: "John R Pierce" ; "PostgreSQL"
;
Sent: Monday, May 18, 2009 3:22 PM
Subject: Re: [GENERAL] Daylight saving time question
"Bayless Kirtley" writes:
Is this a flaw in the JDBC driver or is that the expected behavior
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 c
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 pgs
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 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 '-12345'::interva
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 :
> 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
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_t
Tom Lane wrote:
Sam Mason 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 '-1
Hello,
I wrote a small
program in cpp which uses pqxx API.The file that i included are
#include
#include
After compiling i got the following error :
prog29.cpp:5:21: pqxx/connection: No such file or directory
prog29.cpp:5:21: p
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 destination
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,
o
On Tue, May 19, 2009 at 9:32 AM, Thomas Guettler 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 nextval('XXX');
On Mon, May 18, 2009 at 3:13 PM, 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?
>
Probably the best general approach is to:
select count(*) = 1 from
(
limit 1
)
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;
W
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 item
aravind chandu wrote:
#include
#include
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 were there
Doubtful. I've neve
On Tue, May 19, 2009 at 12:37, aravind chandu wrote:
> Hello,
>
> I wrote a small program in cpp which uses pqxx API.The file that i
> included are
>
> #include
> #include
>
> After compiling i got the following error :
> prog29.cpp:5:21: pqxx/conne
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 you
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 y
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,
o
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 synta
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
> dis
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 fo
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, give
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:
http://www.postgresql.org/m
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 close
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 wrote:
> 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
>
Be
Merlin Moncure writes:
> On Mon, May 18, 2009 at 3:13 PM, Joshua Berry 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 to:
> select count(*) = 1 from
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 isn't
On Tue, May 19, 2009 at 7:08 PM, Jeffrey Trimble wrote:
> 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 f
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)
configure
On Tue, May 19, 2009 at 7:23 PM, Jeffrey Trimble wrote:
> 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
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 wrote:
> 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 problem is 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 Sola
--
From: "Richard Huxton"
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
somewhere. Set the
Merlin Moncure wrote:
> On Tue, May 19, 2009 at 9:32 AM, Thomas Guettler 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 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 -
> fwrapv -D_GNU_SO
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, Carso
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 FRAC
82 matches
Mail list logo
