sort images in database

If I have not misunderstood what you want to do is that only one of the images linked to a specific product is marked as main.

As you mention, this can be done prior to the insertion of each image outside of PostgreSQL, for example, every time an image is marked as "main" by placing all existing ones linked to the product unmarked with:

 UPDATE imagen SET principal = false WHERE id_producto = {id del producto implicado}

But if you want to do everything in PostgreSQL, one way would be by trigger, you have the official documentation in link .

It would be similar, you should only check inside the trigger if the values that are being inserted / updating change "main" and in case they do change with a UPDATE similar to the previous "main" values linked to the product before doing the new update / insert.

Regarding the sorting, if you have an integer field you could sort the records by that field without major problem, but the logic to keep a "main" would be more complicated, imagine that you would only have the whole field and the one that has value 1 it would be the "main"; the update logic update in that case (could also be done with trigger).

So that a product only has a main image, what occurs to me is that in the product table you create the mainImage field and thus you ensure that you always have only one main image, for ordering the images, with a field order should be enough.

You can say that your product has a main image from the list of images.

create table producto(
  id_producto serial,
  nombre character varying(20),
  imagen_principal serial,
  constraint fk_imagen_producto foreign key (imagen_principal) references imagen(id_imagen)
);

create table imagen(
  id_imagen serial,
  id_producto integer,
  nombre character varying(100),
  foreign key(id_producto) references producto(id_producto)
);

When you insert a new product you first insert the image you will have.

insert into imagen values (@idimagen,@idproducto,'nombre');

then insert the product with the id of its main image.

insert into producto values(@idproducto,'nombre',@idimagen);

To add normal images of the product, it is the same

insert into imagen values  (@idimagen,@idproducto,'nombre');

To find the product with its main image, you can search for it by its id

select p.id_producto,p.nombre,i.nombre
from producto p inner join imagen i
on p.imagen_principal = i.id_imagen
where p.id_producto = @idProducto;

To search all your images including the main one

select id_imagen, nombre from imagen where id_producto = @idproducto;

To search all your images without the main

select i.id_imagen, i.nombre 
from imagen i inner join producto p
on i.id_producto = p.id_producto
where p.id_producto = @idproducto
and p.imagen_principal != i.id_imagen;