Setting current_timestamp on every update in PostgreSQL column

I’m trying to create modified_at column in Postgres

What I came out with (out of EF Core generated sql) is:

Users" ADD modified_at timestamp without time zone GENERATED ALWAYS AS (CURRENT_TIMESTAMP) STORED

But then i’m getting :

generation expression is not immutable

What am I doing wrong? I used current_timestamp as default value in my created_at column. What does it mean by not immutable?

I tried to read their docs but I can’t find anything

Please help

postgresql – Select Execute conditional only if value exists Postgres

Hello It has the following select, how can I do to cancel the clauses and if the second value is blank ?
example: and g.id = $ {pParGerEmpresaId}
if the $ {pParGerEmpresaId} parameter is empty I would like to delete this clause … however I would like to do all this in just a select

   Select 
    g.id as f1,
    g.nome as f2 ,
    g.razao_social as f3, 
    g.ativo as f4, 
    g.sigla_empresa as f5,
    g.doc_cnpj as f6,
    g.doc_cpf as f7,
    g.end_logradouro as f8 ,
    g.end_logradouro_nr as f9, 
    g.end_bairro as f10
  from ger_empresa g 
    where g.unit_id = ${pParUnitId} 
    and g.id = ${pParGerEmpresaId} 
    and g.nome like ${pParGerEmpresaNome} 
    and g.sigla_empresa like ${pParGerEmpresaSigla} 
    and g.ativo like ${pParGerEmpresaAtivo} 
    and g.doc_cnpj like ${pParGerEmpresaDocCnpj} 
    and g.doc_cpf like ${pParGerEmpresaDocCpf} 
    and g.log_user_ins like ${pParLogUserIns} 
    and g.log_user_upd like ${pParLogUserUpd} 
    and CAST(g.log_date_ins AS DATE) >= ${pParLogDateInsIni} 
    and CAST(g.log_date_ins AS DATE) <= ${pParLogDateInsFin} 
    and CAST(g.log_date_upd AS DATE) >= ${pParLogDateUpdIni} 
    and CAST(g.log_date_upd AS DATE) <= ${pParLogDateUpdFin}

postgresql – Consultar JSONField desde un mixins.ListModelMixin con Django Filter Backends

Quisiera consultar un campo JSONField desde la URL de un endpoint en django rest framework y que este me retornase la coincidencia que exista dentro de un campo JSONField del modelo.

El siguiente es el modelo:

class TestJsonGuard(models.Model):
   idtest = models.AutoField(primary_key=True)
   product = models.CharField(max_length=100)
   json_leader = JSONField()

La siguiente es la vista desde donde deseo realizar la consulta:

class TestGuardJson(mixins.ListModelMixin,generics.GenericAPIView):

   permission_classes = (permissions.AllowAny,)
   queryset = TestJsonGuard.objects.all()
   serializer_class = TestJsonGuardSerializer
   parser_classes = (MultiPartParser,)
   filter_backends = (DjangoFilterBackend, filters.SearchFilter)
   filterset_fields = ("product", "json_leader")
   search_fields = ("product", "json_leader")
   pagination_class = Pagination

   def get(self, request, *args, **kwargs):
       return self.list(request, *args, **kwargs)

Estoy intentando enviar algo de esta manera desde la URL:

/api/v1/test?json_leader__active_substance__contains=VITAMINA C

Pero ello no me funciona para lograr encontrar las coincidencias en el JSONField almancenado en la base de datos, cuya estructura es:

({"active_substance": "VITAMINA D"}, {"active_substance": "VITAMINA A"}, 
 {"active_substance": "VITAMINA B"})

El error que me lanza al intentar consultarlo de esa manera es:

AutoFilterSet resolved field 'json_leader' with 'exact' lookup to an unrecognized field type 
JSONField. Try adding an override to 'Meta.filter_overrides'. 

¿cómo podría configurar la vista adecuadamente sin afectar los demás campos que el modelo posee y que se pueda consultar en ellos?

