Postgres Email Lists

pgsql-general

❮

Using \copy to populate a table

Jump to comment-1
rob stone<floriparob@tpg.com.au>
Mar 15, 2026, 7:12 AM UTC
psql (18.3 (Debian 18.3-1+b1))
Type "help" for help.
Debian OS is forky.
When setting up a new development database, the following occurred:-
applntestdb=> \copy formstable (itemtype, navgnrefn, htmlname,
tablekey, navgnbar, rowspage, forwardto, secondto, tertiaryto,
activeitem, superonly, systemadmin, insertedby) from
'/home/postgres/loadfiles/formstable.txt' header delimiter '|';
ERROR: invalid input syntax for type smallint: "null"
CONTEXT: COPY formstable, line 2, column rowspage: "null"
applntestdb=>
This is what is on line 2 of the file:-
```
C|0|pageloader|||null|null|null|null|t|f|f|0
```
The test below was simplified to just choose one SMALLINT column.
applntestdb=> insert into formstable (itemtype, navgn_refn,
htmlname, rowspage, active_item,
inserted_by) values ('C', 0, 'pageloader', null, TRUE, 0);
```
INSERT 0 1
applntestdb=> delete from forms_table where item_type = 'C';
DELETE 1
applntestdb=> insert into forms_table (item_type, navgn_refn,
html_name, rows_page, active_item,
inserted_by) values ('C', 0, 'pageloader', '', TRUE, 0);
ERROR:  invalid input syntax for type smallint: ""
LINE 2: inserted_by) values ('C', 0, 'pageloader', '', TRUE, 0)
                                               ^
```
So, psql is quite happy to insert null into a column defined as
smallint, but when you use the \copy mechanism to populate a table it
pulls an error.
How do we fix this? Alter all the nulls to zeroes in the file or write
a program to dissect the file and create individual insert statements?
Has anybody else had this problem and if so what was the solution?
Thanks,
Rob
View in PostgreSQL Archives →
- Jump to comment-1
  Ron Johnson<ronljohnsonjr@gmail.com>
  Mar 15, 2026, 9:48 AM UTC
  On Sun, Mar 15, 2026 at 3:12 AM rob stone <floriparob@tpg.com.au> wrote:
  psql (18.3 (Debian 18.3-1+b1))
  Type "help" for help.
  
  Debian OS is forky.
  
  When setting up a new development database, the following occurred:-
  applntestdb=> \copy formstable (itemtype, navgnrefn, htmlname,
  tablekey, navgnbar, rowspage, forwardto, secondto, tertiaryto,
  activeitem, superonly, systemadmin, insertedby) from
  '/home/postgres/loadfiles/formstable.txt' header delimiter '|';
  ERROR: invalid input syntax for type smallint: "null"
  CONTEXT: COPY formstable, line 2, column rowspage: "null"
  applntestdb=>
  
  This is what is on line 2 of the file:-
  C|0|pageloader|||null|null|null|null|t|f|f|0
  
  The test below was simplified to just choose one SMALLINT column.
  applntestdb=> insert into formstable (itemtype, navgn_refn,
  htmlname, rowspage, active_item,
  inserted_by) values ('C', 0, 'pageloader', null, TRUE, 0);
  INSERT 0 1
  applntestdb=> delete from formstable where itemtype = 'C';
  DELETE 1
  applntestdb=> insert into formstable (itemtype, navgn_refn,
  htmlname, rowspage, active_item,
  inserted_by) values ('C', 0, 'pageloader', '', TRUE, 0);
  ERROR: invalid input syntax for type smallint: ""
  LINE 2: inserted_by) values ('C', 0, 'pageloader', '', TRUE, 0)
  ^
  
  So, psql is quite happy to insert null into a column defined as
  smallint, but when you use the \copy mechanism to populate a table it
  pulls an error.
  
  How do we fix this? Alter all the nulls to zeroes in the file or write
  a program to dissect the file and create individual insert statements?
  
  Has anybody else had this problem and if so what was the solution?
  "null" is not what COPY uses to signal a null value. I think it's \N but
  the best solution i to COPY TO your table to STDOUT and see what it uses as
  the null indicator.
  \copy forms_table TO STDOUT WITH (HEADER, DELIMITER '|');
  --
  Death to <Redacted>, and butter sauce.
  Don't boil me, I'm still alive.
  <Redacted> lobster!
  View in PostgreSQL Archives →
- Jump to comment-1
  Francisco Olarte<folarte@peoplecall.com>
  Mar 15, 2026, 9:51 AM UTC
  Hi Rob:
  On Sun, 15 Mar 2026 at 08:12, rob stone <floriparob@tpg.com.au> wrote:
  
  This is what is on line 2 of the file:-
  C|0|pageloader|||null|null|null|null|t|f|f|0
  
  ...
  
  So, psql is quite happy to insert null into a column defined as
  smallint, but when you use the \copy mechanism to populate a table it
  pulls an error.
  It does not for me, but syntax for INSERT and COPY ( \copy just does a COPY
  from stdin ) is different.
  How do we fix this? Alter all the nulls to zeroes in the file or write
  a program to dissect the file and create individual insert statements?
  Have you tried a little RTFM?
  specifically ....
  https://www.postgresql.org/docs/18/sql-copy.html
  Where somewhere it says:
  NULL
  Specifies the string that represents a null value. The default is \N
  (backslash-N) in text format, and an unquoted empty string in CSV format.
  You might prefer an empty string even in text format for cases where you
  don't want to distinguish nulls from empty strings. This option is not
  allowed when using binary format.
  If you generate four text files I would recommend switching to \N, it will
  be less painful.
  Francisco Olarte.
  View in PostgreSQL Archives →
- Jump to comment-1
  Adrian Klaver<adrian.klaver@aklaver.com>
  Mar 15, 2026, 3:30 PM UTC
  On 3/15/26 12:11 AM, rob stone wrote:
  psql (18.3 (Debian 18.3-1+b1))
  Type "help" for help.
  Debian OS is forky.
  When setting up a new development database, the following occurred:-
  How do we fix this? Alter all the nulls to zeroes in the file or write
  a program to dissect the file and create individual insert statements?
  CSV is a text format and in:
  C|0|pageloader|||null|null|null|null|t|f|f|0
  the null values are the string 'null' which indeed is not a valid syntax for a smallint.
  Has anybody else had this problem and if so what was the solution?
  Do you have control over whatever creates the file?
  An option is to create a staging table that is has all string fields, import into it and then do clean up before moving to final table.
  Thanks,
  Rob
  -- Adrian Klaver
  adrian.klaver@aklaver.com
  View in PostgreSQL Archives →
- Jump to comment-1
  Peter Kleiner<runtfan71@gmail.com>
  Mar 17, 2026, 2:16 AM UTC
  On Sun, Mar 15, 2026 at 3:12 AM rob stone <floriparob@tpg.com.au> wrote:
  psql (18.3 (Debian 18.3-1+b1))
  Type "help" for help.
  
  Debian OS is forky.
  
  When setting up a new development database, the following occurred:-
  applntestdb=> \copy formstable (itemtype, navgnrefn, htmlname,
  tablekey, navgnbar, rowspage, forwardto, secondto, tertiaryto,
  activeitem, superonly, systemadmin, insertedby) from
  '/home/postgres/loadfiles/formstable.txt' header delimiter '|';
  ERROR: invalid input syntax for type smallint: "null"
  CONTEXT: COPY formstable, line 2, column rowspage: "null"
  applntestdb=>
  
  This is what is on line 2 of the file:-
  C|0|pageloader|||null|null|null|null|t|f|f|0
  
  The test below was simplified to just choose one SMALLINT column.
  applntestdb=> insert into formstable (itemtype, navgn_refn,
  htmlname, rowspage, active_item,
  inserted_by) values ('C', 0, 'pageloader', null, TRUE, 0);
  INSERT 0 1
  applntestdb=> delete from formstable where itemtype = 'C';
  DELETE 1
  applntestdb=> insert into formstable (itemtype, navgn_refn,
  htmlname, rowspage, active_item,
  inserted_by) values ('C', 0, 'pageloader', '', TRUE, 0);
  ERROR: invalid input syntax for type smallint: ""
  LINE 2: inserted_by) values ('C', 0, 'pageloader', '', TRUE, 0)
  ^
  
  So, psql is quite happy to insert null into a column defined as
  smallint, but when you use the \copy mechanism to populate a table it
  pulls an error.
  
  How do we fix this? Alter all the nulls to zeroes in the file or write
  a program to dissect the file and create individual insert statements?
  
  Has anybody else had this problem and if so what was the solution?
  
  Caveat: I'm a hack, I rarely respond to questions here, I have limited
  knowledge about PGSQL, databases in general, and generally hesitate to
  chime in where my betters are wont to dwell.
  That said... If it was me, I would at least try switching this
  \copy formstable (itemtype, navgnrefn, htmlname,
  tablekey, navgnbar, rowspage, forwardto, secondto, tertiaryto,
  activeitem, superonly, systemadmin, insertedby) from
  '/home/postgres/loadfiles/formstable.txt' header delimiter '|';
  with this
  \copy formstable (itemtype, navgnrefn, htmlname,
  tablekey, navgnbar, rowspage, forwardto, secondto, tertiaryto,
  activeitem, superonly, systemadmin, insertedby) from
  '/home/postgres/loadfiles/formstable.txt' with header delimiter '|' NULL as
  'null';
  In other words, your CSV file seems to be outputting the string 'null' for
  NULL values.
  Doing it this way, however, would mean that any of your fields whose string
  values might be "null" would be interpreted as NULL values.
  The fact that no one has already offered this as an approach makes me
  skeptical, but perhaps you could give it a shot to see if it works.
  Pete
  View in PostgreSQL Archives →
- Jump to comment-1
  Brent Wood<brent.wood@earthsciences.nz>
  Mar 17, 2026, 10:33 AM UTC
  Have you tried explicitly specifying the string to represent null values?
  COPY table_name FROM 'path/to/file' WITH (FORMAT text, NULL 'null');
  -- Or the older syntax:
  COPY table_name FROM 'path/to/file' WITH DELIMITER ',' NULL AS 'null';
  ________________________________
  From: rob stone <floriparob@tpg.com.au>
  Sent: Sunday, March 15, 2026 8:11 PM
  To: PostGreSQL MailingList <pgsql-general@postgresql.org>
  Subject: Using \copy to populate a table
  psql (18.3 (Debian 18.3-1+b1))
  Type "help" for help.
  Debian OS is forky.
  When setting up a new development database, the following occurred:-
  applntestdb=> \copy formstable (itemtype, navgnrefn, htmlname,
  tablekey, navgnbar, rowspage, forwardto, secondto, tertiaryto,
  activeitem, superonly, systemadmin, insertedby) from
  '/home/postgres/loadfiles/formstable.txt' header delimiter '|';
  ERROR: invalid input syntax for type smallint: "null"
  CONTEXT: COPY formstable, line 2, column rowspage: "null"
  applntestdb=>
  This is what is on line 2 of the file:-
  C|0|pageloader|||null|null|null|null|t|f|f|0
  The test below was simplified to just choose one SMALLINT column.
  applntestdb=> insert into formstable (itemtype, navgn_refn,
  htmlname, rowspage, active_item,
  inserted_by) values ('C', 0, 'pageloader', null, TRUE, 0);
  INSERT 0 1 applntestdb=> delete from forms_table where item_type = 'C'; DELETE 1 applntestdb=> insert into forms_table (item_type, navgn_refn, html_name, rows_page, active_item, inserted_by) values ('C', 0, 'pageloader', '', TRUE, 0); ERROR: invalid input syntax for type smallint: "" LINE 2: inserted_by) values ('C', 0, 'pageloader', '', TRUE, 0) ^
  So, psql is quite happy to insert null into a column defined as
  smallint, but when you use the \copy mechanism to populate a table it
  pulls an error.
  How do we fix this? Alter all the nulls to zeroes in the file or write
  a program to dissect the file and create individual insert statements?
  Has anybody else had this problem and if so what was the solution?
  Thanks,
  Rob
  Brent Wood
  Principal Technician - GIS and Spatial Data Management
  +64-4-386-0529
  301 Evans Bay Parade, Greta Point, Hataitai, Wellington, New Zealand
  Earth Sciences New Zealand
  [Earth Sciences New Zealand]https://earthsciences.nz
  The Institute of Geological and Nuclear Sciences Limited and the National Institute of Water and Atmospheric Research Limited joined to become the New Zealand Institute for Earth Science Limited. We are known as Earth Sciences New Zealand. For more information on the Earth Sciences transition click herehttps://niwa.co.nz/about-niwa/science-sector-reforms.
  Notice: This email and any attachments may contain information which is confidential and/or subject to copyright or legal privilege, and may not be used, published or redistributed without the prior written consent of Earth Sciences New Zealand. If you are not the intended recipient, please immediately notify the sender and delete the email and any attachments. Any opinion or views expressed in this email are those of the individual sender and may not represent those of Earth Sciences New Zealand.
  For information about how we process data and monitor communications please see our privacy policyhttps://niwa.co.nz/about-niwa/privacy-policy.
  View in PostgreSQL Archives →
  Jump to comment-1
  rob stone<floriparob@tpg.com.au>
  Mar 17, 2026, 12:08 PM UTC
  On Sun, 2026-03-15 at 07:21 +0000, Brent Wood wrote:
  
  Have you tried explicitly specifying the string to represent null
  values?
  
  COPY table_name FROM 'path/to/file' WITH (FORMAT text, NULL 'null');
  -- Or the older syntax:
  COPY table_name FROM 'path/to/file' WITH DELIMITER ',' NULL AS
  'null';
  
  Hello,
  I took on board Adrian's suggestion and went back to the source and had
  the file recreated and it has now been loaded successfully.
  Cheers,
  Rob
  View in PostgreSQL Archives →