PostgreSQL

04 Dec 2024 05:36

ok thank. that part show on PGDATA by default

PostgreSQL

04 Dec 2024 05:34

Yes, you can usually find it:
SHOW data_directory;

PostgreSQL

04 Dec 2024 05:23

Effectively a Tablespace in PG is just a folder.
When that device fills up, you are cooked.

Unlike Oracle (where you can add additional "files" to a tablespace, that can be on any or many different drives)...
This is one of the limitations of PG.
When you start running out of space. You need to create a new tablespace somewhere else (on a new drive).
And start moving things to be stored there.
Or you need to use a type of storage that allows you to add to the filesystem.

HTH

PostgreSQL

04 Dec 2024 03:27

anyone experience on that pls help clearify me or provide support document.thank q

PostgreSQL

04 Dec 2024 03:26

Hello team,
I wonder one thing on postgres regrading with Posgres tablespace.

by default is will using pg_default for all database creation .

PostgreSQL

03 Dec 2024 20:39

SELECT *
FROM (
    SELECT product, year, sales
    FROM sales_data
)
PIVOT (
    SUM(sales)
    FOR year IN (2021 AS year_2021, 2022 AS year_2022)
);

PostgreSQL

03 Dec 2024 20:37

Oracle has something, I believe. But I don't know how they make it work.

PostgreSQL

03 Dec 2024 20:34

Said another way, introducing a new SQL construct because it doesn't feel right to you might actually be a great idea, but not if the new thing also doesn't feel right....

PostgreSQL

03 Dec 2024 20:32

I could live with that. It's also probably not worth introducing a whole new SQL construct just because I don't like the way passing queries as strings "feels." :-)

PostgreSQL

03 Dec 2024 20:30

To be more clear. You would use the (...) to define the columns returned.

That's interesting. But that would create a rather specific object that needs to be destroyed/recreated as the years would change in the future?

CREATE CROSSTAB mycrosstab(product TEXT, year_2021 int, year_2023 int) AS SELECT …

PostgreSQL

03 Dec 2024 20:21

I was thinking of something like view syntax:

CREATE CROSSTAB mycrosstab(...) AS SELECT …

PostgreSQL

03 Dec 2024 20:12

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.

PostgreSQL

03 Dec 2024 20:03

Yes, so it was WHAT I was suspecting... You fixed it by using an Aggregate type query in the WITH () section.

PostgreSQL

03 Dec 2024 20:01

It is so hard query for me

PostgreSQL

03 Dec 2024 19:59

Thank you! I can use English with no problem. Amm, I resolved it.

PostgreSQL

04 Dec 2024 05:36

remember, you can run out of diskspace for 2 reasons. WAL (logging), and DATA (tables,rows, etc).

PostgreSQL

04 Dec 2024 05:30

ok noted, base on default_tablespace it's will base on on PGDATA , and PGDATA will depend on disk drive i am correct?

PostgreSQL

04 Dec 2024 03:51

do you have any recommendation to share about geojson
is possible to save it from a POST api rest and save it in a postgres db

sample something like this
https://gist.github.com/rogergcc/913dee43310d35cda9a3383e094bed1b

or a simple crud project about this

PostgreSQL

04 Dec 2024 03:26

my question is the pg_default tablespace is auto growth base on filesystem ?

PostgreSQL

04 Dec 2024 01:52

A while ago, I was editing this PostgreSQL wiki page:

https://wiki.postgresql.org/wiki/Count_estimate

The function count_estimate has the same issue; it accepts a query as text.

I spent a little time playing around but couldn't figure out how to pass an actual query to it, thus ended up adding a warning:

Do not pass an unsanitized query to this function, as it is subject to SQL injection.

PostgreSQL

03 Dec 2024 20:39

Oracle uses a PIVOT syntax and simple sub-query logic. No strings required.

PostgreSQL

03 Dec 2024 20:36

Yeah, that kind of dynamic column list was something SQL didn't have a design item, and it pops up once in a while.

PostgreSQL

03 Dec 2024 20:33

I think there's something weird about the way it works right now, and not in a good way. It is unclear to me what would be better, though.

PostgreSQL

03 Dec 2024 20:31

We both said the same thing at basically the same time. Jinx! LOL

PostgreSQL

03 Dec 2024 20:30

A tricky point about this is that in a crosstab, you don't know what the output column labels should be until you see what rows you get. But that means you don't know it until execution time. Which is a problem because the plan needs to contain that information. So maybe your CREATE CROSSTAB mycrosstab syntax would have to include a column list.

PostgreSQL

03 Dec 2024 20:20

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?

PostgreSQL

03 Dec 2024 20:06

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).

PostgreSQL

03 Dec 2024 20:02

Whole day I was trying resolved it

PostgreSQL

03 Dec 2024 20:00

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;
    """

PostgreSQL

03 Dec 2024 19:57

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!

Кроме того, я надеюсь, что вы используете настольную версию (как и я), чтобы вы могли перевести это на русский язык, если вам это нужно!