#psql

The concept of upsert in postgres....It's something like...

*Inserting* when the data is novel to the system & *Updating* when the data is already in the system (but how do it know?)

In Postgres, UPSERT is performed via INSERT...

But How Do It Know?

we must specify with *ON CONFLICT*

INSERT INTO tasks (name, priority) VALUES ('Respond', 3) ON CONFLICT DO NOTHING

If there is a unique constraint on name, and 'Respond' already exists in the database, nothing will happen.

But what if we want to update the 'Respond' row?

INSERT INTO tasks (name, priority) VALUES ('Respond', 300) ON CONFLICT (name) DO UPDATE SET priority = EXCLUDED.priority

See here, that the column name must be specified.

BONUS: You can call functions to unite the original row value with the incoming row value. Let's look at it

INSERT INTO tasks (name, priority, tags_array) VALUES ('Respond', 3, ARRAY['urgent']) ON CONFLICT DO UPDATE SET priority = EXCLUDED.priority tags = array_cat(EXCLUDED.tags, tasks.tags)

But what about trying to do this seemingly basic task in the twisted clojure honeysql syntax...

It cannot be as easy as this, can it?

Hmmm.....yeah it doesn't look too ****** up, I guess...

So, we have access to a function called (hdb/upsert-values-set-excluded) and another function called (hdb/upsert-values-do-nothing)

Let's start with the easier one I guess:

(hdb/upsert-values-do-nothing customer-schema/tasks [{:name "Respond" :priority 3] [:name] {:tracking? false})

Right, so it's a vector of values...that makes sense. You specify the "ON CONFLICT" columns in the third argument. Okay, sure. The tracking? map appears to be an option that specifies whether or not we pass auditing information to the database. OKAY, I UNDERSTAND EVERYTHING. If there is already a database row with :name value provided, then the row will be left alone, no update at all.

Looks like it's the same deal for upsert-values-set-excluded. For some reason, "set-excluded" confused me in the past. It still seems like a bad name for the function. I think I would have called it upsert-values-set-on-conflict, but that's my brain and I am a young old man.

Let's say we have a unique constraint on two columns and we want to upsert on them...

(hdb/upsert-values-set-excluded customer-schema/cars [{:make "Chevrolet" :model "Prizm" :rating 2000}] [:make :model])

If Chevrolet Prizm already exists in the database, the row will be updated with the new rating. It looks like our codebase doesn't have built-in ability to make function calls upon SETTING values.

Future topics I'd like to understand:

Async Clojure

Websockets

When working with multiple PostgreSQL databases at the same time, it is essential to know how to list them. It can be necessary when you are getting familiar with a new environment or performing server maintenance.

This article, provide you with an illustrated guide on how to show databases in PostgreSQL, filter them, and order them according to certain criteria. You'll find out about:

How to show databases from the command line

What is the basic syntax

How to filter the database list

How to sort the database list

What permissions are required to get the list of databases

If you run a SELECT * query in PSQL, and you only see one row of results (unless if you expand your terminal window to fit your entire computer screen), then it’s probably because you have paging on.

To actually view all the rows from the table, you can either leave paging on and use the space bar, or turn paging off and just vertically scroll through all the results.

To turn paging off, just run this line of code in whatever terminal is running your PSQL: \pset pager off.

https://stackoverflow.com/questions/13581463/filling-all-column-of-a-table-with-specific-value-in-postgresql

http://www.postgresqltutorial.com/postgresql-add-column/

\c my_database - выбрать БД

\d+ - список таблиц с размером на диске