Postgresql - Importing . CSV into an existing table

Asked

Viewed 221 times

0

I have a table with three columns, which already has data, in my Postgresql.

I have a file . CSV with four columns.

I would like to add the data of three columns of . CSV to SQL without replacing those that are already there.

How to do ?

SQL
Schema: cnpj
Table: cnae
Fields: cnpj, cnae, data

cnpj_dados_cnae_secundario_filt.CSV
Colunas: cnpj, cnae, nat_jur, data
  • I haven’t tried anything yet because I’m afraid to overwrite the data that’s already there

Thank you.

postgresql csv importing

  • Explain in detail the meaning of "without replacing those already there". What is the primary key of your table? There are other indexes?

  • There is no primary key, only these fields. " without replacing the ones that are already there" means that the table already has data and I want to add others, keeping the old ones.

  • 1

    Use the command COPY (https://www.postgresql.org/docs/current/sql-copy.html) specifying the parameter FORMAT CSV. It will add the new records, from the file, to the table. It seems that your table contains a non-existent field in your csv file, in this case this field will be with NULL.

  • It would be something like: COPY INTO cnpj_dados_cadastrais_pj.cnae FROM 'G: cnpj_dados_cnae_secundario_filt.csv' WITH ( FIELDTERMINATOR = '#', FIRSTROW = 2, )

1 answer

1


According to what you informed in your question:

  1. If the.csv file is on the postgresql server machine use the SQL command:

    COPY cnpj.cnae(cnpj, cnae, nat_jur) FROM 'G: cnpj_dados_cnae_secundario_filt.csv' WITH FORMAT CSV HEADER DELIMITER ' FORCE_NULL (nat_jur);

  2. If the file resides on a client machine use the \copy of psql.

Browser other questions tagged postgresql csv importing

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