Thread: [GENERAL] COPY: row is too big

On 01/02/2017 03:11 AM, vod vos wrote:
> Hi everyone,
>
> My postgresql is 9.61.
>
> When I copy data from csv file, a very long values for many columns
> (about 1100 columns). The errors appears:

My guess is you are tripping this:

https://www.postgresql.org/about/
Maximum Columns per Table    250 - 1600 depending on column types

So what are you storing in table rius and can you give a general idea of
its schema? Not all 1100 columns just a sampling of the data types involved.

Also what is the COPY command you are using?

>
>
> ERROR:  row is too big: size 11808, maximum size 8160CONTEXT:
>
> COPY rius, line 2
>
> rius is the table.
>
> I have searched the mailing list, but seems no solutions founded.
>
> Thanks.


--
Adrian Klaver
adrian.klaver@aklaver.com

vod vos <vodvos@zoho.com> writes:
> When I copy data from csv file, a very long values for many columns (about 1100 columns). The errors appears:
> ERROR:  row is too big: size 11808, maximum size 8160

You need to rethink your table schema so you have fewer columns.
Perhaps you can combine some of them into arrays, for example.
JSON might be a useful option, too.

            regards, tom lane

On 01/02/2017 09:03 AM, vod vos wrote:
> You know, the csv file was exported from other database of a machine, so
> I really dont want to break it for it is a hard work. Every csv file
> contains headers and values. If I redesign the table, then I have to cut
> all the csv files into pieces one by one.

If it helps:

http://csvkit.readthedocs.io/en/latest/tutorial/1_getting_started.html#csvcut-data-scalpel
>
>
> ---- On 星期一, 02 一月 2017 08:21:29 -0800 *Tom Lane
> <tgl@sss.pgh.pa.us>* wrote ----
>
>     vod vos <vodvos@zoho.com <mailto:vodvos@zoho.com>> writes:
>     > When I copy data from csv file, a very long values for many
>     columns (about 1100 columns). The errors appears:
>     > ERROR: row is too big: size 11808, maximum size 8160
>
>     You need to rethink your table schema so you have fewer columns.
>     Perhaps you can combine some of them into arrays, for example.
>     JSON might be a useful option, too.
>
>                 regards, tom lane
>
>
>     --
>     Sent via pgsql-general mailing list (pgsql-general@postgresql.org
>     <mailto:pgsql-general@postgresql.org>)
>     To make changes to your subscription:
>     http://www.postgresql.org/mailpref/pgsql-general
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

> On Jan 2, 2017, at 10:13 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>
>> On 01/02/2017 09:03 AM, vod vos wrote:
>> You know, the csv file was exported from other database of a machine, so
>> I really dont want to break it for it is a hard work. Every csv file
>> contains headers and values. If I redesign the table, then I have to cut
>> all the csv files into pieces one by one.
>
> If it helps:
>
> http://csvkit.readthedocs.io/en/latest/tutorial/1_getting_started.html#csvcut-data-scalpel
>>
>>
>> ---- On 星期一, 02 一月 2017 08:21:29 -0800 *Tom Lane
>> <tgl@sss.pgh.pa.us>* wrote ----
>>
>>    vod vos <vodvos@zoho.com <mailto:vodvos@zoho.com>> writes:
>>> When I copy data from csv file, a very long values for many
>>    columns (about 1100 columns). The errors appears:
>>> ERROR: row is too big: size 11808, maximum size 8160
>>
>>    You need to rethink your table schema so you have fewer columns.
>>    Perhaps you can combine some of them into arrays, for example.
>>    JSON might be a useful option, too.
>>
>>                regards, tom lane
>>
>>
>>    --
>>    Sent via pgsql-general mailing list (pgsql-general@postgresql.org
>>    <mailto:pgsql-general@postgresql.org>)
>>    To make changes to your subscription:
>>    http://www.postgresql.org/mailpref/pgsql-general
>>
>>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
Perhaps this is your opportunity to correct someone else's mistake. You need to show the table definition to convince
usthat it cannot be improved. That it may be hard work really doesn't mean it's not the right path.  

On 01/04/2017 05:00 AM, vod vos wrote:
> Now I am confused about I can create 1100 columns in a table in
> postgresql, but I can't copy 1100 values into the table. And I really

As pointed out previously:

https://www.postgresql.org/about/
Maximum Columns per Table    250 - 1600 depending on column types

That being dependent on both the number of columns and the actual data
in the columns. Empty columns are not the problem, it is when you start
filling them that you get the error.

> dont want to split the csv file to pieces to avoid mistakes after this
> action.
>
> I create a table with 1100 columns with data type of varchar, and hope
> the COPY command will auto transfer the csv data that contains some

I am afraid the solution is going to require more then hope. You are
going to need to break the data up. I suspect that just splitting it
into half would do the trick. So:

Table 1
column 1 for a primary key(assuming first column of your present data)
columns 2-550

Table 2
column 1 for a primary key(assuming first column of your present data)
columns 551-1100

Using the program I mentioned previously:

http://csvkit.readthedocs.io/en/1.0.1/scripts/csvcut.html

That translates into:

csvcut -c 1,2-550 your_big.csv > table_1.csv

csvcut -c 1,551-1100 your_big.csv > table_2.csv


> character and date, most of which are numeric.

Is this a different data set?
Previously you said:
"The most of the data type are text or varhcar, ..."
>
> I use the command: COPY rius FROM "/var/www/test/test.csv" WITH
> DELIMITER ';' ;
>
> Then it shows:
>
> ERROR:  row is too big: size 11808, maximum size 8160
>
>
>
>
>
>
>
> ---- On 星期二, 03 一月 2017 05:24:18 -0800 *John McKown
> <john.archie.mckown@gmail.com>* wrote ----
>
>     On Mon, Jan 2, 2017 at 2:57 PM, Rob Sargent <robjsargent@gmail.com
>     <mailto:robjsargent@gmail.com>>wrote:
>
>         Perhaps this is your opportunity to correct someone else's
>         mistake. You need to show the table definition to convince us
>         that it cannot be improved. That it may be hard work really
>         doesn't mean it's not the right path.
>
>
>     ​This may not be possible. The data might be coming in from an
>     external source. I imagine you've run into the old "well, _we_ don't
>     have any problems, so it must be on your end!" scenario.
>
>     Example: we receive CSV files from an external source. These files
>     are _supposed_ to be validated. But we have often received files
>     where NOT NULL fields have "nothing" in them them. E.g. a customer
>     bill which has _everything_ in it _except_ the customer number (or
>     an invalid one such as "123{"); or missing some other vital piece of
>     information.
>
>     In this particular case, the OP might want to do what we did in a
>     similar case. We had way too many columns in a table. The
>     performance was horrible. We did an analysis and, as usual, the
>     majority of the selects were for a subset of the columns, about 15%
>     of the total. We "split" the table into the "high use" columns table
>     & the "low use" columns table. We then used triggers to make sure
>     that if we added a new / deleted an old row from one table, the
>     corresponding row in the other was created / deleted.
>
>
>
>
>
>         --
>         Sent via pgsql-general mailing list
>         (pgsql-general@postgresql.org <mailto:pgsql-general@postgresql.org>)
>         To make changes to your subscription:
>         http://www.postgresql.org/mailpref/pgsql-general
>
>
>
>
>     --
>     There’s no obfuscated Perl contest because it’s pointless.
>
>     —Jeff Polk
>
>     Maranatha! <><
>     John McKown
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 01/04/2017 06:54 AM, Pavel Stehule wrote:
> Hi
>
> 2017-01-04 14:00 GMT+01:00 vod vos <vodvos@zoho.com
> <mailto:vodvos@zoho.com>>:
>
>     __
>     Now I am confused about I can create 1100 columns in a table in
>     postgresql, but I can't copy 1100 values into the table. And I
>     really dont want to split the csv file to pieces to avoid mistakes
>     after this action.
>
>
> The PostgreSQL limit is "Maximum Columns per Table250 - 1600 depending
> on column types" - this limit is related to placing values or pointers
> to values to one page (8KB).
>
> You can hit this limit not in CREATE TABLE time, but in INSERT time.
>
>
>
>     I create a table with 1100 columns with data type of varchar, and
>     hope the COPY command will auto transfer the csv data that contains
>     some character and date, most of which are numeric.
>
>
> Numeric is expensive type - try to use float instead, maybe double.

If I am following the OP correctly the table itself has all the columns
declared as varchar. The data in the CSV file is a mix of text, date and
numeric, presumably cast to text on entry into the table.

>
> Regards
>
> Pavel
>
>
>     I use the command: COPY rius FROM "/var/www/test/test.csv" WITH
>     DELIMITER ';' ;
>
>     Then it shows:
>
>     ERROR:  row is too big: size 11808, maximum size 8160
>
>
>
>
>
>
>
>     ---- On 星期二, 03 一月 2017 05:24:18 -0800 *John McKown
>     <john.archie.mckown@gmail.com
>     <mailto:john.archie.mckown@gmail.com>>* wrote ----
>
>         On Mon, Jan 2, 2017 at 2:57 PM, Rob Sargent
>         <robjsargent@gmail.com <mailto:robjsargent@gmail.com>>wrote:
>
>             Perhaps this is your opportunity to correct someone else's
>             mistake. You need to show the table definition to convince
>             us that it cannot be improved. That it may be hard work
>             really doesn't mean it's not the right path.
>
>
>         ​This may not be possible. The data might be coming in from an
>         external source. I imagine you've run into the old "well, _we_
>         don't have any problems, so it must be on your end!" scenario.
>
>         Example: we receive CSV files from an external source. These
>         files are _supposed_ to be validated. But we have often received
>         files where NOT NULL fields have "nothing" in them them. E.g. a
>         customer bill which has _everything_ in it _except_ the customer
>         number (or an invalid one such as "123{"); or missing some other
>         vital piece of information.
>
>         In this particular case, the OP might want to do what we did in
>         a similar case. We had way too many columns in a table. The
>         performance was horrible. We did an analysis and, as usual, the
>         majority of the selects were for a subset of the columns, about
>         15% of the total. We "split" the table into the "high use"
>         columns table & the "low use" columns table. We then used
>         triggers to make sure that if we added a new / deleted an old
>         row from one table, the corresponding row in the other was
>         created / deleted.
>
>
>
>
>
>             --
>             Sent via pgsql-general mailing list
>             (pgsql-general@postgresql.org
>             <mailto:pgsql-general@postgresql.org>)
>             To make changes to your subscription:
>             http://www.postgresql.org/mailpref/pgsql-general
>             <http://www.postgresql.org/mailpref/pgsql-general>
>
>
>
>
>         --
>         There’s no obfuscated Perl contest because it’s pointless.
>
>         —Jeff Polk
>
>         Maranatha! <><
>         John McKown
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 2017-01-04 06:53:31 -0800, Adrian Klaver wrote:
> On 01/04/2017 05:00 AM, vod vos wrote:
> >Now I am confused about I can create 1100 columns in a table in
> >postgresql, but I can't copy 1100 values into the table. And I really
>
> As pointed out previously:
>
> https://www.postgresql.org/about/
> Maximum Columns per Table    250 - 1600 depending on column types
>
> That being dependent on both the number of columns and the actual data in
> the columns.

I think this is confusingly phrased. In my mind "column type" is static
- the type is the same, independent of the values which are stored. So
  "250 - 1600 depending on column types" implies to me that there is
some type A of which I can have only 250 columns and another type B of
which I can have 1600 columns. But it doesn't imply to me that the
number of columns depends on the values which ar put into those columns.

May I suggest the these improvements?

In https://www.postgresql.org/about/:
Instead of
| 250 - 1600 depending on column types
write
| 250 - 1600 depending on column types and data

In https://www.postgresql.org/docs/9.6/static/ddl-basics.html:
Replace the sentence:
| Depending on the column types, it is between 250 and 1600.
with:
| For all columns in a row, some information (either the data itself or
| a pointer to the data) must be stored in a single block (8 kB).
| Because for some types this data is itself of variable length, the
| maximum number of columns depends not only on the types of the columns
| but also on the data (e.g., a NULL uses less space than a non-NULL
| value). Therefore there is no simple way to compute the maximum number
| of columns, and it is possible to declare a table with more columns
| than can be filled. Keeping all this in mind, the limit is between 250
| and 1600.

        hp

--
   _  | Peter J. Holzer    | A coding theorist is someone who doesn't
|_|_) |                    | think Alice is crazy.
| |   | hjp@hjp.at         | -- John Gordon
__/   | http://www.hjp.at/ |    http://downlode.org/Etext/alicebob.html

Hello,
On Wed, 2017-01-04 at 07:11 -0800, Adrian Klaver wrote:
> On 01/04/2017 06:54 AM, Pavel Stehule wrote:
> > Hi
> >
> > 2017-01-04 14:00 GMT+01:00 vod vos <vodvos@zoho.com
> > <mailto:vodvos@zoho.com>>:
> >
> >     __
> >     Now I am confused about I can create 1100 columns in a table in
> >     postgresql, but I can't copy 1100 values into the table. And I
> >     really dont want to split the csv file to pieces to avoid
> > mistakes
> >     after this action.
> >
> >
> > The PostgreSQL limit is "Maximum Columns per Table250 - 1600
> > depending
> > on column types" - this limit is related to placing values or
> > pointers
> > to values to one page (8KB).
> >
> > You can hit this limit not in CREATE TABLE time, but in INSERT
> > time.
> >
> >
> >
> >     I create a table with 1100 columns with data type of varchar,
> > and
> >     hope the COPY command will auto transfer the csv data that
> > contains
> >     some character and date, most of which are numeric.
> >
> >
> > Numeric is expensive type - try to use float instead, maybe double.
>
> If I am following the OP correctly the table itself has all the
> columns 
> declared as varchar. The data in the CSV file is a mix of text, date
> and 
> numeric, presumably cast to text on entry into the table.
>
> >
> > Regards
> >
> > Pavel
> >
> >
> >     I use the command: COPY rius FROM "/var/www/test/test.csv" WITH
> >     DELIMITER ';' ;
> >
> >     Then it shows:
> >
> >     ERROR:  row is too big: size 11808, maximum size 8160
> >
> >


Assuming this is a brand new database instance and not an existing
application, could the OP not compile from source and specify the
 --with-blocksize=16384 so as to overcome the 8k default page size
limit?

My 2 cents.
Rob

On 01/04/2017 08:00 AM, rob stone wrote:
> Hello,
> On Wed, 2017-01-04 at 07:11 -0800, Adrian Klaver wrote:
>> On 01/04/2017 06:54 AM, Pavel Stehule wrote:
>>> Hi
>>>
>>> 2017-01-04 14:00 GMT+01:00 vod vos <vodvos@zoho.com
>>> <mailto:vodvos@zoho.com>>:
>>>
>>>     __
>>>     Now I am confused about I can create 1100 columns in a table in
>>>     postgresql, but I can't copy 1100 values into the table. And I
>>>     really dont want to split the csv file to pieces to avoid
>>> mistakes
>>>     after this action.
>>>
>>>
>>> The PostgreSQL limit is "Maximum Columns per Table250 - 1600
>>> depending
>>> on column types" - this limit is related to placing values or
>>> pointers
>>> to values to one page (8KB).
>>>
>>> You can hit this limit not in CREATE TABLE time, but in INSERT
>>> time.
>>>
>>>
>>>
>>>     I create a table with 1100 columns with data type of varchar,
>>> and
>>>     hope the COPY command will auto transfer the csv data that
>>> contains
>>>     some character and date, most of which are numeric.
>>>
>>>
>>> Numeric is expensive type - try to use float instead, maybe double.
>>
>> If I am following the OP correctly the table itself has all the
>> columns
>> declared as varchar. The data in the CSV file is a mix of text, date
>> and
>> numeric, presumably cast to text on entry into the table.
>>
>>>
>>> Regards
>>>
>>> Pavel
>>>
>>>
>>>     I use the command: COPY rius FROM "/var/www/test/test.csv" WITH
>>>     DELIMITER ';' ;
>>>
>>>     Then it shows:
>>>
>>>     ERROR:  row is too big: size 11808, maximum size 8160
>>>
>>>
>
>
> Assuming this is a brand new database instance and not an existing
> application, could the OP not compile from source and specify the
>  --with-blocksize=16384 so as to overcome the 8k default page size
> limit?

Well I was thinking along those lines also, then I did a search on
BLCKSZ in the docs and saw all the configuration parameters that are
keyed off it. I know I would have to do a lot more homework to
understand the implications to the database instance as a whole and
whether it was worth it to accommodate a single table.


