sql 2021-08-06 | Slack Archive

vemv00:08:52

Anyone has a favorite way to sharpen up the SQL skills? (non-Clojure specific, but I'd use honeysql as the medium) Mine have had their ups and downs as one can use different data stores or abstractions over the years... I bet there are cool corners to be discovered anyway :)

vemv02:08:31

Cheers. PL/SQL is a bit outside of my personal scope, still an interesting pointer So far https://www.masterywithsql.com/ has caught my attention. The video format is not my jam, but I still can do it as they're bite-sized

isak03:08:34

Other than reading docs regarding all the functions/syntax included with the database, trying puzzles people put out, especially ones not obviously really geared toward SQL. For example: https://github.com/mikehadlow/Journeys

isak03:08:02

Sodoku might also be a good challenge (if your database has a reasonable way to do it)

Akiz12:08:58

Hi, I am trying to import multiple CSVs to Postgresql DB. I need to convert values automatically to be compatible with target Postgresql table so it won’t fail as following:

ERROR: column "gcagt6" is of type date but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.
  Position: 187

I am basically looking for automatic conversion like PSQL does. What would be the simple way? The only library i am using right now is next.jdbc.

Akiz15:08:24

I am trying to insert a string comptible with postgresql date - so i dont need any magic here. Just to export table to csv and then import it again using “sql casting” so i dont have to generate type casting into next.jdbc preparestatement

emccue15:08:47

Do you know which column needs to be a date?

emccue15:08:31

maybe https://jdbc.postgresql.org/documentation/publicapi/org/postgresql/util/PGobject.html

🧑‍🎓 3

emccue15:08:38

and don't set the type?

emccue15:08:05

also maybe [:lift "param"] might do it

emccue15:08:18

and maybe those need to be used together

Akiz16:08:24

@emccue i dont. I would have to check database schema first and this is something i am trying to avoid (maybe this is the only way? I would love to know how Dbeaver solves this)

deleted17:08:20

oh wait i misread your question maybe

Akiz17:08:40

@U8QBZBHGD 🙂. I just want JDBC to use String “2020-01-01” and insert it into database and do not care if there is varchar, text or date. Like PSQL Copy or sql insert does.

Akiz17:08:50

Yeah, but lets say that i will extend protocol of string “2020-01-01” to be date. But in the second CSV it will be string / varchar so it will fail during import as it will be casted to date…

Akiz18:08:06

This is the use case: there will some csv files generated every day. They will be called like public.table1.csv, public.table2.csv etc. Their name determines the target database schema.table. The public.table1.csv will contains

event, date
error, 2020-01-1
warning, 2018-02-21

It’s target table has structure: • event: varchar(23) • date: varchar(10) The public.table2.csv will contains

name, birth, car
John, 2000-01-13, Renault
Martin, 1983-04-21, Peugor

It’s target table has structure: • name: varchar(255) • birth: date • car: varchar(50) I want to load every created CSV to Postgresql automatically - every day. And it fails with birth being date..

Akiz19:08:42

Yep. I will query information_schema for types in table and then i will use next.jdbc.types to convert Strings to according types.

2021-08-06

Channels