News for hackers. Stuff that matters.
Re:Health insurers just published close to a trillion hospit... by orangepurple on Tuesday September 06, 2022 @04:43PM (#32742952)
import a CSV into Postgres

    with open(filepath) as fd:
        first_line = fd.readline()
        cols = []
        for col in first_line.strip().split(','):
            col2 = f'''"{col.strip('"')}" text'''
            cols.append(col2)
        cols2 = ','.join(cols)
        print(f"create table {table_name} ({cols2});")
    print(f"\copy {table_name} from '{filepath}' csv header;")
this variant will ingest whatever trash is in your CSV fields as-is (cast & cleanup later)

run the output in a psql instance connected to your db

(important note: \copy is a psql client command and it is critical to use \copy instead of COPY in many cases where the server process may not have the permission to read your CSV file. with \copy you can read any file the user that launched psql client has permission to read. to make things more confusing it is indeed possible to stream stdin through psql but you use the regular COPY for that instead of \copy)

Re:Health insurers just published close to a trillion hospit... by nardi on Tuesday September 06, 2022 @11:31PM (#32746047)
Do NOT read a CSV file by splitting on commas. Python has a perfect CSV library built right in: https://docs.python.org/3/library/csv.html.

If you split on commas, your code will fail for quoted fields with commas in them.

Re:Health insurers just published close to a trillion hospit... by orangepurple on Wednesday September 07, 2022 @01:47AM (#32746819)
This is obviously a quick and dirty hack. It only splits the header by commas. Postgres has its own read in logic you can adjust by passing arguments to \copy or copy. If you have anything more complex you need to handle it by parsing the header more intelligently than splitting on commas and adding appropriate arguments to copy or \copy
Re:Health insurers just published close to a trillion hospit... by RhodesianHunter on Tuesday September 06, 2022 @11:01PM (#32745894)
Loading up 100TB of csv data into a Postgres db is not a realistic proposition.