Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1q40US-0006Lc-7z for pgsql-general@arkaria.postgresql.org; Tue, 30 May 2023 14:38:24 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1q40UR-0005NC-3G for pgsql-general@arkaria.postgresql.org; Tue, 30 May 2023 14:38:23 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1q40UP-0005N3-FA for pgsql-general@lists.postgresql.org; Tue, 30 May 2023 14:38:22 +0000 Received: from out4-smtp.messagingengine.com ([66.111.4.28]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1q40UL-002iV9-J0 for pgsql-general@lists.postgresql.org; Tue, 30 May 2023 14:38:20 +0000 Received: from compute6.internal (compute6.nyi.internal [10.202.2.47]) by mailout.nyi.internal (Postfix) with ESMTP id 5C7FE5C00DC; Tue, 30 May 2023 10:38:16 -0400 (EDT) Received: from mailfrontend2 ([10.202.2.163]) by compute6.internal (MEProxy); Tue, 30 May 2023 10:38:16 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:content-transfer-encoding:content-type:content-type:date:date :from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:sender:subject:subject:to:to; s=fm3; t= 1685457496; x=1685543896; bh=FX6UwBc/o2uwCvf82rJqELAPXG24/RossDP HNuLMC1w=; b=px4C8CUdmrFCXgpbs8t6RSRGH/62Wlg9sSkxDtxXMuLAB/Q4/zP UYiRkJCWaT5B5AVN2C6aOlpJ+LYc+eZkuCJ/8qdDi6bEzjtm55xOs5N8B+wK5TqQ ULuR5Uf48qNX4DgD8defJttXllvLCAxQWn/WXXZHZtMbLG8Ncx23D3VHjcNU4Zol Elu8k/LlbAic2uc1nz04DqxNSLzzXp8omFNtDuVSGo1OgudfAeIv+kd984mZ8BqP cjjO6uutx7iHfvoVbl3PkSWVB9STsn8tmtw+ckrpGGdmsmJsT+hFgmSRVGYIlxE8 0KXGsfTOYtSCjW0IS4fAbOStBeOwWJ3RqEQ== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-transfer-encoding:content-type :content-type:date:date:feedback-id:feedback-id:from:from :in-reply-to:in-reply-to:message-id:mime-version:references :reply-to:sender:subject:subject:to:to:x-me-proxy:x-me-proxy :x-me-sender:x-me-sender:x-sasl-enc; s=fm1; t=1685457496; x= 1685543896; bh=FX6UwBc/o2uwCvf82rJqELAPXG24/RossDPHNuLMC1w=; b=E +NQ3j+RAAvNmrNVVUwJv205NsbHgQO+RL3IDV4u57uoJRujowHejmgWkuZgcL4K5 LT3JsKra/hvK17HoCktuJrLo1d9FKjMPx3DacFW1lm7fRlxNbzdLfkm+dIagRsUu +fP74V+yrWIXP1kFFnrS0E/LRfQgChx60tTN2rrQdPR2dUb9YOn2hSgcqP3EtTGI iJhx1OGVgwuUcGz0KRYl9SRyC3DxKxa0nDO50gJ+vAANAu9uTtQckHmzW8LAow9K FyLdWWcYECKb0QYlSphpX58relNGEAwrsUbd9z8qnv6oqDO2nj6yS4FRBm2GcEER Dr4EyeKMwxbFg5BHjmv9Q== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvhedrfeekjedgjeejucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne cujfgurhepkfffgggfuffvfhfhjggtgfesthekredttdefjeenucfhrhhomheptegurhhi rghnucfmlhgrvhgvrhcuoegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtoh hmqeenucggtffrrghtthgvrhhnpeduffevgeejvdeuiefhffettdevvefhfffgveeivedt heetleejgedtueeuffevleenucffohhmrghinhepmhhokhgruggsrdgtohhmnecuvehluh hsthgvrhfuihiivgeptdenucfrrghrrghmpehmrghilhhfrhhomheprggurhhirghnrdhk lhgrvhgvrhesrghklhgrvhgvrhdrtghomh X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Tue, 30 May 2023 10:38:15 -0400 (EDT) Message-ID: <02bd7734-9e59-8bbf-f39b-e0bc484c3912@aklaver.com> Date: Tue, 30 May 2023 07:38:15 -0700 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.11.2 Subject: Re: syntax pb Content-Language: en-US To: Marc Millas , "pgsql-general@lists.postgresql.org" References: From: Adrian Klaver In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 5/30/23 06:45, Marc Millas wrote: > Hi, > > I always have had difficulties to understand syntax. So... > > If I have: > create table t1 (t text); > create table t2 (a text, b text, c test, d numeric); Is c supposed to be text? Or are you indeed referring to some unspecified type? > insert into t1('azerty'); > INSERT 0 1 > fine ! Not with that syntax: insert into t1('azerty'); ERROR: syntax error at or near "'azerty'" LINE 1: insert into t1('azerty'); insert into t1 values('azerty'); INSERT 0 1 > > > so, now, if I write: > Select distinct test1.t, 'abc' as b, NULL as c, NULL as d > From t1 test1; >    t      |  b   | c  | d > --------+-----+---+--- >  azerty | abc |   | > (1 row) > > ok. Yes > > and , now, if I want to insert that: > Insert into t2 (a, b, c, d) > Select distinct test1.t, 'abc' as b, NULL as c, NULL as d > From t1 test1; > > I get: > ERROR:  column "d" is of type numeric but expression is of type text > LINE 2: Select distinct test1.t, 'abc' as b, NULL as c, NULL as d > > HINT:  You will need to rewrite or cast the expression. > > Can someone give a short SQL syntax hint ? The hint is that though NULL is unknown it can have a type. To get this to work I first did: create table t2 (a text, b text, c text, d numeric); to have c be text for simplicity sake. Then I did: Insert into t2 (a, b, c, d) Select distinct test1.t, 'abc' as b, NULL::test, NULL::numeric From t1 test1; which results in: select * from t2; a | b | c | d --------+-----+------+------ azerty | abc | NULL | NULL > > thanks, > > > > Marc MILLAS > Senior Architect > +33607850334 > www.mokadb.com > -- Adrian Klaver adrian.klaver@aklaver.com