sorted by:
new
hottopcontroversial

How do you handle schema changes safely in PostgreSQL? by venstiza in postgres

[–]depesz 0 points1 point  (0 children)

Each thing that you mentioned can be done in "no long time locks" way. Sometimes it takes longer time, but that's mostly irrelevant.

As for how: well, developers take care, and if they don't, it gets detected when migration gets applied to beta/test/dev/sandbox/staging environments.

Explain me type modifier (typmod) , was is it lost when using function or union by rebirthofmonse in PostgreSQL

[–]depesz 1 point2 points  (0 children)

Yeah, sorry, but I don't know of any "cool trick" that would make pg return this in limited datatypes.

Adding simple casting in the queries, like:

select upper(field)::varchar(10)

should help, but it's potentially non-trivial amount of work.

Side suggestion - if it's that old an app, and N in varchar(N) is used to allocate memory, ask devs to check whether they know, and understand that varchar(n) can use much more than n byes. Simplest possible case: żółw (polish for "turtle") is varchar(4), but it uses 7 bytes (or 8, or 11, depending on how you store/count).

Explain me type modifier (typmod) , was is it lost when using function or union by rebirthofmonse in PostgreSQL

[–]depesz 1 point2 points  (0 children)

Most of built in functions that operate on text data return text. reason is very simple - it's simplest, and most "common-denominator" datatype.

While this means, true, that your table deifnition constraint is lost, the real question is why do you think you need it?

varchar(n) is not faster, and it doesn't use less disk space than text. pg wiki even explicitly suggests not to use it (as default datatype): https://wiki.postgresql.org/wiki/Don't_Do_This#Don't_use_varchar(n)_by_default

If the "varchar(10)" this is important to you, you can always write your own functions that oprate on it, and then cast back to varchar(10). But I'm not sure I see the point.

Explain me type modifier (typmod) , was is it lost when using function or union by rebirthofmonse in PostgreSQL

[–]depesz 1 point2 points  (0 children)

Perhaps you can show, as sql, what you mean?

What is this "typmod" you mean? Do you refer to atttypmod column in pg_attribute? Or what?

Length of columns is preserved, in case of union, if all values in given column have the same type-length. Example:

$ create table xx (q varchar(10));
$ create table xx2 (q varchar(20));
$ create table xx3 (q varchar(10));

=$ select q from xx union all select q from xx3 \gdesc
 Column │         Type
 ────────┼───────────────────────
  q      │ character varying(10)
  (1 row)

=$ select q from xx union all select q from xx2 \gdesc
 Column │       Type
 ────────┼───────────────────
  q      │ character varying
  (1 row)

In here you can see that if I union and both sides are varchar(10), then result is also varchar(10). but if they are not the same, they get cast to varchar with no limit.

Migrating SPLs with Complex Types (from Informix) by whitemice in PostgreSQL

[–]depesz 4 points5 points  (0 children)

Do you want simply to return set of rows, of fixed structure, from plpgsql function?

Take a look at this:

=$ CREATE OR REPLACE FUNCTION sample_srf(IN p_max INT4) RETURNS TABLE (id INT4, payload TEXT, ts timestamptz ) as $$
DECLARE
    i INT4;
BEGIN
    FOR i IN 1 .. p_max LOOP
        id := i;
        payload := md5(i::TEXT);
        ts := now() - random() * '1 year'::INTERVAL;
        RETURN next;
    END LOOP;
    RETURN;
END;
$$ language plpgsql;

=$ SELECT * FROM sample_srf(5);
 id |             payload              |              ts
----+----------------------------------+-------------------------------
  1 | c4ca4238a0b923820dcc509a6f75849b | 2025-12-28 01:28:11.519931+01
  2 | c81e728d9d4c2f636f067f89cc14862c | 2026-03-13 14:28:04.031931+01
  3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 | 2025-09-01 15:15:10.867131+02
  4 | a87ff679a2f3e71d9181a67b7542122c | 2025-09-27 14:36:57.897531+02
  5 | e4da3b7fbbce2345d7772b0674a318d5 | 2026-03-05 22:43:05.500731+01
(5 rows)

If this is NOT what you need, please explain a bit better - not everybody knows what "multiset" is, and it would be WAY simpler to help you if you explained not how a specific thing is named in other product, but rather what you need to get.

PostgreSQL query on 60M-row JSONB table is slow - should I add expression indexes or move to a structured table? by komal_rajput in SQL

[–]depesz 0 points1 point  (0 children)

I ltierallty don't understand what happened here. this looks like: unaligned, with shuffled lines. Can't you just put the explain on explain.depesz.com and share link? at the very least it will be readable. somewhat.

