2830
English speaking PostgreSQL public chat. This group is for discussions on PostgreSQL-related topics and strives to provide best-effort support as well.
I am curious... Exactly how do you pass a query? Would it be an open cursor? Or the name of the prepared statement to be executed?
Читать полностью…
Here is a SIMPLE TRICK for the future. Aggregations can cause confusion.
So, I recommend you remove ANY Aggregation, and run the query for a limited set of data.
This will help you discover things like "statuses" or other tables that reference the same package more than once.
Keep in mind, if I join Cars, with Color_Options (by allowed_for_car_id, for example). I expect to see the SAME Car_ID for each color that is allowed. (Joining is like multiplying!) [Matrix Multiplying is one way to think of it]
But we live in a world where we think we are joining just "one" attribute (status), and if there are multiple, then we get multiple answers... (it happens all of the time).
Now my query looks like that and it is working well for me
query = """Читать полностью…
WITH aggregated_packages AS (
SELECT order_id,
COUNT(id) AS package_count,
SUM(weight) AS package_weight
FROM cdek_package
GROUP BY order_id
),
order_status AS (
SELECT order_id,
MIN(name) AS status_name_last
FROM cdek_statuses
GROUP BY order_id
)
SELECT co.id AS cart_order_id,
co.status AS cart_order_status,
COALESCE(ap.package_count, 0) AS package_count,
COALESCE(ap.package_weight, 0) AS package_weight,
coe.id AS cdek_order_id,
coe.phones,
coe.cdek_number,
coe.address,
coe.name,
coe.total_sum,
coe.date_time,
os.status_name_last,
fo.address AS free_order_address,
fo.phone AS free_order_phone,
fo.buyer AS free_order_buyer,
osh.data AS order_status_data,
sp.place_weight
FROM cart_order AS co
LEFT JOIN cdek_order AS coe ON co.id = coe.external_order_id
LEFT JOIN aggregated_packages AS ap ON coe.id = ap.order_id
LEFT JOIN order_status AS os ON coe.id = os.order_id
LEFT JOIN free_order AS fo ON co.id = fo.external_order_id
LEFT JOIN order_status_history AS osh ON fo.external_order_id = osh.order_id AND osh.status = 9
LEFT JOIN store_places AS sp ON co.id = sp.order_id
WHERE co.status = 6
GROUP BY co.id, co.status, coe.id, coe.phones, coe.cdek_number, coe.address, coe.name, coe.total_sum,
coe.date_time, fo.address, fo.phone, fo.buyer, osh.data, sp.place_weight, ap.package_count, ap.package_weight, os.status_name_last
ORDER BY co.id DESC;
"""
First, please use English here, there is a Russian group out there.
Second, I would DEBUG this by adding to the query:
min(p.weight), max(p.weight).
I would also add COUNT(p.id) as "record_count"
This will tell you how to find the problems. you could have an extra record somewhere (from the joins).
And it is basically bringing that base record in twice.
Imagine there are 2 statuses. Back-ordered and Shipped. Because this package WAS back-ordered.
Those 2 records both apply to this p.id so the COUNT (with distinct) shows a 1, but the weight is doubled.
My count, min(), max() will give you enough detail to debug this better.
HTH.
Also, I hope you use the desktop version (like I do) so you can Translate this to Russian, if you need that!
Кроме того, я надеюсь, что вы используете настольную версию (как и я), чтобы вы могли перевести это на русский язык, если вам это нужно!
No.
You can't trust any such tools (for any DB that allows much tuning — including e.g. Oracle).
Always recheck results with tests.
# WARNING
# this tool not being optimal
# for very high memory systems
# DB Version: 17
# OS Type: linux
# DB Type: web
# Total Memory (RAM): 128 GB
# Data Storage: hdd
Привет!
Не получается рассчитать общий вес pacages из таблицы cdek_package. В одном заказе может быть несколько package с разным весом. Мне необходимо посчитать этот общий вес коробок в заказе. Сейчас мой запрос выглядит вот так, но он почемуто умножает вес на 2
query = """
SELECT co.id AS cart_order_id,
co.status AS cart_order_status,
COUNT(DISTINCT p.id) AS package_count,
SUM(p.weight) AS package_weight, -- здесь мой вес
coe.id AS cdek_order_id,
coe.phones,
coe.cdek_number,
coe.address,
coe.name,
coe.total_sum,
coe.date_time,
MIN(cs.name) AS status_name_last,
fo.address AS free_order_address,
fo.phone AS free_order_phone,
fo.buyer AS free_order_buyer,
osh.data AS order_status_data
FROM cart_order AS co
LEFT JOIN cdek_order AS coe ON co.id = coe.external_order_id
LEFT JOIN cdek_package AS p ON coe.id = p.order_id -- таблица с коробками
LEFT JOIN cdek_statuses AS cs ON coe.id = cs.order_id
LEFT JOIN free_order AS fo ON co.id = fo.external_order_id
LEFT JOIN order_status_history AS osh ON fo.external_order_id = osh.order_id AND osh.status = 9
WHERE co.status = 6
GROUP BY co.id, co.status, coe.id, coe.phones, coe.cdek_number, coe.address, coe.name, coe.total_sum,
coe.date_time, fo.address, fo.phone, fo.buyer, osh.data
ORDER BY co.id DESC
"""
how to dump table 10 partition in one table in postgres? Pls help me.
Читать полностью…
According to sequelize doc, here is an example of how to declare the connection url:
const sequelize = new Sequelize('postgres://user:pass@example.com:5432/dbname') // Example for postgres
It differs from yours, it think you should fix yours and confirm if your connection properties are valid for your postgres environment
We need more info. What is the program you are trying to run?
Are you able to connect via psql?
Your error seems like some encoding issue..
Today in idle wishes for PostgreSQL features: A version of tablefunc's crosstab that takes an actual query rather than a string. I dunno, the "pass a query as a string" thing always feels a bit unfinished to me.
Читать полностью…
Yes, so it was WHAT I was suspecting... You fixed it by using an Aggregate type query in the WITH () section.
Читать полностью…
Thank you! I can use English with no problem. Amm, I resolved it.
Читать полностью…
It depends what you mean by "trust." The results will not be completely insane, and are a reasonable starting point. But you should never trust any tool, no matter how sophisticated, to do a complete job of parameter setting.
Читать полностью…
use it as base line that helps you to start tuning things rather then a single source of truth
Читать полностью…
Simply put, max_wal_size tells the system how much changelog it may accumulate until a CHECKPOINT is forced, while wal_keep_size tells the system to keep WALs around even after the CHECKPOINT (although they're no longer needed then!), in case e.g. a replica goes down briefly and requests older WAL segments.
can someone explain wal_keep_size vs max_wal_size? im getting confused
Читать полностью…
my index.js file inside models folder
const Sequelize = require("sequelize");
const sequelize = new Sequelize("gul", "postgres", "123", {
host: "localhost",
port: 5432,
dialect: "postgres"
});
async () => {
await console.log("I am here");
};
sequelize
.sync({ force: true })
.then(() => console.log("Database synced successfully"))
.catch((err) => {
console.error("Database sync error:", err);
process.exit(1); // Exit the process if syncing fails
});
sequelize
.authenticate()
.then(() => {
console.log("Connection has been established successfully.");
})
.catch((err) => {
console.error("Unable to connect to the database:", err);
});
const db = {};
db.sequelize = sequelize;
db.Sequelize = Sequelize;
db.product = require("./product_models")(sequelize, Sequelize);
db.comment = require("./comment_moduls")(sequelize, Sequelize);
db.admin = require("./admin_models")(sequelize, Sequelize);
db.order = require("./order_models")(sequelize, Sequelize);
db.order.hasMany(db.product, {
foreignKey: "orderId",
as: "products",
});
db.product.belongsTo(db.order, {
foreignKey: "orderId",
as: "order",
});
module.exports = db;
I am using nodejs and I guess I can connect via psql.
What you mean by more info, I need to share my some code?
Hello guys, I am learning postgresql and when i run my program it throws error like this
Unable to connect to the database: ConnectionError [SequelizeConnectionError]: ������������ "postgres" �� ������ �������� ����������� (�� ������)
at Client._connectionCallback (C:\Users\user\Desktop\Gul\node_modules\sequelize\lib\dialects\postgres\connection-manager.js:145:24)
at Client._handleErrorWhileConnecting (C:\Users\user\Desktop\Gul\node_modules\pg\lib\client.js:326:19)
at Client._handleErrorMessage (C:\Users\user\Desktop\Gul\node_modules\pg\lib\client.js:346:19)
at Connection.emit (node:events:519:28)
at C:\Users\user\Desktop\Gul\node_modules\pg\lib\connection.js:116:12
at Parser.parse (C:\Users\user\Desktop\Gul\node_modules\pg-protocol\dist\parser.js:36:17)
at Socket.<anonymous> (C:\Users\user\Desktop\Gul\node_modules\pg-protocol\dist\index.js:11:42)
at Socket.emit (node:events:519:28)
at addChunk (node:internal/streams/readable:559:12)
at readableAddChunkPushByteMode (node:internal/streams/readable:510:3) {
parent: error: ������������ "postgres" �� ������ �������� ����������� (�� ������)
at Parser.parseErrorMessage (C:\Users\user\Desktop\Gul\node_modules\pg-protocol\dist\parser.js:283:98)
at Parser.handlePacket (C:\Users\user\Desktop\Gul\node_modules\pg-protocol\dist\parser.js:122:29)
at Parser.parse (C:\Users\user\Desktop\Gul\node_modules\pg-protocol\dist\parser.js:35:38)
at Socket.<anonymous> (C:\Users\user\Desktop\Gul\node_modules\pg-protocol\dist\index.js:11:42)
at Socket.emit (node:events:519:28)
at addChunk (node:internal/streams/readable:559:12)
at readableAddChunkPushByteMode (node:internal/streams/readable:510:3)
at Readable.push (node:internal/streams/readable:390:5)
at TCP.onStreamRead (node:internal/stream_base_commons:191:23) {