On 08/22/2016 06:23 PM, Tom Lane wrote:
Adrian Klaver writes:
On 08/17/2016 11:02 PM, Silk Parrot wrote:
CREATE TABLE user (
uuid UUID PRIMARY KEY DEFAULT public.uuid_generate_v4(),
google_user system.google_user,
facebook_user system.facebook_user,
UNIQUE (google_user.email)
);
ERROR: syntax
Adrian Klaver writes:
> On 08/17/2016 11:02 PM, Silk Parrot wrote:
>> CREATE TABLE user (
>> uuid UUID PRIMARY KEY DEFAULT public.uuid_generate_v4(),
>> google_user system.google_user,
>> facebook_user system.facebook_user,
>> UNIQUE (google_user.email)
>> );
>> ERROR: syntax error at or near "."
On 08/17/2016 11:02 PM, Silk Parrot wrote:
Hi,
I am trying to model a social login application. The application can
support multiple login providers. I am thinking of creating a custom
type for each provider. e.g.
CREATE TYPE system.google_user AS (
email TEXT
);
CREATE TYPE system.face
On Wed, Aug 17, 2016 at 23:02:53 -0700,
Silk Parrot wrote:
Hi,
I am trying to model a social login application. The application can
support multiple login providers. I am thinking of creating a custom type for
each provider. e.g.
CREATE TABLE user (
uuid UUID PRIMARY KEY DEFAULT pub
On Thu, Aug 18, 2016 at 2:02 AM, Silk Parrot wrote:
>
> However, the above create table query reports syntax error:
>
> ERROR: syntax error at or near "."
> LINE 10: UNIQUE (google_user.email)
>
> Is there a way to create unique constraint on a field inside composite
> type?
>
Not tested h
Hi,
I am trying to model a social login application. The application can
support multiple login providers. I am thinking of creating a custom type for
each provider. e.g.
CREATE TYPE system.google_user AS (
email TEXT
);
CREATE TYPE system.facebook_user AS (
id TEXT
);
And having
On 7/20/16 1:14 PM, Mark Lybarger wrote:
This leads me to think I need to create 2^5 or 32 unique constraints to
handle the various combinations of data that I can store.
Another option would be to create a unique index of a bit varying field
that set a bit to true for each field that was NULL
On Wed, Jul 20, 2016 at 8:14 PM, Mark Lybarger wrote:
> I have a relation such as
> create table order_item ( id uuid not null primary key, order_id number
> not null, item_code text, make text, model text, reason text, size text,
> expiration_date timestamp );
>
> where the combination of the co
Mark Lybarger writes:
> I have a relation such as
> create table order_item ( id uuid not null primary key, order_id number not
> null, item_code text, make text, model text, reason text, size text,
> expiration_date
> timestamp );
>
> where the combination of the columns order_id, item_code, m
On Wed, Jul 20, 2016 at 1:48 PM, David G. Johnston
wrote:
> On Wed, Jul 20, 2016 at 2:14 PM, Mark Lybarger wrote:
>> Another solution I can think of is to just use a trigger to
>> prevent the duplicate rows.
If you go that route you will need to use serializable
transactions, explicit locking,
On Wed, Jul 20, 2016 at 2:14 PM, Mark Lybarger wrote:
> I have a relation such as
> create table order_item ( id uuid not null primary key, order_id number
> not null, item_code text, make text, model text, reason text, size text,
> expiration_date timestamp );
>
> where the combination of the co
I have a relation such as
create table order_item ( id uuid not null primary key, order_id number not
null, item_code text, make text, model text, reason text, size text,
expiration_date timestamp );
where the combination of the columns order_id, item_code, make, model,
reason, size must be unique
On Thu, Aug 22, 2013 at 2:46 AM, Ivan Radovanovic wrote:
> Just to verify:
> - when unique index is created row is added only to pg_index table but not
> to pg_constraint table (although in fact that index is behaving like
> constraint on table)
Yep.
postgres=# create table foo (a int);
CREATE TAB
Just to verify:
- when unique constraint is created using appropriate syntax rows are
added to tables pg_constraint and pg_index (pg_constraint with type 'u'
and referring to index with indisunique set to true)
- when unique index is created row is added only to pg_index table but
not to pg_con
Thank you for the suggestion Steven.
Originally I did implement a solution using savepoints and that worked as a
way to keep all the work done on the transaction leading up to the
constraint violation, but errors would still show up in the Postgres log.
With this new function approach there are no
On Jun 26, 2013, at 11:04 AM, pg noob wrote:
>
> Hi all,
>
> There are some places in our application where unique constraint violations
> are difficult to avoid due to multithreading.
> What we've done in most places to handle this is to retry in a loop.
>
> Generally it starts by checking
Hi all,
There are some places in our application where unique constraint violations
are difficult to avoid due to multithreading.
What we've done in most places to handle this is to retry in a loop.
Generally it starts by checking if a value already exists, if not - try to
insert it, which may ca
Ah. A pair of constraints. I see.
Thanks!
__
*Mike Blackwell | Technical Analyst, Distribution Services/Rollout
Management | RR Donnelley*
1750 Wallace Ave | St Charles, IL 60174-3401
Office: 630.313.7818
mike.black
On 09/25/2012 05:05 PM, Mike Blackwell wrote:
How would one go about building a multi-column unique constraint where
null is a significant value, eg. (1, NULL) <> (2, NULL)?
I see a number of references to not being able to use an index for
this, but no mention of an alternative. Any pointers
On Tue, Sep 25, 2012 at 11:34:36AM -0500, Mike Blackwell wrote:
> Interesting, but that assumes there's a value to use in the coalesce that
> isn't a valid data value.
no, it doesn't.
Best regards,
depesz
--
The best thing about modern society is how easy it is to avoid contact with it.
Interesting, but that assumes there's a value to use in the coalesce that
isn't a valid data value.
__
*Mike Blackwell | Technical Analyst, Distribution Services/Rollout
Management | RR Donnelley*
1750 Wallace Ave | St
On Tue, Sep 25, 2012 at 10:05:15AM -0500, Mike Blackwell wrote:
> How would one go about building a multi-column unique constraint where null
> is a significant value, eg. (1, NULL) <> (2, NULL)?
>
> I see a number of references to not being able to use an index for this,
> but no mention of an al
How would one go about building a multi-column unique constraint where null
is a significant value, eg. (1, NULL) <> (2, NULL)?
I see a number of references to not being able to use an index for this,
but no mention of an alternative. Any pointers would be appreciated
___
I am afraid I over-simplyfied my use-case. Sorry.
In fact, I am not using a character sequence, but a custom COMPOSITE TYPE;
part of which is a hstore:
CREATE TYPE SSTORE AS (scope TEXT, kvp hstore);
I created some functions and operators around it -- sort-of costly
operations for comparing SSTOR
Could you index the reverse of the string so the unique part appears first?
On May 14, 2011, at 11:20, InterRob wrote:
> Dear list,
>
> I would be pleased if you could share some thoughts with me on the following:
> say I wish to maintain a table with all distinct character sequences
> (varia
Dear list,
I would be pleased if you could share some thoughts with me on the
following: say I wish to maintain a table with all distinct character
sequences (variable length) showing series with strong similarities.
Example:
"abbbabacccdef"
"abbbabaccdcdf"
"abbbabaccdcgf"
...
"qwtrhdffdd"
...
"qw
On Wed, 2010-10-20 at 16:10 -0400, Michael Glaesemann wrote:
> Upgrade if you can. Otherwise you can do two updates when rearranging
> paragraphs. The first updates them to an "invalid" range which isn't used in
> "correct" data and so won't conflict with other values, and the second moves
> the
On Oct 20, 2010, at 15:58 , Jamie Kahgee wrote:
> I have a table of paragraphs for pages that are in a specific order (1st,
> 2nd, 3rd, etc...).
> I tried using a unique constraint on the page/pos columns, but was running
> into constraint errors when I did an update to move positions - in a
>
I have a table of paragraphs for pages that are in a specific order (1st,
2nd, 3rd, etc...).
demo=# \d paragraphs
Table "toolbox.paragraphs"
Column| Type |Modifiers
-+-+---
On Jan 24, 2008, at 12:36 PM, Dominique Bessette - Halsema wrote:
I'm getting the following error, and I think it's because when i
insert into postgres from geoserver's WFS, i first delete then
insert the track, and geoserver does this in succession but very
fast. If I personally test ins
I'm getting the following error, and I think it's because when i insert into
postgres from geoserver's WFS, i first delete then insert the track, and
geoserver does this in succession but very fast. If I personally test
inserting two tracks with the same guid in a row, then it works, but because
g
On Fri, 2007-04-20 at 15:52, Jonathan Vanasco wrote:
> I need a certain unique constraint in pg that i can't figure out.
>
> Given:
>
> create table test_a (
> id serial ,
> name_1 varchar(32) ,
> name_2 varchar(32)
> );
>
> I need name_1 and
On Fri, 2007-04-20 at 18:32 -0400, Jonathan Vanasco wrote:
> On Apr 20, 2007, at 6:13 PM, Jeff Davis wrote:
>
> > This is more correct structure, and yes, it would involve a join.
>
> I know thats the 'more correct' way -- but I can't do the join ,
> which is why I posted about a 2 column uniqu
On Apr 20, 2007, at 6:13 PM, Jeff Davis wrote:
This is more correct structure, and yes, it would involve a join.
I know thats the 'more correct' way -- but I can't do the join ,
which is why I posted about a 2 column unique index.
I tested with a join before posting - i have an already larg
On Fri, 2007-04-20 at 17:56 -0400, Jonathan Vanasco wrote:
> On Apr 20, 2007, at 5:43 PM, Vladimir Zelinski wrote:
>
> > This looks like more table design problem than
> > database limitation.
> > The one column should accommodate values from both
> > columns with unique index built on this column
Jonathan Vanasco <[EMAIL PROTECTED]> writes:
> Given:
> create table test_a (
> id serial ,
> name_1 varchar(32) ,
> name_2 varchar(32)
> );
> I need name_1 and name_2 to both be unique so that:
> name_1 never appears in name_1 or name_2
On Apr 20, 2007, at 5:43 PM, Vladimir Zelinski wrote:
This looks like more table design problem than
database limitation.
The one column should accommodate values from both
columns with unique index built on this column. Your
requirements tell me that these values are the same
nature and should
This looks like more table design problem than
database limitation.
The one column should accommodate values from both
columns with unique index built on this column. Your
requirements tell me that these values are the same
nature and should be placed in the same column. To
distinguish between them
Jonathan Vanasco <[EMAIL PROTECTED]> writes:
> I need a certain unique constraint in pg that i can't figure out.
>
> Given:
>
> create table test_a (
> id serial ,
> name_1 varchar(32) ,
> name_2 varchar(32)
> );
>
> I need name_1 and name_2 to
I need a certain unique constraint in pg that i can't figure out.
Given:
create table test_a (
id serial ,
name_1 varchar(32) ,
name_2 varchar(32)
);
I need name_1 and name_2 to both be unique so that:
name_1 never appear
nodupes
before insert on bla_b
for each row
execute procedure tf_nodupes();
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Akbar
> Sent: Thursday, December 28, 2006 4:01 AM
> To: pgsql-general@postgresql.org
> Subje
Hi,
Imagine I have two tables, like this:
create table bla_a (
id serial primary key,
name varchar(31) not null,
comment varchar(31)
);
create table bla_b (
id serial primary key,
name varchar(31) not null,
blabla int
);
I want to make sure that both tables could not have the same value
On Tue, Aug 22, 2006 at 08:39:00AM +0800, gao iqiang wrote:
> Hello,
>I met the following problem when i am using PostgreSQL as a backend
> database:
>I have a table "tabrel(a int primary key, b int)", and now there are for
> example 100 records with column 'a' be from 1 to 100. When i'm go
am Tue, dem 22.08.2006, um 8:39:00 +0800 mailte gao iqiang folgendes:
> Hello,
> I met the following problem when i am using PostgreSQL as a backend
> database:
> I have a table "tabrel(a int primary key, b int)", and now there are for
> example 100 records with column 'a' be from 1 to 10
Hello, I met the following problem when i am using PostgreSQL as a backend database: I have a table "tabrel(a int primary key, b int)", and now there are for example 100 records with column 'a' be from 1 to 100. When i'm going to add one to each 'a' and intended to get 'a' varing from 2 to 10
>Tom Lane <[EMAIL PROTECTED]> wrote on 04/06/2006
11:33:57 AM:
> [EMAIL PROTECTED] writes:
> > CREATE UNIQUE INDEX resource_refullname
> > ON resource USING btree (redtid, (upper(refullname)
text_ops));
>
> You need something newer than PG 7.3 to do that. 7.3 can't handle
> functional indexe
[EMAIL PROTECTED] writes:
> CREATE UNIQUE INDEX resource_refullname
> ON resource USING btree (redtid, (upper(refullname) text_ops));
You need something newer than PG 7.3 to do that. 7.3 can't handle
functional indexes with more than one column. There are many other good
reasons to upgrade a
I am on version 7.3. I have been able
to build a case insensitive index to keep the refullname column unique
with the following:
CREATE UNIQUE INDEX resource_refullname
ON resource USING btree
(upper(refullname) text_ops);
However I have a table where I want
to allow a duplicate refullname
CSN <[EMAIL PROTECTED]> writes:
> I have three columns, and one of them can be null. I'd
> like to create a unique constraint across all three
> columns and allow only one null value. e.g.
UNIQUE won't do that for you, but you could brute force it with a
unique constraint plus a check constraint a
rg"
Sent: Fri, 30 Dec 2005 17:02:48 -0500
Subject: Re: [GENERAL] unique constraint with a null column?
> try something like this
>
> jim=# create table a (a text,b text, c text);
> CREATE TABLE
> jim=# create unique index a_idx on a(a,b,(coalesce(c,'*** NULL IS HERE
>
| text |
b | text |
c | text |
Indexes:
"a_idx" unique, btree (a, b, (COALESCE(c, '*** NULL IS HERE ***'::text)))
-- Original Message ---
From: Bruno Wolff III <[EMAIL PROTECTED]>
To: CSN <[EMAIL PROTECTED]>
Cc: "pgsql-gene
--- Bruno Wolff III <[EMAIL PROTECTED]> wrote:
> On Fri, Dec 30, 2005 at 13:30:40 -0800,
> CSN <[EMAIL PROTECTED]> wrote:
> > I have three columns, and one of them can be null.
> I'd
> > like to create a unique constraint across all
> three
> > columns and allow only one null value. e.g.
> >
>
On Fri, Dec 30, 2005 at 13:30:40 -0800,
CSN <[EMAIL PROTECTED]> wrote:
> I have three columns, and one of them can be null. I'd
> like to create a unique constraint across all three
> columns and allow only one null value. e.g.
>
> a|b|c
> abc|123|null
> abc|123|null # not allowed
> abc|456|null
I have three columns, and one of them can be null. I'd
like to create a unique constraint across all three
columns and allow only one null value. e.g.
a|b|c
abc|123|null
abc|123|null # not allowed
abc|456|null
abc|456|987
abc|456|876
def|456|null
def|456|null # not allowed
Currently, the 'not all
All,
In the pg_dump output is the command:
COPY event_tbl (d1, ..., evt_id) FROM stdin;
followed by all the data for the table. There are
23040 rows. The last value for evt_id is 23040. So
far so good. Then the last statement in the pg_dump
output is:
SELECT pg_catalog.setval('event_tbl_evt
On Tue, Apr 12, 2005 at 08:58:41AM -0700, Bill Chandler wrote:
> I did not intend to put explicit values in the
> 'evt_id' column. I thought the six values in the
> insert command correspond to the 1st six columns in
> the create table command, namely d1, obj_id, d2, val,
> correction and delta an
I did not intend to put explicit values in the
'evt_id' column. I thought the six values in the
insert command correspond to the 1st six columns in
the create table command, namely d1, obj_id, d2, val,
correction and delta and 'evt_id' is set to the
nextval() automagically. Is that not correct?
On Tue, Apr 12, 2005 at 08:48:15AM -0700, Bill Chandler wrote:
> How does one find out the current sequence value? Is
> there a way to change it?
Using the function setval() you can change it. SELECT * from
sequencename to find out.
--
Alvaro Herrera (<[EMAIL PROTECTED]>)
"Si quieres ser crea
I was able to get a pg_dump of the table in question.
It has 23040 rows in it. evt_id column ranges from 1
to 23040.
I used the dump to create a new database. All inserts
fail with same error (unique constraint violation).
However, I am wondering if this is just the result of
the fact that
On Tue, Apr 12, 2005 at 07:59:55AM -0700, Bill Chandler wrote:
> I'm sorry, was working on little sleep yesterday. You
> are right, the table was created with the columns in
> the following order:
>
> d1, obj_id, d2, val, correction, delta, evt_id
>
> The insert command looks something like:
>
I'm sorry, was working on little sleep yesterday. You
are right, the table was created with the columns in
the following order:
d1, obj_id, d2, val, correction, delta, evt_id
The insert command looks something like:
INSERT INTO EVENT_TBL VALUES(1039850293991, 145,
1039110343000, '10.25', 1, 739
On Mon, 2005-04-11 at 11:03 -0700, Bill Chandler wrote:
> ERROR: duplicate key violates unique constraint
> event_tbl_evt_id_key
> EVENT_TBL
> evt_id bigserial, unique
> d1 numeric(13)
> obj_id numeric(6)
> d2 numeric(13)
> val varchar(22)
> correction n
Tom,
This is not the EXACT command (don't have that since
this a client site and they did not have logging
turned on) but the insert command would have looked
something like:
INSERT INTO EVENT_TBL VALUES(1039850293991, 'X.Y.Z',
1039110343000, '10.25', 1, 739950991)
For what its worth, the comman
Bill Chandler <[EMAIL PROTECTED]> writes:
> Client is getting the following error when attempting
> to do an insert on a table:
> ERROR: duplicate key violates unique constraint
> event_tbl_evt_id_key
What's the exact query (or queries) causing this? Do you have any
triggers or rules that might
Hello,
Client is getting the following error when attempting
to do an insert on a table:
ERROR: duplicate key violates unique constraint
event_tbl_evt_id_key
Client is using PostgreSQL 7.4.2 on Sparcv9 running
Solaris.
We have the following tables:
EVENT_TBL
evt_id bigserial, unique
d1
Hi!
I've got a UNIQUE constraint on a field, and obviously, when I try to
insert a duplicate value, I get a WARNING via psql (and an Exception via
the JDBC drivers) saying I tried to violate the constraint. No biggie.
This is what I expect.
The tricky part comes in when I violate the constraint
On Thu, Sep 27, 2001 at 06:30:50PM -0400, some SMTP stream spewed forth:
> Is the index, created implicitely by "UNIQUE" constraint, the same
> kind as created explicitely with "CREATE INDEX"? In other words,
> is the following piece of SQL redundant?
>
> create table foo (
>bar serial
With 7.1b4:
test=# create table auction_type(id serial,login text,birthday timestamp);
test=# create table auction(unique(login)) inherits("auction_type");
ERROR: inherited attribute "login" cannot be a PRIMARY KEY because it is not marked
NOT NULL
But I didn't ask that "login" be a PRIMARY K
Tom Lane wrote:
>Merrill Oveson <[EMAIL PROTECTED]> writes:
>> It appears as though the null value inserted for column b causes an
>> abrogation of the unique constaint.
>
>Two nulls are never considered equal, therefore the unique constraint
>does not trigger.
>
>This is correct b
Merrill Oveson <[EMAIL PROTECTED]> writes:
> It appears as though the null value inserted for column b causes an
> abrogation of the unique constaint.
Two nulls are never considered equal, therefore the unique constraint
does not trigger.
This is correct behavior according to SQL92 4.10.2:
> unique (a, b, c)
> );
>
> insert into letter values('1','2','3');
> insert into letter values('1','2','3');
>
> insert into letter (a,c) values ('1','3');
>
All:
Run the following script:
drop table letter;
create table letter (
a char(1),
b char(1),
c char(1),
unique (a, b, c)
POSTGRESQL BUG REPORT TEMPLATE
Your name : Mark Dalphin
Your email address : [EMAIL PRO
73 matches
Mail list logo
