pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Actual expression of a constraint
9+ messages / 4 participants
[nested] [flat]

* Actual expression of a constraint
@ 2006-02-06 10:05  lrotger <lrotger@aircomp.aero>
  0 siblings, 1 reply; 9+ messages in thread

From: lrotger @ 2006-02-06 10:05 UTC (permalink / raw)
  To: pgsql-admin

Hi,

I know I could dig this out of the manuals so don't be too hard on me, 
I'd like to know how to query the actual text of the expression of a 
constraint. I know the name of the constraint and of the table.

Thanks,
L Rotger



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Actual expression of a constraint
@ 2006-02-06 18:05  Tom Lane <tgl@sss.pgh.pa.us>
  parent: lrotger <lrotger@aircomp.aero>
  0 siblings, 1 reply; 9+ messages in thread

From: Tom Lane @ 2006-02-06 18:05 UTC (permalink / raw)
  To: lrotger <lrotger@aircomp.aero>; +Cc: pgsql-admin

lrotger <lrotger@aircomp.aero> writes:
> I know I could dig this out of the manuals so don't be too hard on me, 
> I'd like to know how to query the actual text of the expression of a 
> constraint. I know the name of the constraint and of the table.

Something like this:

regression=# create table t1 (f1 int constraint c1 check (f1 > 0));
CREATE TABLE
regression=# select pg_get_constraintdef(c.oid)
regression-#   from pg_constraint c join pg_class t on c.conrelid = t.oid
regression-#   where t.relname = 't1' and c.conname = 'c1';
 pg_get_constraintdef
----------------------
 CHECK ((f1 > 0))
(1 row)

You can probably also get it from the information_schema.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Actual expression of a constraint
@ 2006-02-07 15:33  lrotger <lrotger@aircomp.aero>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 9+ messages in thread

From: lrotger @ 2006-02-07 15:33 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin

Tom Lane wrote:
> lrotger <lrotger@aircomp.aero> writes:
> 
>>I know I could dig this out of the manuals so don't be too hard on me, 
>>I'd like to know how to query the actual text of the expression of a 
>>constraint. I know the name of the constraint and of the table.
> 
> 
> Something like this:
> 
> regression=# create table t1 (f1 int constraint c1 check (f1 > 0));
> CREATE TABLE
> regression=# select pg_get_constraintdef(c.oid)
> regression-#   from pg_constraint c join pg_class t on c.conrelid = t.oid
> regression-#   where t.relname = 't1' and c.conname = 'c1';
>  pg_get_constraintdef
> ----------------------
>  CHECK ((f1 > 0))
> (1 row)
> 
> You can probably also get it from the information_schema.
> 
> 			regards, tom lane
> 

I forgot to mention that my version is 7.2.1. I replaced the column 
names for the ones I see in my installation hoping it would work but the 
function, pg_get_conbstraintdef() doesn't exist.

I see that information_schema was introduced in 8.0 too.

Anyway I found out that \d table shows the table definition so it's solved.

Thanks a lot
L Rotger



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Actual expression of a constraint
@ 2006-02-07 16:01  Tom Lane <tgl@sss.pgh.pa.us>
  parent: lrotger <lrotger@aircomp.aero>
  0 siblings, 1 reply; 9+ messages in thread

From: Tom Lane @ 2006-02-07 16:01 UTC (permalink / raw)
  To: lrotger <lrotger@aircomp.aero>; +Cc: pgsql-admin

lrotger <lrotger@aircomp.aero> writes:
> I forgot to mention that my version is 7.2.1.

Egad, don't tell me you're still using 7.2.1 for anything important :-(
The list of bugs fixed since then would curl your toes.
http://developer.postgresql.org/docs/postgres/release.html

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Actual expression of a constraint
@ 2006-02-07 16:51  lrotger <lrotger@aircomp.aero>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 9+ messages in thread

From: lrotger @ 2006-02-07 16:51 UTC (permalink / raw)
  To: Tom Lane <tgl@pgh.pa.us>; +Cc: pgsql-admin

Tom Lane wrote:
> lrotger <lrotger@aircomp.aero> writes:
> 
>>I forgot to mention that my version is 7.2.1.
> 
> 
> Egad, don't tell me you're still using 7.2.1 for anything important :-(
> The list of bugs fixed since then would curl your toes.
> http://developer.postgresql.org/docs/postgres/release.html
> 
> 			regards, tom lane
> 

Well it probably would, changelogs are scary and I really appreciate the 
work of the postgres hackers, but you know the old adage: "if it's not 
broken, don't fix it"; it works wonderfully for me and it's accessed in 
a controlled environment by a single application done by me. Should I be 
worried?

Anyway I'll have a look at the successive changelogs there and possibly 
I'll find enough reasons to schedule an upgrade.

Thanks a lot
L Rotger



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Actual expression of a constraint
@ 2006-02-07 18:16  Tom Lane <tgl@sss.pgh.pa.us>
  parent: lrotger <lrotger@aircomp.aero>
  1 sibling, 1 reply; 9+ messages in thread

From: Tom Lane @ 2006-02-07 18:16 UTC (permalink / raw)
  To: lrotger <lrotger@aircomp.aero>; +Cc: pgsql-admin

lrotger <lrotger@aircomp.aero> writes:
> Tom Lane wrote:
>> Egad, don't tell me you're still using 7.2.1 for anything important :-(

> Well it probably would, changelogs are scary and I really appreciate the 
> work of the postgres hackers, but you know the old adage: "if it's not 
> broken, don't fix it"; it works wonderfully for me and it's accessed in 
> a controlled environment by a single application done by me. Should I be 
> worried?

Yes, you should.  Someday one of those bugs will eat your data.  The
fact that it hasn't happened yet does not mean you're not at risk;
a lot of them are simply race conditions with very small windows.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Actual expression of a constraint
@ 2006-02-07 21:47  Alvaro Herrera <alvherre@commandprompt.com>
  parent: lrotger <lrotger@aircomp.aero>
  1 sibling, 0 replies; 9+ messages in thread

From: Alvaro Herrera @ 2006-02-07 21:47 UTC (permalink / raw)
  To: lrotger <lrotger@aircomp.aero>; +Cc: Tom Lane <tgl@pgh.pa.us>; pgsql-admin

lrotger wrote:

> Well it probably would, changelogs are scary and I really appreciate the 
> work of the postgres hackers, but you know the old adage: "if it's not 
> broken, don't fix it";

The problem is that it _is_ broken, so it needs fixing.  You have just
been lucky, or haven't yet detected that your data has problems.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Actual expression of a constraint
@ 2006-02-08 10:36  lrotger <lrotger@aircomp.aero>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 9+ messages in thread

From: lrotger @ 2006-02-08 10:36 UTC (permalink / raw)
  To: ; +Cc: pgsql-admin

Tom Lane wrote:
> 
> 
> Yes, you should.  Someday one of those bugs will eat your data.  The
> fact that it hasn't happened yet does not mean you're not at risk;
> a lot of them are simply race conditions with very small windows.
> 
> 			regards, tom lane

I read some of the logs up until 7.3 (from my old 7.2.1); I should 
benefit from upgrading but there are issues to consider, for example, I 
see 'timestamp' was changed from with to without time zone in 7.3, I'll 
have to follow these changes carefully to upgrade without problems.

Thanks,
L Rotger



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Actual expression of a constraint
@ 2006-02-08 15:52  Kris Deugau <kdeugau@vianet.ca>
  parent: lrotger <lrotger@aircomp.aero>
  0 siblings, 0 replies; 9+ messages in thread

From: Kris Deugau @ 2006-02-08 15:52 UTC (permalink / raw)
  To: pgsql-admin

lrotger wrote:
> I read some of the logs up until 7.3 (from my old 7.2.1); I should 
> benefit from upgrading but there are issues to consider, for example, I 
> see 'timestamp' was changed from with to without time zone in 7.3, I'll 
> have to follow these changes carefully to upgrade without problems.

Tom's major recommendation is to move up to the latest 7.2 release - 
which, while currently unsupported, contains a number of BIG bugfixes 
relating to data integrity.  (IIRC the version number is 7.2.5).  You 
should be able to install packages or build from source and install in 
place without any changes to your code.  Getting a dump of your data 
beforehand is always a good idea Just In Case, but it shouldn't be 
necessary most of the time.

Jumping between "major" versions (eg, 7.2->7.3) is where there are 
changes to data types and the back-end storage;  for a major version 
change the usual recommendation is to dump and reload.

Check back through the list archives;  this general recommendation ("For 
PG version x.x.n, always run the latest point-release (n) for any given 
major version (x.x)") has been posted quite a few times over this past 
year alone.

-kgd



^ permalink  raw  reply  [nested|flat] 9+ messages in thread


end of thread, other threads:[~2006-02-08 15:52 UTC | newest]

Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-02-06 10:05 Actual expression of a constraint lrotger <lrotger@aircomp.aero>
2006-02-06 18:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-02-07 15:33   ` lrotger <lrotger@aircomp.aero>
2006-02-07 16:01     ` Tom Lane <tgl@sss.pgh.pa.us>
2006-02-07 16:51       ` lrotger <lrotger@aircomp.aero>
2006-02-07 18:16         ` Tom Lane <tgl@sss.pgh.pa.us>
2006-02-08 10:36           ` lrotger <lrotger@aircomp.aero>
2006-02-08 15:52             ` Kris Deugau <kdeugau@vianet.ca>
2006-02-07 21:47         ` Alvaro Herrera <alvherre@commandprompt.com>

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