How to import data from AWS S3 into RDS PostgreSQL without including auto-generated primary key ID column in CSV file?

0

Hi, I am following this doc for copying CSV files from S3 directly into an Aurora serverless V2 database in RDS: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PostgreSQL.S3Import.html

However, I’m running into an issue with the primary key column on our RDS table. There is a primary key column in our table called id that is not present in the CSV file I am attempting to upload. This id column gets auto-incremented on generic inserts into the table.

A sample query I am running locally on pgadmin is:

SELECT aws_s3.table_import_from_s3 (
	'schema_ace.journal_entries',
'columna_a,column_b,column_c',
	'(format csv,header true)',
	aws_commons.create_s3_uri (
		'test-s3-bucket',
		'RDS/300_rows.csv',
		'us-west-2'
	)
);

The file I am trying to insert has all the columns in the RDS table except for the id column. When I attempt to run this query it fails. If I add an empty column called ID to the csv file the query everything inserts correctly, however the rows inserted into RDS do not have any values for id.

Does anyone know if there is a way to auto-generate the values for the primary key id column in the table during the copy command without me needing to do anything to the CSV file?

1回答
1
承認された回答

Hi, to achieve what you want,

  1. you can use type SERIAL for your id column: see https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-serial/
  2. then you modify the table_import_from s3 above with only 2 columns instead of 3, the id column will autogenerate via its SERIAL type.

Best, Didier

profile pictureAWS
エキスパート
回答済み 1年前
profile picture
エキスパート
レビュー済み 1年前
  • Thank you, that worked!

ログインしていません。 ログイン 回答を投稿する。

優れた回答とは、質問に明確に答え、建設的なフィードバックを提供し、質問者の専門分野におけるスキルの向上を促すものです。

質問に答えるためのガイドライン

関連するコンテンツ