pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedINSERT UNIQUE row?
8+ messages / 5 participants
[nested] [flat]
* INSERT UNIQUE row?
@ 2023-07-09 22:58 pf@pfortin.com
2023-07-09 23:02 ` Re: INSERT UNIQUE row? Christophe Pettus <xof@thebuild.com>
2023-07-09 23:42 ` Re: INSERT UNIQUE row? David G. Johnston <david.g.johnston@gmail.com>
2023-07-10 00:04 ` Re: INSERT UNIQUE row? Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 3 replies; 8+ messages in thread
From: pf@pfortin.com @ 2023-07-09 22:58 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
Hi,
Trying to figure out how to insert new property addresses into an
existing table.
Can a UNIQUE constraint be applied to an entire row? Adding UNIQUE to
each column won't work in such a case since there are multiple properties
* on the same street
* in the same town
* with the same number on different streets
* etc...
Each row is only unique as an entity; but the columns are not...
Is this covered in the docs?
Thanks,
Pierre
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: INSERT UNIQUE row?
2023-07-09 22:58 INSERT UNIQUE row? pf@pfortin.com
@ 2023-07-09 23:02 ` Christophe Pettus <xof@thebuild.com>
2 siblings, 0 replies; 8+ messages in thread
From: Christophe Pettus @ 2023-07-09 23:02 UTC (permalink / raw)
To: pf@pfortin.com; +Cc: pgsql-general@lists.postgresql.org
A UNIQUE index can have any number of columns, so you can create an index with all of the appropriate columns listed. This is different from having a UNIQUE index on each individual column. In the former case, all of the columns together must be unique; in the latter case, as you mention, each index is evaluated separately.
> On Jul 9, 2023, at 15:58, pf@pfortin.com wrote:
>
> Hi,
>
> Trying to figure out how to insert new property addresses into an
> existing table.
>
> Can a UNIQUE constraint be applied to an entire row? Adding UNIQUE to
> each column won't work in such a case since there are multiple properties
> * on the same street
> * in the same town
> * with the same number on different streets
> * etc...
>
> Each row is only unique as an entity; but the columns are not...
>
> Is this covered in the docs?
>
> Thanks,
> Pierre
>
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: INSERT UNIQUE row?
2023-07-09 22:58 INSERT UNIQUE row? pf@pfortin.com
@ 2023-07-09 23:42 ` David G. Johnston <david.g.johnston@gmail.com>
2023-07-10 02:29 ` Re: INSERT UNIQUE row? pf@pfortin.com
2 siblings, 1 reply; 8+ messages in thread
From: David G. Johnston @ 2023-07-09 23:42 UTC (permalink / raw)
To: pf@pfortin.com; +Cc: pgsql-general@lists.postgresql.org
On Sun, Jul 9, 2023 at 3:58 PM <pf@pfortin.com> wrote:
> Hi,
>
> Trying to figure out how to insert new property addresses into an
> existing table.
>
> Can a UNIQUE constraint be applied to an entire row? Adding UNIQUE to
> each column won't work in such a case since there are multiple properties
> * on the same street
> * in the same town
> * with the same number on different streets
> * etc...
>
> Each row is only unique as an entity; but the columns are not...
>
> Is this covered in the docs?
>
>
Yes, the mechanics of defining multi-column unique constraints on tables is
covered in the docs.
This seems like a waste of space though. That is a lot of text data
(though I suppose not as bad so long as you store integer foreign keys for
many of the columns) to put into an index that isn't really even going to
catch typos and other malformed data situations while preventing something
that is basically impossible to encounter in real life. Especially if you
also have separate individual indexes to make searching for specific subset
of the database faster (i.e., everything in a state).
David J.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: INSERT UNIQUE row?
2023-07-09 22:58 INSERT UNIQUE row? pf@pfortin.com
2023-07-09 23:42 ` Re: INSERT UNIQUE row? David G. Johnston <david.g.johnston@gmail.com>
@ 2023-07-10 02:29 ` pf@pfortin.com
0 siblings, 0 replies; 8+ messages in thread
From: pf@pfortin.com @ 2023-07-10 02:29 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
On Sun, 9 Jul 2023 16:42:15 -0700 David G. Johnston wrote:
>Yes, the mechanics of defining multi-column unique constraints on tables is
>covered in the docs.
Good to know I'm not searching in vain...
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: INSERT UNIQUE row?
2023-07-09 22:58 INSERT UNIQUE row? pf@pfortin.com
@ 2023-07-10 00:04 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-07-10 00:06 ` Re: INSERT UNIQUE row? Adrian Klaver <adrian.klaver@aklaver.com>
2023-07-10 02:25 ` Re: INSERT UNIQUE row? pf@pfortin.com
2 siblings, 2 replies; 8+ messages in thread
From: Adrian Klaver @ 2023-07-10 00:04 UTC (permalink / raw)
To: pf@pfortin.com; pgsql-general@lists.postgresql.org
On 7/9/23 15:58, pf@pfortin.com wrote:
> Hi,
>
> Trying to figure out how to insert new property addresses into an
> existing table.
>
> Can a UNIQUE constraint be applied to an entire row? Adding UNIQUE to
> each column won't work in such a case since there are multiple properties
> * on the same street
> * in the same town
> * with the same number on different streets
> * etc...
Does the locality you are in have something like the Property ID# and/or
Parcel # / Geo ID shown here:
https://property.whatcomcounty.us/propertyaccess/PropertySearch.aspx?cid=0
>
> Each row is only unique as an entity; but the columns are not...
>
> Is this covered in the docs?
>
> Thanks,
> Pierre
>
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: INSERT UNIQUE row?
2023-07-09 22:58 INSERT UNIQUE row? pf@pfortin.com
2023-07-10 00:04 ` Re: INSERT UNIQUE row? Adrian Klaver <adrian.klaver@aklaver.com>
@ 2023-07-10 00:06 ` Adrian Klaver <adrian.klaver@aklaver.com>
1 sibling, 0 replies; 8+ messages in thread
From: Adrian Klaver @ 2023-07-10 00:06 UTC (permalink / raw)
To: pf@pfortin.com; pgsql-general@lists.postgresql.org
On 7/9/23 17:04, Adrian Klaver wrote:
> On 7/9/23 15:58, pf@pfortin.com wrote:
>> Hi,
>>
>> Trying to figure out how to insert new property addresses into an
>> existing table.
>>
>> Can a UNIQUE constraint be applied to an entire row? Adding UNIQUE to
>> each column won't work in such a case since there are multiple properties
>> * on the same street
>> * in the same town
>> * with the same number on different streets
>> * etc...
>
> Does the locality you are in have something like the Property ID# and/or
> Parcel # / Geo ID shown here:
>
> https://property.whatcomcounty.us/propertyaccess/PropertySearch.aspx?cid=0
Should have added, can you create your own surrogate PK to fill the same
role?
>
>>
>> Each row is only unique as an entity; but the columns are not...
>>
>> Is this covered in the docs?
>>
>> Thanks,
>> Pierre
>>
>>
>>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: INSERT UNIQUE row?
2023-07-09 22:58 INSERT UNIQUE row? pf@pfortin.com
2023-07-10 00:04 ` Re: INSERT UNIQUE row? Adrian Klaver <adrian.klaver@aklaver.com>
@ 2023-07-10 02:25 ` pf@pfortin.com
2023-07-10 07:43 ` Re: INSERT UNIQUE row? Erik Wienhold <ewie@ewie.name>
1 sibling, 1 reply; 8+ messages in thread
From: pf@pfortin.com @ 2023-07-10 02:25 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
On Sun, 9 Jul 2023 17:04:03 -0700 Adrian Klaver wrote:
>On 7/9/23 15:58, pf@pfortin.com wrote:
>> Hi,
>>
>> Trying to figure out how to insert new property addresses into an
>> existing table.
>>
>> Can a UNIQUE constraint be applied to an entire row? Adding UNIQUE to
>> each column won't work in such a case since there are multiple properties
>> * on the same street
>> * in the same town
>> * with the same number on different streets
>> * etc...
>
>Does the locality you are in have something like the Property ID# and/or
>Parcel # / Geo ID shown here:
>
>https://property.whatcomcounty.us/propertyaccess/PropertySearch.aspx?cid=0
Thanks! Just getting started on this issue and this made me realize my
current data source may not be the best... Much appreciated!
>>
>> Each row is only unique as an entity; but the columns are not...
>>
>> Is this covered in the docs?
>>
>> Thanks,
>> Pierre
>>
>>
>>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: INSERT UNIQUE row?
2023-07-09 22:58 INSERT UNIQUE row? pf@pfortin.com
2023-07-10 00:04 ` Re: INSERT UNIQUE row? Adrian Klaver <adrian.klaver@aklaver.com>
2023-07-10 02:25 ` Re: INSERT UNIQUE row? pf@pfortin.com
@ 2023-07-10 07:43 ` Erik Wienhold <ewie@ewie.name>
0 siblings, 0 replies; 8+ messages in thread
From: Erik Wienhold @ 2023-07-10 07:43 UTC (permalink / raw)
To: pf@pfortin.com; pgsql-general@lists.postgresql.org
> On 10/07/2023 04:25 CEST pf@pfortin.com wrote:
>
> On Sun, 9 Jul 2023 17:04:03 -0700 Adrian Klaver wrote:
>
> >On 7/9/23 15:58, pf@pfortin.com wrote:
> >> Hi,
> >>
> >> Trying to figure out how to insert new property addresses into an
> >> existing table.
> >>
> >> Can a UNIQUE constraint be applied to an entire row? Adding UNIQUE to
> >> each column won't work in such a case since there are multiple properties
> >> * on the same street
> >> * in the same town
> >> * with the same number on different streets
> >> * etc...
> >
> >Does the locality you are in have something like the Property ID# and/or
> >Parcel # / Geo ID shown here:
> >
> >https://property.whatcomcounty.us/propertyaccess/PropertySearch.aspx?cid=0
>
> Thanks! Just getting started on this issue and this made me realize my
> current data source may not be the best... Much appreciated!
Also keep in mind that it's not trivial to model addresses, even in a single
country. Some database constraints may become a footgun.
https://www.mjt.me.uk/posts/falsehoods-programmers-believe-about-addresses/
--
Erik
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2023-07-10 07:43 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-07-09 22:58 INSERT UNIQUE row? pf@pfortin.com
2023-07-09 23:02 ` Christophe Pettus <xof@thebuild.com>
2023-07-09 23:42 ` David G. Johnston <david.g.johnston@gmail.com>
2023-07-10 02:29 ` pf@pfortin.com
2023-07-10 00:04 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-07-10 00:06 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-07-10 02:25 ` pf@pfortin.com
2023-07-10 07:43 ` Erik Wienhold <ewie@ewie.name>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox