Feb 18, 2022
PGSync is a middleware for syncing data from Postgres to Elasticsearch effortlessly. It allows you to keep Postgres as your
seen from Finland
seen from Malaysia
seen from United States
seen from United Kingdom
seen from Italy
seen from United States
seen from Netherlands
seen from China
seen from Germany
seen from Türkiye
seen from Palestinian Territories
seen from Türkiye
seen from Türkiye
seen from Thailand
seen from Germany
seen from United States
seen from United States
seen from Ecuador
seen from India
seen from United Kingdom
PGSync is a middleware for syncing data from Postgres to Elasticsearch effortlessly. It allows you to keep Postgres as your
Upsert (SQL)
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
React.js Advanced Concepts
How to show PostgreSQL databases using psql and GUI tools
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
How to get and manage a database list with a PostgreSQL GUI tool
I published this a long time ago, but thought it might be of interest in my latest blog iteration. 10/11/2000 I have been writing b
"The Hidden and Prevailing Ideology, I believe that just about every technical book comes with a body of politics, an ideology that governs and usually restricts its example set. We don't notice the political slant because it reflects the dominant viewpoint in our society and is thus invisible."
I'm trying to see all the data I have in a PostgreSQL table, but as there is plenty of data to make the data fit horizontally I use: \x auto and then: SELECT * FROM table_name; Nevertheless I can
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.
Note: You will have to do this for each table individually; it isn't a global change.
In this article, we are going to learn How to install PostgreSQL (PSQL) in Ubuntu 18.04 - Advanced Open Source Database management system.
psql direct
https://stackoverflow.com/questions/13581463/filling-all-column-of-a-table-with-specific-value-in-postgresql
http://www.postgresqltutorial.com/postgresql-add-column/
https://stackoverflow.com/questions/12618232/copy-a-few-of-the-columns-of-a-csv-file-into-a-table
psql: полезные команды
\c my_database - выбрать БД
\d+ - список таблиц с размером на диске
\d table_name - список колонок указанной таблицы с типами, индексами и ключами