From singh.gurjeet@gmail.com Wed Jun 10 16:29:07 2026 Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id D25D9632215; Tue, 6 Jan 2009 02:23:11 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 76922-04; Tue, 6 Jan 2009 02:23:08 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from rv-out-0506.google.com (rv-out-0506.google.com [209.85.198.226]) by mail.postgresql.org (Postfix) with ESMTP id 8EA9F632185; Tue, 6 Jan 2009 02:23:08 -0400 (AST) Received: by rv-out-0506.google.com with SMTP id f9so6904766rvb.7 for ; Mon, 05 Jan 2009 22:23:07 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:mime-version:content-type; bh=VqP0w2lLFAo9bjIsUm5dM3IcS//Zn0KPjalPSSns1dM=; b=GE8Rb5WjqKbE9xPnIm511ENHYasQ/SGuhoTRF3wAD9n4y5NXaBZL3X35JsZYpJttVJ XJUDMaSeFj9Z0NZE83A/41PMZkMvBInYXeVtXsuQcyuZhKhQpJwdwtb0LCQ0nN7i0DKp SWRmT7sqsDwY8/mkG0nNhk2QwGgpYByAP7hJI= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:mime-version:content-type; b=JMzqYpZ6VZECqfP3P0EVx7XmyKYEbETmHRU9m+7u4YikcNnCMWf0O3U2pBaxRTtHqj y/p4QD6fN6KKByTNELD+bbefiQ41yVQSIoppenE4tkn8Vva7kZgQb6OUcrNaGF4KjTlB SZvm5Yie5n/AVm7JiNnzh/jaOCoEmdOzKPluQ= Received: by 10.141.18.15 with SMTP id v15mr10775167rvi.197.1231222987382; Mon, 05 Jan 2009 22:23:07 -0800 (PST) Received: by 10.141.98.4 with HTTP; Mon, 5 Jan 2009 22:23:07 -0800 (PST) Message-ID: <65937bea0901052223w162a977dyeaaf888a854f7324@mail.gmail.com> Date: Tue, 6 Jan 2009 11:53:07 +0530 From: "Gurjeet Singh" To: "PGSQL General" , "PGSQL Hackers" Subject: ERROR: failed to find conversion function from "unknown" to text MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_49707_2996763.1231222987375" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200901/66 X-Sequence-Number: 142151 ------=_Part_49707_2996763.1231222987375 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Q1: select '' union all select '' Q2: select '' union all select * from (select '' ) as s version: PostgreSQL 8.3.1, compiled by Visual C++ build 1400 Hi All, Q1 works just fine, but Q2 fails with: ERROR: failed to find conversion function from "unknown" to text Q2 is a generalization of a huge query we are facing, which we cannot modify. I don't think this is a 'removed-casts' problem generally faced in 8.3, but I may be wrong. Will adding some cast resolve this? Best regards, -- gurjeet[.singh]@EnterpriseDB.com singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com Mail sent from my BlackLaptop device ------=_Part_49707_2996763.1231222987375 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Q1: select '' union all select ''
Q2: select '' union all select * from (select '' ) as s

version: PostgreSQL 8.3.1, compiled by Visual C++ build 1400

Hi All,

    Q1 works just fine, but Q2 fails with:

ERROR:  failed to find conversion function from "unknown" to text

    Q2 is a generalization of a huge query we are facing, which we cannot modify. I don't think this is a 'removed-casts' problem generally faced in 8.3, but I may be wrong. Will adding some cast resolve this?

Best regards,
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB      http://www.enterprisedb.com

Mail sent from my BlackLaptop device
------=_Part_49707_2996763.1231222987375-- From pavel.stehule@gmail.com Wed Jun 10 16:29:07 2026 Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 0069F62DBEA; Tue, 6 Jan 2009 02:30:58 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 77995-07; Tue, 6 Jan 2009 02:30:55 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-bw0-f33.google.com (mail-bw0-f33.google.com [209.85.218.33]) by mail.postgresql.org (Postfix) with ESMTP id AE98362DBE4; Tue, 6 Jan 2009 02:30:54 -0400 (AST) Received: by bwz14 with SMTP id 14so5612680bwz.19 for ; Mon, 05 Jan 2009 22:30:53 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:cc:in-reply-to:mime-version:content-type :content-transfer-encoding:content-disposition:references; bh=yfYw8j0Z+TwPctHzxj1orWG38u926YvMRvhwtyjA8pA=; b=xjgIy+xKw0aKr8Cnz7wRexjBdqHz9uMJALB5gSM/DMIhUH8Vh7vMSrS828doKtyQoy arTnqxhmgBs+Tg2m8zc5qUaKMioi1MhRqTht9BApiH6alz0YfbOZB6Wkj1vI//HfSEew 3h9W+DmilWC+gUa5SabHcJBNhyKtSvfMILBaA= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:content-transfer-encoding:content-disposition :references; b=wvwE+DN/ZuyInqlJ4l1ekM8otzvv8Ru8bmV5wCkFkIGvoeKiQdwqNf89VHqHWei2gq eweRu/fhV8FCuYkpuaiGKTHAYbGp8OqTQzu92G8vYMx3pVuuV43A/Q/tUZW7KspO/5TD XDIeRsLqtMN7SgWK6NfjmYEb8A5idBbx+WsLc= Received: by 10.181.61.7 with SMTP id o7mr7413099bkk.51.1231223453102; Mon, 05 Jan 2009 22:30:53 -0800 (PST) Received: by 10.180.226.20 with HTTP; Mon, 5 Jan 2009 22:30:53 -0800 (PST) Message-ID: <162867790901052230l25b10f08qa35f56856b9cc4c6@mail.gmail.com> Date: Tue, 6 Jan 2009 07:30:53 +0100 From: "Pavel Stehule" To: "Gurjeet Singh" Subject: Re: [HACKERS] ERROR: failed to find conversion function from "unknown" to text Cc: "PGSQL General" , "PGSQL Hackers" In-Reply-To: <65937bea0901052223w162a977dyeaaf888a854f7324@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <65937bea0901052223w162a977dyeaaf888a854f7324@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200901/67 X-Sequence-Number: 142152 Hello 2009/1/6 Gurjeet Singh : > Q1: select '' union all select '' > Q2: select '' union all select * from (select '' ) as s > > version: PostgreSQL 8.3.1, compiled by Visual C++ build 1400 > > Hi All, > > Q1 works just fine, but Q2 fails with: > > ERROR: failed to find conversion function from "unknown" to text > > Q2 is a generalization of a huge query we are facing, which we cannot > modify. I don't think this is a 'removed-casts' problem generally faced in > 8.3, but I may be wrong. Will adding some cast resolve this? yes postgres=# select '' union all select * from (select ''::text ) as s; ?column? ---------- (2 rows) regards Pavel Stehule > > Best regards, > -- > gurjeet[.singh]@EnterpriseDB.com > singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com > > EnterpriseDB http://www.enterprisedb.com > > Mail sent from my BlackLaptop device > From singh.gurjeet@gmail.com Wed Jun 10 16:29:07 2026 Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id D475862E0FE; Tue, 6 Jan 2009 02:37:56 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 79818-02; Tue, 6 Jan 2009 02:37:53 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from rv-out-0506.google.com (rv-out-0506.google.com [209.85.198.229]) by mail.postgresql.org (Postfix) with ESMTP id 0182A62DBC5; Tue, 6 Jan 2009 02:37:52 -0400 (AST) Received: by rv-out-0506.google.com with SMTP id b25so9014746rvf.43 for ; Mon, 05 Jan 2009 22:37:51 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:cc:in-reply-to:mime-version:content-type:references; bh=hsy2bgPmPT+Y9YkVwqqDvf/oFszGVeoKwv/rxo1Z4UE=; b=Pbhpaw0OIeAibUw8H42b4jnrT+EYNW5F7wLz6YbYWNNsQu+6MMnoHeVLd9tQDrJ2cq 8GYtH2iinqDSILgMp1AgFsMvKZWuSSnudROEvsN1Yu1sjT5smqn4GxoNsdHa0oB9Ze/F Wv+85WCDFyj8G00iF6IybCuo4G9FM69d4/YCI= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:references; b=ZZjVoJbD/Cdg9ZGZ7VNHDUSUSkfPxheDqWyi5aer8gG9U1h8yeVYe40rro0QzPV239 4j2WOs152gQEwKuU6nEEmnWcD3HOLTEw9cYrX5MRhEiEOlkNR50nnUS9sieIM9Xgtz1C G5NwHLtz6Aa0n8TKYcaVyhSYznzYhSkAmF5LQ= Received: by 10.140.161.11 with SMTP id j11mr10778094rve.247.1231223871762; Mon, 05 Jan 2009 22:37:51 -0800 (PST) Received: by 10.141.98.4 with HTTP; Mon, 5 Jan 2009 22:37:51 -0800 (PST) Message-ID: <65937bea0901052237j50657573i8c980f9f5f4814d5@mail.gmail.com> Date: Tue, 6 Jan 2009 12:07:51 +0530 From: "Gurjeet Singh" To: "Pavel Stehule" Subject: Re: [HACKERS] ERROR: failed to find conversion function from "unknown" to text Cc: "PGSQL General" , "PGSQL Hackers" In-Reply-To: <162867790901052230l25b10f08qa35f56856b9cc4c6@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_49790_32664644.1231223871755" References: <65937bea0901052223w162a977dyeaaf888a854f7324@mail.gmail.com> <162867790901052230l25b10f08qa35f56856b9cc4c6@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200901/68 X-Sequence-Number: 142153 ------=_Part_49790_32664644.1231223871755 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline As I mentioned, we cannot change the query, so adding casts to the query is not an option. I was looking for something external to the query, like a CREATE CAST command that'd resolve the issue. Best regards, On Tue, Jan 6, 2009 at 12:00 PM, Pavel Stehule wrote: > Hello > > 2009/1/6 Gurjeet Singh : > > Q1: select '' union all select '' > > Q2: select '' union all select * from (select '' ) as s > > > > version: PostgreSQL 8.3.1, compiled by Visual C++ build 1400 > > > > Hi All, > > > > Q1 works just fine, but Q2 fails with: > > > > ERROR: failed to find conversion function from "unknown" to text > > > > Q2 is a generalization of a huge query we are facing, which we cannot > > modify. I don't think this is a 'removed-casts' problem generally faced > in > > 8.3, but I may be wrong. Will adding some cast resolve this? > > yes > > postgres=# select '' union all select * from (select ''::text ) as s; > ?column? > ---------- > > > (2 rows) > > regards > Pavel Stehule > > > > > Best regards, > > -- > > gurjeet[.singh]@EnterpriseDB.com > > singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com > > > > EnterpriseDB http://www.enterprisedb.com > > > > Mail sent from my BlackLaptop device > > > -- gurjeet[.singh]@EnterpriseDB.com singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com Mail sent from my BlackLaptop device ------=_Part_49790_32664644.1231223871755 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline As I mentioned, we cannot change the query, so adding casts to the query is not an option. I was looking for something external to the query, like a CREATE CAST command that'd resolve the issue.

Best regards,

On Tue, Jan 6, 2009 at 12:00 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
Hello

2009/1/6 Gurjeet Singh <singh.gurjeet@gmail.com>:
> Q1: select '' union all select ''
> Q2: select '' union all select * from (select '' ) as s
>
> version: PostgreSQL 8.3.1, compiled by Visual C++ build 1400
>
> Hi All,
>
>     Q1 works just fine, but Q2 fails with:
>
> ERROR:  failed to find conversion function from "unknown" to text
>
>     Q2 is a generalization of a huge query we are facing, which we cannot
> modify. I don't think this is a 'removed-casts' problem generally faced in
> 8.3, but I may be wrong. Will adding some cast resolve this?

yes

postgres=#  select '' union all select * from (select ''::text ) as s;
 ?column?
----------


(2 rows)

regards
Pavel Stehule

>
> Best regards,
> --
> gurjeet[.singh]@EnterpriseDB.com
> singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
>
> EnterpriseDB      http://www.enterprisedb.com
>
> Mail sent from my BlackLaptop device
>



--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB      http://www.enterprisedb.com

Mail sent from my BlackLaptop device
------=_Part_49790_32664644.1231223871755-- From pavel.stehule@gmail.com Wed Jun 10 16:29:07 2026 Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 0CB6963219E; Tue, 6 Jan 2009 02:45:27 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 80849-06; Tue, 6 Jan 2009 02:45:23 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-fx0-f26.google.com (mail-fx0-f26.google.com [209.85.220.26]) by mail.postgresql.org (Postfix) with ESMTP id 7473063219D; Tue, 6 Jan 2009 02:45:23 -0400 (AST) Received: by fxm7 with SMTP id 7so1418969fxm.19 for ; Mon, 05 Jan 2009 22:45:21 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:cc:in-reply-to:mime-version:content-type :content-transfer-encoding:content-disposition:references; bh=IQhxAw40Dvrido0o/mDnJ6YiD+OdGuYkRPFL9tR68O4=; b=RcL/lZfPusykpsP3gBnsoF/VPREOBFxofIFoidTiaky16ENEC7Fqs2GxGrE/zPrwIa iwAkQ6PsOsBy8/nPn8E1CJ38Y5S+LT/1WpZRIMptuaMlrRHipRjGyjaDwHznCBGhgJfZ zi6jHYDnd42N7uv7f25QJ2wwbFGbl74ZZ00uk= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:content-transfer-encoding:content-disposition :references; b=Bj2EcoRmt9Y/YN6iIVWBYeG8LJp0jr5AhzMI2rHyVR4HFDDv6g+o8dpNAwYBZjeFTf a+ve5fmziTM32I+EIq0OgTh//pBnv+ZI0m/RL0kiuoYp/eqvU2qJgCEOtv+gWqF7k6zq 60UtSzIgfoKroILgGMEU8KDt5sYxBd93bSM10= Received: by 10.181.226.2 with SMTP id d2mr7415700bkr.15.1231224321693; Mon, 05 Jan 2009 22:45:21 -0800 (PST) Received: by 10.180.226.20 with HTTP; Mon, 5 Jan 2009 22:45:21 -0800 (PST) Message-ID: <162867790901052245l4e4fa9b2ydc03781b45b13276@mail.gmail.com> Date: Tue, 6 Jan 2009 07:45:21 +0100 From: "Pavel Stehule" To: "Gurjeet Singh" Subject: Re: [HACKERS] ERROR: failed to find conversion function from "unknown" to text Cc: "PGSQL General" , "PGSQL Hackers" In-Reply-To: <65937bea0901052237j50657573i8c980f9f5f4814d5@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <65937bea0901052223w162a977dyeaaf888a854f7324@mail.gmail.com> <162867790901052230l25b10f08qa35f56856b9cc4c6@mail.gmail.com> <65937bea0901052237j50657573i8c980f9f5f4814d5@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200901/69 X-Sequence-Number: 142154 2009/1/6 Gurjeet Singh : > As I mentioned, we cannot change the query, so adding casts to the query is > not an option. I was looking for something external to the query, like a > CREATE CAST command that'd resolve the issue. I am sorry, I blind - I tested casting on 8.3.0 and it doesn't work (but I am have old 8.3) postgres=# create function unknown2text(unknown) returns text as $$select $1::text$$ language sql; CREATE FUNCTION postgres=# create cast(unknown as text) with function unknown2text(unknown) as implicit; CREATE CAST postgres=# select '' union all select * from (select '' ) as s; ERROR: stack depth limit exceeded HINT: Increase the configuration parameter "max_stack_depth", after ensuring the platform's stack depth limit is adequate. CONTEXT: SQL function "unknown2text" during startup SQL function "unknown2text" statement 1 SQL function "unknown2text" statement 1 SQL function "unknown2text" statement 1 SQL function "unknown2text" statement 1 SQL function "unknown2text" statement 1 It working on 8.4 postgres=# create cast (unknown as text) with inout as implicit; CREATE CAST postgres=# select '' union all select * from (select '' ) as s; ?column? ---------- (2 rows) regards Pavel Stehule > > Best regards, > > On Tue, Jan 6, 2009 at 12:00 PM, Pavel Stehule > wrote: >> >> Hello >> >> 2009/1/6 Gurjeet Singh : >> > Q1: select '' union all select '' >> > Q2: select '' union all select * from (select '' ) as s >> > >> > version: PostgreSQL 8.3.1, compiled by Visual C++ build 1400 >> > >> > Hi All, >> > >> > Q1 works just fine, but Q2 fails with: >> > >> > ERROR: failed to find conversion function from "unknown" to text >> > >> > Q2 is a generalization of a huge query we are facing, which we >> > cannot >> > modify. I don't think this is a 'removed-casts' problem generally faced >> > in >> > 8.3, but I may be wrong. Will adding some cast resolve this? >> >> yes >> >> postgres=# select '' union all select * from (select ''::text ) as s; >> ?column? >> ---------- >> >> >> (2 rows) >> >> regards >> Pavel Stehule >> >> > >> > Best regards, >> > -- >> > gurjeet[.singh]@EnterpriseDB.com >> > singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com >> > >> > EnterpriseDB http://www.enterprisedb.com >> > >> > Mail sent from my BlackLaptop device >> > > > > > -- > gurjeet[.singh]@EnterpriseDB.com > singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com > > EnterpriseDB http://www.enterprisedb.com > > Mail sent from my BlackLaptop device > From singh.gurjeet@gmail.com Wed Jun 10 16:29:07 2026 Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 0233B63228C; Tue, 6 Jan 2009 05:04:38 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 06340-10; Tue, 6 Jan 2009 05:04:30 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from rv-out-0506.google.com (rv-out-0506.google.com [209.85.198.226]) by mail.postgresql.org (Postfix) with ESMTP id 2E8D863228B; Tue, 6 Jan 2009 05:04:27 -0400 (AST) Received: by rv-out-0506.google.com with SMTP id b25so9057841rvf.43 for ; Tue, 06 Jan 2009 01:04:25 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:cc:in-reply-to:mime-version:content-type:references; bh=n9JAqeeABdK5XbCgldYHgIqRRHaELKMzjlK1RR78WqY=; b=T/eqKzUlFW25kWucfx5oAnl6veKgOhgn37bajRVsn5NZN0P57RJXmrW/GNdcHD3WZt ZZMyAY3kBSubZmyZLwc/CdliskJk5AXlfvAsFG12rVOVlvkcLBxSe8IttrC1W1hVrKrB ut8Punh+AuEIDYsW4Y4+Uv5E0ga570dRm1Xpw= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:references; b=XwzrOJnMC4l5T/uLEUhnio2pRImR95ugwwAyDvSufDxDsHWJObmz1WLqVe9pUlC588 YFp7Fmc3hrS74tf/xg4K4DLohIqxzu/X4LYNMhRrv/EN0tgwGOpe6YsEidzcDcxUpjLC q5+Tr6AWbDI4P+B1lav9jP7/LVQ20IKZVkolI= Received: by 10.140.164.6 with SMTP id m6mr10849842rve.144.1231232665572; Tue, 06 Jan 2009 01:04:25 -0800 (PST) Received: by 10.141.98.4 with HTTP; Tue, 6 Jan 2009 01:04:25 -0800 (PST) Message-ID: <65937bea0901060104n399cdec8ye5e2b8e247e5139a@mail.gmail.com> Date: Tue, 6 Jan 2009 14:34:25 +0530 From: "Gurjeet Singh" To: "Pavel Stehule" Subject: Re: [HACKERS] ERROR: failed to find conversion function from "unknown" to text Cc: "PGSQL General" , "PGSQL Hackers" In-Reply-To: <162867790901052245l4e4fa9b2ydc03781b45b13276@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_50748_31507576.1231232665556" References: <65937bea0901052223w162a977dyeaaf888a854f7324@mail.gmail.com> <162867790901052230l25b10f08qa35f56856b9cc4c6@mail.gmail.com> <65937bea0901052237j50657573i8c980f9f5f4814d5@mail.gmail.com> <162867790901052245l4e4fa9b2ydc03781b45b13276@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200901/71 X-Sequence-Number: 142156 ------=_Part_50748_31507576.1231232665556 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline I took your cue, and have formulated this solution for 8.3.1 : create or replace function unknown2text(unknown) returns text as $$ begin return text($1::char); end $$ language plpgsql; drop cast (unknown as text); create cast (unknown as text) with function unknown2text( unknown ) as implicit; select '' union all select * from (select '' ) as s; Thanks for your help Pavel. Best regards, PS: I was getting the same error as yours (stack depth) in EDB version 8.3.0.12, so I had to use the following code for unknown2text: return charin( unknownout($1) ); It works for PG 8.3.1 too. On Tue, Jan 6, 2009 at 12:15 PM, Pavel Stehule wrote: > 2009/1/6 Gurjeet Singh : > > As I mentioned, we cannot change the query, so adding casts to the query > is > > not an option. I was looking for something external to the query, like a > > CREATE CAST command that'd resolve the issue. > > I am sorry, I blind - I tested casting on 8.3.0 and it doesn't work > (but I am have old 8.3) > postgres=# create function unknown2text(unknown) returns text as > $$select $1::text$$ language sql; > CREATE FUNCTION > postgres=# create cast(unknown as text) with function > unknown2text(unknown) as implicit; > CREATE CAST > postgres=# select '' union all select * from (select '' ) as s; > ERROR: stack depth limit exceeded > HINT: Increase the configuration parameter "max_stack_depth", after > ensuring the platform's stack depth limit is adequate. > CONTEXT: SQL function "unknown2text" during startup > SQL function "unknown2text" statement 1 > SQL function "unknown2text" statement 1 > SQL function "unknown2text" statement 1 > SQL function "unknown2text" statement 1 > SQL function "unknown2text" statement 1 > > It working on 8.4 > > postgres=# create cast (unknown as text) with inout as implicit; > CREATE CAST > postgres=# select '' union all select * from (select '' ) as s; > ?column? > ---------- > > > (2 rows) > > regards > Pavel Stehule > > > > > > Best regards, > > > > > > On Tue, Jan 6, 2009 at 12:00 PM, Pavel Stehule > > wrote: > >> > >> Hello > >> > >> 2009/1/6 Gurjeet Singh : > >> > Q1: select '' union all select '' > >> > Q2: select '' union all select * from (select '' ) as s > >> > > >> > version: PostgreSQL 8.3.1, compiled by Visual C++ build 1400 > >> > > >> > Hi All, > >> > > >> > Q1 works just fine, but Q2 fails with: > >> > > >> > ERROR: failed to find conversion function from "unknown" to text > >> > > >> > Q2 is a generalization of a huge query we are facing, which we > >> > cannot > >> > modify. I don't think this is a 'removed-casts' problem generally > faced > >> > in > >> > 8.3, but I may be wrong. Will adding some cast resolve this? > >> > >> yes > >> > >> postgres=# select '' union all select * from (select ''::text ) as s; > >> ?column? > >> ---------- > >> > >> > >> (2 rows) > >> > >> regards > >> Pavel Stehule > >> > >> > > >> > Best regards, > >> > -- > >> > gurjeet[.singh]@EnterpriseDB.com > >> > singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com > >> > > >> > EnterpriseDB http://www.enterprisedb.com > >> > > >> > Mail sent from my BlackLaptop device > >> > > > > > > > > > -- > > gurjeet[.singh]@EnterpriseDB.com > > singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com > > > > EnterpriseDB http://www.enterprisedb.com > > > > Mail sent from my BlackLaptop device > > > -- gurjeet[.singh]@EnterpriseDB.com singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com Mail sent from my BlackLaptop device ------=_Part_50748_31507576.1231232665556 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline I took your cue, and have formulated this solution for 8.3.1 :

create or replace function unknown2text(unknown) returns text as
$$ begin return text($1::char); end $$ language plpgsql;

drop cast (unknown as text);

create cast (unknown as text) with function unknown2text( unknown ) as implicit;

select '' union all select * from (select '' ) as s;

Thanks for your help Pavel.

Best regards,

PS: I was getting the same error as yours (stack depth) in EDB version 8.3.0.12, so I had to use the following code for unknown2text:

return charin( unknownout($1) );

It works for PG 8.3.1 too.

On Tue, Jan 6, 2009 at 12:15 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
2009/1/6 Gurjeet Singh <singh.gurjeet@gmail.com>:
> As I mentioned, we cannot change the query, so adding casts to the query is
> not an option. I was looking for something external to the query, like a
> CREATE CAST command that'd resolve the issue.

I am sorry, I blind - I tested casting on 8.3.0 and it doesn't work
(but I am have old 8.3)
postgres=# create function unknown2text(unknown) returns text as
$$select $1::text$$ language sql;
CREATE FUNCTION
postgres=# create cast(unknown as text) with function
unknown2text(unknown) as implicit;
CREATE CAST
postgres=# select '' union all select * from (select '' ) as s;
ERROR:  stack depth limit exceeded
HINT:  Increase the configuration parameter "max_stack_depth", after
ensuring the platform's stack depth limit is adequate.
CONTEXT:  SQL function "unknown2text" during startup
SQL function "unknown2text" statement 1
SQL function "unknown2text" statement 1
SQL function "unknown2text" statement 1
SQL function "unknown2text" statement 1
SQL function "unknown2text" statement 1

