[GENERAL] A question about plans and multi-key pks

2015-05-01 Thread Steve Rogerson
We have a table: sjr_local1db=# \d cons_refs Table "public.cons_refs" Column | Type | Modifiers ---++-- code | bigint | not null ... ref_type

Re: [GENERAL] How well does PostgreSQL 9.6.1 support unicode?

2016-12-21 Thread Steve Rogerson
On 21/12/16 05:24, Tom Lane wrote: > James Zhou writes: >> - *But their sorting order seems to be undefined. Can anyone comment >> the sorting rules?* > > Well, it would depend on lc_collate, which you have not told us, and > it would also depend on how well your platform's strcoll()

[GENERAL] Unique constraints and indexes.

2016-01-05 Thread Steve Rogerson
Is this a bug? I create a "unique" index, directly but it doesn't add a unique constraint. Add a unique constraint and it adds the index and the constraint. (pg version 9.4.5 on fedora 22, but also occurs in other versions). Functionally I can't see a difference. mydb=# create table test_table ( f

Re: [GENERAL] Unique constraints and indexes.

2016-01-05 Thread Steve Rogerson
On 05/01/16 19:47, Tom Lane wrote: > Steve Rogerson writes: >> Is this a bug? I create a "unique" index, directly but it doesn't add a >> unique >> constraint. Add a unique constraint and it adds the index and the constraint. > > That's operating a

[GENERAL] Postgres and timezones

2016-01-20 Thread Steve Rogerson
Hi, this is wrong: # select to_char('2016-01-20 00:00'::timestamp at time zone 'Europe/Lisbon', 'TZ'); to_char - GMT (1 row) It should be WET, "Western European Time". Is there something I'm doing wrong? Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) T

Re: [GENERAL] Postgres and timezones

2016-01-20 Thread Steve Rogerson
On 20/01/16 12:53, Albe Laurenz wrote: > Steve Rogerson wrote: >> Hi, this is wrong: >> >> # select to_char('2016-01-20 00:00'::timestamp at time zone 'Europe/Lisbon', >> 'TZ'); >> to_char >> - >> GMT >> (

Re: [GENERAL] Postgres and timezones

2016-01-20 Thread Steve Rogerson
On 20/01/16 13:27, Pavel Stehule wrote: > > > > Postgres doesn't store original TZ. It does recalculation to local TZ. If you > need original TZ, you have to store it separetely. > I know and that's what I'm trying to deal with. Given I know the origin TZ - as in Europe/Lisbon I'm trying to de

Re: [GENERAL] Postgres and timezones

2016-01-20 Thread Steve Rogerson
On 20/01/16 15:38, Steve Crawford wrote: > Is this of any use? > > select * from pg_timezone_names where name = 'Europe/Lisbon'; > name | abbrev | utc_offset | is_dst > ---+++ > Europe/Lisbon | WET| 00:00:00 | f > A bit - but what's missing

Re: [GENERAL] Postgres and timezones

2016-01-21 Thread Steve Rogerson
On 20/01/16 19:19, Vik Fearing wrote: > On 01/20/2016 04:24 PM, Steve Rogerson wrote: >>> Postgres doesn't store original TZ. It does recalculation to local TZ. If >>> you >>> need original TZ, you have to store it separetely. >>> >> >> I kn

[GENERAL] setting time zone in a function

2016-04-21 Thread Steve Rogerson
I want the time zone if a function a bit like ... CREATE OR REPLACE FUNCTION short_name (timestamp with time zone, varchar) RETURNS varchar AS $$ DECLARE ... BEGIN SET LOCAL TIME ZONE $2; SELECT to_char($1, 'TZ')::varchar ... END $$ LANGUAGE 'plpgsq

Re: [GENERAL] setting time zone in a function

2016-04-21 Thread Steve Rogerson
On 21/04/16 14:47, Tom Lane wrote: > Steve Rogerson writes: >> I want the time zone if a function a bit like ... >> CREATE OR REPLACE FUNCTION >>short_name (timestamp with time zone, varchar) RETURNS varchar AS $$ >> DECLARE >> ... >>

[GENERAL] Composite fields and the query planner

2013-05-05 Thread Steve Rogerson
I'm seeing a problem with the query planner not doing what's expected, and I think it is because we are using composite fields. Here is a stripped down example. create type type1 as ( part1 varchar, part2 varchar); create table table1 (field1 type1, field2 varchar); create function get_part1(ty

Re: [GENERAL] Composite fields and the query planner

2013-05-07 Thread Steve Rogerson
On 05/05/13 15:06, Tom Lane wrote: Steve Rogerson writes: I'm seeing a problem with the query planner not doing what's expected, and I think it is because we are using composite fields. Here is a stripped down example. I tested this example in HEAD and 9.0.x and didn't see

Re: [GENERAL] Composite fields and the query planner

2013-05-09 Thread Steve Rogerson
On 05/05/13 15:06, Tom Lane wrote: Steve Rogerson writes: I'm seeing a problem with the query planner not doing what's expected, and I think it is because we are using composite fields. Here is a stripped down example. create type type1 as ( part1 varchar, part2 varchar); create ta

[GENERAL] Oddity with time zones.

2017-04-03 Thread Steve Rogerson
# select (now()); now --- 2017-04-03 11:57:09.891043+01 (1 row) sjr_local1db=# select (now() AT TIME ZONE 'UTC'); timezone 2017-04-03 10:57:11.714571 (1 row) sjr_local1db=# select (now() AT TIME ZONE 'UTC') AT TI

[GENERAL] Curious planning decision

2017-08-10 Thread Steve Rogerson
I have a query that behaves in an odd way, specifically it does different things depending on if I use "=" or ">=" : mydb=# explain analyze select count(*) from ctable c where c.date1 >= '2017-08-10' and c.col2 = '637' and c.col3 = 'TEXT3' and c.col4 not in ('TEXT4');