pg array types support; more tests

5 files changed, 472 insertions, 185 deletions

diff --git a/ydb/core/kqp/ut/pg/kqp_pg_ut.cpp b/ydb/core/kqp/ut/pg/kqp_pg_ut.cpp
index a2a1dd6d31a..ef4192ffe17 100644
--- a/ydb/core/kqp/ut/pg/kqp_pg_ut.cpp
+++ b/ydb/core/kqp/ut/pg/kqp_pg_ut.cpp
@@ -1,5 +1,7 @@
 #include <ydb/core/kqp/ut/common/kqp_ut_common.h>
 
+#include <ydb/library/yql/parser/pg_catalog/catalog.h>
+
 extern "C" {
 #include "postgres.h"
 #include "catalog/pg_type_d.h"
@@ -17,147 +19,301 @@ Y_UNIT_TEST_SUITE(KqpPg) {
 
     Y_UNIT_TEST(CreateTableBulkUpsertAndRead) {
         TKikimrRunner kikimr;
-        auto db = kikimr.GetTableClient();
-        auto session = db.CreateSession().GetValueSync().GetSession();
-
-        auto builder = TTableBuilder()
-            .AddNullableColumn("boolean", makePgType(BOOLOID))
-            .AddNullableColumn("char", makePgType(CHAROID))
-            .AddNullableColumn("int2", makePgType(INT2OID))
-            .AddNullableColumn("int4", makePgType(INT4OID))
-            .AddNullableColumn("int8", makePgType(INT8OID))
-            .AddNullableColumn("float4", makePgType(FLOAT4OID))
-            .AddNullableColumn("float8", makePgType(FLOAT8OID))
-            .AddNullableColumn("text", makePgType(TEXTOID))
-            .AddNullableColumn("bytea", makePgType(BYTEAOID))
-            .AddNullableColumn("bpchar", makePgType(BPCHAROID))
-            .AddNullableColumn("varchar", makePgType(VARCHAROID))
-            .SetPrimaryKeyColumn("int2")
-            .SetPrimaryKeyColumn("int4")
-            .SetPrimaryKeyColumn("int8")
-            .SetPrimaryKeyColumn("float4")
-            .SetPrimaryKeyColumn("float8")
-            .SetPrimaryKeyColumn("text")
-            .SetPrimaryKeyColumn("bytea")
-            .SetPrimaryKeyColumn("bpchar");
-
-        auto result = session.CreateTable("/Root/Pg", builder.Build()).GetValueSync();
-        UNIT_ASSERT_C(result.IsSuccess(), result.GetIssues().ToString());
-
-        NYdb::TValueBuilder rows;
-        rows.BeginList();
-        for (size_t i = 0; i < 10; ++i) {
-            auto boolVal = true;
-            TString boolStr((const char*)&boolVal, sizeof(boolVal));
-            auto charVal = (char)i;
-            TString charStr((const char*)&charVal, sizeof(charVal));
-            auto int2Val = (i16)i;
-            TString int2Str((const char*)&int2Val, sizeof(int2Val));
-            auto int4Val = (i32)i;
-            TString int4Str((const char*)&int4Val, sizeof(int4Val));
-            auto int8Val = (i64)i;
-            TString int8Str((const char*)&int8Val, sizeof(int8Val));
-            auto float4Val = (float)i;
-            TString float4Str((const char*)&float4Val, sizeof(float4Val));
-            auto float8Val = (double)i;
-            TString float8Str((const char*)&float8Val, sizeof(float8Val));
-            TString textStr = Sprintf("text %" PRIu64, i);
-            TString byteaStr = Sprintf("bytea %" PRIu64, i);
-            TString bpcharStr = Sprintf("bpchar %" PRIu64, i);
-            TString varcharStr = Sprintf("varchar %" PRIu64, i);
-
-            rows.AddListItem()
-                .BeginStruct()
-                .AddMember("boolean").Pg(TPgValue(TPgValue::VK_BINARY, boolStr, makePgType(BOOLOID)))
-                .AddMember("char").Pg(TPgValue(TPgValue::VK_BINARY, charStr, makePgType(CHAROID)))
-                .AddMember("int2").Pg(TPgValue(TPgValue::VK_BINARY, int2Str, makePgType(INT2OID)))
-                .AddMember("int4").Pg(TPgValue(TPgValue::VK_BINARY, int4Str, makePgType(INT4OID)))
-                .AddMember("int8").Pg(TPgValue(TPgValue::VK_BINARY, int8Str, makePgType(INT8OID)))
-                .AddMember("float4").Pg(TPgValue(TPgValue::VK_BINARY, float4Str, makePgType(FLOAT4OID)))
-                .AddMember("float8").Pg(TPgValue(TPgValue::VK_BINARY, float8Str, makePgType(FLOAT8OID)))
-                .AddMember("text").Pg(TPgValue(TPgValue::VK_BINARY, textStr, makePgType(TEXTOID)))
-                .AddMember("bytea").Pg(TPgValue(TPgValue::VK_BINARY, byteaStr, makePgType(BYTEAOID)))
-                .AddMember("bpchar").Pg(TPgValue(TPgValue::VK_BINARY, bpcharStr, makePgType(BPCHAROID)))
-                .AddMember("varchar").Pg(TPgValue(TPgValue::VK_BINARY, varcharStr, makePgType(VARCHAROID)))
-                .EndStruct();
-        }
-        rows.EndList();
-
-        result = db.BulkUpsert("/Root/Pg", rows.Build()).GetValueSync();
-        UNIT_ASSERT_C(result.IsSuccess(), result.GetIssues().ToString());
-
-        session.Close().GetValueSync();
-
-        auto readSettings = TReadTableSettings()
-            .AppendColumns("boolean")
-            .AppendColumns("char")
-            .AppendColumns("int2")
-            .AppendColumns("int4")
-            .AppendColumns("int8")
-            .AppendColumns("float4")
-            .AppendColumns("float8")
-            .AppendColumns("text")
-            .AppendColumns("bytea")
-            .AppendColumns("bpchar")
-            .AppendColumns("varchar");
-
-        auto it = session.ReadTable("/Root/Pg", readSettings).GetValueSync();
-        UNIT_ASSERT_C(it.IsSuccess(), result.GetIssues().ToString());
-
-        bool eos = false;
-        while (!eos) {
-            auto part = it.ReadNext().ExtractValueSync();
-            if (!part.IsSuccess()) {
-                eos = true;
-                UNIT_ASSERT_C(part.EOS(), result.GetIssues().ToString());
-                continue;
+
+        auto testSingleType = [&kikimr] (ui32 id, bool isKey,
+            std::function<TString(size_t)> binaryIn,
+            std::function<TString(size_t)> textOut)
+        {
+            auto db = kikimr.GetTableClient();
+            auto session = db.CreateSession().GetValueSync().GetSession();
+
+            TTableBuilder builder;
+            if (isKey) {
+                builder.AddNullableColumn("key", makePgType(id));
+            } else {
+                builder.AddNullableColumn("key", makePgType(INT2OID));
+            }
+            builder.AddNullableColumn("value", makePgType(id));
+            builder.SetPrimaryKeyColumn("key");
+
+            auto tableName = Sprintf("/Root/Pg%u", id);
+            auto result = session.CreateTable(tableName, builder.Build()).GetValueSync();
+            UNIT_ASSERT_C(result.IsSuccess(), result.GetIssues().ToString());
+
+            NYdb::TValueBuilder rows;
+            rows.BeginList();
+            for (size_t i = 0; i < 10; ++i) {
+                auto str = binaryIn(i);
+                if (isKey) {
+                    rows.AddListItem()
+                        .BeginStruct()
+                        .AddMember("key").Pg(TPgValue(TPgValue::VK_BINARY, str, makePgType(id)))
+                        .AddMember("value").Pg(TPgValue(TPgValue::VK_BINARY, str, makePgType(id)))
+                        .EndStruct();
+                } else {
+                    auto int2Val = (i16)i;
+                    TString int2Str((const char*)&int2Val, sizeof(int2Val));
+                    rows.AddListItem()
+                        .BeginStruct()
+                        .AddMember("key").Pg(TPgValue(TPgValue::VK_BINARY, int2Str, makePgType(INT2OID)))
+                        .AddMember("value").Pg(TPgValue(TPgValue::VK_BINARY, str, makePgType(id)))
+                        .EndStruct();
+                }
             }
-            auto resultSet = part.ExtractPart();
-            TResultSetParser parser(resultSet);
-            for (size_t i = 0; parser.TryNextRow(); ++i) {
-                auto boolVal = true;
-                TString boolStr((const char*)&boolVal, sizeof(boolVal));
-                auto charVal = (char)i;
-                TString charStr((const char*)&charVal, sizeof(charVal));
-                auto int2Val = (i16)i;
-                TString int2Str((const char*)&int2Val, sizeof(int2Val));
-                auto int4Val = (i32)i;
-                TString int4Str((const char*)&int4Val, sizeof(int4Val));
-                auto int8Val = (i64)i;
-                TString int8Str((const char*)&int8Val, sizeof(int8Val));
-                auto float4Val = (float)i;
-                TString float4Str((const char*)&float4Val, sizeof(float4Val));
-                auto float8Val = (double)i;
-                TString float8Str((const char*)&float8Val, sizeof(float8Val));
-                TString textStr = Sprintf("text %" PRIu64, i);
-                TString byteaStr = Sprintf("bytea %" PRIu64, i);
-                TString bpcharStr = Sprintf("bpchar %" PRIu64, i);
-                TString varcharStr = Sprintf("varchar %" PRIu64, i);
-
-#define COLUMN(type, valStr) \
-                { \
-                    auto& column = parser.ColumnParser(type); \
-                    column.OpenOptional(); \
-                    UNIT_ASSERT_VALUES_EQUAL(valStr, column.GetPg().Content_); \
-                    column.CloseOptional(); \
+            rows.EndList();
+
+            result = db.BulkUpsert(tableName, rows.Build()).GetValueSync();
+            UNIT_ASSERT_C(result.IsSuccess(), result.GetIssues().ToString());
+
+            auto readSettings = TReadTableSettings()
+                .AppendColumns("key")
+                .AppendColumns("value");
+
+            auto it = session.ReadTable(tableName, readSettings).GetValueSync();
+            UNIT_ASSERT_C(it.IsSuccess(), result.GetIssues().ToString());
+
+            bool eos = false;
+            while (!eos) {
+                auto part = it.ReadNext().ExtractValueSync();
+                if (!part.IsSuccess()) {
+                    eos = true;
+                    UNIT_ASSERT_C(part.EOS(), result.GetIssues().ToString());
+                    continue;
+                }
+                auto resultSet = part.ExtractPart();
+                TResultSetParser parser(resultSet);
+                for (size_t i = 0; parser.TryNextRow(); ++i) {
+                    auto check = [&parser, &id] (const TString& column, const TString& expected) {
+                        auto& c = parser.ColumnParser(column);
+                        c.OpenOptional();
+                        UNIT_ASSERT_VALUES_EQUAL(expected, NPg::PgNativeTextFromNativeBinary(c.GetPg().Content_, id));
+                        Cerr << expected << Endl;
+                        c.CloseOptional();
+                    };
+                    auto expected = textOut(i);
+                    if (isKey) {
+                        check("key", expected);
+                    }
+                    check("value", expected);
                 }
-                COLUMN("boolean", boolStr);
-                COLUMN("char", charStr);
-                COLUMN("int2", int2Str);
-                COLUMN("int4", int4Str);
-                COLUMN("int8", int8Str);
-                COLUMN("float4", float4Str);
-                COLUMN("float8", float8Str);
-                COLUMN("text", textStr);
-                COLUMN("bytea", byteaStr);
-                COLUMN("bpchar", bpcharStr);
-                COLUMN("varchar", varcharStr);
-#undef COLUMN
             }
-        }
-   }
 
-   Y_UNIT_TEST(EmptyQuery) {
+            session.Close().GetValueSync();
+        };
+
+        auto testType = [&] (ui32 id, bool isKey,
+            std::function<TString(size_t)> binaryIn,
+            std::function<TString(size_t)> textOut,
+            std::function<TString(TString)> arrayPrint = [] (auto s) { return Sprintf("{%s,%s}", s.c_str(), s.c_str()); })
+        {
+            testSingleType(id, isKey, binaryIn, textOut);
+
+            auto arrayId = NYql::NPg::LookupType(id).ArrayTypeId;
+
+            auto binaryInArray = [&] (auto i) {
+                auto binary = binaryIn(i);
+                auto str = NPg::PgNativeTextFromNativeBinary(binary, id);
+                auto arrayStr = arrayPrint(str);
+                return NPg::PgNativeBinaryFromNativeText(arrayStr, arrayId);
+            };
+            auto textOutArray = [&] (auto i) {
+                auto str = textOut(i);
+                return arrayPrint(str);
+            };
+
+            testSingleType(arrayId, false, binaryInArray, textOutArray);
+        };
+
+        auto testByteaType = [&] () {
+            testSingleType(BYTEAOID, true,
+                [] (auto i) { return NPg::PgNativeBinaryFromNativeText(Sprintf("bytea %u", i), BYTEAOID); },
+                [] (auto i) { return Sprintf("\\x627974656120%x", i + 48); });
+
+            testSingleType(BYTEAARRAYOID, false,
+                [] (auto i) { return NPg::PgNativeBinaryFromNativeText(Sprintf("{a%u, b%u}", i, i + 10), BYTEAARRAYOID); },
+                [] (auto i) { return Sprintf("{\"\\\\x61%x\",\"\\\\x6231%x\"}", i + 48, i + 48); });
+        };
+
+        testType(BOOLOID, true,
+            [] (auto i) { auto val = (bool)i; return TString((const char*)&val, sizeof(val)); },
+            [] (auto i) { return TString(i ? "t" : "f"); });
+
+        testType(CHAROID, true,
+            [] (auto i) { auto val = (char)(i + '0'); return TString((const char*)&val, sizeof(val)); },
+            [] (auto i) { return Sprintf("%c", (char)(i + '0')); });
+
+        testType(INT2OID, true,
+            [] (auto i) { auto val = (i16)i; return TString((const char*)&val, sizeof(val)); },
+            [] (auto i) { return Sprintf("%u", i); });
+
+        testType(INT4OID, true,
+            [] (auto i) { auto val = (i32)i; return TString((const char*)&val, sizeof(val)); },
+            [] (auto i) { return Sprintf("%u", i); });
+
+        testType(INT8OID, true,
+            [] (auto i) { auto val = (i64)i; return TString((const char*)&val, sizeof(val)); },
+            [] (auto i) { return Sprintf("%u", i); });
+
+        testType(FLOAT4OID, true,
+            [] (auto i) { auto val = (float)i; return TString((const char*)&val, sizeof(val)); },
+            [] (auto i) { return Sprintf("%g", (float)i); });
+
+        testType(FLOAT8OID, true,
+            [] (auto i) { auto val = (double)i; return TString((const char*)&val, sizeof(val)); },
+            [] (auto i) { return Sprintf("%lg", (double)i); });
+
+        testByteaType();
+
+        testType(TEXTOID, true,
+            [] (auto i) { return NPg::PgNativeBinaryFromNativeText(Sprintf("text %u", i), TEXTOID); },
+            [] (auto i) { return Sprintf("text %u", i); },
+            [] (auto s) { return Sprintf("{\"%s\",\"%s\"}", s.c_str(), s.c_str()); });
+
+        testType(BPCHAROID, true,
+            [] (auto i) { return NPg::PgNativeBinaryFromNativeText(Sprintf("bpchar %u", i), BPCHAROID); },
+            [] (auto i) { return Sprintf("bpchar %u", i); },
+            [] (auto s) { return Sprintf("{\"%s\",\"%s\"}", s.c_str(), s.c_str()); });
+
+        testType(VARCHAROID, false,
+            [] (auto i) { return NPg::PgNativeBinaryFromNativeText(Sprintf("varchar %u", i), VARCHAROID); },
+            [] (auto i) { return Sprintf("varchar %u", i); },
+            [] (auto s) { return Sprintf("{\"%s\",\"%s\"}", s.c_str(), s.c_str()); });
+
+        testType(NAMEOID, true,
+            [] (auto i) { return NPg::PgNativeBinaryFromNativeText(Sprintf("name %u", i), NAMEOID); },
+            [] (auto i) { return Sprintf("name %u", i); },
+            [] (auto s) { return Sprintf("{\"%s\",\"%s\"}", s.c_str(), s.c_str()); });
+
+        testType(NUMERICOID, true,
+            [] (auto i) { return NPg::PgNativeBinaryFromNativeText(Sprintf("%lg", i + 0.12345), NUMERICOID); },
+            [] (auto i) { return Sprintf("%lg", i + 0.12345); });
+
+//        testType(DATEOID, true,
+//            [] (auto i) { return NPg::PgNativeBinaryFromNativeText(Sprintf("1970-01-%02u", i + 1), DATEOID); },
+//            [] (auto i) { return Sprintf("1970-01-%02u", i + 1); });
+
+//        testType(TIMEOID, true,
+//            [] (auto i) { return NPg::PgNativeBinaryFromNativeText(Sprintf("%02u:01:02.345", i), TIMEOID); },
+//            [] (auto i) { return ""; });
+
+//        testType(TIMESTAMPOID, true,
+//            [] (auto i) { return NPg::PgNativeBinaryFromNativeText(Sprintf("1970-01-01 %02u:01:02.345", i), TIMESTAMPOID); },
+//            [] (auto i) { return ""; });
+
+        testType(TIMETZOID, true,
+            [] (auto i) { return NPg::PgNativeBinaryFromNativeText(Sprintf("%02u:01:02.345-03", i), TIMETZOID); },
+            [] (auto i) { return Sprintf("%02u:01:02.345-03", i); });
+
+//        testType(TIMESTAMPTZOID, true,
+//            [] (auto i) { return NPg::PgNativeBinaryFromNativeText(Sprintf("1970-01-01 %02u:01:02.345 -3:00", i), TIMESTAMPTZOID); },
+//            [] (auto i) { return ""; });
+
+        testType(INTERVALOID, true,
+            [] (auto i) { return NPg::PgNativeBinaryFromNativeText(Sprintf("P01-02-03T04:05:%02u", i), INTERVALOID); },
+            [] (auto i) { return Sprintf("1 year 2 mons 3 days 04:05:%02u", i); },
+            [] (auto s) { return Sprintf("{\"%s\",\"%s\"}", s.c_str(), s.c_str()); });
+
+        testType(BITOID, true,
+            [] (auto i) { return NPg::PgNativeBinaryFromNativeText(Sprintf("%c%c%c%c", (i&8)?'1':'0', (i&4)?'1':'0', (i&2)?'1':'0', (i&1)?'1':'0'), BITOID); },
+            [] (auto i) { return Sprintf("%c%c%c%c", (i&8)?'1':'0', (i&4)?'1':'0', (i&2)?'1':'0', (i&1)?'1':'0'); });
+
+        testType(VARBITOID, true,
+            [] (auto i) { return NPg::PgNativeBinaryFromNativeText(Sprintf("%c%c%c%c", (i&8)?'1':'0', (i&4)?'1':'0', (i&2)?'1':'0', (i&1)?'1':'0'), VARBITOID); },
+            [] (auto i) { return Sprintf("%c%c%c%c", (i&8)?'1':'0', (i&4)?'1':'0', (i&2)?'1':'0', (i&1)?'1':'0'); });
+
+        testType(POINTOID, false,
+            [] (auto i) { return NPg::PgNativeBinaryFromNativeText(Sprintf("(10, %u)", i), POINTOID); },
+            [] (auto i) { return Sprintf("(10,%u)", i); },
+            [] (auto s) { return Sprintf("{\"%s\",\"%s\"}", s.c_str(), s.c_str()); });
+
+        testType(LINEOID, false,
+            [] (auto i) { return NPg::PgNativeBinaryFromNativeText(Sprintf("{1, 2, %u}", i), LINEOID); },
+            [] (auto i) { return Sprintf("{1,2,%u}", i); },
+            [] (auto s) { return Sprintf("{\"%s\",\"%s\"}", s.c_str(), s.c_str()); });
+
+        testType(LSEGOID, false,
+            [] (auto i) { return NPg::PgNativeBinaryFromNativeText(Sprintf("[(0, 0), (1, %u)]", i), LSEGOID); },
+            [] (auto i) { return Sprintf("[(0,0),(1,%u)]", i); },
+            [] (auto s) { return Sprintf("{\"%s\",\"%s\"}", s.c_str(), s.c_str()); });
+
+        testType(BOXOID, false,
+            [] (auto i) { return NPg::PgNativeBinaryFromNativeText(Sprintf("(1, %u), (0, 0)", i + 1), BOXOID); },
+            [] (auto i) { return Sprintf("(1,%u),(0,0)", i + 1); },
+            [] (auto s) { return Sprintf("{%s;%s}", s.c_str(), s.c_str()); });
+
+        testType(PATHOID, false,
+            [] (auto i) { return NPg::PgNativeBinaryFromNativeText(Sprintf("((0, 1), (2, 3), (4, %u))", i), PATHOID); },
+            [] (auto i) { return Sprintf("((0,1),(2,3),(4,%u))", i); },
+            [] (auto s) { return Sprintf("{\"%s\",\"%s\"}", s.c_str(), s.c_str()); });
+
+        testType(POLYGONOID, false,
+            [] (auto i) { return NPg::PgNativeBinaryFromNativeText(Sprintf("((0, 1), (2, 3), (4, %u))", i), POLYGONOID); },
+            [] (auto i) { return Sprintf("((0,1),(2,3),(4,%u))", i); },
+            [] (auto s) { return Sprintf("{\"%s\",\"%s\"}", s.c_str(), s.c_str()); });
+
+        testType(CIRCLEOID, false,
+            [] (auto i) { return NPg::PgNativeBinaryFromNativeText(Sprintf("<(0, 1), %u>", i), CIRCLEOID); },
+            [] (auto i) { return Sprintf("<(0,1),%u>", i); },
+            [] (auto s) { return Sprintf("{\"%s\",\"%s\"}", s.c_str(), s.c_str()); });
+
+        testType(INETOID, false,
+            [] (auto i) { return NPg::PgNativeBinaryFromNativeText(Sprintf("128.%u.0.0/16", i), INETOID); },
+            [] (auto i) { return Sprintf("128.%u.0.0/16", i); });
+
+        testType(CIDROID, false,
+            [] (auto i) { return NPg::PgNativeBinaryFromNativeText(Sprintf("128.%u.0.0/16", i), CIDROID); },
+            [] (auto i) { return Sprintf("128.%u.0.0/16", i); });
+
+        testType(MACADDROID, false,
+            [] (auto i) { return NPg::PgNativeBinaryFromNativeText(Sprintf("08:00:2b:01:02:%02u", i), MACADDROID); },
+            [] (auto i) { return Sprintf("08:00:2b:01:02:%02u", i); });
+
+        testType(MACADDR8OID, false,
+            [] (auto i) { return NPg::PgNativeBinaryFromNativeText(Sprintf("08:00:2b:01:02:03:04:%02u", i), MACADDR8OID); },
+            [] (auto i) { return Sprintf("08:00:2b:01:02:03:04:%02u", i); });
+
+        testType(UUIDOID, false,
+            [] (auto i) { return NPg::PgNativeBinaryFromNativeText(Sprintf("00000000-0000-0000-0000-0000000000%02u", i), UUIDOID); },
+            [] (auto i) { return Sprintf("00000000-0000-0000-0000-0000000000%02u", i); });
+
+        testType(JSONOID, false,
+            [] (auto i) { return NPg::PgNativeBinaryFromNativeText(Sprintf("[%u]", i), JSONOID); },
+            [] (auto i) { return Sprintf("[%u]", i); });
+
+        testType(JSONBOID, false,
+            [] (auto i) { return NPg::PgNativeBinaryFromNativeText(Sprintf("[%u]", i), JSONBOID); },
+            [] (auto i) { return Sprintf("[%u]", i); });
+
+        testType(JSONPATHOID, false,
+            [] (auto i) { return NPg::PgNativeBinaryFromNativeText(Sprintf("$[%u]", i), JSONPATHOID); },
+            [] (auto i) { return Sprintf("$[%u]", i); });
+
+        testType(XMLOID, false,
+            [] (auto i) { return NPg::PgNativeBinaryFromNativeText(Sprintf("<a>%u</a>", i), XMLOID); },
+            [] (auto i) { return Sprintf("<a>%u</a>", i); });
+
+        testType(TSQUERYOID, false,
+            [] (auto i) { return NPg::PgNativeBinaryFromNativeText(Sprintf("a&b%u", i), TSQUERYOID); },
+            [] (auto i) { return Sprintf("'a' & 'b%u'", i); },
+            [] (auto s) { return Sprintf("{\"%s\",\"%s\"}", s.c_str(), s.c_str()); });
+
+        testType(TSVECTOROID, false,
+            [] (auto i) { return NPg::PgNativeBinaryFromNativeText(Sprintf("a:1 b:%u", i + 2), TSVECTOROID); },
+            [] (auto i) { return Sprintf("'a':1 'b':%u", i + 2); },
+            [] (auto s) { return Sprintf("{\"%s\",\"%s\"}", s.c_str(), s.c_str()); });
+
+        testType(INT2VECTOROID, false,
+            [] (auto i) { return NPg::PgNativeBinaryFromNativeText(Sprintf("%u %u %u", i, i + 1, i + 2), INT2VECTOROID); },
+            [] (auto i) { return Sprintf("%u %u %u", i, i + 1, i + 2); },
+            [] (auto s) { return Sprintf("{\"%s\",\"%s\"}", s.c_str(), s.c_str()); });
+
+        // TODO: varchar as a key
+        // TODO: date/time types (?)
+        // TODO: money (uses PGLC_localeconv())
+        // TODO: native range/multirange types (use get_range_io_data())
+    }
+
+    Y_UNIT_TEST(EmptyQuery) {
         auto kikimr = DefaultKikimrRunner();
         NYdb::NScripting::TScriptingClient client(kikimr.GetDriver());
 
diff --git a/ydb/core/scheme/scheme_types_defs.h b/ydb/core/scheme/scheme_types_defs.h
index 9e51f936fd9..7e3e604f71d 100644
--- a/ydb/core/scheme/scheme_types_defs.h
+++ b/ydb/core/scheme/scheme_types_defs.h
@@ -60,7 +60,7 @@ inline ui32 GetFixedSize(TTypeInfo typeInfo) {
     KIKIMR_FOREACH_TYPE(KIKIMR_TYPE_MACRO)
 #undef KIKIMR_TYPE_MACRO
     case NTypeIds::Pg:
-        return NPg::TypeDescGetTypeLen(typeInfo.GetTypeDesc());
+        return NPg::TypeDescGetStoredSize(typeInfo.GetTypeDesc());
     default:
         return 0;
     }
diff --git a/ydb/library/yql/parser/pg_wrapper/comp_factory.cpp b/ydb/library/yql/parser/pg_wrapper/comp_factory.cpp
index e0b4d7e2efa..741eecedcb7 100644
--- a/ydb/library/yql/parser/pg_wrapper/comp_factory.cpp
+++ b/ydb/library/yql/parser/pg_wrapper/comp_factory.cpp
@@ -2936,55 +2936,74 @@ public:
     explicit TPgTypeDescriptor(const NYql::NPg::TTypeDesc& desc)
         : NYql::NPg::TTypeDesc(desc)
     {
-        // TODO: btarraycmp and hash_array for array types
-        if (CompareProcId) {
-            InitFunc(CompareProcId, &CompareProcInfo, 2, 2);
-        }
-        if (HashProcId) {
-            InitFunc(HashProcId, &HashProcInfo, 1, 1);
-        }
-        if (ReceiveFuncId) {
-            InitFunc(ReceiveFuncId, &ReceiveFuncInfo, 1, 3);
-        }
-        if (SendFuncId) {
-            InitFunc(SendFuncId, &SendFuncInfo, 1, 1);
+        if (TypeId == ArrayTypeId) {
+            const auto& typeDesc = NYql::NPg::LookupType(ElementTypeId);
+            if (typeDesc.CompareProcId) {
+                CompareProcId = NYql::NPg::LookupProc("btarraycmp", { 0, 0 }).ProcId;
+            }
+            if (typeDesc.HashProcId) {
+                HashProcId = NYql::NPg::LookupProc("hash_array", { 0 }).ProcId;
+            }
+            if (typeDesc.ReceiveFuncId) {
+                ReceiveFuncId = NYql::NPg::LookupProc("array_recv", { 0, 0, 0 }).ProcId;
+            }
+            if (typeDesc.SendFuncId) {
+                SendFuncId = NYql::NPg::LookupProc("array_send", { 0 }).ProcId;
+            }
+            if (typeDesc.InFuncId) {
+                InFuncId = NYql::NPg::LookupProc("array_in", { 0, 0, 0 }).ProcId;
+            }
+            if (typeDesc.OutFuncId) {
+                OutFuncId = NYql::NPg::LookupProc("array_out", { 0 }).ProcId;
+            }
+        } else {
+            StoredSize = TypeLen < 0 ? 0 : TypeLen;
+            if (TypeId == NAMEOID) {
+                StoredSize = 0; // store 'name' as usual string
+            }
         }
     }
 
     int Compare(const char* dataL, size_t sizeL, const char* dataR, size_t sizeR) const {
         NMiniKQL::TScopedAlloc alloc(__LOCATION__);
         NMiniKQL::TPAllocScope scope;
+        Datum datumL = 0, datumR = 0;
+        Y_DEFER {
+            if (!PassByValue) {
+                if (datumL)
+                    pfree((void*)datumL);
+                if (datumR)
+                    pfree((void*)datumR);
+            }
+        };
         PG_TRY();
         {
-            Datum datumL, datumR;
             if (PassByValue) {
                 datumL = ScalarDatumFromData(dataL, sizeL);
                 datumR = ScalarDatumFromData(dataR, sizeR);
             } else {
-                datumL = Unpack(dataL, sizeL);
-                datumR = Unpack(dataR, sizeR);
+                datumL = PointerDatumFromData(dataL, sizeL);
+                datumR = PointerDatumFromData(dataR, sizeR);
             }
-
+            FmgrInfo finfo;
+            InitFunc(CompareProcId, &finfo, 2, 2);
             LOCAL_FCINFO(callInfo, 2);
             Zero(*callInfo);
-            callInfo->flinfo = (FmgrInfo*)&CompareProcInfo;
+            callInfo->flinfo = &finfo;
             callInfo->nargs = 2;
             callInfo->fncollation = DEFAULT_COLLATION_OID;
             callInfo->isnull = false;
             callInfo->args[0] = { datumL, false };
             callInfo->args[1] = { datumR, false };
 
-            auto result = CompareProcInfo.fn_addr(callInfo);
+            auto result = finfo.fn_addr(callInfo);
             Y_ENSURE(!callInfo->isnull);
-            if (!PassByValue) {
-                pfree((void*)datumL);
-                pfree((void*)datumR);
-            }
             return DatumGetInt32(result);
         }
         PG_CATCH();
         {
             // TODO
+            Y_VERIFY(false, "PG error in Compare");
         }
         PG_END_TRY();
         return 0;
@@ -2993,40 +3012,141 @@ public:
     ui64 Hash(const char* data, size_t size) const {
         NMiniKQL::TScopedAlloc alloc(__LOCATION__);
         NMiniKQL::TPAllocScope scope;
+        Datum datum = 0;
+        Y_DEFER {
+            if (!PassByValue && datum) {
+                pfree((void*)datum);
+            }
+        };
         PG_TRY();
         {
-            Datum datum;
             if (PassByValue) {
                 datum = ScalarDatumFromData(data, size);
             } else {
-                datum = Unpack(data, size);
+                datum = PointerDatumFromData(data, size);
             }
+            FmgrInfo finfo;
+            InitFunc(HashProcId, &finfo, 1, 1);
+            LOCAL_FCINFO(callInfo, 1);
+            Zero(*callInfo);
+            callInfo->flinfo = &finfo;
+            callInfo->nargs = 1;
+            callInfo->fncollation = DEFAULT_COLLATION_OID;
+            callInfo->isnull = false;
+            callInfo->args[0] = { datum, false };
+
+            auto result = finfo.fn_addr(callInfo);
+            Y_ENSURE(!callInfo->isnull);
+            return DatumGetUInt32(result);
+        }
+        PG_CATCH();
+        {
+            // TODO
+            Y_VERIFY(false, "PG error in Hash");
+        }
+        PG_END_TRY();
+        return 0;
+    }
 
+    TString NativeBinaryFromNativeText(const TString& str) const {
+        NMiniKQL::TScopedAlloc alloc(__LOCATION__);
+        NMiniKQL::TPAllocScope scope;
+        Datum datum = 0;
+        text* serialized = nullptr;
+        Y_DEFER {
+            if (!PassByValue && datum) {
+                pfree((void*)datum);
+            }
+            if (serialized) {
+                pfree(serialized);
+            }
+        };
+        PG_TRY();
+        {
+            {
+                FmgrInfo finfo;
+                InitFunc(InFuncId, &finfo, 1, 3);
+                LOCAL_FCINFO(callInfo, 3);
+                Zero(*callInfo);
+                callInfo->flinfo = &finfo;
+                callInfo->nargs = 3;
+                callInfo->fncollation = DEFAULT_COLLATION_OID;
+                callInfo->isnull = false;
+                callInfo->args[0] = { (Datum)str.c_str(), false };
+                callInfo->args[1] = { ObjectIdGetDatum(NMiniKQL::MakeTypeIOParam(*this)), false };
+                callInfo->args[2] = { Int32GetDatum(-1), false };
+
+                datum = finfo.fn_addr(callInfo);
+                Y_ENSURE(!callInfo->isnull);
+            }
+            FmgrInfo finfo;
+            InitFunc(SendFuncId, &finfo, 1, 1);
             LOCAL_FCINFO(callInfo, 1);
             Zero(*callInfo);
-            callInfo->flinfo = (FmgrInfo*)&HashProcInfo;
+            callInfo->flinfo = &finfo;
             callInfo->nargs = 1;
             callInfo->fncollation = DEFAULT_COLLATION_OID;
             callInfo->isnull = false;
             callInfo->args[0] = { datum, false };
 
-            auto result = HashProcInfo.fn_addr(callInfo);
+            serialized = (text*)finfo.fn_addr(callInfo);
             Y_ENSURE(!callInfo->isnull);
-            if (!PassByValue) {
+            return TString(NMiniKQL::GetVarBuf(serialized));
+        }
+        PG_CATCH();
+        {
+            // TODO
+            Y_VERIFY(false, "PG error in NativeBinaryFromNativeText");
+        }
+        PG_END_TRY();
+        return 0;
+    }
+
+    TString NativeTextFromNativeBinary(const TString& binary) const {
+        NMiniKQL::TScopedAlloc alloc(__LOCATION__);
+        NMiniKQL::TPAllocScope scope;
+        Datum datum = 0;
+        char* str = nullptr;
+        Y_DEFER {
+            if (!PassByValue && datum) {
                 pfree((void*)datum);
             }
-            return DatumGetUInt32(result);
+            if (str) {
+                pfree(str);
+            }
+        };
+        PG_TRY();
+        {
+            if (PassByValue) {
+                datum = ScalarDatumFromData(binary.Data(), binary.Size());
+            } else {
+                datum = PointerDatumFromData(binary.Data(), binary.Size());
+            }
+            FmgrInfo finfo;
+            InitFunc(OutFuncId, &finfo, 1, 1);
+            LOCAL_FCINFO(callInfo, 1);
+            Zero(*callInfo);
+            callInfo->flinfo = &finfo;
+            callInfo->nargs = 1;
+            callInfo->fncollation = DEFAULT_COLLATION_OID;
+            callInfo->isnull = false;
+            callInfo->args[0] = { datum, false };
+
+            str = (char*)finfo.fn_addr(callInfo);
+            Y_ENSURE(!callInfo->isnull);
+            return TString(str);
         }
         PG_CATCH();
         {
             // TODO
+            Y_VERIFY(false, "PG error in NativeTextFromNativeBinary");
         }
         PG_END_TRY();
         return 0;
     }
 
 private:
-    inline Datum ScalarDatumFromData(const char* data, size_t size) const {
+    Datum ScalarDatumFromData(const char* data, size_t size) const {
         switch (TypeId) {
         case BOOLOID:
             Y_ENSURE(size == sizeof(bool));
@@ -3058,29 +3178,31 @@ private:
         }
     }
 
-    Datum Unpack(const char* data, size_t size) const {
+    Datum PointerDatumFromData(const char* data, size_t size) const {
         StringInfoData stringInfo;
         stringInfo.data = (char*)data;
         stringInfo.len = size;
         stringInfo.maxlen = size;
         stringInfo.cursor = 0;
 
+        FmgrInfo finfo;
+        InitFunc(ReceiveFuncId, &finfo, 1, 3);
         LOCAL_FCINFO(callInfo, 3);
         Zero(*callInfo);
-        callInfo->flinfo = (FmgrInfo*)&ReceiveFuncInfo;
+        callInfo->flinfo = &finfo;
         callInfo->nargs = 3;
         callInfo->fncollation = DEFAULT_COLLATION_OID;
         callInfo->isnull = false;
         callInfo->args[0] = { (Datum)&stringInfo, false };
-        callInfo->args[1] = { ObjectIdGetDatum(NKikimr::NMiniKQL::MakeTypeIOParam(*this)), false };
+        callInfo->args[1] = { ObjectIdGetDatum(NMiniKQL::MakeTypeIOParam(*this)), false };
         callInfo->args[2] = { Int32GetDatum(-1), false };
 
-        auto result = ReceiveFuncInfo.fn_addr(callInfo);
+        auto result = finfo.fn_addr(callInfo);
         Y_ENSURE(!callInfo->isnull);
         return result;
     }
 
-    static void InitFunc(ui32 funcId, FmgrInfo* info, ui32 argCountMin, ui32 argCountMax) {
+    static inline void InitFunc(ui32 funcId, FmgrInfo* info, ui32 argCountMin, ui32 argCountMax) {
         Zero(*info);
         Y_ENSURE(funcId);
         fmgr_info(funcId, info);
@@ -3088,11 +3210,8 @@ private:
         Y_ENSURE(info->fn_nargs >= argCountMin && info->fn_nargs <= argCountMax);
     }
 
-private:
-    FmgrInfo CompareProcInfo;
-    FmgrInfo HashProcInfo;
-    FmgrInfo ReceiveFuncInfo;
-    FmgrInfo SendFuncInfo;
+public:
+    ui32 StoredSize = 0; // size in local db, 0 for variable size
 };
 
 class TPgTypeDescriptors {
@@ -3122,9 +3241,6 @@ public:
 
 private:
     void InitType(ui32 pgTypeId, const NYql::NPg::TTypeDesc& type) {
-        if (type.TypeId == type.ArrayTypeId) { // TODO: support arrays
-            return;
-        }
         PgTypeDescriptors[pgTypeId] = TPgTypeDescriptor(type);
         ByName[type.Name] = pgTypeId;
     }
@@ -3166,12 +3282,11 @@ bool TypeDescIsComparable(void* typeDesc) {
     return static_cast<TPgTypeDescriptor*>(typeDesc)->CompareProcId != 0;
 }
 
-ui32 TypeDescGetTypeLen(void* typeDesc) {
+ui32 TypeDescGetStoredSize(void* typeDesc) {
     if (!typeDesc) {
         return 0;
     }
-    i32 res = static_cast<TPgTypeDescriptor*>(typeDesc)->TypeLen;
-    return res < 0 ? 0 : (ui32)res;
+    return static_cast<TPgTypeDescriptor*>(typeDesc)->StoredSize;
 }
 
 int PgNativeBinaryCompare(const char* dataL, size_t sizeL, const char* dataR, size_t sizeR, void* typeDesc) {
@@ -3182,4 +3297,16 @@ ui64 PgNativeBinaryHash(const char* data, size_t size, void* typeDesc) {
     return static_cast<TPgTypeDescriptor*>(typeDesc)->Hash(data, size);
 }
 
+TString PgNativeBinaryFromNativeText(const TString& str, ui32 pgTypeId) {
+    auto* typeDesc = TypeDescFromPgTypeId(pgTypeId);
+    Y_VERIFY(typeDesc);
+    return static_cast<TPgTypeDescriptor*>(typeDesc)->NativeBinaryFromNativeText(str);
+}
+
+TString PgNativeTextFromNativeBinary(const TString& binary, ui32 pgTypeId) {
+    auto* typeDesc = TypeDescFromPgTypeId(pgTypeId);
+    Y_VERIFY(typeDesc);
+    return static_cast<TPgTypeDescriptor*>(typeDesc)->NativeTextFromNativeBinary(binary);
+}
+
 } // namespace NKikimr::NPg
diff --git a/ydb/library/yql/parser/pg_wrapper/interface/type_desc.h b/ydb/library/yql/parser/pg_wrapper/interface/type_desc.h
index 533db3b90b2..277f9faf334 100644
--- a/ydb/library/yql/parser/pg_wrapper/interface/type_desc.h
+++ b/ydb/library/yql/parser/pg_wrapper/interface/type_desc.h
@@ -11,10 +11,14 @@ const char* PgTypeNameFromTypeDesc(void* typeDesc);
 void* TypeDescFromPgTypeName(const TStringBuf name);
 
 bool TypeDescIsComparable(void* typeDesc);
-ui32 TypeDescGetTypeLen(void* typeDesc);
+ui32 TypeDescGetStoredSize(void* typeDesc);
 
 int PgNativeBinaryCompare(const char* dataL, size_t sizeL, const char* dataR, size_t sizeR, void* typeDesc);
 
 ui64 PgNativeBinaryHash(const char* data, size_t size, void* typeDesc);
 
+// for tests
+TString PgNativeBinaryFromNativeText(const TString& str, ui32 pgTypeId);
+TString PgNativeTextFromNativeBinary(const TString& binary, ui32 pgTypeId);
+
 } // namespace NKikimr::NPg
diff --git a/ydb/library/yql/sql/pg_dummy/pg_sql_dummy.cpp b/ydb/library/yql/sql/pg_dummy/pg_sql_dummy.cpp
index bc2fedc05a5..020bc146d5f 100644
--- a/ydb/library/yql/sql/pg_dummy/pg_sql_dummy.cpp
+++ b/ydb/library/yql/sql/pg_dummy/pg_sql_dummy.cpp
@@ -294,7 +294,7 @@ bool TypeDescIsComparable(void* typeDesc) {
     throw yexception() << "PG types are not supported";
 }
 
-ui32 TypeDescGetTypeLen(void* typeDesc) {
+ui32 TypeDescGetStoredSize(void* typeDesc) {
     Y_UNUSED(typeDesc);
     throw yexception() << "PG types are not supported";
 }