CODEWITHSUNDEEP
mysqlnode.jspostgresqlcsv
Nithin P.H
Nithin P.H

Reputation: 732

updating table using csv file from query in pgsql

The concept is on uploading a csv file the old table should be deleted and the new file should replace the old data I'm using nodejs in it I have written two queries

TRUNCATE TABLE test

then the second query for updating the table is

UPDATE test SET 
 t1 = replace(t1,"")
,t2 = replace(t2,"")
,t3 = replace(t3,"")
,t4 = replace(t4,"")

My dummy csv file also have same 4 columns t1,t2,t3,t4 of datatype var char

while executing Im getting the error 

error: zero-length delimited identifier at or near """"

please correct me if the query is wrong

Upvotes: 0

Views: 46

Answers (2)

yanman1234
yanman1234

Reputation: 1019

This appears to be a postgres issue with your syntax. Change your "" to '' in your replace's. Double quotes identify delimited values, which an empty string can't be representative of, while single quotes just mark a string value. If you require a delimited value here you'll have to add something within the double quotes to make it not an empty string.

As Vao Tsun has pointed out, you will likely need to change your UPDATE to an INSERT. UPDATE modifies current rows, while INSERT adds new rows. Since TRUNCATE TABLE removes all of your rows, you will none to update in the following query. This won't throw an error since this is valid, but you will likely see no effect from the query.

Upvotes: 1

Vao Tsun
Vao Tsun

Reputation: 51629

  1. after you truncate table you don't have rows to update - you need insert instead.

  2. double quotes are used for identifiers - databse objects, so in replace function you use 'string' and "column_name"

Upvotes: 1

Related Questions