Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qokhB-002iv5-1t for pgsql-general@arkaria.postgresql.org; Fri, 06 Oct 2023 13:16:45 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qokh8-0076B7-T8 for pgsql-general@arkaria.postgresql.org; Fri, 06 Oct 2023 13:16:43 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qokh8-0076AF-IC for pgsql-general@lists.postgresql.org; Fri, 06 Oct 2023 13:16:43 +0000 Received: from mail-yw1-x1135.google.com ([2607:f8b0:4864:20::1135]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qokh3-000JdB-1Q for pgsql-general@lists.postgresql.org; Fri, 06 Oct 2023 13:16:42 +0000 Received: by mail-yw1-x1135.google.com with SMTP id 00721157ae682-59f7f46b326so24509537b3.0 for ; Fri, 06 Oct 2023 06:16:36 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1696598196; x=1697202996; darn=lists.postgresql.org; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :from:to:cc:subject:date:message-id:reply-to; bh=obIdPDHpzwAtQO3vKO0+uXJ5tfjhlOP6qJPH02ZV0y8=; b=gREcACl7jHtSZ73bWwCUhVrrklMOqAXUzCSgwesh55wRyD3X4BkU+4UYmgJwQyIhhO D97cViWO8ZGOlO9clvcHXVWqWJ5B7+NQ1XdyaaqE4jY99sT94bnxX8VgImSJA2cHByLV G7UzrxYBo1ea0TnlE0NbsVprbJGpSSBVnEuGFYlLYPwjiFJ+UxBhmkw12quGrdUfJLay +e1XV1/3YRzIiVJxPXWXtY+11f3bJE90SeiFy+j2XaCSCATOE495yuWrHRyJLAoBZIEU VnwkefVdtVKJatkeWlC7eWCniVKacuDgPTL9qngR8lURxkVEw+nGnaHheUfRIciepk6a Cz1A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1696598196; x=1697202996; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=obIdPDHpzwAtQO3vKO0+uXJ5tfjhlOP6qJPH02ZV0y8=; b=oHpbihx4keylBf7JD4bj2ZCdKfUdBUNJx7xdmQN7I9glIdpFdnIZ6PWiPgQyLl/d78 LX8Xt060S7lv7+8+yL/F1PqmDzku0LjVs7i03zlD2wKk4AkaW4oJqDg3Tdm8Fn0fuYCc xtkWIxYToGnPzBNgrGskEFnrkl7ScANDszi8LONk8jTCaqbBLo4exgAqAIZtXVehNt5x CxqhapgabypaBJ6IqtzyarwsluB6tBKOhNuL49BwMOSlteuspaKLnJJ0pLHo4mAVNaEN PBCv/ltMboXOaS4xgCH/okRhhAi7EBBjPS+04IU5nxGSLvv2f47ZS1dJ2K7iQlFF/nTv VpEQ== X-Gm-Message-State: AOJu0Yy92IRNScfTvbJaPXdO3SpZ/v92lN8WDaoqpdxEDmx+Nnn1p4ei 4aSEnM4wgH+qTjHLUsujWkWnWv356YI= X-Google-Smtp-Source: AGHT+IHUtA3EgtcAqmrahqyV9ERMXt7xQ8mz2IUhmssD4TfK8NnEzHaf3V6tt0iZ9lhOqAHBvfCbZA== X-Received: by 2002:a0d:e614:0:b0:59b:ec85:54ed with SMTP id p20-20020a0de614000000b0059bec8554edmr7992121ywe.7.1696598196027; Fri, 06 Oct 2023 06:16:36 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:5cc2:23a0:afa6:cd2? ([2600:1700:bd2c:b810:5cc2:23a0:afa6:cd2]) by smtp.googlemail.com with ESMTPSA id u206-20020a8147d7000000b0059b4e981fe6sm1248672ywa.102.2023.10.06.06.16.35 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Fri, 06 Oct 2023 06:16:35 -0700 (PDT) Message-ID: <2fc8c4ab-4ecc-2adb-4236-3142fde13bde@gmail.com> Date: Fri, 6 Oct 2023 08:16:35 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.11.0 Subject: Re: why generated columsn cannot be used in COPY TO? Content-Language: en-US To: pgsql-general@lists.postgresql.org References: <3183166f-ff95-7ca3-48fe-8e87827bf202@a-kretschmer.de> From: Ron In-Reply-To: <3183166f-ff95-7ca3-48fe-8e87827bf202@a-kretschmer.de> 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 10/6/23 07:20, Andreas Kretschmer wrote: > > > Am 06.10.23 um 13:53 schrieb Luca Ferrari: >> Hi all, >> I'm wondering why in COPY TO (file or program) I cannot use generated >> columns: since I'm pushing data out of the table, why they are not >> allowed? >> >> Example: >> >> testdb=# CREATE TABLE test( pk int generated always as identity primary key >> , ts timestamp default current_timestamp >> , month int generated always as ( extract( month from ts ) ) stored >> ); >> >> testdb=# insert into test( ts ) values( current_timestamp ); >> >> testdb=# copy test to program 'head'; -- ok but silently removes the column >> COPY 1 >> testdb=# copy test( month ) to program 'head'; >> ERROR:  column "month" is a generated column >> DETAIL:  Generated columns cannot be used in COPY. > > you can use copy (select * ...) to ... But why not? -- Born in Arizona, moved to Babylonia.