[SQL] Common table expression - parsing questions

[the6campbells]

Couple of questions:

1. Why does Postgres not throw a parsing error during sqlPrepare for this
statement vs at sqlExecute

with t_cte ( c1, ctr ) as (
select 1,0 from tversion union
select 2,0 from tversion union all
select c1, ctr + 1 from t_cte where c1=1 and ctr < 5 union all
select c1, ctr + 1 from t_cte where c1=2 and ctr < 5)
select c1, ctr from t_cte

2. Do you intend to remove the requirement to include the recursive keyword
- as other vendors allow

3. Is it a documented restriction that you can only have one reference to
the CTE .. see above example which fails while this modified version works.
The former
works in other vendors.

with recursive t_cte ( c1, ctr ) as (
select 1,0 from tversion union
select 2,0 from tversion union all
select c1, ctr + 1 from t_cte where c1=1 and ctr < 5 )
select c1, ctr from t_cte

Re: [SQL] Common table expression - parsing questions

[the6campbells]

the db2 family does not

On Sun, Oct 4, 2009 at 2:10 AM, Thomas Kellerer  wrote:

> the6campbells wrote on 29.09.2009 04:54:
>
>> 2. Do you intend to remove the requirement to include the recursive
>> keyword - as other vendors allow
>>
>
> The standard *requires* the keyword.
> As far as I can tell there are two DBMS that require it (Postgres,
> Firebird) and two that don't (SQL Server and Oracle with the newest release)
>
> Thomas
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

[SQL] question about timestamp with tz

[the6campbells]

Question.. is there a way that I can get Postgres to return the tz as
supplied on the insert statement

insert into TTSTZ(RNUM, CTSTZ) values ( 0, null);
insert into TTSTZ(RNUM, CTSTZ) values ( 1, timestamp with time zone
'2000-12-31 12:00:00.0-05:00');
insert into TTSTZ(RNUM, CTSTZ) values ( 2, timestamp with time zone
'2000-12-31 12:00:00+00:00');
insert into TTSTZ(RNUM, CTSTZ) values ( 3, timestamp with time zone
'2000-12-31 12:00:00.0+05:00');

select rnum, CTSTZ

0;""
1;"2000-12-31 12:00:00-05"
2;"2000-12-31 07:00:00-05"
3;"2000-12-31 02:00:00-05"

select rnum, extract (hour from ctstz), extract (timezone_hour from ctstz)
from ttstz

0;;
1;12;-5
2;7;-5
3;2;-5

[SQL] date + interval year - why is the return type convered to a timestamp?

[the6campbells]

Why is Postgres returning a timestamp instead of the expected date data type
for the first expression (the second returns a date)?

In other words, is this a known bug or is it design intent. The manuals did
not seem to appear to say this is intentional behaviour but is not SQL
standard.

select date '2001-03-30' - interval '1' year, date '2001-03-30' - integer
'365' from tversion

[SQL] problems with changing the case of turkish characters

[the6campbells]

Just want to clarify if there is something I've overlooked or if this is a
known issue in PG 8.4 and 8.3


CREATE DATABASE test
  WITH OWNER = postgres
   ENCODING = 'UTF8'
   LC_COLLATE = 'English, United States, UTF-8'
   LC_CTYPE = 'English, United States, UTF-8'


select ('İsteği') from tversion
"İsteği" was expecting "isteği"

select upper('iışğüçö') from tversion
"IıŞĞÜÇÖ"

select lower ('İIŞĞÜÇÖ') from tversion
"İişğüçö"

Re: [SQL] problems with changing the case of turkish characters

[the6campbells]

so where would I define something akin to what I can do in DB2 LUW where
collate using system means to sort by the codeset. ie. without english,
united states in LC_COLLATE.

USING CODESET UTF-8 TERRITORY US COLLATE USING SYSTEM




On Mon, Dec 7, 2009 at 12:28 PM, Tom Lane  wrote:

> the6campbells  writes:
> > Just want to clarify if there is something I've overlooked or if this is
> a
> > known issue in PG 8.4 and 8.3
>
> > CREATE DATABASE test
> >   WITH OWNER = postgres
> >ENCODING = 'UTF8'
> >LC_COLLATE = 'English, United States, UTF-8'
> >LC_CTYPE = 'English, United States, UTF-8'
>
> Not sure why you'd be expecting an English locale to follow Turkish
> case-changing rules.
>
>regards, tom lane
>

Re: [SQL] problems with changing the case of turkish characters

[the6campbells]

to clarify. just trying to ensure I understand what PG 8.4 and 8.3 provide
where you may have data from alternate languages where ideally you'd like
them in the same table in the same database etc.

create table T ( c1 char( ...), c2 char (...) ... ) where c1 may contain
thai, c2 korean, c3 turkish etc names
vs
create table TKO (c1 char(...)), create table TTH (c1 char (...))
vs
different databases etc



On Mon, Dec 7, 2009 at 4:20 PM, the6campbells wrote:

> so where would I define something akin to what I can do in DB2 LUW where
> collate using system means to sort by the codeset. ie. without english,
> united states in LC_COLLATE.
>
> USING CODESET UTF-8 TERRITORY US COLLATE USING SYSTEM
>
>
>
>
> On Mon, Dec 7, 2009 at 12:28 PM, Tom Lane  wrote:
>
>> the6campbells  writes:
>> > Just want to clarify if there is something I've overlooked or if this is
>> a
>> > known issue in PG 8.4 and 8.3
>>
>> > CREATE DATABASE test
>> >   WITH OWNER = postgres
>> >ENCODING = 'UTF8'
>> >LC_COLLATE = 'English, United States, UTF-8'
>> >LC_CTYPE = 'English, United States, UTF-8'
>>
>> Not sure why you'd be expecting an English locale to follow Turkish
>> case-changing rules.
>>
>>regards, tom lane
>>
>
>

[SQL] clarification about ARRAY constructor implementation

[the6campbells]

consider the following

create table TARRBINT ( RNUM integer  not null , CARRBINT bigint array[5] )
;

Can someone clarify why Postgres does not like examples 2, 6 and 7

1.insert into TARRBINT(RNUM, CARRBINT) values ( 0, null);
2.insert into TARRBINT(RNUM, CARRBINT) values ( 0, ARRAY[]);
3.insert into TARRBINT(RNUM, CARRBINT) values ( 0, ARRAY[]::integer[]);
4.insert into TARRBINT(RNUM, CARRBINT) values ( 0, ARRAY[null,1]);
5.insert into TARRBINT(RNUM, CARRBINT) values ( 0, ARRAY[1, null]);
6.insert into TARRBINT(RNUM, CARRBINT) values ( 0, ARRAY[null]);
7.insert into TARRBINT(RNUM, CARRBINT) values ( 0, ARRAY[null,null]);

Similarly, why does it seem to force casting expressions with other
instances of literal values used in the array constructor. Is this due to
Postgres treating some literal values to be a 'lob' type and thus
concluding that it cannot be used in the context of array constructor?

For example, this will work

create table TARRCHAR ( RNUM integer  not null , CARRCHAR char(72)
array[5]   ) ;
insert into TARRCHAR(RNUM, CARRCHAR) values ( 1, ARRAY [''])

But scenarios like this will not

create table TXML ( RNUM integer  not null , CXML xml   ) ;
insert into TXML(RNUM, CXML) values ( 1, '');

create table TARRXML ( RNUM integer  not null , CARRXML xml array[5]   ) ;
insert into TARRXML(RNUM, CARRXML) values ( 1, ARRAY ['']);

ERROR:  column "carrxml" is of type xml[] but expression is of type text[]
LINE 1: insert into TARRXML(RNUM, CARRXML) values ( 1, ARRAY ['