On 2010-12-13, Viktor Bojović wrote:
> --0015175cd20209e2030497532e39
> Content-Type: text/plain; charset=UTF-8
> Content-Transfer-Encoding: quoted-printable
>
> Hi
> im trying to create foreign key constraint which checks not only the
> existence of key in foreign table, but it has to check if no
Hi
im trying to create foreign key constraint which checks not only the
existence of key in foreign table, but it has to check if node field has
value=true
but i don't know how to do that, so im asking for help.
alter table wg.nc
add CONSTRAINT "nc_fk" FOREIGN KEY ("Id1")
REFERENCES "wg"."entiti
Hi folks.
I'm trying to model a vehicle compound structure and have the following:
Four seperate compounds, North, East, South and West,
Each compound has a number of rows with Labels fastened to the fences
As the compounds are not perfect rectangles, the rows are of differing
lengths.
I have
I see. Thanks for the tip.
Regards,
Fernando.
-Mensaje original-
De: Tom Lane [mailto:[EMAIL PROTECTED]
Enviado el: Miércoles, 20 de Junio de 2007 19:37
Para: Fernando Hevia
CC: 'PostgreSQL SQL List'
Asunto: Re: [SQL] Constraint exclusion
"Fernando Hevia" <[E
"Fernando Hevia" <[EMAIL PROTECTED]> writes:
> -- Constraints: one partition per month
> ALTER TABLE table_p01 ADD CONSTRAINT chk_table_p01_setuptime CHECK
> (EXTRACT(MONTH FROM setuptime) = 1::DOUBLE PRECISION);
The planner is not able to do anything with these constraints, other
than if there i
It seems constraint exclusion is not working with my partitioned tables and
the rules I wrote.
This is my partition deployment:
-- Master table
CREATE TABLE table_master (
setuptime timestamp with time zone NOT NULL,
...
};
-- Partitions
CREATE TABLE table_p01 INHERITS (table_mas
> > But if I want the next item following t=(a=10,b=100,c=1000):
>
> > select * from T
> > where (a=10 AND b=100 AND c>1000) OR (a=10 AND b>100) OR (a>10)
> > order by a,b,c;
>
> The correct way to handle this is to use a SQL-spec row comparison:
>
> where (a,b,c) > (10,100,1000)
>
> Unfortunate
"Stuart Brooks" <[EMAIL PROTECTED]> writes:
> But if I want the next item following t=(a=10,b=100,c=1000):
> select * from T
> where (a=10 AND b=100 AND c>1000) OR (a=10 AND b>100) OR (a>10)
> order by a,b,c;
The correct way to handle this is to use a SQL-spec row comparison:
where (a
Hi,
I am not sure this can be done but I'm trying to constrain a sorted set
efficiently using a multicolumn index in postgres. The (simplified)
scenario is this:
CREATE TABLE T
(
a INT,
b INT,
c INT
);
CREATE INDEX t_idx ON T(a,b,c);
Now I can sort using t_idx:
select * from T order by
> -Message d'origine-
> De : Michael Glaesemann [mailto:[EMAIL PROTECTED]
> Envoyé : samedi 1 juillet 2006 10:01
> À : Daniel CAUNE
> Cc : pgsql-sql@postgresql.org
> Objet : Re: [SQL] Constraint UNIQUE on a column not case sensitive
>
>
> On Jul 1, 200
Daniel CAUNE <[EMAIL PROTECTED]> writes:
> Hi,
>
> I would like to find an efficient solution for adding/implementing a
> constraint UNIQUE on a VARCHAR column not case sensitive:
>
> ALTER TABLE MyTable
> ADD CONSTRAINT UNQ_MyTable_MyColumn
> UNIQUE (lower(MyColumn)); -- invalid synt
On Jul 1, 2006, at 22:47 , Daniel CAUNE wrote:
The idea is to have an index on that column, in a not case
sensitive form, i.e. lower(MyColumn).
I think you're really close. Try
CREATE UNIQUE INDEX ci_mycolumn_idx ON mytable(lower(mycolumn));
Does that do what you're looking for?
Michael G
Hi,
I would like to find an efficient solution for adding/implementing a constraint
UNIQUE on a VARCHAR column not case sensitive:
ALTER TABLE MyTable
ADD CONSTRAINT UNQ_MyTable_MyColumn
UNIQUE (lower(MyColumn)); -- invalid syntax
The idea is to have an index on that column, in a n
Hi, Andreas,
Andreas Joseph Krogh wrote:
>>CREATE UNIQUE INDEX foo ON onp_crm_businessfield_company(company_id)
>>WHERE is_prefferred;
> I also figured out that an index like this also works:
> UNIQUE(company_id, is_preferred)
> This works because NULL = NULL is false. But I guess your solution
On Thursday 18 May 2006 18:38, Markus Schaber wrote:
> Hi, Andreas,
>
> Andreas Joseph Krogh wrote:
> > create table onp_crm_businessfield_company(
> > businessfield_id integer not null references onp_crm_businessfield(id),
> > company_id integer not null references onp_crm_relation(id),
> > is_pre
Hi, Andreas,
Andreas Joseph Krogh wrote:
> create table onp_crm_businessfield_company(
> businessfield_id integer not null references onp_crm_businessfield(id),
> company_id integer not null references onp_crm_relation(id),
> is_preferred boolean,
> UNIQUE(businessfield_id, company_id)
> );
> I
I have the following table:
create table onp_crm_businessfield_company(
businessfield_id integer not null references onp_crm_businessfield(id),
company_id integer not null references onp_crm_relation(id),
is_preferred boolean,
UNIQUE(businessfield_id, company_id)
);
I want a constraint on "is_pr
Christian Paul B. Cosinas wrote:
Then I have a program that insert 1(one) million times like this:
Insert into unique_items(item_id) values('Item001)
Ofcourse we all know that it will fail because there is already a record in
the database.
Would there be any bad effect on the database or none
Hi Fellow PostgreSQL users,
Just a question here.
Is there any bad effect on the PostgreSQL performance If I encounter many
fails on inserting records to database with primary key column.
For example I have this table
CREATE TABLE unique_items
(
item_id text NOT NULL,
CONSTRAINT unique_item
Daryl Richter wrote:
>> No. A constraint only applies to one row at a time. If you try to
>> work around
>> this by calling a function that does queries it isn't guarenteed to
>> work.
>> And if you are thinking of calling a function that does a query, you
>> aren't
>> looking at saving time ove
On Dec 29, 2005, at 2:16 AM, Bruno Wolff III wrote:
On Wed, Dec 28, 2005 at 00:52:18 +0700,
David Garamond <[EMAIL PROTECTED]> wrote:
Is it possible to use only CHECK constraint (and not triggers) to
completely enforce ordered value of a column (colx) in a table? By
that
I mean:
1. Rows
On Wed, Dec 28, 2005 at 00:52:18 +0700,
David Garamond <[EMAIL PROTECTED]> wrote:
> Is it possible to use only CHECK constraint (and not triggers) to
> completely enforce ordered value of a column (colx) in a table? By that
> I mean:
>
> 1. Rows must be inserted in the order of colx=1, then colx
Is it possible to use only CHECK constraint (and not triggers) to
completely enforce ordered value of a column (colx) in a table? By that
I mean:
1. Rows must be inserted in the order of colx=1, then colx=2, 3, and so on;
2. When deleting (or updating), "holes" must not be formed, e.g. if
there a
Thanks.
I managed it via a trigger.
> -Original Message-
> From: Michael Fuhr [mailto:[EMAIL PROTECTED]
> Sent: Sonntag, 13. Februar 2005 02:57
> To: KÖPFERL Robert
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] Constraint doesn't see a currently inserte
On Fri, Feb 11, 2005 at 07:10:50PM +0100, KÖPFERL Robert wrote:
> Another Idea was to make a trigger. But BTW how do I access a trigger
> parameter if my trigger function must not have any parameter??
PL/pgSQL triggers can access arguments via TG_ARGV.
http://www.postgresql.org/docs/8.0/
Hi,
yes that's my problem.
I've got a table and I put lots of contraints on it so that data stays
consistent. One constraint calls a fcn to do some kind of count() over that
table but it omits the 'to be inserted record'. What to do?
concrete problem:
Suppose a table
i | tel | status | ...
..|
On Thu, 27 Jan 2005 13:44:32 +0200, Andrei Bintintan <[EMAIL PROTECTED]> wrote:
>
> Hi,
>
> I have a table:
> CREATE TABLE werke1(
> id SERIAL,
> id_hr int4 NOT NULL,
> id_wk int4 NOT NULL
> );
>
> CREATE TABLE contact(
> id SERIAL,
> type varchar(20),
> );
>
>
> Now id_hr and id_wk are all referen
On Jan 27, 2005, at 6:44 AM, Andrei Bintintan wrote:
Hi,
I have a table:
CREATE TABLE werke1(
id SERIAL,
id_hr int4 NOT NULL,
id_wk int4 NOT NULL
);
CREATE TABLE contact(
id SERIAL,
type varchar(20),
);
It looks like you want a two-column primary key for table contact and
then you can refere
Hi,
I have a table:CREATE TABLE werke1(id SERIAL,id_hr int4 NOT NULL,id_wk int4 NOT NULL);
CREATE TABLE contact(id SERIAL,type
varchar(20),
);
Now id_hr and id_wk are all referencing the same
table contact(id). In the contact table I have another column called type.
How can I write
Dear Rod,
That sounds as good as simple ;) As for as patching, we do something like
that. Developed a PHP script that compares schema files (not dumps, but
source codes instead) to the actual. Say, it creates a temp table and
compares it to the existing one, examining pg_attributes, pg_indexes,
pg
On Tue, 2004-07-13 at 13:42, SZŰCS Gábor wrote:
> Dear Rod,
>
> Thanks. It'll be a pain to have two versions between the prod and devel
> servers, but I'll forward this info to the chief.
You can make this part easier on yourself.
Dump the structure from production and migrate it to devel (fix t
Dear Rod,
Thanks. It'll be a pain to have two versions between the prod and devel
servers, but I'll forward this info to the chief.
Thanks again,
G.
%--- cut here ---%
\end
- Original Message -
From: "Rod Taylor" <[EMAIL PROTECTED]>
Sent: Tuesday,
> Checked, and So do you say, this problem persists in dbs dumped from 7.4 to
> 7.4 too? i.e. upgrading to 7.4 (which we tested for quite some time now)
> won't help?
There may have been some minor fiddling to make it easier, but I
wouldn't call it fixed by any means.
> trying dump confirmed thi
ything.
G.
%--- cut here ---%
\end
- Original Message -
From: "Rod Taylor" <[EMAIL PROTECTED]>
Sent: Monday, July 12, 2004 2:51 PM
Subject: Re: [SQL] Constraint->function dependency and dump in 7.3
> On Mon, 2004-07-12 at 08:29, SZ
Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] Behalf Of SZŰCS Gábor
> Sent: 2004. július 12. 14:30
> To: [EMAIL PROTECTED]
> Subject: Re: [SQL] Constraint->function dependency and dump in 7.3
>
>
> Dear Fellow Countymen,
>
> I fear none of your
On Mon, 2004-07-12 at 08:29, SZŰCS Gábor wrote:
> Dear Fellow Countymen,
>
> I fear none of your answers are acceptable for me. Also, from Csaba's
> answer, this sounds to me more like a bug in pg_dump v7.3.
It is a bug (or a missing feature) that should be solved with v7.5 for
restoring to 7.5 o
start with the schema file.
I hope I was clear. Another advance of this method is that it is absolutely
Y3K safe. :)
Bye,
-- Csaba Együd
> -Original Message-
> From: [EMAIL PROTECTED]
> Sent: 2004. július 8. 13:10
> Subject: [SQL] Constraint->function dependency and dump
L PROTECTED]
Subject: Re: [SQL] Constraint->function dependency and dump in 7.3
Hi Gábor!
I had the same problem and someone pointed me the right direction. I tried
to define a table default clause refering a function. Reloading the dump
file an error messaged raised up saying that the referred
the schema file.
I hope I was clear. Another advance of this method is that it is absolutely
Y3K safe. :)
Bye,
-- Csaba Együd
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] Behalf Of SZŰCS Gábor
> Sent: 2004. július 8. 13:10
> To: [EMAIL P
Dear Gurus,
Just recently realized that our daily dump from a 7.3 (production) db to a
7.4 (development) server has problems. I have no idea where to search for an
answer so please feel free to point me to the appropriate thread, doc or TFM
:)
Below is two portions of the dump, which seems to be
On Fri, 27 Sep 2002 11:29:34 -0700 (PDT)
Stephan Szabo <[EMAIL PROTECTED]> wrote:
> On Fri, 27 Sep 2002, GB Clark wrote:
>
> > CREATE TABLE sysusers (
> > user_id INTEGER DEFAULT nextval('sysusers_user_id_seq')
> > UNIQUE NOT NULL PRIMARY K
On Fri, 27 Sep 2002, GB Clark wrote:
> CREATE TABLE sysusers (
> user_id INTEGER DEFAULT nextval('sysusers_user_id_seq')
> UNIQUE NOT NULL PRIMARY KEY,
> username text NOT NULL UNIQUE,
> password
Hello,
I'm trying to create a constraint that will check to see if the inserted data is found
in another column in the table.
It could be that I'm going about this the wrong way, any clues for me?
Here is the present setup data for the table.
--SNIP--
--
--
--
DROP SEQUENCE sysusers_user_id_s
t; To: [EMAIL PROTECTED]
> Subject: [SQL] constraint
>
>
>
>
> Hi list,
> I need your help.
> How I can delete or DROP a constraint?
> I use POSTGRESQL 7.0.3
>
> _
> MSN. Más Útil cada Día. http://www.
Hi list,
I need your help.
How I can delete or DROP a constraint?
I use POSTGRESQL 7.0.3
_
MSN. Más Útil cada Día. http://www.msn.es/intmap/
---(end of broadcast)---
TIP 6: Have y
add an foriegn key on address(country_id), let country(id) be a primary
key.
Jie LIANG
St. Bernard Software
Internet Products Inc.
10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873
[EMAIL PROTECTED]
www.stbernard.com
www.ipinc.com
On Wed, 14 Feb 2001, Olaf Marc Za
From: "Olaf Marc Zanger" <[EMAIL PROTECTED]>
> hi there,
>
> with two tables i want to make some constraint-restrictions
>
> to make sure that now country-row is deleted if there is still a
country_id
> in address table.
>
> e.g.
>
> address: 1, 2, ...
> country: 2, ...
>
> now country wouldn't b
hi there,
with two tables i want to make some constraint-restrictions
create table address ( id serial, country_id int4, );
and
create table country (id serial, ...);
to make sure that now country-row is deleted if there is still a country_id
in address table.
e.g.
address: 1, 2, ...
co
Bernie Huang wrote:
> Hi,
>
> I am a newbie to SQL. Could someone please explain why do I need a
> contraint, and how do I use it in SQL? An example is needed. Thanks
Constraints are used to have the database engine ensure the
integrity of the data. It'l like teaching the database it
Hi,
I am a newbie to SQL. Could someone please explain why do I need a
contraint, and how do I use it in SQL? An example is needed. Thanks
- Bernie
begin:vcard
n:Huang;Bernie
tel;fax:(604)664-9195
tel;work:(604)664-9172
x-mozilla-html:TRUE
org:Environment Canada;Standards and Technology Se
50 matches
Mail list logo
