Thread: why generated columsn cannot be used in COPY TO?
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. Thanks, Luca
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 ... test=*# 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 ); CREATE TABLE test=*# commit; COMMIT test=# insert into test( ts ) values( current_timestamp ); INSERT 0 1 test=*# select * from test; pk | ts | month ----+---------------------------+------- 1 | 06-OCT-23 14:18:28.742152 | 10 (1 row) test=*# commit; COMMIT test=# copy test to stdout; 1 06-OCT-23 14:18:28.742152 test=*# copy test to stdout; 1 06-OCT-23 14:18:28.742152 test=*# copy (select * from test) to stdout; 1 06-OCT-23 14:18:28.742152 10 test=*# Andreas -- Andreas Kretschmer - currently still (garden leave) Technical Account Manager (TAM) www.enterprisedb.com
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.
Luca Ferrari <fluca1978@gmail.com> writes:
> 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?
There's a comment about that in copy.c:
* We don't include generated columns in the generated full list and we don't
* allow them to be specified explicitly. They don't make sense for COPY
* FROM, but we could possibly allow them for COPY TO. But this way it's at
* least ensured that whatever we copy out can be copied back in.
Not sure how convincing that reasoning is, but it was at least
thought about. I do agree with it as far as the default column
list goes, but maybe we could allow explicit selection of these
columns in COPY TO.
regards, tom lane
Am 06.10.23 um 15:47 schrieb Tom Lane: > Luca Ferrari <fluca1978@gmail.com> writes: >> 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? > There's a comment about that in copy.c: > > * We don't include generated columns in the generated full list and we don't > * allow them to be specified explicitly. They don't make sense for COPY > * FROM, but we could possibly allow them for COPY TO. But this way it's at > * least ensured that whatever we copy out can be copied back in. ha, as always: read the source ;-) > > Not sure how convincing that reasoning is, but it was at least > thought about. I do agree with it as far as the default column > list goes, but maybe we could allow explicit selection of these > columns in COPY TO. sounds okay Andreas -- Andreas Kretschmer - currently still (garden leave) Technical Account Manager (TAM) www.enterprisedb.com
> On Oct 6, 2023, at 7:47 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Luca Ferrari <fluca1978@gmail.com> writes: >> 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? > > There's a comment about that in copy.c: > > * We don't include generated columns in the generated full list and we don't > * allow them to be specified explicitly. They don't make sense for COPY > * FROM, but we could possibly allow them for COPY TO. But this way it's at > * least ensured that whatever we copy out can be copied back in. > > Not sure how convincing that reasoning is, but it was at least > thought about. I do agree with it as far as the default column > list goes, but maybe we could allow explicit selection of these > columns in COPY TO. > > regards, tom lane > > What would be copied? The forumla? Seems to me one is using “the fast option” so adding the column which can be regeneratedis overhead.
On Friday, October 6, 2023, Rob Sargent <robjsargent@gmail.com> wrote:
> On Oct 6, 2023, at 7:47 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Luca Ferrari <fluca1978@gmail.com> writes:
>> 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?
>
> There's a comment about that in copy.c:
>
> * We don't include generated columns in the generated full list and we don't
> * allow them to be specified explicitly. They don't make sense for COPY
> * FROM, but we could possibly allow them for COPY TO. But this way it's at
> * least ensured that whatever we copy out can be copied back in.
>
> Not sure how convincing that reasoning is, but it was at least
> thought about. I do agree with it as far as the default column
> list goes, but maybe we could allow explicit selection of these
> columns in COPY TO.
>
> regards, tom lane
>
>
What would be copied? The forumla? Seems to me one is using “the fast option” so adding the column which can be regenerated is overhead.
This question and statement makes zero sense to me. If you specify the column name you’d get the values of that column like always.
I’m good, though, with the columns you can choose being a strict subset of those that are output when you do not list any. Writing a select query to get a faithful reproduction of the entire table’s contents is fine for an API.
David J.
On Fri, Oct 6, 2023 at 4:59 PM Rob Sargent <robjsargent@gmail.com> wrote:
What would be copied? The formula?
Of course not. That's DDL, not DML IMHO.
Seems to me one is using “the fast option” so adding the column which can be regenerated is overhead.
Regenerated by whom? COPY TO to is output to the outside world.
Nothing says it will come back to PostgreSQL later. COPY is not only for backups.
I use COPY to "stream" rows out for processing, combines the best of SELECT
and the incremental nature of CURSOR, for the best latency and throughput.
All that said, that's a work-around like Andreas showed.
I agree with Tom that ignoring them in the default list makes sense,
but that if explicitly asked for, they should be copied out. My $0.02.
On 10/6/23 09:04, Andreas Kretschmer wrote:
Nah. "The programmer -- and DBA -- on the Clapham omnibus" quite reasonably expects that COPY table_name TO (output)" copies all the columns listed in "\d table_name".
https://en.wikipedia.org/wiki/Man_on_the_Clapham_omnibus
"The man on the Clapham omnibus is a hypothetical ordinary and reasonable person, used by the courts in English law where it is necessary to decide whether a party has acted as a reasonable person would – for example, in a civil action for negligence. The character is a reasonably educated, intelligent but nondescript person, against whom the defendant's conduct can be measured."
Am 06.10.23 um 15:47 schrieb Tom Lane:Luca Ferrari <fluca1978@gmail.com> writes:I'm wondering why in COPY TO (file or program) I cannot use generatedThere's a comment about that in copy.c:
columns: since I'm pushing data out of the table, why they are not
allowed?
* We don't include generated columns in the generated full list and we don't
* allow them to be specified explicitly. They don't make sense for COPY
* FROM, but we could possibly allow them for COPY TO. But this way it's at
* least ensured that whatever we copy out can be copied back in.
ha, as always: read the source ;-)
Not sure how convincing that reasoning is, but it was at least
thought about. I do agree with it as far as the default column
list goes, but maybe we could allow explicit selection of these
columns in COPY TO.
sounds okay
Nah. "The programmer -- and DBA -- on the Clapham omnibus" quite reasonably expects that COPY table_name TO (output)" copies all the columns listed in "\d table_name".
https://en.wikipedia.org/wiki/Man_on_the_Clapham_omnibus
"The man on the Clapham omnibus is a hypothetical ordinary and reasonable person, used by the courts in English law where it is necessary to decide whether a party has acted as a reasonable person would – for example, in a civil action for negligence. The character is a reasonably educated, intelligent but nondescript person, against whom the defendant's conduct can be measured."
--
Born in Arizona, moved to Babylonia.
Born in Arizona, moved to Babylonia.
On 10/6/23 08:45, Ron wrote: > On 10/6/23 09:04, Andreas Kretschmer wrote: >> >>> Not sure how convincing that reasoning is, but it was at least >>> thought about. I do agree with it as far as the default column >>> list goes, but maybe we could allow explicit selection of these >>> columns in COPY TO. >> >> sounds okay > > Nah. "The programmer -- and DBA -- on the Clapham omnibus" quite > reasonably expects that COPY table_name TO (output)" copies all the > columns listed in "\d table_name". > Yeah, I would agree. > -- > Born in Arizona, moved to Babylonia. -- Adrian Klaver adrian.klaver@aklaver.com
On Fri, Oct 6, 2023 at 8:54 AM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 10/6/23 08:45, Ron wrote:
> On 10/6/23 09:04, Andreas Kretschmer wrote:
>>
>>> Not sure how convincing that reasoning is, but it was at least
>>> thought about. I do agree with it as far as the default column
>>> list goes, but maybe we could allow explicit selection of these
>>> columns in COPY TO.
>>
>> sounds okay
>
> Nah. "The programmer -- and DBA -- on the Clapham omnibus" quite
> reasonably expects that COPY table_name TO (output)" copies all the
> columns listed in "\d table_name".
>
Yeah, I would agree.
Sure, but it doesn't. Mainly since copy's original design was intended to solve the dump/restore problem and it doesn't make sense to specify data for inbound generated data. So while we do have a POLA violation here the desirability to now fix it years later is basically zero. And the current behavior is at least defensible and consistent. And there is a very easy way to get the desired output making any change that much harder a sell.
The error message maybe could use some help though, and if there isn't a hint maybe add one.
David J.
On 10/6/23 11:08, David G. Johnston wrote:
At least it's explicitly mentioned in the docs that generated columns are excluded.
On Fri, Oct 6, 2023 at 8:54 AM Adrian Klaver <adrian.klaver@aklaver.com> wrote:On 10/6/23 08:45, Ron wrote:
> On 10/6/23 09:04, Andreas Kretschmer wrote:
>>
>>> Not sure how convincing that reasoning is, but it was at least
>>> thought about. I do agree with it as far as the default column
>>> list goes, but maybe we could allow explicit selection of these
>>> columns in COPY TO.
>>
>> sounds okay
>
> Nah. "The programmer -- and DBA -- on the Clapham omnibus" quite
> reasonably expects that COPY table_name TO (output)" copies all the
> columns listed in "\d table_name".
>
Yeah, I would agree.Sure, but it doesn't. Mainly since copy's original design was intended to solve the dump/restore problem and it doesn't make sense to specify data for inbound generated data. So while we do have a POLA violation here the desirability to now fix it years later is basically zero. And the current behavior is at least defensible and consistent. And there is a very easy way to get the desired output making any change that much harder a sell.
At least it's explicitly mentioned in the docs that generated columns are excluded.
The error message maybe could use some help though, and if there isn't a hint maybe add one.David J.
--
Born in Arizona, moved to Babylonia.
Born in Arizona, moved to Babylonia.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
>> On 10/6/23 08:45, Ron wrote:
>>> Nah. "The programmer -- and DBA -- on the Clapham omnibus" quite
>>> reasonably expects that COPY table_name TO (output)" copies all the
>>> columns listed in "\d table_name".
> Sure, but it doesn't. Mainly since copy's original design was intended to
> solve the dump/restore problem and it doesn't make sense to specify data
> for inbound generated data. So while we do have a POLA violation here the
> desirability to now fix it years later is basically zero. And the current
> behavior is at least defensible and consistent. And there is a very easy
> way to get the desired output making any change that much harder a sell.
Changing the default behavior now is certainly a non-starter.
I don't really see any backwards-compatibility problem with
allowing cases that had been errors, though.
regards, tom lane
On Friday, October 6, 2023, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
>> On 10/6/23 08:45, Ron wrote:
>>> Nah. "The programmer -- and DBA -- on the Clapham omnibus" quite
>>> reasonably expects that COPY table_name TO (output)" copies all the
>>> columns listed in "\d table_name".
> Sure, but it doesn't. Mainly since copy's original design was intended to
> solve the dump/restore problem and it doesn't make sense to specify data
> for inbound generated data. So while we do have a POLA violation here the
> desirability to now fix it years later is basically zero. And the current
> behavior is at least defensible and consistent. And there is a very easy
> way to get the desired output making any change that much harder a sell.
Changing the default behavior now is certainly a non-starter.
I don't really see any backwards-compatibility problem with
allowing cases that had been errors, though.
I wouldn’t vote against it but the current simplicity seems sufficient. “Copy table doesn’t recognize generated columns, use copy (select) if you want to include them in the output.”
David J.