buriy.com »Posts

08/20: Как достать первые символы из utf8-таблицы

Categories: code, postgresql

Posted by: buriy

Задача, которая встала передо мной на этот раз — немного необычна.
Требуется: достать из таблицы БД postgresql все уникальные первые символы. Алфавитный справочник составляю.
Проблема: postgres работает в режиме sql-ascii, символы могут занимать разное число байт.
Проблема2: postgres не имеет функции конвертации символов, а substr работает с байтами.

Решение:
continue reading

Sidebar

SELECT "django_session"."session_key",
"django_session"."session_data",
"django_session"."expire_date"
FROM "django_session"
WHERE ("django_session"."session_key" = b8d83b3f6a88855830698247d30d6395
AND "django_session"."expire_date" > 2010-03-11 17:50:01.193513 )

SELECT "blog_post"."id"

FROM "blog_post",
"tagged_item"

WHERE "tagged_item".content_type_id = 35

AND "tagged_item".tag_id IN (13)

AND "blog_post"."id" = "tagged_item".object_id
GROUP BY "blog_post"."id"

SELECT COUNT(*)
FROM "blog_post"
WHERE ("blog_post"."site_id" = 1
AND "blog_post"."is_draft" = False
AND "blog_post"."date" <= 2010-03-11 17:50:01.197929
AND "blog_post"."id" IN (6))

SELECT (SELECT COUNT(*)
FROM comment_nodes,
django_content_type
WHERE comment_nodes.content_type_id = django_content_type.id
AND django_content_type.model = 'post'
AND comment_nodes.object_id = blog_post.id
AND comment_nodes.approved) AS "approved_comments_count",
(SELECT COUNT(*)
FROM watchlist_subscription,
django_content_type
WHERE watchlist_subscription.content_type_id = django_content_type.id
AND django_content_type.model = 'post'
AND watchlist_subscription.object_id = blog_post.id) AS "watchlist_count",
(SELECT COUNT(*)
FROM pingback,
django_content_type
WHERE pingback.content_type_id = django_content_type.id
AND django_content_type.model = 'post'
AND pingback.object_id = blog_post.id) AS "pingback_count",
"blog_post"."id",
"blog_post"."site_id",
"blog_post"."author_id",
"blog_post"."name",
"blog_post"."slug",
"blog_post"."text",
"blog_post"."render_method",
"blog_post"."html",
"blog_post"."date",
"blog_post"."upd_date",
"blog_post"."is_draft",
"blog_post"."enable_comments",
"blog_post"."tags"
FROM "blog_post"
WHERE ("blog_post"."site_id" = 1
AND "blog_post"."is_draft" = False
AND "blog_post"."date" <= 2010-03-11 17:50:01.197929
AND "blog_post"."id" IN (6))
ORDER BY "blog_post"."date" DESC LIMIT 1

SELECT "auth_user"."id",
"auth_user"."username",
"auth_user"."first_name",
"auth_user"."last_name",
"auth_user"."email",
"auth_user"."password",
"auth_user"."is_staff",
"auth_user"."is_active",
"auth_user"."is_superuser",
"auth_user"."last_login",
"auth_user"."date_joined",
"auth_user"."site",
"auth_user"."email_new"
FROM "auth_user"
WHERE "auth_user"."id" = 1

