Re: Adding time to DATE type

Can someone give me a TODO summary for this issue?

[ Charset ISO-8859-1 unsupported, converting... ]

On Fri, 17 Mar 2000, Bruce Momjian wrote:

test=> SELECT date('1/1/1992') + timespan('1 year');

If I may point something out here, the correct syntax for this in SQL92 is

SELECT DATE '1/1/1992' + INTERVAL '1 year'

(Ignoring the fact that neither the date nor the interval strings have the
correct format.)

This converts to a cast in PostgreSQL, which is fine, but the standard
makes a semantic distinction:

CAST('2000-02-29' AS DATE)

converts a character literal to date

DATE '2000-02-29'

*is* a date literal. Furthermore, just

'2000-02-29'

is not a date literal.

I've been doing some lobbying to get rid of the "unknown" type because SQL
is perfectly clear about what "quote-stuff-quote" means (character type)
and in absence of any evidence to the contrary (such as a function only
taking date arguments, inserting it into a date field) it should be
treated as such. That will get rid of such embarrassments as

SELECT 'a' LIKE 'a' -- try it

Tom believes that this will create a pain for the odd data type crowd but
I don't think that this is so (or at least has to be so) whereas the
current behavior creates a pain for the normal data type crowd.

Just my ideas.

--
Peter Eisentraut Sernanders v?g 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Was this resolved?

Yeah, I think it's working fairly well now. The current code first
tries to look up an actual function matching the name + arguments,
and only if that fails does it try to interpret the construct as a
binary-compatible type coercion.

regards, tom lane

Bruce Momjian writes:

Can someone give me a TODO summary for this issue?

* make 'text' constants default to text type (not unknown)

(I think not everyone's completely convinced on this issue, but I don't
recall anyone being firmly opposed to it.)

* add SQL interval syntax

On Fri, 17 Mar 2000, Bruce Momjian wrote:

test=> SELECT date('1/1/1992') + timespan('1 year');

If I may point something out here, the correct syntax for this in SQL92 is

SELECT DATE '1/1/1992' + INTERVAL '1 year'

(Ignoring the fact that neither the date nor the interval strings have the
correct format.)

This converts to a cast in PostgreSQL, which is fine, but the standard
makes a semantic distinction:

CAST('2000-02-29' AS DATE)

converts a character literal to date

DATE '2000-02-29'

*is* a date literal. Furthermore, just

'2000-02-29'

is not a date literal.

I've been doing some lobbying to get rid of the "unknown" type because SQL
is perfectly clear about what "quote-stuff-quote" means (character type)
and in absence of any evidence to the contrary (such as a function only
taking date arguments, inserting it into a date field) it should be
treated as such. That will get rid of such embarrassments as

SELECT 'a' LIKE 'a' -- try it

Tom believes that this will create a pain for the odd data type crowd but
I don't think that this is so (or at least has to be so) whereas the
current behavior creates a pain for the normal data type crowd.

Just my ideas.

--
Peter Eisentraut Sernanders v?g 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden

--
Peter Eisentraut Sernanders v�g 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden

[ Charset ISO-8859-1 unsupported, converting... ]

Bruce Momjian writes:

Can someone give me a TODO summary for this issue?

* make 'text' constants default to text type (not unknown)

(I think not everyone's completely convinced on this issue, but I don't
recall anyone being firmly opposed to it.)

I don't know but I know it came up in the last month. Something about
character strings not being considered TEXT, and they had to be cast to
TEXT to be used.

* add SQL interval syntax

These must be your own items. I don't see them on the main TODO list.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

On Sun, 11 Jun 2000, Bruce Momjian wrote:

Can someone give me a TODO summary for this issue?

* make 'text' constants default to text type (not unknown)

(I think not everyone's completely convinced on this issue, but I don't
recall anyone being firmly opposed to it.)

I don't know but I know it came up in the last month. Something about
character strings not being considered TEXT, and they had to be cast to
TEXT to be used.

* add SQL interval syntax

These must be your own items. I don't see them on the main TODO list.

?? You asked for a TODO summary, and these are the things that would need
TO be DOne in order to address the issue originally at hand.

--
Peter Eisentraut Sernanders v�g 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden

Peter Eisentraut <peter_e@gmx.net> writes:

Bruce Momjian writes:

Can someone give me a TODO summary for this issue?

* make 'text' constants default to text type (not unknown)

(I think not everyone's completely convinced on this issue, but I don't
recall anyone being firmly opposed to it.)

It would be a mistake to eliminate the distinction between unknown and
text. See for example my just-posted response to John Cochran on
pgsql-general about why 'BOULEVARD'::text behaves differently from
'BOULEVARD'::char. If string literals are immediately assigned type
text then we will have serious problems with char(n) fields.

I think it's fine to assign string literals a type of 'unknown'
initially. What we need to do is add a phase of type resolution that
considers treating them as text, but only after the existing logic fails
to deduce a type.

(BTW it might be better to treat string literals as defaulting to char(n)
instead of text, allowing the normal promotion rules to replace char(n)
with text if necessary. Not sure if that would make things more or less
confusing for operations that intermix fixed- and variable-width char
types.)

regards, tom lane

On Mon, 12 Jun 2000, Tom Lane wrote:

* make 'text' constants default to text type (not unknown)

I think it's fine to assign string literals a type of 'unknown'
initially. What we need to do is add a phase of type resolution that
considers treating them as text, but only after the existing logic fails
to deduce a type.

Hence "default to"

--
Peter Eisentraut Sernanders v�g 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden

Oh, OK.

[ Charset ISO-8859-1 unsupported, converting... ]

On Sun, 11 Jun 2000, Bruce Momjian wrote:

Can someone give me a TODO summary for this issue?

* make 'text' constants default to text type (not unknown)

(I think not everyone's completely convinced on this issue, but I don't
recall anyone being firmly opposed to it.)

I don't know but I know it came up in the last month. Something about
character strings not being considered TEXT, and they had to be cast to
TEXT to be used.

* add SQL interval syntax

These must be your own items. I don't see them on the main TODO list.

?? You asked for a TODO summary, and these are the things that would need
TO be DOne in order to address the issue originally at hand.

--
Peter Eisentraut Sernanders v?g 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Is this something worth addressing?

Peter Eisentraut <peter_e@gmx.net> writes:

Bruce Momjian writes:

Can someone give me a TODO summary for this issue?

* make 'text' constants default to text type (not unknown)

(I think not everyone's completely convinced on this issue, but I don't
recall anyone being firmly opposed to it.)

It would be a mistake to eliminate the distinction between unknown and
text. See for example my just-posted response to John Cochran on
pgsql-general about why 'BOULEVARD'::text behaves differently from
'BOULEVARD'::char. If string literals are immediately assigned type
text then we will have serious problems with char(n) fields.

I think it's fine to assign string literals a type of 'unknown'
initially. What we need to do is add a phase of type resolution that
considers treating them as text, but only after the existing logic fails
to deduce a type.

(BTW it might be better to treat string literals as defaulting to char(n)
instead of text, allowing the normal promotion rules to replace char(n)
with text if necessary. Not sure if that would make things more or less
confusing for operations that intermix fixed- and variable-width char
types.)

regards, tom lane

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Is this something worth addressing?

Yes, but not when we're already overdue for beta. We've been around
on the question of type promotion rules several times, and no one has
yet put forward a solution that everyone else liked. I don't expect
to see a usable solution both proposed and implemented in the next
three weeks...

regards, tom lane