Re: index unique

quite funny to see how a tech question seems to end into an english grammar
thing :-)

quote
> You make this sound like an either-or proposition,

While he is talking about *a* primary key, it should be *the* primary
key. There can be only one (that's why it is the primary key).

There can be several unique keys, though.

> but personally it takes a very exceptional circumstance to forgo
> defining a unique natural key.

True, but not what he's talking about.
end quote

when I did write the question, I write: "on a table we need a primary key"
its quite obvious that there is only one PK, but, at that stage, I
dont need one for functional reasons
so, I need "a" primary key, whatever it is, just because postgres logical
replication needs one to accept updates.

then, to build "the" needed PK, we need to decide which columns or set of
will be appropriate
as it has to be able to identify something unique. and this is how we get
to this list of 3 columns.

So, ok, this set doesn't work asis.
all solutions we thought of are just tech workaround, providing no
functionnal meaning.

still, its somehow surprising: (at least to me !)
postgres has no problem creating a btree for a geometry column, and, so, no
problem for putting a geom column in a PK.
(very different from Oracle...)

the only pb observed is the size of the object accepted. if the geom is a
bit "big" then the index errors.about btree size of index object.
but if I create a table test_l with a text column blabla as a PK, and
insert a 100 000 character long string , no pb.
if I do an explain analyze select blabla from test_l order by blabla,
postgres tells me that it did an index only scan.
so, I wonder why Postgres is able to put a 100 000 long text in a btree
index and NOT a geom column which wkt is 10 000 bytes long.

there, sure, is a reason.

please, let me know :-)

Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com

On Tue, Jun 8, 2021 at 12:09 AM Peter J. Holzer <hjp-pgsql(at)hjp(dot)at> wrote:

> On 2021-06-07 10:20:22 -0700, David G. Johnston wrote:
> > On Sun, Jun 6, 2021 at 11:55 PM Peter J. Holzer <hjp-pgsql(at)hjp(dot)at>
> wrote:
> > On 2021-06-03 22:51:55 +0200, Marc Millas wrote:
> > > postgres 12 with postgis.
> > > on a table we need a primary key and to get a unique combinaison,
> we need
> > > 3 columns of that table:
> > > 1 of type integer,
> > > 1 of type text,
> > > 1 of type geometry
> > >
> > > creating the PK constraint doesn work:
> >
> > I find that if a natural primary key candidate is so complex, it is
> > usually better to use a surrogate key.
> >
> >
> > You make this sound like an either-or proposition,
>
> While he is talking about *a* primary key, it should be *the* primary
> key. There can be only one (that's why it is the primary key).
>
> There can be several unique keys, though.
>
> > but personally it takes a very exceptional circumstance to forgo
> > defining a unique natural key.
>
> True, but not what he's talking about.
>
> hp
>
> --
> _ | Peter J. Holzer | Story must make more sense than reality.
> |_|_) | |
> | | | hjp(at)hjp(dot)at | -- Charles Stross, "Creative writing
> __/ | http://www.hjp.at/ | challenge!"
>