PostgreSQL

25 March 2026 21:46

I have to migrate and need help 🙏

PostgreSQL

25 March 2026 21:44

Hello
I am looking for someone who has successfully migrated dbs from SQL server to postgresql?

PostgreSQL

24 March 2026 23:24

The only time i tried using cursor, it destroyed my whole project without asking by filling with LLMese, i suppose there's not enough training data from WMS :P

PostgreSQL

24 March 2026 23:21

You can do something like send the output of /dt of the needed table and then say Please complete this query, that should return a json object using json_build_object: __SELECT {item.*}, {location.*} FROM ... WHERE item.barcode = *insert function to check if the barcode is a valid GS1 datamatrix paylod, using ~ and a regex*

PostgreSQL

24 March 2026 23:17

Do you really trust AI crap to rewrite of queries? If you have complicated functions with large number of sqls then figuring out where AI messed up is going to be bigger pain than actually doing rewrite with manual or non-LLM based tools.

PostgreSQL

24 March 2026 22:01

Chatgpt, claude code, gemini, DeepSeek-V3.2 and Qwen3.5. All to generate elephant meme for my blog.

PostgreSQL

24 March 2026 12:19

Any real timers who is working in postgre SQL???

PostgreSQL

23 March 2026 21:51

Yes, I know it. Knew it, I should say.

PostgreSQL

23 March 2026 13:18

Yes, the table at the other end can AFAIK be different as long as the columns selected for reproduction are compatible with the replicated data. The same with indexes (which can be useful first to speed up initial reproduction as well as for ongoing use). However it is up to you to manually reapply DDL changes (if this is desired) because they are really two separate databases/clusters in a logical sense, you just have agreed to maybe copy or apply or delete some columns of some rows of some tables of it as they are in the other one.

PostgreSQL

23 March 2026 11:25

Oh, wait! If you didn't have any identity, how would you have a subscriber?

PostgreSQL

23 March 2026 11:17

Great thanks! this sounds like something possible to do.
One more thing - when I create a table by default REPLICA IDENTITY is DEFAULT = NOTHING .
Now if I want to change it to

ALTER TABLE test REPLICA IDENTITY FULL;

PostgreSQL

23 March 2026 11:07

I was travelling over the weekend, also wanted to let that sink in a bit...

First of all: I do understand your frustration with psql. Especially on Windows it sucks, and hard so. Goes for other platforms without proper readline support too (looking at you, AIX!).

But as many here know, I use it *a lot*. As a matter of fact, I don't use any GUI unless I'm forced to. And I second @unfoxo in saying I'm quite happy with it. Yes, tab completion leaves a lot to wish etc., and sometimes, when features get added, I think "that's smart, I wonder why that wasn't in there before", but hey.

Rewriting psql will face one major culprit IMNSHO: the metric gigaton of tooling that has been written around it in the past two and a half decades. A rewrite would have to make sure that the new tool behaves *exactly* like the old one, anywhere on the globe, with any kind of legacy data you throw at it etc.pp. Remember the pain that pgAdmin4 brought to all those pgAdmin3 users? And now we're talking about batch stuff that maybe runs once a year, at 00:00 on January 1, and can't be repeated when it fails. That level of backwards compatibility.
There's probably a reason why SQLPlus is among the worst pieces of software out there... 😏

That will probably break the deal if you suggest a rewrite on -hackers.

That being said, I could well imagine having a psql2. I really like pgcli for example (_much_ better tab completion, dropdown suggestions, ...), and I started using pspg as my $PSQL_PAGER recently. But those tools have the downside that they need to be added to your servers, which often means a lot of red tape etc. If a fancy, modern psql2 would come with the default postgresql-client package, OTOH...

PostgreSQL

22 March 2026 18:11

Or else Postgresql official docs could help you better

PostgreSQL

22 March 2026 18:01

Anyone have postgresql architecture

PostgreSQL

22 March 2026 17:40

You can have physical replication as long as wal_level is replica or higher (aka logical).

PostgreSQL

25 March 2026 21:44

Ping me if anyone has done that

PostgreSQL

25 March 2026 09:08

We had hundreds of Oracle (+) styled join queries.
We were able to have the AI convert these to proper JOIN syntax, and cleaning the where clauses.
I would say it was 97% accurate. It also fixed the CONNECT BY syntax.

And some of these were NARLY SQL statements (most of them were views).

In fact, one trick we used when we had "incompatible" queries, was to create views for them.
So we were nudging the old code (Clients, Web, etc) into the direction we were going, and hiding the syntax changes.
Then we were rewriting the Oracle stuff to PG Compatible.

But YES... We trusted it a lot. After testing.
Also, keep in mind, we have a testing harness, so EVERY time we fixed a query, we compared the output to both versions.

PostgreSQL

24 March 2026 23:23

Then i'll have to probably tune/adjust the query anyway, but that's expected

PostgreSQL

24 March 2026 23:19

It depends on what i'm doing. I use it as a smart search/replace/refactor queryable with natural language

PostgreSQL

24 March 2026 22:17

pgadmin4, psql
zhipu ai or perplexity, but only to ask stuff or mechanical work (like, "rewrite all queries to use table xyz instead of abc)

asyncpg everywhere

PostgreSQL

24 March 2026 21:27

For the DBA/ Data engineers here -
Mind to share which tools you work with on daily basis?
And how you use AI in your work?

PostgreSQL

24 March 2026 11:28

If anyone has Panther Lake aka Core Ultra Series 3 (or will be getting Nova Lake) on Linux, this might be worth enabling manually with 'fred=on' judging by the PG performance: https://www.phoronix.com/review/intel-fred-panther-lake/4

PostgreSQL

23 March 2026 21:48

Not sure event trigger is even needed, just found out about pglogical extension. Did you know this?

PostgreSQL

23 March 2026 11:30

I am just playing around with it -
I created pub-sub all working on the tables created before.
Now creating some dummy table so by default I see its creating it with IDENTITY DEFAILT = NOTHING
Then I tried to test DELETE (even without PK) so it raised error then I changed the IDENTITY to FULL but this didn't work until I run the same ALTER also on the SUB.
I guess maybe because logical replication doesn't replicate DDL the ALTER TABLE doesn't replicate also on the SUB.
does it make sense?

PostgreSQL

23 March 2026 11:25

Not sure tbth. Can't hurt, I guess.
But why don't you define a proper PRIMARY KEY?!?

PostgreSQL

23 March 2026 11:10

Because the publication as such is static. There's no magic command to tell PG "please watch all schema changes and adapt this publication to include them". Keep in mind that your subscribers need to cope with the changes as well.

If you want that, you could probably write an event trigger for it. Don't forget to blog about it if you succeed! ;-)

PostgreSQL

23 March 2026 10:43

im having some problem with logical replication
I set

CREATE PUBLICATION my_pub FOR ALL TABLES;

ALTER SUBSCRIPTION my_sub REFRESH PUBLICATION;
GRANT USAGE ON SCHEMA public TO replicator;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO replicator;

PostgreSQL

22 March 2026 18:11

Do you wish to have a look at https://www.interdb.jp/pg/

PostgreSQL

22 March 2026 17:46

People used minimal to save on wal size and io but that was worth a decade ago. Current generation hardware (onprem) it hardly matters.

PostgreSQL

22 March 2026 14:31

also for physical replication?? 😮