>
> My 2 cents.
> Rob
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 01/04/2017 08:32 AM, Steve Crawford wrote:
> ...
>
>         Numeric is expensive type - try to use float instead, maybe double.
>
>
>     If I am following the OP correctly the table itself has all the
>     columns declared as varchar. The data in the CSV file is a mix of
>     text, date and numeric, presumably cast to text on entry into the table.
>
>
> But a CSV *is* purely text - no casting to text is needed. Conversion is
> only needed when the strings in the CSV are text representations of
> *non*-text data.

Yeah, muddled thinking.

>
> I'm guessing that the OP is using all text fields to deal with possibly
> flawed input data and then validating and migrating the data in
> subsequent steps. In that case, an ETL solution may be a better
> approach. Many options, both open- closed- and hybrid-source exist.
>
> Cheers,
> Steve


--
Adrian Klaver
adrian.klaver@aklaver.com

On 01/05/2017 04:44 AM, vod vos wrote:
> I finally figured it out as follows:
>
> 1. modified the corresponding data type of the columns to the csv file
>
> 2. if null values existed, defined the data type to varchar. The null
> values cause problem too.

Did you change the NULLs to something else?

As Pavel said the type does not really matter for NULL:

https://www.postgresql.org/docs/9.6/static/storage-page-layout.html

See marked(<***>) up part

"All table rows are structured in the same way. There is a fixed-size
header (occupying 23 bytes on most machines), followed by an optional
null bitmap, an optional object ID field, and the user data. The header
is detailed in Table 65-4. The actual user data (columns of the row)
begins at the offset indicated by t_hoff, which must always be a
multiple of the MAXALIGN distance for the platform. <***>The null bitmap
is only present if the HEAP_HASNULL bit is set in t_infomask. If it is
present it begins just after the fixed header and occupies enough bytes
to have one bit per data column (that is, t_natts bits altogether). In
this list of bits, a 1 bit indicates not-null, a 0 bit is a null. When
the bitmap is not present, all columns are assumed not-null. <***> The
object ID is only present if the HEAP_HASOID bit is set in t_infomask.
If present, it appears just before the t_hoff boundary. Any padding
needed to make t_hoff a MAXALIGN multiple will appear between the null
bitmap and the object ID. (This in turn ensures that the object ID is
suitably aligned.)"

In this post:

https://www.postgresql.org/message-id/1595fd48444.ba3ec57e13739.3837934651947496063%40zoho.com

you said:

"And some the values in the csv file contain nulls, do this null values
matter?"

It looks like there are a good deal of NULLs in a row. In your original
post COPY failed on the second line, so assuming the same data what is
the NULL count in that line. Or can you provide some estimate of the
high count of NULLS in your data rows?

>
> so 1100 culumns work well now.
>
> This problem wasted me three days. I have lots of csv data to COPY.

You solved the problem so it was not entirely wasted and it provided
information for future reference when folks hit this list with a similar
issue.

>
>
>
>
> ---- On 星期三, 04 一月 2017 08:39:42 -0800 *Adrian Klaver
> <adrian.klaver@aklaver.com>* wrote ----
>
>     On 01/04/2017 08:32 AM, Steve Crawford wrote:
>     > ...
>     >
>     > Numeric is expensive type - try to use float instead, maybe double.
>     >
>     >
>     > If I am following the OP correctly the table itself has all the
>     > columns declared as varchar. The data in the CSV file is a mix of
>     > text, date and numeric, presumably cast to text on entry into the
>     table.
>     >
>     >
>     > But a CSV *is* purely text - no casting to text is needed.
>     Conversion is
>     > only needed when the strings in the CSV are text representations of
>     > *non*-text data.
>
>     Yeah, muddled thinking.
>
>     >
>     > I'm guessing that the OP is using all text fields to deal with
>     possibly
>     > flawed input data and then validating and migrating the data in
>     > subsequent steps. In that case, an ETL solution may be a better
>     > approach. Many options, both open- closed- and hybrid-source exist.
>     >
>     > Cheers,
>     > Steve
>
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>
>     --
>     Sent via pgsql-general mailing list (pgsql-general@postgresql.org
>     <mailto:pgsql-general@postgresql.org>)
>     To make changes to your subscription:
>     http://www.postgresql.org/mailpref/pgsql-general
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

I finally figured it out as follows:

1. modified the corresponding data type of the columns to the csv file

2. if null values existed, defined the data type to varchar. The null values cause problem too.

so 1100 culumns work well now. 

This problem wasted me three days. I have lots of csv data to COPY.

On 01/05/2017 08:31 AM, Rob Sargent wrote:
>
>
> On 01/05/2017 05:44 AM, vod vos wrote:
>> I finally figured it out as follows:
>>
>> 1. modified the corresponding data type of the columns to the csv file
>>
>> 2. if null values existed, defined the data type to varchar. The null
>> values cause problem too.
>>
>> so 1100 culumns work well now.
>>
>> This problem wasted me three days. I have lots of csv data to COPY.
>>
>>
> Yes, you cost yourself a lot of time by not showing the original table
> definition into which you were trying insert data.

Given that the table had 1100 columns I am not sure I wanted to see it:)

Still the OP did give it to us in description:

https://www.postgresql.org/message-id/15969913dd3.ea2ff58529997.7460368287916683127%40zoho.com
"I create a table with 1100 columns with data type of varchar, and hope
the COPY command will auto transfer the csv data that contains some
character and date, most of which are numeric."

In retrospect I should have pressed for was a more complete description
of the data. I underestimated this description:

"And some the values in the csv file contain nulls, do this null values
matter? "


--
Adrian Klaver
adrian.klaver@aklaver.com

On 01/05/2017 11:46 AM, Adrian Klaver wrote:
> On 01/05/2017 08:31 AM, Rob Sargent wrote:
>>
>>
>> On 01/05/2017 05:44 AM, vod vos wrote:
>>> I finally figured it out as follows:
>>>
>>> 1. modified the corresponding data type of the columns to the csv file
>>>
>>> 2. if null values existed, defined the data type to varchar. The null
>>> values cause problem too.
>>>
>>> so 1100 culumns work well now.
>>>
>>> This problem wasted me three days. I have lots of csv data to COPY.
>>>
>>>
>> Yes, you cost yourself a lot of time by not showing the original table
>> definition into which you were trying insert data.
>
> Given that the table had 1100 columns I am not sure I wanted to see it:)
>
> Still the OP did give it to us in description:
>
> https://www.postgresql.org/message-id/15969913dd3.ea2ff58529997.7460368287916683127%40zoho.com
>
> "I create a table with 1100 columns with data type of varchar, and
> hope the COPY command will auto transfer the csv data that contains
> some character and date, most of which are numeric."
>
> In retrospect I should have pressed for was a more complete
> description of the data. I underestimated this description:
>
> "And some the values in the csv file contain nulls, do this null
> values matter? "
>
>
My apologies for missing that.  Was sure there would be room for some
normalization but so be it: OP's happy, I'm happy

I am piggy-backing in this thread because I have the same issue as well. I
need to import a csv file that is 672 columns long and each column consists
of 12 alpha-numeric characters. Such as:

SA03ARE1015D    SA03ARE1S15N    SB03ARE1015D  ...
356412            275812            43106  ...

I am aware this is not normalized, however, we (or try to) keep source data
intact, and normalize after importing into our system.

While trying to import all columns to type `text` I get this error:

[54000] ERROR: row is too big: size 8760, maximum size 8160
Where: COPY temp_table, line 3
SQL statement "copy temp_table from
'/home/edgleweb/data/raw/TX/TAPR/2015/ADV/SSTAAR1ADV.csv' with delimiter ','
quote '"' csv "

I tried varchar(12) also, nothing changed. My questions is 1) I have
672x12=8,064 characters in the first row (which are actually the headers),
why would it complain that it is 8760. I am assuming here type `text`
occupies 1 byte for a character. 2) Is there anything I can do to work
around this situation?

Thanks in advance.



--
View this message in context: http://www.postgresql-archive.org/COPY-row-is-too-big-tp5936997p5963385.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

BTW, we have pg9.5 run on ubuntu.



--
View this message in context: http://www.postgresql-archive.org/COPY-row-is-too-big-tp5936997p5963386.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 05/26/2017 05:07 AM, doganmeh wrote:
> I am piggy-backing in this thread because I have the same issue as well. I
> need to import a csv file that is 672 columns long and each column consists
> of 12 alpha-numeric characters. Such as:
>
> SA03ARE1015D    SA03ARE1S15N    SB03ARE1015D  ...
> 356412            275812            43106  ...
>
> I am aware this is not normalized, however, we (or try to) keep source data
> intact, and normalize after importing into our system.
>
> While trying to import all columns to type `text` I get this error:
>
> [54000] ERROR: row is too big: size 8760, maximum size 8160
> Where: COPY temp_table, line 3
> SQL statement "copy temp_table from
> '/home/edgleweb/data/raw/TX/TAPR/2015/ADV/SSTAAR1ADV.csv' with delimiter ','
> quote '"' csv "
>
> I tried varchar(12) also, nothing changed. My questions is 1) I have
> 672x12=8,064 characters in the first row (which are actually the headers),
> why would it complain that it is 8760. I am assuming here type `text`
> occupies 1 byte for a character. 2) Is there anything I can do to work

