Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.92) (envelope-from ) id 1j6Ztb-00066o-A5 for pgsql-bugs@arkaria.postgresql.org; Tue, 25 Feb 2020 13:05:08 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1j6Zta-0004Pe-1g for pgsql-bugs@arkaria.postgresql.org; Tue, 25 Feb 2020 13:05:06 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1j6ZlM-0005HN-O0 for pgsql-bugs@lists.postgresql.org; Tue, 25 Feb 2020 12:56:36 +0000 Received: from mout-p-101.mailbox.org ([80.241.56.151]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_CHACHA20_POLY1305:256) (Exim 4.92) (envelope-from ) id 1j6ZlH-0002tt-3E for pgsql-bugs@postgresql.org; Tue, 25 Feb 2020 12:56:35 +0000 Received: from smtp2.mailbox.org (smtp2.mailbox.org [IPv6:2001:67c:2050:105:465:1:2:0]) (using TLSv1.2 with cipher ECDHE-RSA-CHACHA20-POLY1305 (256/256 bits)) (No client certificate requested) by mout-p-101.mailbox.org (Postfix) with ESMTPS id 48Rf7s6fkZzKn42 for ; Tue, 25 Feb 2020 13:56:25 +0100 (CET) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=mailbox.org; h= content-transfer-encoding:content-type:content-type:mime-version :subject:subject:message-id:reply-to:from:from:date:date :received; s=mail20150812; t=1582635383; bh=66Wbw/C4bEbiP360cYpi Y6lsjoEECaWmOfUZx7p5Ow0=; b=MSYoPxDKHvNzusGtxUGCHN44zkIbCgRmVEwd UQI47Naz66UP4uYKCVdk3clFS58BrzG6AAA5YGv1x0cJ+IeEPMyMHADBpaXLFhuA fNkiz4xnVRnclNgq1Gy1DCNN/kAMfavW/L+zyscAVhqNFxp78EqZT5yi8WcimugD mi9uShRcx+LStP2w/e1Kgw1x6ZWRPuJlTRM5mBhYHtOvXeK7CqYum8C4oQAFATHB N8VtSJBzGIl/7/gPxu6kyxfpruWSfft/ReiXP/FN9895ELwYoGVR9SoVktFzoYIV N4MwUDNwapOoUKE+n3LV8HV0JC3b1VsFFzipve7qOyIySbb1bA== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=mailbox.org; s=mail20150812; t=1582635384; h=from:from:reply-to:reply-to:subject:subject:date:date: message-id:message-id:to:to:cc:mime-version:mime-version: content-type:content-type: content-transfer-encoding:content-transfer-encoding; bh=oPqdoP4KYPJfqTBVzjSVkreqd05rz/mNGVvOnVbc8Jo=; b=rO8hXtd5cnnuXxS6TipnLrJT9E5MFVDEwQLzzXnBBIktEVpkimv/nlYT9Fay5o7Imvqyyb Oj0gadoo9QQ7+rrr7O8MTDWVvkmY6bvVSQaX+YIYCF9/iavN9w3/4rhXeEIut7JDb3NZSr rS2Hw8WkSyGdanoaXs2qdwUshCG4wkbkiFi7GyOYbCUax5INblUk4x9ld5VpMTbMPCNece v3HFLAjm8+kN6HSMxQODJ6ISQ8cxgpf00e3tYBrgDBqedV3DSFLpftenDFc9IhAjGGzBKi W7MNMluVmbMMgIY4B1Mdosbh4NceV83rtXl33VT++dePceAlOmFP0O0a2BqRAg== X-Virus-Scanned: amavisd-new at heinlein-support.de Received: from smtp2.mailbox.org ([80.241.60.241]) by spamfilter05.heinlein-hosting.de (spamfilter05.heinlein-hosting.de [80.241.56.123]) (amavisd-new, port 10030) with ESMTP id sl-EPGIql5VB for ; Tue, 25 Feb 2020 13:56:23 +0100 (CET) Date: Tue, 25 Feb 2020 13:56:22 +0100 (CET) From: eli.mach@mailbox.org Reply-To: eli.mach@mailbox.org To: "pgsql-bugs@postgresql.org" Message-ID: <490036036.13996.1582635382921@office.mailbox.org> Subject: pretty_bool in pg_get_constraintdef has no effect since pg >= 9 MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit X-Priority: 3 Importance: Normal List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Hello, since postgres 9, `pg_get_constraintdef(cons.oid, pretty_bool)` does not work as expected. The result is always in "pretty"-format (with newlines), regardless of whether `pretty_bool` is true or false. Calling `pg_get_constraintdef(constraint_oid)` without `pretty_bool`, also returns "pretty"-format. Test with: ``` CREATE TABLE tbl ( id int NOT NULL DEFAULT 0 ,CHECK ( CASE WHEN id < 0 THEN false WHEN id >= 0 THEN true END ) ); SELECT cons.conname as name, pg_get_constraintdef(cons.oid, false) as src FROM pg_catalog.pg_constraint cons WHERE cons.conrelid IN (SELECT oid from pg_class WHERE relname='tbl') AND cons.contype = 'c'; ``` Result on postgres 8: ``` CHECK (CASE WHEN (id < 0) THEN false WHEN (id >= 0) THEN true ELSE NULL::boolean END) ``` Result on postgres >= 9: ``` CHECK ( CASE WHEN (id < 0) THEN false WHEN (id >= 0) THEN true ELSE NULL::boolean END) ``` I'm migrating from postgres 8 to 11 and a sqlalchemy script throws a warning "SAWarning: Could not parse CHECK constraint text" because there a no newlines expected in `re.match(r"^CHECK *\((.+)\)( NOT VALID)?$", src)`. Greetings, elim.