public inbox for [email protected]  

help / color / mirror / Atom feed

From: Adrian Klaver <[email protected]>
To: Dan Kortschak <[email protected]>
To: Dominique Devienne <[email protected]>
Cc: Vincent Veyron <[email protected]>
Cc: [email protected]
Subject: Re: re-novice coming back to pgsql: porting an SQLite update statement to postgres
Date: Wed, 24 Jul 2024 12:50:13 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<[email protected]>
	<CAFCRh-8d-gsERdu-6W2fb0d6HLi8_bPkS7zVqm0J4ZakpvJQYw@mail.gmail.com>
	<[email protected]>

On 7/23/24 17:23, Dan Kortschak wrote:
> On 7/23/24 13:11, Vincent Veyron wrote:
>> On Mon, 15 Jul 2024 20:31:13 +0000
>>
>> This is the goto page for anything SQL :
>> https://www.postgresql.org/docs/current/sql-commands.html
>>
>> For DateTime types :
>> https://www.postgresql.org/docs/current/datatype-datetime.html
>>
>> For JSON types :
>> https://www.postgresql.org/docs/current/datatype-json.html
> 
> Thanks, I will work through those.
> 
> 
> On Tue, 2024-07-23 at 23:52 +0200, Dominique Devienne wrote:
>> On Tue, Jul 23, 2024 at 10:35 PM Adrian Klaver
>> <[email protected]> wrote:
>>> Just know that SQLite does not enforce types [...]
>>
>> That's true, and applies to the OP's schema.
> 
> Thank you both. Yes, I was aware of this weirdness of the schema (I
> inherited it) and was shocked that it worked when I relaised. I'll be
> happier when types are properly enforced, but I don't think I can
> retrospectively enforce that on the SQLite implementation I have.
> 

Which gets back to verifying the data coming from SQLite will work in 
the Postgres tables with the Postgres types specified in the table 
definitions.

You can either:

1) Just import the data into the Postgres tables as defined and see if 
it works and if not what blows up.

2) Create Postgres staging tables that have all the column type's set to 
varchar or text for every column. Then import the data. Then you could 
do select col::<the_type_desired> from the_table and see what works and 
what fails.

-- 
Adrian Klaver
[email protected]

view thread (9+ messages)  latest in thread

reply

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: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: re-novice coming back to pgsql: porting an SQLite update statement to postgres
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox