updating table using csv file from query in pgsql

Question

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

Vao Tsun · Accepted Answer · 2017-07-11 12:21:21Z

1

after you truncate table you don't have rows to update - you need insert instead.
double quotes are used for identifiers - databse objects, so in replace function you use 'string' and "column_name"

answered Jul 11, 2017 at 12:21

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

yanman1234 · Accepted Answer · 2017-07-11 12:28:40Z

1

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.

edited Jul 11, 2017 at 12:28

answered Jul 11, 2017 at 12:22

yanman1234

1,01911 silver badges28 bronze badges

Collectives™ on Stack Overflow

updating table using csv file from query in pgsql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related