Re: [GENERAL] Optimize Query

[drum.lu...@gmail.com]

Anyone can help with that please?

Cheers

On Thursday, 11 February 2016, drum.lu...@gmail.com 
wrote:

> oh ok!
>
> thanks
>
>
>
> Lucas Possamai
>
> - kinghost.co.nz
> 
> - DigitalOcean 
>
> On 11 February 2016 at 15:41, Melvin Davidson  > wrote:
>
>>
>>
>>
>>> Thank you Melvin.
>>> Sorry but I was unable to see the *n_quote_status = 0*
>>>
>>> Did you use it?
>>>
>>>
>> No, I just revised you "new" query to be more efficient.
>>
>>
>> --
>> *Melvin Davidson*
>> I reserve the right to fantasize.  Whether or not you
>> wish to share my fantasy is entirely up to you.
>>
>
>

-- 


Lucas Possamai

- kinghost.co.nz

- DigitalOcean 

Re: [GENERAL] Test CMake build

[Yury Zhuravlev]

Andy Colson wrote:

cmake and make -j2 fine, but then

You can try again I removed some features from CMake 3.x .

Realy big thanks for testing!
--
Yury Zhuravlev
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Custom conflict handlers

[Nikhil]

Hello,

Is there any documentation with details of how to custom conflict handlers?
Wanted to write update-update conflict handler. Any help is highly
appreciated.

Best Regards,
Nikhil

Re: [GENERAL] Test CMake build

[Andy Colson]

On 02/13/2016 04:33 AM, Yury Zhuravlev wrote:

Andy Colson wrote:

cmake and make -j2 fine, but then

You can try again I removed some features from CMake 3.x .

Realy big thanks for testing!

My pleasure.

Still didn't work.  'make check' seems to make it through make install first, 
but then same sort of error:


andy@mapper:~/projects/postgres_cmake/build$ make check
[  1%] Built target gen_errorcodes
[  3%] Built target port

-- Installing: 
/home/andy/projects/postgres_cmake/build/src/test/regress/tmp_install/tmp/pg99/share/postgresql/extension/timetravel--1.0.sql
-- Installing: 
/home/andy/projects/postgres_cmake/build/src/test/regress/tmp_install/tmp/pg99/share/postgresql/extension/timetravel--unpackaged--1.0.sql
Built target tablespace-setup
CMake Error: cmake version 2.8.12
Usage: /usr/bin/cmake -E [command] [arguments ...]
Available commands:
  chdir dir cmd [args]...   - run command in a given directory
  compare_files file1 file2 - check if file1 is same as file2
  copy file destination - copy file to destination (either file or 
directory)
  copy_directory source destination   - copy directory 'source' content to 
directory 'destination'
  copy_if_different in-file out-file  - copy file if input has changed
  echo [string]...  - displays arguments as text
  echo_append [string]...   - displays arguments as text but no new line
  environment   - display the current environment
  make_directory dir- create a directory
  md5sum file1 [...]- compute md5sum of files
  remove [-f] file1 file2 ... - remove the file(s), use -f to force it
  remove_directory dir  - remove a directory and its contents
  rename oldname newname- rename a file or directory (on one volume)
  tar [cxt][vfz][cvfj] file.tar [file/dir1 file/dir2 ...]
- create or extract a tar or zip archive
  time command [args] ...   - run command and return elapsed time
  touch file- touch a file.
  touch_nocreate file   - touch a file but do not create it.
Available on UNIX only:
  create_symlink old new- create a symbolic link new -> old

make[7]: *** [src/test/regress/CMakeFiles/installcheck_tmp] Error 1
make[6]: *** [src/test/regress/CMakeFiles/installcheck_tmp.dir/all] Error 2
make[5]: *** [src/test/regress/CMakeFiles/installcheck_tmp.dir/rule] Error 2
make[4]: *** [installcheck_tmp] Error 2
make[3]: *** [src/test/regress/CMakeFiles/check] Error 2
make[2]: *** [src/test/regress/CMakeFiles/check.dir/all] Error 2
make[1]: *** [src/test/regress/CMakeFiles/check.dir/rule] Error 2
make: *** [check] Error 2



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Custom conflict handlers

[Nikhil]

This is for system that use BDR (Bi-Directional Replication). BDR
documentation
http://bdr-project.org/docs/next/functions-conflict-handlers.html talks
about postgresql functions. I am looking for samples.

