Re: Postgres SQL unable to handle Null values for Text datatype

[Lutz Horn]

> org.postgresql.util.PSQLException: ERROR: operator does not exist: character 
> varying = bytea

This has been discussed on Stack Overflow[0]. 

The answer with the highest approval suggests to use coalesce[1]:

```
Select * from A where middle_name = coalesce(?1)
```

Lutz

[0] https://stackoverflow.com/a/54223586
[1] 
https://www.postgresql.org/docs/current/functions-conditional.html#FUNCTIONS-COALESCE-NVL-IFNULL

Re: [Beginner Querstion]Where can I find the news of database?

[Lutz Horn]

> As you can see,I want to get some latest news,opinions of the database,but I 
> don't know where I can get them.

You can subscribe to https://www.postgresql.org/list/pgsql-announce/

Lutz

Re: Select "todays" timestamps in an index friendly way

[Lutz Horn]

On Tue, Oct 23, 2018 at 03:50:14PM +0200, Francisco Olarte wrote:
> It'is not as the problem was stated. Although ts defaulted to now(),
> and it is probably defaulted, nothing prohibits him from inserting
> timestamps in the future.

Yes, this table is only used as an example for the technical question.
In my real use case there are columns like "due_date" which usually
contain future dates inserted by application code.

> the  "timestamps in today" pattern is commonly used in calendaring
> applications, which usually insert appointments in the future and
> recover this way to print "todays schedule".

Exactly. The application must be able to execute queries like "give me
all my tasks due today" without having to use a concrete value for
"today".

Lutz

Re: Select "todays" timestamps in an index friendly way

[Lutz Horn]

Hi Thomas,

On Tue, Oct 23, 2018 at 12:11:55PM +0200, Thomas Kellerer wrote:
> I typically use:
> 
>   where ts >= date '2018-10-23'
> and ts < date '2018-10-23' + 1

But here the date is an explicit value. Francisco reworded my question:

> if your definition of today is 'same value as now() when truncated to
> days'

That's what I am (was, thanks to Francisco) looking for.

Lutz

Re: Select "todays" timestamps in an index friendly way

[Lutz Horn]

Hi Francisco,

On Tue, Oct 23, 2018 at 12:05:17PM +0200, Francisco Olarte wrote:
> 1st remark. Do NOT use closed interval for timestamps. Always use
> half-open or you'll run into problems

Good point, thanks.

> where ts >=  date_trunc('day',now())
> and ts < date_trunc('day',now()+'1 day') as tomorrow;
> 
> IIRC this should use the index

And it does! Thanks!

Lutz

Select "todays" timestamps in an index friendly way

[Lutz Horn]

Hi,

I am looking for a way to select all timestamps that are "today" in an
index friendly way. This select should not depend on the concrete value
of "today".

Given a table

create temporary table t (
  id SERIAL primary key,
  ts timestamp not null default now()
);

with some data

insert into t (ts)
select ts
from generate_series(
  '2018-01-01T00:00:01'::timestamp,
  '2018-12-31T23:59:59'::timestamp,
  '2 minutes')
as ts;

and an index

create index on t (ts, id);

I can of course make an explicit select for `ts` values that are
"today":

select ts, id
  from t
 where ts >= '2018-10-23T00:00:00'::timestamp
   and ts <= '2018-10-23T23:59:59'::timestamp;

This uses an Bitmap Index Scan on `t_ts_id_idx`. Good.

But the where conditions depends on concrete values of "today" which
will not return the intended result if I execute it tomorrow. I will
have to change the where condition. Not good.

I am looking for a way to make the where condition independed of the
date of execution. I can create a function

create function is_today(timestamp) returns boolean as $$
select to_char(now(), '-MM-DD') = to_char($1, '-MM-DD');
$$ language sql;

that converts the timestamps to text. But using this function

select * from t where is_today(ts);

will not benefit from the existing index. A Seq Scan on `t` will be
used. Not good.

Is there a way to have both: be independed of the concrete value of
"today" *and* use the index on the timestamp column?

Lutz

Re: Code of Conduct plan

[Lutz Horn]

Am 05.06.2018 17:26 schrieb Joshua D. Drake:

As one of the people that interacts with external members of the
community more than most, I can tell you that a CoC is something the
wider community wants. I have sat in feedback meetings with hundreds
of people who are potential community members. These people have
ranged in age, gender, sexual orientation and technical capability on
all realms of the spectrum. The majority of them aren't interested if
we do not have a written Code of Conduct.


May I ask what the context of these meetings was? Where where they held? 
For which country or part of the broader community where the 
participants representative?


Regards

Lutz

Re: Code of Conduct plan

[Lutz Horn]

Am 05.06.2018 17:33 schrieb Joshua D. Drake:

Let's remember that we are an International project and let's not
direct particular frustration at any particular set of values. It
would be very easy to start a culture war within this thread alone.


I am not quite sure what you mean by "particular frustration". I think 
that it is obvious that most CoCs are not, for example, developed by 
communities in Africa or Asia. Most are developed in North America and 
Europe with a strong weight in the US. Observing this does not claim 
that the values voiced by the vocal majority are good or bad, only that 
they can be biased.


That's why I support the nottion of making the international character 
of both the project and the board explicit.


Regards

Lutz

Re: Code of Conduct plan

[Lutz Horn]

Am 05.06.2018 17:03 schrieb Chris Travers:

On to the code of conduct committee:

This needs to be explicitly international and ideally people from very
different cultures.  This is the best protection against one small 
group

within one country deciding to push a political agenda via the Code of
Conduct.  I would recommend adding a note here that the committee will 
be
international and culturally diverse, and tasked with keeping the peace 
and

facilitating a productive and collegial environment.


I strongly agree with this.

CoCs discussed in other projects have an inclination towards US view 
points. Maybe the reason for this is that many community members are US 
residents and are having the problems of their society in mind when 
thinking of what a CoC should be. But what is acceptable in the US might 
be unacceptable in other parts of the world and vice versa.


Please procure that the CoC is not a vehicle to propagate US values.

Regards

Lutz