Skip to content

Instantly share code, notes, and snippets.

@laroo

laroo/postgresql_csv2table.sql

Created July 3, 2019 09:59
Show Gist options
  • Save laroo/53b528665993116db44fd4487e88fa00 to your computer and use it in GitHub Desktop.
Save laroo/53b528665993116db44fd4487e88fa00 to your computer and use it in GitHub Desktop.
Download ZIP
Quick CSV to table/CTE in PostgreSQL (RDS Aurora)
Raw
postgresql_csv2table.sql
WITH csv_data AS (
SELECT string_to_array(regexp_split_to_table(
'2019-07-01,6001,49
2019-07-02,6001,91
2019-07-03,6001,23
2019-07-04,6001,79
2019-07-05,6001,16
2019-07-06,6001,
2019-07-07,6001,
2019-07-08,6001,54
2019-07-09,6001,48
2019-07-10,6001,36
2019-07-11,6001,06
2019-07-12,6001,34', '\n'), ',') as csv_row
)
SELECT
csv_row[1]::date AS order_date,
csv_row[2]::int AS item_id,
NULLIF(csv_row[3], '')::int AS units
FROM
csv_data
@laroo
Copy link
Author

laroo commented May 2, 2022

Quick CSV to table/CTE in AWS Redshift:

WITH csv_data AS (SELECT 
'2019-07-01,6001,49
2019-07-02,6001,91
2019-07-03,6001,23
2019-07-04,6001,79
2019-07-05,6001,16
2019-07-06,6001,
2019-07-07,6001,
2019-07-08,6001,54
2019-07-09,6001,48
2019-07-10,6001,36
2019-07-11,6001,06
2019-07-12,6001,34'::text as dta
)
, csv_rows AS (
SELECT
  row_cnt.row_no,
  SPLIT_PART(csv_data.dta, '\n', row_cnt.row_no) AS row_data
FROM csv_data
CROSS JOIN (SELECT generate_series(1,1000) as row_no) as row_cnt -- update when more than 1000 rows
WHERE row_cnt.row_no <= regexp_count(csv_data.dta, '\n')
)
SELECT 
  csv_rows.row_no,
  SPLIT_PART(row_data, ',', 1)::date AS order_date,
  SPLIT_PART(row_data, ',', 2)::int AS item_id,
  NULLIF(SPLIT_PART(row_data, ',', 3), '')::int AS units
FROM csv_rows

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment