[SQL] ask about epoch query with datetime
Hi All,
my institution wants me to migrate from old postgresql to new
postgresql, but when i test one of the query it failed at query :
select date_part('epoch',datetime('2009-04-20
17:08:01')-datetime('2009-04-20 17:04:24'))::int;
that query runs well in previous postgresql (PostgreSQL 7.1.3). The
error message is ERROR: function datetime("unknown") does not exist
HINT: No function matches the given name and argument types. You may
need to add explicit type casts.
If i read the error message, it means the function has changed or
something. May I know the latest function so my query can run well in
my PostgreSQL 8.1.11 ?
Thanks
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Distinct oddity
Hi there, does this look right? FAKDB=# select count(distinct(f.land)) from firmen f where f.typlist='Redaktion'; count --- 1975 (1 row) FAKDB=# select count(distinct(f.land||'1')) from firmen f where f.typlist='Redaktion'; count --- 4944 (1 row) FAKDB=# select version(); version -- PostgreSQL 8.3.3 on powerpc-apple-darwin, compiled by GCC i686-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc. build 5370) (1 row) Why would the 2 queries give different results? No inserts occurred in the meantime. tia, Maximilian Tyrtania -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] ask about epoch query with datetime
2009/5/7 haries fajar nugroho :
> Hi All,
>
> my institution wants me to migrate from old postgresql to new
> postgresql, but when i test one of the query it failed at query :
> select date_part('epoch',datetime('2009-04-20
> 17:08:01')-datetime('2009-04-20 17:04:24'))::int;
> that query runs well in previous postgresql (PostgreSQL 7.1.3). The
> error message is ERROR: function datetime("unknown") does not exist
> HINT: No function matches the given name and argument types. You may
> need to add explicit type casts.
>
> If i read the error message, it means the function has changed or
> something. May I know the latest function so my query can run well in
> my PostgreSQL 8.1.11 ?
Try something like:
select date_part('epoch','2009-04-20 17:08:01'::TIMESTAMP -
'2009-04-20 17:04:24'::TIMESTAMP)::int;
Ian Barwick
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] select regexp_matches('a a a', '([a-z]) a','g');
Hello,
I wonder if someone has an idea for this problem:
I have a string that contains a serie of chars, separated by single
spaces.
e.g 'a b x n r a b c b'
Having such a string, I d'like to get a list of all predecessors of a
given character.
In the example, the predecessors of b are a,a,c.
If I now have the string 'a a a', the predecessors of 'a' are a,a
I tried to use regexp_matches for this:
select regexp_matches('a a a', '([a-z]) a','g');
=> {"a "} only
As the second parameter of the function matches the first 2 'a',
only the trailing ' a' will be used to seek for further matching...
Cheers,
Marc Mamin
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Distinct oddity
On Thu, May 7, 2009 at 2:21 AM, Maximilian Tyrtania wrote: > Hi there, > > does this look right? > > FAKDB=# select count(distinct(f.land)) from firmen f where > f.typlist='Redaktion'; > count > --- > 1975 > (1 row) > > FAKDB=# select count(distinct(f.land||'1')) from firmen f where > f.typlist='Redaktion'; > count > --- > 4944 > (1 row) Yeah, that does seem odd. Could it be something like nulls in your data set? just guessing really. If you could make a small test case that shows it happening and allows others to reproduce it you're likely to get more bites. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] select regexp_matches('a a a', '([a-z]) a','g');
First: Please don't reply to an existing message to create a new thread.
Your mail client copies the replied-to message ID into the References:
header, and well-implemented mail clients will thread your message under
a now-unrelated thread.
Compose a new message instead.
Marc Mamin wrote:
> I have a string that contains a serie of chars, separated by single
> spaces.
>
> e.g 'a b x n r a b c b'
>
> Having such a string, I d'like to get a list of all predecessors of a
> given character.
> In the example, the predecessors of b are a,a,c.
OK, so wherever `b' occurs, you want the character at index `b -2'.
> select regexp_matches('a a a', '([a-z]) a','g');
> => {"a "} only
The issue is that regular expressions don't like to overlap matches. The
first match consumes _two_ leading `a' characters.
What you need is a zero-width lookahead assertion, available in
Perl-style extended regular expressions. Handily, recent PostgreSQL
versions support these, so you can write:
test=> select regexp_matches( 'a a a', '([a-z]) (?=a)', 'g');
regexp_matches
{a}
{a}
(2 rows)
--
Craig Ringer
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