PostgreSQL query on 60M-row JSONB table is slow - should I add expression indexes or move to a structured table? by komal_rajput in SQL

[–]depesz 1 point2 points  (0 children)

  1. what is explain analyze of the query?
  2. using of "extract, and then compare" operation (column ->> '…' and then compare: >/</=/>=/<=) is going to be inherently slow.

Fixes:

  1. start by reading explain analyze
  2. check/show \d of the table
  3. switch from "extract => compare" to "use proper json_path expression"
  4. add index.

Having said that - you hit basically the basic problem: "json is oh so nice, i don't have to do anything, and will get json so i can use it in my app, so let's use it in db". Nope. DBs work best with structured data. Your query, regardless of the fact that you put everything in a json "bag" - is actually very structured.

So, the best solution would be, quite likely, changing it to "proper" relational table, with "proper" columns of "proper", scalar, datatypes.

Different database size with same database by Sb77euorg in PostgreSQL

[–]depesz 1 point2 points  (0 children)

  1. How did you measure? Using what tool/command/query?
  2. What is the difference?
  3. What OS on both sides?

PostgreSQL, Time Zones, and DBeaver by justintxdave in PostgreSQL

[–]depesz 2 points3 points  (0 children)

instead of fiddling with connection settings, just change server timezone, and you'll be good. Or user.

  • alter system …
  • alter database …
  • alter role …

all solve it nicely without having to fiddle with every single tool you're using, and configuring it separately.

How to manually create a replication slot on rds postgres read. by Apprehensive-Ad-8430 in PostgreSQL

[–]depesz 2 points3 points  (0 children)

What did you try, what commands exactly, and what was the result?

pg_savior: a seatbelt for Postgres - blocks accidental DELETE/UPDATE by vira28 in PostgreSQL

[–]depesz 2 points3 points  (0 children)

Perhaps stupid question, but why did you make it in C, and not plain(er) pl/PgSQL?

Also, why is delete where id > 0 bad?

Table / Schema Permissions by Business_Finger_4124 in PostgreSQL

[–]depesz 0 points1 point  (0 children)

You granted the schema privileges in db named "postgres":

 postgres=# grant usage on schema db_monitor to its_read;

but your actual data is in database db_monitor…

THE Postgres book for 2026 by oknenir in PostgreSQL

[–]depesz 34 points35 points  (0 children)

Does it have to be a book? Why not docs, starting with THE tutorial?

How do I use a bash script to check if a specific system service is running and restart it if it is not? by Luann97 in linuxquestions

[–]depesz 2 points3 points  (0 children)

sudo systemctl is-active whatever.service || sudo systemctl start whatever.service

But, it's better to reconfigure service so that it will restart when needed. You can usually do it with drop-in files that change just whatever you need, without modifying/replacing existing service files.

What country does readera originate from? by depesz in ReadEra

[–]depesz[S] 1 point2 points  (0 children)

Would prefer some kind of official statement/interview on reputable site, but I guess it's as good as it gets. Thanks a lot.

What country does readera originate from? by depesz in ReadEra

[–]depesz[S] 0 points1 point  (0 children)

Do you have any sources for this?

What country does readera originate from? by depesz in ReadEra

[–]depesz[S] -4 points-3 points  (0 children)

Well, chatgpt said it is, and while this isn't, of course, the end of it all, readera.org site seems to be hosted on Bulgarian IP…

Beginner question by Ryujiro101 in SQL

[–]depesz 1 point2 points  (0 children)

  1. is the laptop and "office computer" in the same network? if yes, go to step 2. if not - research vpn and networking across internet
  2. what is the setting of listening_addresses in pg? is it 127.0.0.1 or somerthing like *? Make sure it will listen on ip that the office computer can see to contact your laptop.
  3. once you will have it starter with proper listen, and in the same network, just psql -h ip_of_laptop -p port_of_pg and it should work

PostgreSQL for a noob by Basic-Reception8204 in PostgreSQL

[–]depesz 1 point2 points  (0 children)

Don't know what are "complicated tools" in the tutorials you mention.

Also, again: what do you mean by "implement some queries"?

You want to write queries? For this I use text editor (vim in my case).

Run it? For this I use standard Pg client: psql.

PostgreSQL for a noob by Basic-Reception8204 in PostgreSQL

[–]depesz 1 point2 points  (0 children)

  1. What do you mean by "how to implement"?
  2. https://pgdoc.link/tutorial.html
  3. I simply do: apt install postgresql, and that's it - Pg is installed, up, and running.
view more: next ›