postgresql – When using class inheritance, can all PKs of the derived tables exist as PKs of the base table?

Brief questions

  1. When class inheritance is used, the child table inherits the key from the parent, right? Thus, you INSERT INTO the parent and then the child?
    1. This indicates that the PK of the child table (derived class) becomes FK in the parent table (base class). See “but now the primary key of these tables also becomes a foreign key to the People table.” That’s not true is it? The examples indicate that the child tables reference the parent table. Did I misunderstand something?
  2. Can the PK of both the child and parent tables be the same?

Details

I’m new to handling supertype-subtype instances in RDBs, but I’ve read a number of posts about three main options. The one I’m interested to implement is class inheritance where I have a base table with all common attributes and derived tables with attributes that are unique to each subtype.

I generically understand that class inheritance and table inheritance aren’t the same. Based on the postgres documentation, table inheritance will allow for duplicate data in the child tables that I can’t allow. Thus, I don’t think table inheritance is a good way for me to go.

I’m interested in class inheritance because I need multiple different subtypes to be referenced as FK in a table. For example, I have a base class table vehicle and derived classes boat and car. I have another table for vehicle_maintenance. As a side note, I have many tables that are equivalent to vehicle_maintenance in that they can be applied to both boat and car. I also have many more subtypes than just boat and car with many attributes that are unique to each subtype.

Here’s an example adapted from this post.

CREATE TABLE vehicle (
   vehicle_id          int PRIMARY KEY,
   paint_type    text,

   -- // other common attributes 
);

CREATE TABLE boat (
    boat_id         int PRIMARY KEY REFERENCES vehicle (vehicle_id),
    propeller_type    text,
    water_exposure     text,

   -- // other attributes specific to boat ...

   
);

CREATE TABLE car (
    car_id         int PRIMARY KEY REFERENCES vehicle (vehicle_id),
    steering_type    text,
    wheel_type     text,

   -- // other attributes specific to car ...

   
);

CREATE TABLE vehicle_maintenance_id (
   vehicle_maintenance_id          int,
   date_complete        datetime,
   FOREIGN KEY (vehicle_id) REFERENCES vehicle (vehicle_id)
   
);

In this example, I would create a unique ID for every vehicle in the base table and carry that over to the derived tables. This would mean that the same paint_type would be repeated without any other attributes being UNIQUE, except the PK. This doesn’t seem very normalized, but it’s the only way I understand to get all of the PKs from the derived tables into one table without just doing an all-in-one table instead of class inheritance.

Am I just confusing myself, and the example I provided above is acceptable?

Edit

Perhaps I need to make this a separate question: I can’t create composite keys based on attributes in the child tables and use them as PKs in the base table, right? This is because each child table will have unique attributes and the base table would require only one set of attributes to form the composite FK, correct?

haproxy – PostgreSQL High Memory consumption

We have been getting a lot of issues pertaining memory consumption in Postgres. I am thinking of deploying PgBouncer to resolve this issue, but wanted to know what could be the cause for this.

ps aux --sort=-%mem | head -30

USER        PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
postgres  23438 41.0  6.2 78075200 8258220 ?    Ssl  Aug09 525:40 postgres: xxxxx: xxxxx(60028) idle
postgres   4225 31.7  5.9 77797888 7903220 ?    Ssl  Aug09 438:58 postgres: xxxxx: xxxxx(45012) idle
postgres 118999 39.3  4.8 76290020 6395136 ?    Ssl  Aug09 306:52 postgres: xxxxx: xxxxx(50026) idle

free -g
              total        used        free      shared  buff/cache   available

Mem:            125          36          35           3          54          84
Swap:            19           0          19



System RAM : 128 GB
CPU(s) : 32
Shared_buffer : 32 GB
DB Size : 24 GB
Max Connections : 200
Average Connections: 50
Effective_Cache_Size : 64 GB
Work_mem : 41MB
PG Version : 11
OS : Ubuntu

A lot of the idle connections are getting reused despite it being closed from the app side. I have tuned the database by changing Shared_buffer, work_mem to values as per PostgreSQL expectations, but still getting these issues.

