Feedback-ID: i76984098:Fastmail
Message-ID: <419abc41-4683-4eaf-9381-0a1188a3e262@aklaver.com>
Date: Wed, 24 Jul 2024 12:50:13 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: re-novice coming back to pgsql: porting an SQLite update
 statement to postgres
To: Dan Kortschak <dan+pgsql@kortschak.io>,
 Dominique Devienne <ddevienne@gmail.com>
Cc: Vincent Veyron <vv.lists@wanadoo.fr>, pgsql-general@postgresql.org
References: <1aea530e4c96109b91fdde53a29e38beb625dc77.camel@kortschak.io>
 <20240723221121.51f2b3872d0ebfc36a6fa8ff@wanadoo.fr>
 <2f11b2ba-3182-492a-ab46-23cfa5ec913c@aklaver.com>
 <CAFCRh-8d-gsERdu-6W2fb0d6HLi8_bPkS7zVqm0J4ZakpvJQYw@mail.gmail.com>
 <735913e4c9601a2a7ad57d2253d74451c42ba899.camel@kortschak.io>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <735913e4c9601a2a7ad57d2253d74451c42ba899.camel@kortschak.io>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/419abc41-4683-4eaf-9381-0a1188a3e262%40aklaver.com>
Precedence: bulk

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.klaver@aklaver.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.klaver@aklaver.com