It working on 8.4

postgres=# create cast (unknown as text) with inout as implicit;
CREATE CAST
postgres=# select '' union all select * from (select '' ) as s;
 ?column?
----------


(2 rows)

regards
Pavel Stehule


>
> Best regards,


>
> On Tue, Jan 6, 2009 at 12:00 PM, Pavel Stehule <pavel.stehule@gmail.com>
> wrote:
>>
>> Hello
>>
>> 2009/1/6 Gurjeet Singh <singh.gurjeet@gmail.com>:
>> > Q1: select '' union all select ''
>> > Q2: select '' union all select * from (select '' ) as s
>> >
>> > version: PostgreSQL 8.3.1, compiled by Visual C++ build 1400
>> >
>> > Hi All,
>> >
>> >     Q1 works just fine, but Q2 fails with:
>> >
>> > ERROR:  failed to find conversion function from "unknown" to text
>> >
>> >     Q2 is a generalization of a huge query we are facing, which we
>> > cannot
>> > modify. I don't think this is a 'removed-casts' problem generally faced
>> > in
>> > 8.3, but I may be wrong. Will adding some cast resolve this?
>>
>> yes
>>
>> postgres=#  select '' union all select * from (select ''::text ) as s;
>>  ?column?
>> ----------
>>
>>
>> (2 rows)
>>
>> regards
>> Pavel Stehule
>>
>> >
>> > Best regards,
>> > --
>> > gurjeet[.singh]@EnterpriseDB.com
>> > singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
>> >
>> > EnterpriseDB      http://www.enterprisedb.com
>> >
>> > Mail sent from my BlackLaptop device
>> >
>
>
>
> --
> gurjeet[.singh]@EnterpriseDB.com
> singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
>
> EnterpriseDB      http://www.enterprisedb.com
>
> Mail sent from my BlackLaptop device
>



--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB      http://www.enterprisedb.com