PS : We have setup HAProxy between App and DB.

postgresql – `ts_rank` is returning a numeric/decimal/real that fails a “greater than” check

The following yields (seemingly exactly) 0.1:

select ts_rank_cd(to_tsvector('cat'), to_tsquery('cat'));

Yet the following yields t:

select ts_rank_cd(to_tsvector('cat'), to_tsquery('cat')) > 0.1;

I would expect it to yield t if the operation was >=, not > How can I get the “real” value of the ts_rank_cd so that it returns f?


As background, I’m writing a full text search query that uses keyset pagination with ts_rank, so being able to order results based on a concrete, precise value of ts_rank is important. With keyset pagination, the client provides the last result from the current page as a parameter and asks for the next page. Currently, when my client passes back 0.1, the above > behavior causes erroneous results

postgresql – How to store a reference to any other column in another table

I have a table "Discounts" with a column "share" which holds the percentage to be applied to the value of column "cost", "price" or "shipping" of the table "Items".

"Discounts" also holds a foreign key to "item_id".

I need to add another column "base" to table "Discounts" where to store a reference to one of the column of table "Items", and calculate the percentage of the value of that column.
For example, given these values:

Discounts
share    base                 item_id
-------------------------------------
50       (item's cost)        3
25       (item's price)       1
100      (item's shipping)    2


Items
id    cost    price    shipping
-------------------------------
1     10      40       20
2     55      60       30
3     50      85       10

I want to be able to calculate:

  • 50% of 50 (cost of item 3)
  • 25% of 40 (price of item 1)
  • 100% of 30 (shipping of item 2)

The column “base” should contain neither the name (e.g. “price”) nor the number (e.g. 3) of the referenced column, because the name or the order of each table could change.

If there is no DB agnostic way to do this, then assume the database is PostgreSQL (v12+).

postgresql – attach csv file on email sent from postgres

I have the following code to send emails and wanted to see if it’s possible to attach a csv file?

CREATE OR REPLACE FUNCTION public.send_email_html(text, text, text, text)
 RETURNS integer
 LANGUAGE plperlu
AS $function$
    use Net::SMTP;

    my ($recipientString, $sendFrom, $subject, $message) = @_;
    my @recipients = split /;/, $recipientString;
    my $smtp=Net::SMTP->new("localhost");
    
    $smtp->mail($sendFrom);
    foreach(@recipients)
    {
        $smtp->recipient($_);
    }
    $smtp->data();
    foreach(@recipients)
    {
        $smtp->datasend("To: $_n");
    }
    
    $smtp->datasend("Subject: $subjectn");
    $smtp->datasend("Content-Type: text/html;n");
    $smtp->datasend("$messagen");
    $smtp->quit();

    return 1;
$function$
;

ubuntu – Postgresql prompts for configuring tzdata when installing during docker image building

I am trying to build a docker image with postgresql with Ubuntu. When the image is being built during the postgresql installation apt prompts for the config data (e.g. time zone settings), but the terminal emulator does not send a keypress for the apt’s prompt. How do I either suppress this prompts or send some default values?

Host OS: Windows 10

Dockerfile (meaning strings):

FROM ubuntu:18.04
RUN apt-get update -y
RUN apt-get install -y  python3-pip python-dev build-essential postgresql-server-dev-10 postgresql
RUN pip3 install -r requirements.txt

Why is PostgreSQL not using composite Index on JSONB Fields?

Two of my PostgreSQL DB columns are JSONB fields (J1, J2). My queries are on (J1->>'f1')::int and (J2->>'f2'), for example:

SELECT * from table where (J1->>'f1')::int = 1 AND (J2->>'f2') IS NULL;

Since I have millions of records, I created a composite index:

CREATE INDEX f1_f2 ON table (((J1->>'f1')::int), (J2->>'f2'));

However, looking at EXPLAIN, I see that Seq Scan on table is still being used.
Is this because PG does not handle composite JSONB fields? Any other reason that I should check?