https://www.postgresql.org/docs/9.6/static/datatype-character.html

"The storage requirement for a short string (up to 126 bytes) is 1 byte
plus the actual string, which includes the space padding in the case of
character."

> around this situation?

Use csvkit's csvcut tool to split the file?:

http://csvkit.readthedocs.io/en/1.0.2/scripts/csvcut.html

>
> Thanks in advance.
>
>
>
> --
> View this message in context: http://www.postgresql-archive.org/COPY-row-is-too-big-tp5936997p5963385.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Hello

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of doganmeh
> Sent: Freitag, 26. Mai 2017 14:08
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] COPY: row is too big
>
> I am piggy-backing in this thread because I have the same issue as well. I need to import a csv file that is 672
> columns long and each column consists of 12 alpha-numeric characters. Such as:
>
> SA03ARE1015D    SA03ARE1S15N    SB03ARE1015D  ...
> 356412            275812            43106  ...
>
> I am aware this is not normalized, however, we (or try to) keep source data intact, and normalize after importing
> into our system.
>
> While trying to import all columns to type `text` I get this error:
>
> [54000] ERROR: row is too big: size 8760, maximum size 8160
> Where: COPY temp_table, line 3
> SQL statement "copy temp_table from
> '/home/edgleweb/data/raw/TX/TAPR/2015/ADV/SSTAAR1ADV.csv' with delimiter ','
> quote '"' csv "

Is the delimiter really ','? In the lines above it seems to be different.
Did you check line 3?

Regards,
Charles

> I tried varchar(12) also, nothing changed. My questions is 1) I have
> 672x12=8,064 characters in the first row (which are actually the headers), why would it complain that it is 8760. I
> am assuming here type `text` occupies 1 byte for a character. 2) Is there anything I can do to work around this
> situation?
>
> Thanks in advance.
>
>
>
> --
> View this message in context: http://www.postgresql-archive.org/COPY-row-is-too-big-tp5936997p5963385.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Am 26.05.2017 um 14:07 schrieb doganmeh:
>
> I tried varchar(12) also, nothing changed. My questions is 1) I have
> 672x12=8,064 characters in the first row (which are actually the headers),
> why would it complain that it is 8760. I am assuming here type `text`
> occupies 1 byte for a character.

please consider special chars, a little example:

test=*# create table demo(id int, t text);
CREATE TABLE
test=*# insert into demo values (1, '123')
test-# ;
INSERT 0 1
test=*# insert into demo values (2, '€€€');
INSERT 0 1
test=*# select id, t, length(t), pg_column_size(t) from demo;
  id |  t  | length | pg_column_size
----+-----+--------+----------------
   1 | 123 |      3 |              4
   2 | €€€ |      3 |             10
(2 Zeilen)



--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

doganmeh <mehmet@edgle.com> writes:
> I tried varchar(12) also, nothing changed. My questions is 1) I have
> 672x12=8,064 characters in the first row (which are actually the headers),
> why would it complain that it is 8760.

No, you have 672*13, because each varchar value will require a length
word (which is only 1 byte for short values like these).  Adding the
24-byte row header comes to 8760.

> 2) Is there anything I can do to work
> around this situation?

Maybe you could combine the strings into an array?  A large array would
be subject to compression and/or out-of-line storage, but 12-byte fields
are too small to benefit from either.

            regards, tom lane

Yes, the delimiter was indeed ",". I fixed my original post . Seems I
carelessly copy/pasted from excel.



--
View this message in context: http://www.postgresql-archive.org/COPY-row-is-too-big-tp5936997p5963558.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Yes, csvkit is what I decided to go with. Thank you all!



--
View this message in context: http://www.postgresql-archive.org/COPY-row-is-too-big-tp5936997p5963559.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.