How to define column types when making a copy for Postgresql

Asked

Viewed 117 times

2

I’m trying to make a copy for a table I have in Postgre, but is giving the following error:

ERROR: invalid input syntax for type timestamp: "data_register"

I wanted to know how to define that this field is of the type timestamp.

Follows the code:

COPY tb_reclamacao(ordem, processo, data_cadastro, data_resposta, data_programacao, id_base, id_usuario, id_tipo_reclamacao, id_situacao) 
FROM 'C:\ANEXOS\tb_reclamacao.txt'  
using delimiters ';'
postgresql
  • 1

    The date format in the file is different than expected. Show some lines of the file.

  • You can include a portion of the archive contents C:\ANEXOS\tb_reclamacao.txt?

1 answer

1

Date format is not the same as the bank.

By default, the format is YYYY-MM-DD HH:MM:SS

You can change the date format of the section with the command:

SET datestyle TO (format);

Following is the list of formats below:

  • MDY | Month-day-year | 12/16/2011
  • DMY | day-Month-year | 16/12/2011
  • YMD | year-Month-day | 2011-12-16
  • ISO | ISO 8601/SQL standard (default) | 2011-12-16 07:37:16-08
  • POSTGRES | verbose style | Fri Dec 16 07:37:16 2012 PST
  • SQL | Traditional style | 12/16/2011 07:37:16.00 PST
  • GERMAN | regional style | 16.12.2011 07:37:16.00 PST

To use the SQL option, you must specify the order of month and day. Ex:

  • SQL, DMY | day/Month/year | 17/12/2007 15:37:16.00 CET
  • SQL, MDY | Month/day/year | 12/17/2007 07:37:16.00 PST

To enter the date data in Brazilian format, just run the select below before performing COPY:

//This will change the date format only for this session. SET datestyle TO (SQL, MDY);

Browser other questions tagged postgresql

You are not signed in. Login or sign up in order to post.