Re: re-novice coming back to pgsql: porting an SQLite update statement to postgres

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
>> <adrian(dot)klaver(at)aklaver(dot)com> 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
adrian(dot)klaver(at)aklaver(dot)com