SELECT "comment_nodes"."id",
"comment_nodes"."user_id",
"comment_nodes"."pub_date",
"comment_nodes"."upd_date",
"comment_nodes"."body",
"comment_nodes"."body_html",
"comment_nodes"."reply_to_id",
"comment_nodes"."approved",
"comment_nodes"."content_type_id",
"comment_nodes"."object_id",
"comment_nodes"."lft",
"comment_nodes"."rght",
"auth_user"."id",
"auth_user"."username",
"auth_user"."first_name",
"auth_user"."last_name",
"auth_user"."email",
"auth_user"."password",
"auth_user"."is_staff",
"auth_user"."is_active",
"auth_user"."is_superuser",
"auth_user"."last_login",
"auth_user"."date_joined",
"auth_user"."site",
"auth_user"."email_new",
"django_content_type"."id",
"django_content_type"."name",
"django_content_type"."app_label",
"django_content_type"."model"
FROM "comment_nodes"
INNER JOIN "django_content_type"
ON ("comment_nodes"."content_type_id" = "django_content_type"."id")
INNER JOIN "auth_user"
ON ("comment_nodes"."user_id" = "auth_user"."id")
WHERE ("comment_nodes"."approved" = True
AND "comment_nodes"."object_id" IN (2,
3,
4,
5,
6,
7,
8,
9,
10,
11,
12,
13,
14,
15,
16,
17,
18,
19,
20,
21,
22,
23,
24,
25,
27,
30,
31,
32,
35)
AND "comment_nodes"."content_type_id" = 35 )
ORDER BY "comment_nodes"."pub_date" DESC LIMIT 5

SELECT DISTINCT "tag".id,
"tag".name,
COUNT("blog_post"."id")

FROM
"tag"

INNER JOIN "tagged_item"

ON "tag".id = "tagged_item".tag_id

INNER JOIN "blog_post"

ON "tagged_item".object_id = "blog_post"."id"

WHERE "tagged_item".content_type_id = 35

AND ("blog_post"."site_id" = 1
AND "blog_post"."is_draft" = False )
GROUP BY "tag".id,
"tag".name

ORDER BY "tag".name ASC

SELECT (SELECT COUNT(*)
FROM comment_nodes,
django_content_type
WHERE comment_nodes.content_type_id = django_content_type.id
AND django_content_type.model = 'post'
AND comment_nodes.object_id = blog_post.id
AND comment_nodes.approved) AS "approved_comments_count",
(SELECT COUNT(*)
FROM watchlist_subscription,
django_content_type
WHERE watchlist_subscription.content_type_id = django_content_type.id
AND django_content_type.model = 'post'
AND watchlist_subscription.object_id = blog_post.id) AS "watchlist_count",
(SELECT COUNT(*)
FROM pingback,
django_content_type
WHERE pingback.content_type_id = django_content_type.id
AND django_content_type.model = 'post'
AND pingback.object_id = blog_post.id) AS "pingback_count",
"blog_post"."id",
"blog_post"."site_id",
"blog_post"."author_id",
"blog_post"."name",
"blog_post"."slug",
"blog_post"."text",
"blog_post"."render_method",
"blog_post"."html",
"blog_post"."date",
"blog_post"."upd_date",
"blog_post"."is_draft",
"blog_post"."enable_comments",
"blog_post"."tags"
FROM "blog_post"
WHERE "blog_post"."id" = 35
ORDER BY "blog_post"."date" DESC

SELECT (SELECT COUNT(*)
FROM comment_nodes,
django_content_type
WHERE comment_nodes.content_type_id = django_content_type.id
AND django_content_type.model = 'post'
AND comment_nodes.object_id = blog_post.id
AND comment_nodes.approved) AS "approved_comments_count",
(SELECT COUNT(*)
FROM watchlist_subscription,
django_content_type
WHERE watchlist_subscription.content_type_id = django_content_type.id
AND django_content_type.model = 'post'
AND watchlist_subscription.object_id = blog_post.id) AS "watchlist_count",
(SELECT COUNT(*)
FROM pingback,
django_content_type
WHERE pingback.content_type_id = django_content_type.id
AND django_content_type.model = 'post'
AND pingback.object_id = blog_post.id) AS "pingback_count",
"blog_post"."id",
"blog_post"."site_id",
"blog_post"."author_id",
"blog_post"."name",
"blog_post"."slug",
"blog_post"."text",
"blog_post"."render_method",
"blog_post"."html",
"blog_post"."date",
"blog_post"."upd_date",
"blog_post"."is_draft",
"blog_post"."enable_comments",
"blog_post"."tags"
FROM "blog_post"
WHERE "blog_post"."id" = 27
ORDER BY "blog_post"."date" DESC