Mail sent from my BlackLaptop device
------=_Part_50748_31507576.1231232665556-- From scott.marlowe@gmail.com Wed Jun 10 16:29:07 2026 Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 58CF96321AF; Tue, 6 Jan 2009 05:13:19 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 07968-03; Tue, 6 Jan 2009 05:13:10 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.180]) by mail.postgresql.org (Postfix) with ESMTP id 9544763228A; Tue, 6 Jan 2009 05:13:08 -0400 (AST) Received: by wa-out-1112.google.com with SMTP id j4so4309746wah.19 for ; Tue, 06 Jan 2009 01:13:05 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:cc:in-reply-to:mime-version:content-type :content-transfer-encoding:content-disposition:references; bh=HB/e3nyTPOYjUfYfMs8FsWTxLJDJ33xMSf3YNX3Tbbo=; b=ahKpiY8mddi2HoSZTOu/OiMIOekZabL6WSL0fCguOUU9N6F1HkCjxjfXDqTbNucbT+ ClhhYCsJXj1OOWS/yaXGHiI7x2ihtHqiQtKu4WVpr1is0ovYyo+7xWXxqTJWVbpFUyD4 0BsR1pWNjFAMkLFmyoraUKuLiQkJTy6SAWAds= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:content-transfer-encoding:content-disposition :references; b=MRZnY+5JCO0fgq308BrA/xdxZRQiq4UZD9622Y4X5lC84ZKPMhn3QCorX+yHHXDo1c iz3UwOJJzEkKdoBBFSgKnO4xGNEnACqFRCEmvcgtheKn9Tj4K5PvpnORzKEcOlZiQ0jt jx8lP1glzDHGrjLtduiIrvIxAPTJk4bLiKRls= Received: by 10.114.196.13 with SMTP id t13mr14392418waf.82.1231233185683; Tue, 06 Jan 2009 01:13:05 -0800 (PST) Received: by 10.115.108.3 with HTTP; Tue, 6 Jan 2009 01:13:05 -0800 (PST) Message-ID: Date: Tue, 6 Jan 2009 02:13:05 -0700 From: "Scott Marlowe" To: "Gurjeet Singh" Subject: Re: [HACKERS] ERROR: failed to find conversion function from "unknown" to text Cc: "Pavel Stehule" , "PGSQL General" , "PGSQL Hackers" In-Reply-To: <65937bea0901060104n399cdec8ye5e2b8e247e5139a@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <65937bea0901052223w162a977dyeaaf888a854f7324@mail.gmail.com> <162867790901052230l25b10f08qa35f56856b9cc4c6@mail.gmail.com> <65937bea0901052237j50657573i8c980f9f5f4814d5@mail.gmail.com> <162867790901052245l4e4fa9b2ydc03781b45b13276@mail.gmail.com> <65937bea0901060104n399cdec8ye5e2b8e247e5139a@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200901/72 X-Sequence-Number: 142157 On Tue, Jan 6, 2009 at 2:04 AM, Gurjeet Singh wrote: > I took your cue, and have formulated this solution for 8.3.1 : Is there a good reason you're running against a db version with known bugs instead of 8.3.5? Seriously, it's an easy upgrade and running a version missing over a year of updates is not a best practice. From singh.gurjeet@gmail.com Wed Jun 10 16:29:07 2026 Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 9A6A66321D6; Tue, 6 Jan 2009 05:24:56 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 10118-08; Tue, 6 Jan 2009 05:24:47 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from rv-out-0506.google.com (rv-out-0506.google.com [209.85.198.230]) by mail.postgresql.org (Postfix) with ESMTP id 7718B62E68C; Tue, 6 Jan 2009 05:24:46 -0400 (AST) Received: by rv-out-0506.google.com with SMTP id b25so9063933rvf.43 for ; Tue, 06 Jan 2009 01:24:43 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:cc:in-reply-to:mime-version:content-type:references; bh=wlsyNJ0wbh5Gi+gWOc26LCEHeYXE9m7IzXa1ipKm83U=; b=ToBn8FSbuC0dq/I6SLoS0nkrSmw54ezXTo3sr/lOfnTMrRJsLZKt0XkjE+1kJodjCY tAdMcWM/6eyKuL2B018aTW6joEo7nSM/G1Sk5cyAP983BLg+MuaBtToEwDHzfS/Bnoec pxMnlhSbfda9tDgOlKoY5unoQEQgCadtmVY3k= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:references; b=oVGdC2nvwSNB1WkV/2WYQnwjHwQr0dHqr4mIuxSjsy5SkpBzF814TT0+L+iNGPfsjj ZUPl2XKu7up5GYAwMSElCgedKN/Qb5zkO7KWCp9sbq/O/rzcZpKC4aPklhZhyrWNiOt/ JqFSJIUtuiZ4L/rkQRoSbS6P7lck0M5fOiwhM= Received: by 10.141.29.20 with SMTP id g20mr10862329rvj.156.1231233883001; Tue, 06 Jan 2009 01:24:43 -0800 (PST) Received: by 10.141.98.4 with HTTP; Tue, 6 Jan 2009 01:24:42 -0800 (PST) Message-ID: <65937bea0901060124m1a6f389en21d38cc857f72b12@mail.gmail.com> Date: Tue, 6 Jan 2009 14:54:42 +0530 From: "Gurjeet Singh" To: "Scott Marlowe" Subject: Re: [HACKERS] ERROR: failed to find conversion function from "unknown" to text Cc: "Pavel Stehule" , "PGSQL General" , "PGSQL Hackers" In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_50804_6326977.1231233882998" References: <65937bea0901052223w162a977dyeaaf888a854f7324@mail.gmail.com> <162867790901052230l25b10f08qa35f56856b9cc4c6@mail.gmail.com> <65937bea0901052237j50657573i8c980f9f5f4814d5@mail.gmail.com> <162867790901052245l4e4fa9b2ydc03781b45b13276@mail.gmail.com> <65937bea0901060104n399cdec8ye5e2b8e247e5139a@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200901/73 X-Sequence-Number: 142158 ------=_Part_50804_6326977.1231233882998 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On Tue, Jan 6, 2009 at 2:43 PM, Scott Marlowe wrote: > On Tue, Jan 6, 2009 at 2:04 AM, Gurjeet Singh > wrote: > > I took your cue, and have formulated this solution for 8.3.1 : > > Is there a good reason you're running against a db version with known > bugs instead of 8.3.5? Seriously, it's an easy upgrade and running a > version missing over a year of updates is not a best practice. > That's just a development instance that I have kept for long; actual issue was on EDB 8.3.0.12, which the customer is using. As noted in the PS of previous mail, the solution that worked for PG 8.3.1 didn't work on EDB 8.3.0.12, so had to come up with a different code for that! Best regards, -- gurjeet[.singh]@EnterpriseDB.com singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com Mail sent from my BlackLaptop device ------=_Part_50804_6326977.1231233882998 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On Tue, Jan 6, 2009 at 2:43 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
On Tue, Jan 6, 2009 at 2:04 AM, Gurjeet Singh <singh.gurjeet@gmail.com> wrote:
> I took your cue, and have formulated this solution for 8.3.1 :

Is there a good reason you're running against a db version with known
bugs instead of 8.3.5?  Seriously, it's an easy upgrade and running a
version missing over a year of updates is not a best practice.

That's just a development instance that I have kept for long; actual issue was on EDB 8.3.0.12, which the customer is using. As noted in the PS of previous mail, the solution that worked for PG 8.3.1 didn't work on EDB 8.3.0.12, so had to come up with a different code for that!

Best regards,
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB      http://www.enterprisedb.com

Mail sent from my BlackLaptop device
------=_Part_50804_6326977.1231233882998-- From scott.marlowe@gmail.com Wed Jun 10 16:29:07 2026 Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 02DB4632206; Tue, 6 Jan 2009 05:29:12 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 11366-09; Tue, 6 Jan 2009 05:29:03 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.176]) by mail.postgresql.org (Postfix) with ESMTP id 964F46321FA; Tue, 6 Jan 2009 05:29:02 -0400 (AST) Received: by wa-out-1112.google.com with SMTP id j4so4312285wah.19 for ; Tue, 06 Jan 2009 01:29:00 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:cc:in-reply-to:mime-version:content-type :content-transfer-encoding:content-disposition:references; bh=1XVdB3lxdkb/ZFf/tJU4G4uTHiTmzBeYqbbX2+kXkZc=; b=bDimhHRGFIE1LGSJlQsmtPC3Z8hMx7j7ZjurUddKEjbeBiBK/0+bM416zBfeKo0JhM NLfNf7LDZgUopAlIDR2dMNzYz/uhFpq0obN+CDyGPleeXMrS/FPc1BYJ8aZYkZWK3Fci HlDnAR7QiBIbFMdpoKMXFJPTiUlU0ZwjwTV9w= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:content-transfer-encoding:content-disposition :references; b=WG/DsBgqA+/KazmWUSIkl1MQ9ELQbNv7ofHG7PoHbiwfk93NFOIKLTUMNkS9EK5BDA y5Gri0CHgeC6CcrOVH/Cr+p509N8allYhD7Fm3ModMVPw0vY0Lo2aja8jYFGEW5Fqd+i KnIRHykRS/MAwcXPleZG/RyZvG6/eVP8yG1nw= Received: by 10.114.24.1 with SMTP id 1mr14381942wax.179.1231234140112; Tue, 06 Jan 2009 01:29:00 -0800 (PST) Received: by 10.115.108.3 with HTTP; Tue, 6 Jan 2009 01:29:00 -0800 (PST) Message-ID: Date: Tue, 6 Jan 2009 02:29:00 -0700 From: "Scott Marlowe" To: "Gurjeet Singh" Subject: Re: [HACKERS] ERROR: failed to find conversion function from "unknown" to text Cc: "Pavel Stehule" , "PGSQL General" , "PGSQL Hackers" In-Reply-To: <65937bea0901060124m1a6f389en21d38cc857f72b12@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <65937bea0901052223w162a977dyeaaf888a854f7324@mail.gmail.com> <162867790901052230l25b10f08qa35f56856b9cc4c6@mail.gmail.com> <65937bea0901052237j50657573i8c980f9f5f4814d5@mail.gmail.com> <162867790901052245l4e4fa9b2ydc03781b45b13276@mail.gmail.com> <65937bea0901060104n399cdec8ye5e2b8e247e5139a@mail.gmail.com> <65937bea0901060124m1a6f389en21d38cc857f72b12@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200901/75 X-Sequence-Number: 142160 On Tue, Jan 6, 2009 at 2:24 AM, Gurjeet Singh wrote: > On Tue, Jan 6, 2009 at 2:43 PM, Scott Marlowe > wrote: >> >> On Tue, Jan 6, 2009 at 2:04 AM, Gurjeet Singh >> wrote: >> > I took your cue, and have formulated this solution for 8.3.1 : >> >> Is there a good reason you're running against a db version with known >> bugs instead of 8.3.5? Seriously, it's an easy upgrade and running a >> version missing over a year of updates is not a best practice. > > That's just a development instance that I have kept for long; actual issue > was on EDB 8.3.0.12, which the customer is using. As noted in the PS of > previous mail, the solution that worked for PG 8.3.1 didn't work on EDB > 8.3.0.12, so had to come up with a different code for that! Ahh, ok. I was just worried you were ignoring updates. I don't know anything about the numbering scheme for EDB. What does 8.3.0.12 translate to in regular pgsql versions? From tgl@sss.pgh.pa.us Wed Jun 10 16:29:07 2026 Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 757F563221E; Tue, 6 Jan 2009 09:01:51 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 50945-08; Tue, 6 Jan 2009 09:01:45 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id 94630632223; Tue, 6 Jan 2009 09:01:44 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id n06D1d8l007589; Tue, 6 Jan 2009 08:01:39 -0500 (EST) To: "Gurjeet Singh" cc: "Pavel Stehule" , "PGSQL General" , "PGSQL Hackers" Subject: Re: [HACKERS] ERROR: failed to find conversion function from "unknown" to text In-reply-to: <65937bea0901060104n399cdec8ye5e2b8e247e5139a@mail.gmail.com> References: <65937bea0901052223w162a977dyeaaf888a854f7324@mail.gmail.com> <162867790901052230l25b10f08qa35f56856b9cc4c6@mail.gmail.com> <65937bea0901052237j50657573i8c980f9f5f4814d5@mail.gmail.com> <162867790901052245l4e4fa9b2ydc03781b45b13276@mail.gmail.com> <65937bea0901060104n399cdec8ye5e2b8e247e5139a@mail.gmail.com> Comments: In-reply-to "Gurjeet Singh" message dated "Tue, 06 Jan 2009 14:34:25 +0530" Date: Tue, 06 Jan 2009 08:01:39 -0500 Message-ID: <7588.1231246899@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200901/77 X-Sequence-Number: 142162 "Gurjeet Singh" writes: > create cast (unknown as text) with function unknown2text( unknown ) as > implicit; This is a horrendously bad idea; it will bite your *ss sooner or later, probably sooner. regards, tom lane From singh.gurjeet@gmail.com Wed Jun 10 16:29:07 2026 Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 20E5663225E; Tue, 6 Jan 2009 09:38:35 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 57053-08; Tue, 6 Jan 2009 09:38:26 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from rv-out-0506.google.com (rv-out-0506.google.com [209.85.198.230]) by mail.postgresql.org (Postfix) with ESMTP id AB65A63227F; Tue, 6 Jan 2009 09:38:25 -0400 (AST) Received: by rv-out-0506.google.com with SMTP id b25so9150484rvf.43 for ; Tue, 06 Jan 2009 05:38:22 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:cc:in-reply-to:mime-version:content-type:references; bh=SAluc4eTVgQBfYMkDMP3y5g5vNzSNOHtgCNPkABCbLE=; b=EPsiKLmxk9PxArllTLxaHpqrxHGHuZjx5mxBpHZFA2C9SZAQTz4enROSXaFU/aw3mX oaJZM3zm4lLijDLZpL8nuEAXZN6u5mOb4P8d9Xrrv2AAqAxkZLVKrXvBWaGQ+2U1vaMh wZnWbuQhIcNwaqrVaZE1izHsZhYrOKU+s3jUg= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:references; b=HRR1VuREZBcA408UHWLe9Ph+u/qqpOQY48VXg7a6ZdHvtBkOKnQJZ0bjj2Mce7WHdW D9950SPWwM/K/ITMwITWb4XF2qzj9k+OfxEv/lWn4ttAkoWCZs7jcd2dR2OrktFwo3Nr 8VQQzDyO9Ms+EMypGOvcCH9l1dERmF2okBziU= Received: by 10.141.197.21 with SMTP id z21mr10939564rvp.267.1231249101953; Tue, 06 Jan 2009 05:38:21 -0800 (PST) Received: by 10.141.98.4 with HTTP; Tue, 6 Jan 2009 05:38:21 -0800 (PST) Message-ID: <65937bea0901060538n13099005p62f05ef60ed352bd@mail.gmail.com> Date: Tue, 6 Jan 2009 19:08:21 +0530 From: "Gurjeet Singh" To: "Tom Lane" Subject: Re: [HACKERS] ERROR: failed to find conversion function from "unknown" to text Cc: "Pavel Stehule" , "PGSQL General" , "PGSQL Hackers" In-Reply-To: <7588.1231246899@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_53290_13969257.1231249101939" References: <65937bea0901052223w162a977dyeaaf888a854f7324@mail.gmail.com> <162867790901052230l25b10f08qa35f56856b9cc4c6@mail.gmail.com> <65937bea0901052237j50657573i8c980f9f5f4814d5@mail.gmail.com> <162867790901052245l4e4fa9b2ydc03781b45b13276@mail.gmail.com> <65937bea0901060104n399cdec8ye5e2b8e247e5139a@mail.gmail.com> <7588.1231246899@sss.pgh.pa.us> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200901/78 X-Sequence-Number: 142163 ------=_Part_53290_13969257.1231249101939 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On Tue, Jan 6, 2009 at 6:31 PM, Tom Lane wrote: > "Gurjeet Singh" writes: > > create cast (unknown as text) with function unknown2text( unknown ) as > > implicit; > > This is a horrendously bad idea; it will bite your *ss sooner or later, > probably sooner. > > regards, tom lane > I guessed so, but couldn't figure out exactly how! That's why I have suggested this as a temp solution until we confirmed this with someone more knowledgeable. Can you please let us know how this would be problematic? And can you suggest a better solution? Thanks and best regards, -- gurjeet[.singh]@EnterpriseDB.com singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com Mail sent from my BlackLaptop device ------=_Part_53290_13969257.1231249101939 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On Tue, Jan 6, 2009 at 6:31 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"Gurjeet Singh" <singh.gurjeet@gmail.com> writes:
> create cast (unknown as text) with function unknown2text( unknown ) as
> implicit;

This is a horrendously bad idea; it will bite your *ss sooner or later,
probably sooner.

                       regards, tom lane

I guessed so, but couldn't figure out exactly how! That's why I have suggested this as a temp solution until we confirmed this with someone more knowledgeable.

Can you please let us know how this would be problematic? And can you suggest a better solution?

Thanks and best regards,
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB      http://www.enterprisedb.com

Mail sent from my BlackLaptop device
------=_Part_53290_13969257.1231249101939-- From tgl@sss.pgh.pa.us Wed Jun 10 16:29:07 2026 Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 427F9632235; Tue, 6 Jan 2009 09:48:12 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 59237-04; Tue, 6 Jan 2009 09:48:08 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id 39746632226; Tue, 6 Jan 2009 09:48:09 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id n06Dm6G3008184; Tue, 6 Jan 2009 08:48:06 -0500 (EST) To: "Gurjeet Singh" cc: "Pavel Stehule" , "PGSQL General" , "PGSQL Hackers" Subject: Re: [HACKERS] ERROR: failed to find conversion function from "unknown" to text In-reply-to: <65937bea0901060538n13099005p62f05ef60ed352bd@mail.gmail.com> References: <65937bea0901052223w162a977dyeaaf888a854f7324@mail.gmail.com> <162867790901052230l25b10f08qa35f56856b9cc4c6@mail.gmail.com> <65937bea0901052237j50657573i8c980f9f5f4814d5@mail.gmail.com> <162867790901052245l4e4fa9b2ydc03781b45b13276@mail.gmail.com> <65937bea0901060104n399cdec8ye5e2b8e247e5139a@mail.gmail.com> <7588.1231246899@sss.pgh.pa.us> <65937bea0901060538n13099005p62f05ef60ed352bd@mail.gmail.com> Comments: In-reply-to "Gurjeet Singh" message dated "Tue, 06 Jan 2009 19:08:21 +0530" Date: Tue, 06 Jan 2009 08:48:06 -0500 Message-ID: <8183.1231249686@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200901/79 X-Sequence-Number: 142164 "Gurjeet Singh" writes: >> This is a horrendously bad idea; it will bite your *ss sooner or later, >> probably sooner. > Can you please let us know how this would be problematic? The point is that it's going to have unknown, untested effects on the default coercion rules, possibly leading to silent changes in the behavior of queries that used to work. If you'd rather retest every one of your other queries than fix this one, then go ahead. regards, tom lane From Rob.Richardson@rad-con.com Wed Jun 10 16:29:08 2026 Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 209C96322B1 for ; Tue, 6 Jan 2009 10:07:58 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 63843-05 for ; Tue, 6 Jan 2009 10:07:52 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from svr-grp1-mail.brickcitywebs.net (svr-grp1-mail.brickcitywebs.net [209.208.74.172]) by mail.postgresql.org (Postfix) with ESMTP id 021EE63225E for ; Tue, 6 Jan 2009 10:07:51 -0400 (AST) Received: (qmail 6635 invoked from network); 6 Jan 2009 07:47:58 -0500 Received: from svr-grp1-mail.brickcitywebs.net (HELO outlook.rad-con.com) (12.49.144.162) by svr-grp1-mail.brickcitywebs.net with SMTP; 6 Jan 2009 07:47:57 -0500 Content-class: urn:content-classes:message Subject: Is there a way to do an exact-match search on this list? Date: Tue, 6 Jan 2009 09:06:43 -0500 MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable Message-ID: <04A6DB42D2BA534FAC77B90562A6A03DBF2611@server.rad-con.local> In-reply-to: <8183.1231249686@sss.pgh.pa.us> X-MimeOLE: Produced By Microsoft Exchange V6.5 X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Is there a way to do an exact-match search on this list? thread-index: AclwBwC2JJlw/sWkTJS1aik9TD5OngAAKXvA References: <65937bea0901052223w162a977dyeaaf888a854f7324@mail.gmail.com> <162867790901052230l25b10f08qa35f56856b9cc4c6@mail.gmail.com> <65937bea0901052237j50657573i8c980f9f5f4814d5@mail.gmail.com> <162867790901052245l4e4fa9b2ydc03781b45b13276@mail.gmail.com> <65937bea0901060104n399cdec8ye5e2b8e247e5139a@mail.gmail.com> <7588.1231246899@sss.pgh.pa.us> <65937bea0901060538n13099005p62f05ef60ed352bd@mail.gmail.com> <8183.1231249686@sss.pgh.pa.us> From: "Rob Richardson" To: "PGSQL General" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200901/80 X-Sequence-Number: 142165 Greetings! I just tried to do a search in the archives of this list for ".Net provider". The search returned results contained "provided" and "providing". Is there a way to make sure that my searches return only messages containing strings that exactly match what I'm looking for? Thank you very much. RobR From sam@samason.me.uk Wed Jun 10 16:29:08 2026 Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 4956E6322A6 for ; Tue, 6 Jan 2009 11:11:33 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 76987-02 for ; Tue, 6 Jan 2009 11:11:24 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from frubble.xen.chris-lamb.co.uk (frubble.xen.chris-lamb.co.uk [89.16.166.12]) by mail.postgresql.org (Postfix) with ESMTP id BD95B632223 for ; Tue, 6 Jan 2009 11:11:24 -0400 (AST) Received: from sam by frubble.xen.chris-lamb.co.uk with local (Exim 4.63) (envelope-from ) id 1LKDaQ-0003Yf-5r for pgsql-general@postgresql.org; Tue, 06 Jan 2009 15:11:18 +0000 Date: Tue, 6 Jan 2009 15:11:18 +0000 From: Sam Mason To: pgsql-general@postgresql.org Subject: Re: Is there a way to do an exact-match search on this list? Message-ID: <20090106151118.GX3008@frubble.xen.chris-lamb.co.uk> References: <04A6DB42D2BA534FAC77B90562A6A03DBF2611@server.rad-con.local> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <04A6DB42D2BA534FAC77B90562A6A03DBF2611@server.rad-con.local> User-Agent: Mutt/1.5.13 (2006-08-11) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200901/82 X-Sequence-Number: 142167 On Tue, Jan 06, 2009 at 09:06:43AM -0500, Rob Richardson wrote: > I just tried to do a search in the archives of this list for ".Net > provider". The search returned results contained "provided" and > "providing". Is there a way to make sure that my searches return only > messages containing strings that exactly match what I'm looking for? would google do what you want? http://www.google.com/search?q=".Net+provider"+site:archives.postgresql.org/pgsql-general a few useful variations: ``intext:".net provider" site:archives.postgresql.org'' ``intitle:".net provider" site:archives.postgresql.org'' gleaned from: http://www.googleguide.com/advanced_operators.html Sam From singh.gurjeet@gmail.com Wed Jun 10 16:29:09 2026 Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id E954F63233B; Tue, 6 Jan 2009 13:46:10 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 09234-04-2; Tue, 6 Jan 2009 13:46:02 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from rv-out-0506.google.com (rv-out-0506.google.com [209.85.198.236]) by mail.postgresql.org (Postfix) with ESMTP id C8E556322B9; Tue, 6 Jan 2009 13:44:01 -0400 (AST) Received: by rv-out-0506.google.com with SMTP id b25so9255080rvf.43 for ; Tue, 06 Jan 2009 09:43:59 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:cc:in-reply-to:mime-version:content-type:references; bh=A0oMOyNe/s8w+22fUzIF9P41Tyasrd0kNL4MxoSC8BU=; b=bz2EyEz6X1AIgmKpMNlwDfitYkdZ3WTkSNt1BjPWCZ0HDlI8YSXGeK0iZ0ShpTtf9A WResfUf3J7/JXHZXNYg1RrNcd6vkTZAfl3K5+zzLFFa0LSJ2r8BtDpn8CmQRU3EH3Ixq HC37J86Rgldgfjig1Joabshd+/nKaCRDuDyiw= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:references; b=qUPZtQO5fc0r5u5nHyY5sxfE2RYP+5ZKQpxZpNyKOP1Q+kPR1KKcAh5LD6n82Es8jT lueUlaA0UqcEqY/k/t1h3I00+TxCMW40ThmSdoNHhqyxMyoSpWsGaz1uxVU05tb2JVUE np9D6LEBiAVGm1SIV61ZySrw7WMxiYoqsKmnw= Received: by 10.141.89.13 with SMTP id r13mr11043136rvl.76.1231263839583; Tue, 06 Jan 2009 09:43:59 -0800 (PST) Received: by 10.141.98.4 with HTTP; Tue, 6 Jan 2009 09:43:59 -0800 (PST) Message-ID: <65937bea0901060943w52c5473ucee1a90f36842d4c@mail.gmail.com> Date: Tue, 6 Jan 2009 23:13:59 +0530 From: "Gurjeet Singh" To: "Tom Lane" Subject: Re: [HACKERS] ERROR: failed to find conversion function from "unknown" to text Cc: "Pavel Stehule" , "PGSQL General" , "PGSQL Hackers" In-Reply-To: <8183.1231249686@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_56878_17175816.1231263839573" References: <65937bea0901052223w162a977dyeaaf888a854f7324@mail.gmail.com> <162867790901052230l25b10f08qa35f56856b9cc4c6@mail.gmail.com> <65937bea0901052237j50657573i8c980f9f5f4814d5@mail.gmail.com> <162867790901052245l4e4fa9b2ydc03781b45b13276@mail.gmail.com> <65937bea0901060104n399cdec8ye5e2b8e247e5139a@mail.gmail.com> <7588.1231246899@sss.pgh.pa.us> <65937bea0901060538n13099005p62f05ef60ed352bd@mail.gmail.com> <8183.1231249686@sss.pgh.pa.us> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200901/93 X-Sequence-Number: 142178 ------=_Part_56878_17175816.1231263839573 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On Tue, Jan 6, 2009 at 7:18 PM, Tom Lane wrote: > "Gurjeet Singh" writes: > >> This is a horrendously bad idea; it will bite your *ss sooner or later, > >> probably sooner. > > > Can you please let us know how this would be problematic? > > The point is that it's going to have unknown, untested effects on the > default coercion rules, possibly leading to silent changes in the > behavior of queries that used to work. If you'd rather retest every one > of your other queries than fix this one, then go ahead. > > Changing the query is an option not given to us. It is being migrated from a BigDB. I was working on these solutions assuming that these are workarounds to a bug. But from your mails, it seems that it is an expected behaviour; is it? If we consider the second branch of UNION ALL of both the queries above, if "select '' " yields a text column, then so should a "select * from (select '')". Its not exactly a bug, but sure is a problem that we should try to resolve. Thanks and best regards, -- gurjeet[.singh]@EnterpriseDB.com singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com Mail sent from my BlackLaptop device ------=_Part_56878_17175816.1231263839573 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On Tue, Jan 6, 2009 at 7:18 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"Gurjeet Singh" <singh.gurjeet@gmail.com> writes:
>> This is a horrendously bad idea; it will bite your *ss sooner or later,
>> probably sooner.

> Can you please let us know how this would be problematic?

The point is that it's going to have unknown, untested effects on the
default coercion rules, possibly leading to silent changes in the
behavior of queries that used to work.  If you'd rather retest every one
of your other queries than fix this one, then go ahead.


Changing the query is an option not given to us. It is being migrated from a BigDB.

I was working on these solutions assuming that these are workarounds to a bug. But from your mails, it seems that it is an expected behaviour; is it?

If we consider the second branch of UNION ALL of both the queries above, if "select '' " yields a text column, then so should a "select * from (select '')".

Its not exactly a bug, but sure is a problem that we should try to resolve.

Thanks and best regards,
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB      http://www.enterprisedb.com

Mail sent from my BlackLaptop device
------=_Part_56878_17175816.1231263839573-- From kleptog@svana.org Wed Jun 10 16:29:10 2026 Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id A8DFB6321ED; Tue, 6 Jan 2009 18:14:03 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 65097-05; Tue, 6 Jan 2009 18:13:57 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from svana.org (svana.org [203.190.233.187]) by mail.postgresql.org (Postfix) with ESMTP id 6317C632186; Tue, 6 Jan 2009 18:13:55 -0400 (AST) Received: from kleptog by svana.org with local (Exim 4.63) (envelope-from ) id 1LKKBB-0008FN-2m; Wed, 07 Jan 2009 09:13:41 +1100 Date: Tue, 6 Jan 2009 23:13:41 +0100 From: Martijn van Oosterhout To: Gurjeet Singh Cc: Tom Lane , Pavel Stehule , PGSQL General , PGSQL Hackers Subject: Re: [HACKERS] ERROR: failed to find conversion function from "unknown" to text Message-ID: <20090106221340.GA25467@svana.org> Reply-To: Martijn van Oosterhout Mail-Followup-To: Gurjeet Singh , Tom Lane , Pavel Stehule , PGSQL General , PGSQL Hackers References: <65937bea0901052223w162a977dyeaaf888a854f7324@mail.gmail.com> <162867790901052230l25b10f08qa35f56856b9cc4c6@mail.gmail.com> <65937bea0901052237j50657573i8c980f9f5f4814d5@mail.gmail.com> <162867790901052245l4e4fa9b2ydc03781b45b13276@mail.gmail.com> <65937bea0901060104n399cdec8ye5e2b8e247e5139a@mail.gmail.com> <7588.1231246899@sss.pgh.pa.us> <65937bea0901060538n13099005p62f05ef60ed352bd@mail.gmail.com> <8183.1231249686@sss.pgh.pa.us> <65937bea0901060943w52c5473ucee1a90f36842d4c@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="tThc/1wpZn/ma/RB" Content-Disposition: inline In-Reply-To: <65937bea0901060943w52c5473ucee1a90f36842d4c@mail.gmail.com> X-PGP-Key-ID: Length=1024; ID=0x6F0B424C X-PGP-Key-Fingerprint: 34A8 8EAC F902 8C8D CA72 DCC7 201E DB34 6F0B 424C User-Agent: Mutt/1.5.13 (2006-08-11) X-SA-Exim-Connect-IP: X-SA-Exim-Mail-From: kleptog@svana.org X-SA-Exim-Scanned: No (on svana.org); SAEximRunCond expanded to false X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200901/115 X-Sequence-Number: 142200 --tThc/1wpZn/ma/RB Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable On Tue, Jan 06, 2009 at 11:13:59PM +0530, Gurjeet Singh wrote: > If we consider the second branch of UNION ALL of both the queries above, = if > "select '' " yields a text column, then so should a "select * from (select > '')". The problem is ofcourse that "select ''" doesn't produce a text column in postgres. This generally works fine, except in the case of UNION where none of the branches provide the necessary type info. Have a nice day, --=20 Martijn van Oosterhout http://svana.org/kleptog/ > Please line up in a tree and maintain the heap invariant while=20 > boarding. Thank you for flying nlogn airlines. --tThc/1wpZn/ma/RB Content-Type: application/pgp-signature; name="signature.asc" Content-Description: Digital signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFJY9eUIB7bNG8LQkwRAoBKAJ9uSJAVS/sAZHOQfGZDz0a7YSgeZgCgj4jr gNB+eQ6s8Mz3TGihA95GOwA= =EVuu -----END PGP SIGNATURE----- --tThc/1wpZn/ma/RB--