From: Salim KOC <salim@saad.onmicrosoft.com>
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Inserting rpt content from a file into a table column in json format.
Date: Sun, 21 Apr 2024 17:34:20 +0000
Message-ID: <9B28B5D6-A3AD-40BB-943A-2AA23A764B3E@saad.onmicrosoft.com> (raw)
Hello everyone,
I have a 5gb rpt file.
Rpt file content is as follows:
(base) skoc@skoc ~ % head -5 '/Users/skoc/postgresql/lx/lxRo.rpt'
lxOid|lxFlags|lxType|lxFromLat|lxFromId|lxToLat|lxToId|lxCrDate|lxRelRul|lxModDate|lxOwner|lxAltOwn1|lxAltOwn2|lxTenant|lxDOV|lxIOV|lxToType
-2147483508|67108864|-542076010|341897887|-40640797|341897887|-40639830|2020-01-24 11:14:52.000|1|2020-01-24 11:14:52.000|-1062416028|-655292577|-1283415274|1|1|1|-2099227551
-2147483247|67108864|-366961227|341897887|-1257644257|341897887|1179980124|2023-04-26 07:43:53.000|1|2023-04-26 07:43:53.000|-1536708294|-655292577|-2119128014|1|-1071039821|-1071043321|708897621
-2147483213|67108864|1076431157|341897887|1128100732|341897887|1128103344|2021-02-05 13:48:55.000|1|2021-02-05 13:48:55.000|-1400837562|-655292577|54575254|1|1|1|-1487879320
-2147482748|67108864|-1075776416|341897887|-215312115|341897887|-215311217|2022-01-24 07:31:56.000|1|2022-01-24 07:31:56.000|1282737577|-655292577|54575254|1|1|1|-60774595
Delimiters is “|”
Current posttgresql version:
PostgreSQL 16.2 on x86_64-apple-darwin20.6.0, compiled by Apple clang version 12.0.5 (clang-1205.0.22.9), 64-bit
plm=# show work_mem;
work_mem
----------
16MB
(1 row)
I created a table with the following command.
CREATE TABLE IF NOT EXISTS public.lxbo
(
c1 serial NOT NULL,
c2 jsonb NOT NULL,
CONSTRAINT lxbo_pkey PRIMARY KEY (c1)
)
The process I want to do:
insert the data whose content is specified above into column c2 in jsonb format.
1-How should the rpt file content be converted to jsonb format?
2-What is the way to import the relevant jsonb file into the table I specified?
3-Do I need to update the work_mem value and/or any other value?
Thanks
Salim KOÇ
salim@saad.onmicrosoft<mailto:salim@saad.onmicrosoft>.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: salim@saad.onmicrosoft.com, pgsql-admin@lists.postgresql.org
Subject: Re: Inserting rpt content from a file into a table column in json format.
In-Reply-To: <9B28B5D6-A3AD-40BB-943A-2AA23A764B3E@saad.onmicrosoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox