In order to migrate the POSGRE database to AWS dynamicb, we would like to extract it in json format first.

In order to migrate the POSGRE database to AWS dynamicb, we would like to extract it in json format first.
Therefore, we have considered the following statements to range multiple tables and output select statements to json format for all columns in the range.

SELECT to_json(table_100) from table_100 date(timestamp) BETWEEN'198707050010'AND'202007052400';

However, if things go on like this, there will be a sintax error in between.

It will be a long process to migrate the POSGRE database to AWS dynamicb.

json postgresql amazon-dynamodb

2022-09-30 21:45

1 Answers

There are about two problems with SQL mentioned in the questionnaire.

If you still get errors after dealing with these two points, please post a new question with the error message you see.

If you specify a date and time, there must be a space between

I think the revised SQL will be as follows.

SELECT to_json(table_100)
FROM table_100
WHERE date (timestamp) BETWEEN'198707050010'AND'20200705 2400';

Also, since I use date(), I think specifying the time makes little sense. For '20200705 2400', 2020-07-06 data cannot be retrieved.

If you also want to retrieve data for 2020-07-06, change the condition to '2020-07-06' or '20200706'.
(If there is no problem with the data up to '2020-07-05', there is no need to modify it.)

Refer to the documentation for an example of the date format available in PostgreSQL.

https://www.postgresql.jp/document/12/html/datatype-datetime.html#DATATYPE-DATETIME-DATE-TABLE

2022-09-30 21:45

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656