Best Regards,
Nikhil

On Sat, Feb 13, 2016 at 5:31 PM, Nikhil  wrote:

> Hello,
>
> Is there any documentation with details of how to custom conflict
> handlers? Wanted to write update-update conflict handler. Any help is
> highly appreciated.
>
> Best Regards,
> Nikhil
>

Re: [GENERAL] Test CMake build

[Yury Zhuravlev]

Andy Colson wrote:
Still didn't work.  'make check' seems to make it through make 
install first, but then same sort of error:


Now it looks like I can do "make check" only cmake 3.x (I will try to solve 
it later).
But "make installcheck" with DESTDIR should work. I now have no close 
system with CMake 2.8.x .


In order not to clutter up maillist I would prefer to discuss such errors 
on github.


Thanks!

--
Yury Zhuravlev
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Question on how to use to_timestamp()

[Deven Phillips]

I'm trying to convert a series of ISO8601 strings into TIMESTAMPs for use
with a function:

CREATE OR REPLACE FUNCTION v1_nexus_vlan_count(id TEXT, start_time
TIMESTAMP, end_time TIMESTAMP)
RETURNS TEXT AS $$
SELECT jsonb_pretty(jsonb_agg(row_to_json(datapoints))) AS data_array FROM (
SELECT
data->>'timestamp' AS collection_time,
data->'data'->'vlans'->>'available' AS available,
data->'data'->'vlans'->>'total' AS total,
data->'data'->'vlans'->>'used' AS used
FROM
gathered_data
WHERE
data->>'id'=$1 AND
to_timestamp(data->>'timestamp', '-MM-DDTHH24:MI:SSZ')>=$2 AND
to_timestamp(data->>'timetsamp', '-MM-DDTHH24:MI:SSZ')<=$3
ORDER BY
to_timestamp(data->>'timestamp', '-MM-DDTHH24:MI:SSZ')) AS
datapoints $$
LANGUAGE SQL;

The conversions for to_timestamp() seems to be my problem. I keep getting
an error:

# SELECT to_timestamp('2016-01-01T00:00:00Z', '-MM-DDTHH24:MI:SSZ');
ERROR:  invalid value ":0" for "MI"
DETAIL:  Value must be an integer.
Time: 1.016 ms

Could anyone suggest what it is that I might be doing wrong here?

Thanks in advance!!!

Deven

Re: [GENERAL] Question on how to use to_timestamp()

[Adrian Klaver]

On 02/13/2016 07:42 PM, Deven Phillips wrote:
> I'm trying to convert a series of ISO8601 strings into TIMESTAMPs for 
> use with a function:
> 
> CREATE OR REPLACE FUNCTION v1_nexus_vlan_count(id TEXT, start_time 
> TIMESTAMP, end_time TIMESTAMP)
> RETURNS TEXT AS $$
> SELECT jsonb_pretty(jsonb_agg(row_to_json(datapoints))) AS data_array FROM (
> SELECT
>  data->>'timestamp' AS collection_time,
>  data->'data'->'vlans'->>'available' AS available,
>  data->'data'->'vlans'->>'total' AS total,
>  data->'data'->'vlans'->>'used' AS used
> FROM
>  gathered_data
> WHERE
>  data->>'id'=$1 AND
>  to_timestamp(data->>'timestamp', '-MM-DDTHH24:MI:SSZ')>=$2 AND
>  to_timestamp(data->>'timetsamp', '-MM-DDTHH24:MI:SSZ')<=$3
> ORDER BY
>  to_timestamp(data->>'timestamp', '-MM-DDTHH24:MI:SSZ')) AS 
> datapoints $$
> LANGUAGE SQL;
> 
> The conversions for to_timestamp() seems to be my problem. I keep 
> getting an error:
> 
> # SELECT to_timestamp('2016-01-01T00:00:00Z', '-MM-DDTHH24:MI:SSZ');
> 
> ERROR:  invalid value ":0" for "MI"
> DETAIL:  Value must be an integer.
> Time: 1.016 ms
> 
> Could anyone suggest what it is that I might be doing wrong here?

test=>  SELECT to_timestamp('2016-01-01T00:00:00Z', '-MM-DD"T"HH24:MI:SSZ');

  to_timestamp  

 2016-01-01 00:00:00-08

http://www.postgresql.org/docs/9.5/interactive/functions-formatting.html
"Ordinary text is allowed in to_char templates and will be output literally. 
You can put a substring in double quotes to force it to be interpreted as 
literal text even if it contains pattern key words. For example, in '"Hello 
Year "', the  will be replaced by the year data, but the single Y in 
Year will not be. In to_date, to_number, and to_timestamp, double-quoted 
strings skip the number of input characters contained in the string, e.g. "XX" 
skips two input characters."

> 
> Thanks in advance!!!
> 
> Deven


-- 
Adrian Klaver
adrian.kla...@aklaver.com


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Question on how to use to_timestamp()

[Vitaly Burovoy]

On 2/13/16, Deven Phillips  wrote:
> I'm trying to convert a series of ISO8601 strings into TIMESTAMPs for use
> with a function:
>
> CREATE OR REPLACE FUNCTION v1_nexus_vlan_count(id TEXT, start_time
> TIMESTAMP, end_time TIMESTAMP)
> RETURNS TEXT AS $$
> SELECT jsonb_pretty(jsonb_agg(row_to_json(datapoints))) AS data_array FROM
> (
> SELECT
> data->>'timestamp' AS collection_time,
> data->'data'->'vlans'->>'available' AS available,
> data->'data'->'vlans'->>'total' AS total,
> data->'data'->'vlans'->>'used' AS used
> FROM
> gathered_data
> WHERE
> data->>'id'=$1 AND
> to_timestamp(data->>'timestamp', '-MM-DDTHH24:MI:SSZ')>=$2 AND
> to_timestamp(data->>'timetsamp', '-MM-DDTHH24:MI:SSZ')<=$3
> ORDER BY
> to_timestamp(data->>'timestamp', '-MM-DDTHH24:MI:SSZ')) AS
> datapoints $$
> LANGUAGE SQL;
>
> The conversions for to_timestamp() seems to be my problem. I keep getting
> an error:
>
> # SELECT to_timestamp('2016-01-01T00:00:00Z', '-MM-DDTHH24:MI:SSZ');

If your data is already in a correct ISO8601 format, you can use a
direct cast to timestamptz type:

# SELECT '2016-01-01T00:00:00Z'::timestamptz;
  timestamptz

 2016-01-01 00:00:00+00
(1 row)

"to_timestamp" is used for some complex cases:
> to_timestamp and to_date exist to handle input formats
> that cannot be converted by simple casting.

> ERROR:  invalid value ":0" for "MI"
> DETAIL:  Value must be an integer.
> Time: 1.016 ms
>
> Could anyone suggest what it is that I might be doing wrong here?
>
> Thanks in advance!!!
> Deven
>

[1] http://www.postgresql.org/docs/devel/static/functions-formatting.html
-- 
Best regards,
Vitaly Burovoy


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Question on how to use to_timestamp()

[Tom Lane]

Vitaly Burovoy  writes:
> On 2/13/16, Deven Phillips  wrote:
>> I'm trying to convert a series of ISO8601 strings into TIMESTAMPs for use
>> with a function: ...

> If your data is already in a correct ISO8601 format, you can use a
> direct cast to timestamptz type:

Yeah.  95% of the time, the answer to "how to use to_timestamp()" is
"don't".  The native input converter for the date/timestamp/timestamptz
data types is perfectly capable of parsing most common date formats,
with a lot less muss and fuss than to_timestamp.  At worst you might have
to give it a hint about DMY vs. MDY field ordering via the DateStyle
setting.  If your input is YMD order then you don't have to worry about
that at all.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Question on how to use to_timestamp()

[Deven Phillips]

Thanks all!
On Feb 13, 2016 11:06 PM, "Tom Lane"  wrote:

> Vitaly Burovoy  writes:
> > On 2/13/16, Deven Phillips  wrote:
> >> I'm trying to convert a series of ISO8601 strings into TIMESTAMPs for
> use
> >> with a function: ...
>
> > If your data is already in a correct ISO8601 format, you can use a
> > direct cast to timestamptz type:
>
> Yeah.  95% of the time, the answer to "how to use to_timestamp()" is
> "don't".  The native input converter for the date/timestamp/timestamptz
> data types is perfectly capable of parsing most common date formats,
> with a lot less muss and fuss than to_timestamp.  At worst you might have
> to give it a hint about DMY vs. MDY field ordering via the DateStyle
> setting.  If your input is YMD order then you don't have to worry about
> that at all.
>
> regards, tom lane
>