PostgreSQL

17 Oct 2024 08:38

Hi, I need help with my 'json_playload' column as datatype JSONB with the remaining all other columns, the table has only 200 records but the json_ payload column is huge. Hence while retrieving the data from table using select * query, it is taking longer for output. 1 example for the json_ payload column : {"SchemaName":"dbo","ChangedTableName":"StmData","NumberOfRows":2,"Batches":1,"Lsn":"0x000010620000335E0007","TranEndTimeUTC":"2024-09-17T16:23:43.803"},
{"SchemaName":"dbo","ChangedTableName":"StmData","NumberOfRows":2,"Batches":1,"Lsn":"0x00001062000033C10007","TranEndTimeUTC":"2024-09-17T16:24:27.597"},
{"SchemaName":"dbo","ChangedTableName":"StmData","NumberOfRows":2,"Batches":1,"Lsn":"0x00001062000033C40005","TranEndTimeUTC":"2024-09-17T16:24:27.597"}]

PostgreSQL

17 Oct 2024 08:24

I don't like obsolete relations in pg_class after partial restore

PostgreSQL

17 Oct 2024 08:19

Firstly, write a proposal to pgsql hackers with intended design for pg_basebackup

PostgreSQL

17 Oct 2024 07:36

I bet there must be someone more crazy

PostgreSQL

17 Oct 2024 07:33

yeah, I mean are there any disadvantages of being "too crazy"?

PostgreSQL

17 Oct 2024 07:19

“too crazy”
Are there any disadvantages?

PostgreSQL

17 Oct 2024 07:15

Wal-g https://github.com/wal-g/wal-g has partial restore feature btw.

PostgreSQL

17 Oct 2024 01:07

Hello Gurus ..any expert willing to help setup Postgres patroni cluster , willing to pay for the expertise .Will need to show failiver,failback,recovery,ha etc ..please let know the total fees ...admins please remove this chat if this is not the place or if this is not alllowed

PostgreSQL

16 Oct 2024 23:32

Excel of all tools is not a reference for checking the sanity of a CSV...
I'm quite certain you'll have to adjust your quoting and/or delimiter settings (COPY expects tabs, Excel expects semicolons (in COMMA separated values, oh the irony))

PostgreSQL

16 Oct 2024 23:28

What copy command are you using?

PostgreSQL

16 Oct 2024 23:26

It means one of the rows has more columns than expected. Usually happens for example when you have unquoted text


For example this csv file:

id,sentence
1,Hello
2,I'm fine thanks
3,I like bananas, you?

PostgreSQL

16 Oct 2024 23:23

hello, please can anyone help?
I tried import csv file into my PostgreSQL 14 server and it is giving me error
ERROR: extra data after last expected column

PostgreSQL

16 Oct 2024 23:06

the problem is the join is based on the M-M relation, the index for the FK is there, but the date to sort it is in the B table

PostgreSQL

16 Oct 2024 23:05

Would have thought lateral would be the way to go. Is there an index on the column determining the 'first' row?

PostgreSQL

16 Oct 2024 23:02

Uh well, these days the optimizer will produce the same plan for a CTE and a classic JOIN anyway.

*Do* you have an index on that a_id column?

PostgreSQL

17 Oct 2024 08:26

It is very dangerous and I don't like it

PostgreSQL

17 Oct 2024 08:23

Nope. It would be too costly to have wal-g features in basebackup.
What exactly is wrong with current design? Don’t you like that oid-relfilenode map?

PostgreSQL

17 Oct 2024 08:15

We are not crazy. We are ordinary people. Our feature is misdesigned, that's why it is crazy

PostgreSQL

17 Oct 2024 07:34

Zero change of being adapted by anyone except us.
It is an disadvantage

PostgreSQL

17 Oct 2024 07:20

Yes, design is wrong. I know it for sure, it is designed by me

PostgreSQL

17 Oct 2024 07:16

Maybe pg_basebackup should have too. But wal-g design of this feature is too crazy for being adapted by pg community as-is

PostgreSQL

17 Oct 2024 06:51

dm I could help yeah

PostgreSQL

16 Oct 2024 23:35

Also, the encoding must be the same as of the database, and should include the first row as headers also in pgadmin

PostgreSQL

16 Oct 2024 23:32

You're using pgadmin?

PostgreSQL

16 Oct 2024 23:28

I opened it on excel, it looks so arranged

PostgreSQL

16 Oct 2024 23:26

the a_id column is in join table B only have date

PostgreSQL

16 Oct 2024 23:08

Maybe an index on B.date or composite of B.a_id, B.date might make the lateral approach fast.

PostgreSQL

16 Oct 2024 23:06

nope, how can I do that?

PostgreSQL

16 Oct 2024 23:04

Mind sharing the plan? On explain.dalibo.com for example?

PostgreSQL

16 Oct 2024 23:01

OP obviously was at the *very beginning* of his PG journey. As much as I'd *love* to give every newbie coming here a 4-day beginners course, *especially* on backup & recovery, as much do I not have the time to do so.

I do link my 50-min talk here on a regular basis (it seems), which I - to this day - conceive as a nice primer.

Let's just settle on that we won't overwhelm newbies with bold claims like "pg_dump is not a backup tool" in the future, eh?