[SQL] Database object names and libpq in UTF-8 locale on Windows
Hello, Using PostgreSQL 9.2.1 on Windows, I am facing a strange character set issue with a UTF-8 database. Maybe this is expected, but want to be sure that I am not missing something. On Windows, I have created a database with: ENCODING = 'UTF-8' LC_COLLATE = 'English_United States.1252' LC_CTYPE = 'English_United States.1252' I am using libpq with a simple C program. The program handles UTF-8 strings. While it's not a traditional "UNICODE" Windows application using WCHAR, it should be possible to send and get back UTF-8 data with PostgreSQL. Interacting with the system in the Windows locale is another story, but from a pure C / SQL / libpq point of view, as long as the PostgreSQL client encoding is properly defined to UTF-8, it should work, and it does mainly: - I can use UTF-8 string constants in my queries. - I can pass UTF-8 data to the database with parameterized queries. - I can fetch UTF-8 data from the database. - I can create db object names with UTF-8 characters. But the db object names must be specified with double quotes: When I do not use quoted db object names, I get a strange problem. The table is created, I can use it in my program, I can even use it in the pgAdmin query tool, but in the pgAdmin db browser, there is no table name displayed in the treeview... Further, when selecting schema information from pg_class, I can see that the table exists, but there is nothing displayed in the relname attribute... It appears that the problem disappears when using a "C" collation are char type: ENCODING = 'UTF-8' LC_COLLATE = 'C' LC_CTYPE = 'C' I suspect this has something to do with the fact that non-quoted identifiers are converted to lowercase, and because my LC_CTYPE is English_United States.1252, the conversion to lowercase fails... But: - why does PostgreSQL accept to create the table with invalid UTF-8 characters? - why can I make queries in the query tool with the UTF-8 table name? (note that show client_encoding in the query tool gives me "UNICODE" - is this the same as "UTF-8"?) So is there a bug, or do I have to use a C collation and char type for UTF-8 databases on Windows? I know that UTF-8 is not supported by the Windows C/POSIX library (setlocale, and co). Does it mean that it's not realistic to use UTF-8 encoding for PostgreSQL databases on Windows...? Thanks for reading. Sebastien FLAESCH Four Js Development Tools -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Database object names and libpq in UTF-8 locale on Windows
Sebastien FLAESCH writes: > - I can use UTF-8 string constants in my queries. > - I can pass UTF-8 data to the database with parameterized queries. > - I can fetch UTF-8 data from the database. > - I can create db object names with UTF-8 characters. > But the db object names must be specified with double quotes: > When I do not use quoted db object names, I get a strange problem. > The table is created, I can use it in my program, I can even use it in the > pgAdmin query tool, but in the pgAdmin db browser, there is no table name > displayed in the treeview... That sounds like a pgAdmin bug. You should report it in the pgAdmin mailing lists. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Database object names and libpq in UTF-8 locale on Windows
Tom, I don't think so because the pg_class schema table shows an empty relation name for the table created without double quoted identifier containing UTF-8 chars. Seb On 10/11/2012 04:03 PM, Tom Lane wrote: Sebastien FLAESCH writes: - I can use UTF-8 string constants in my queries. - I can pass UTF-8 data to the database with parameterized queries. - I can fetch UTF-8 data from the database. - I can create db object names with UTF-8 characters. But the db object names must be specified with double quotes: When I do not use quoted db object names, I get a strange problem. The table is created, I can use it in my program, I can even use it in the pgAdmin query tool, but in the pgAdmin db browser, there is no table name displayed in the treeview... That sounds like a pgAdmin bug. You should report it in the pgAdmin mailing lists. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] checking the gaps in intervals
On 2012-10-05, Anton Gavazuk wrote:
> Hi dear community,
>
> Have probably quite simple task but cannot find the solution,
>
> Imagine the table A with 2 columns start and end, data type is date
>
> start end
> 01 dec. 10 dec
> 11 dec. 13 dec
> 17 dec. 19 dec
> .
>
> If I have interval, for example, 12 dec-18 dec, how can I determine
> that the interval cannot be fully covered by values from table A
> because of the gap 14-16 dec? Looking for solution and unfortunately
> nothing has come to the mind yet...
perhaps you can do a with-recursive query ?
create temp table Gavazuk
(id serial primary key, start date ,fin date);
insert into Gavazuk (start,fin)
values ('2012-12-01','2012-12-10')
,('2012-12-11','2012-12-13')
,('2012-12-17','2012-12-19');
-- this version treats ('2012-12-01','2012-12-10') ('2012-12-11','2012-12-13')
-- as contiguous
with recursive a as (
select max (fin) as f from Gavazuk
where ('2012-12-12') between start and fin
union all
select distinct (fin) from gavazuk,a
where a.f+1 between start and fin and start <= '2012-12-12'
)
select max(f) >= '2012-12-18' from a;
-- this version treats ('2012-12-01','2012-12-10') ('2012-12-11','2012-12-13')
-- as non-contiguous
with recursive a as (
select max (fin) as f from Gavazuk
where ('2012-12-12') between start and fin
union all
select distinct (fin) from gavazuk,a
where a.f between start and fin-1 and start <= '2012-12-12'
)
select max(f) >= '2012-12-18' from a;
--
⚂⚃ 100% natural
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Calling the CTE for multiple inputs
On 2012-10-04, air wrote: > I have a CTE that takes top left and bottom right latitude/longitude values > along with a start and end date and it then calculates the amount of user > requests that came from those coordinates per hourly intervals between the > given start and end date. However, I want to execute this query for about > 2600 seperate 4-tuples of lat/lon corner values instead of typing them in > one-by-one. How would I do that? The code is as below: I see that your're using the CTE only to fill-in the nulls in main query group-by is it the same plan for the coordinates (non-overlapping ranges)? how are your coordinates respresented? -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
