471,354 Members | 2,139 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,354 software developers and data experts.

7.4b4 domain usage and select question


I'm using 7.4b4 with domains, and am having a problem with selecting without
casting.

create domain test_domain as integer check( (value notnull) and (value >= 1) );
create table test_table( field_1 test_domain, field_2 integer );
insert into test_table values( 1, 1 );
insert into test_table values( '2', '2' );
select * from test_table where field_2 = '1';
select * from test_table where field_1 = '1'; -- fails

The last select fails with 'operator is not unique: test_domain = "unknown"'

The insert with quoted values works fine without casting.

The HINT indicates I need to cast, which works find with either '1'::integer or
'1'::test_domain. Is this the correct behavior? Do I have to cast every quoted
value? The values are quoted through the usage of DBD::Pg.

Thanks,
Rob

--
10:32:54 up 78 days, 2:58, 4 users, load average: 2.00, 2.00, 2.00

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (GNU/Linux)

iEYEARECAAYFAj+RbW8ACgkQgy51bQc2FFl5vgCgn6afbAnfzx joSTc+jP4CYsAS
pzgAn3so+rDuCWPiWQFaUeMBx3V/iHZo
=dS3J
-----END PGP SIGNATURE-----

Nov 12 '05 #1
5 1652
Robert Creager <Ro************@LogicalChaos.org> writes:
select * from test_table where field_1 = '1'; -- fails
The last select fails with 'operator is not unique: test_domain = "unknown"'


Works as expected in CVS tip ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #2
When grilled further on (Sat, 18 Oct 2003 13:12:41 -0400),
Tom Lane <tg*@sss.pgh.pa.us> confessed:
Robert Creager <Ro************@LogicalChaos.org> writes:
select * from test_table where field_1 = '1'; -- fails
The last select fails with 'operator is not unique: test_domain = "unknown"'


Works as expected in CVS tip ...

regards, tom lane


Per your suggestion, I grabbed the nightly snapshot. It works fine for the
domain problem I was encountering.

But, I cannot turn of logging of the duration! It's set to
log_min_duration_statement = 0, but still logs the duration of every statement.
This didn't happen with 7.4b4... I've set it high to not log most statements.

Cheers,
Rob

--
13:36:33 up 78 days, 6:02, 4 users, load average: 2.00, 2.00, 2.00

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (GNU/Linux)

iEYEARECAAYFAj+RlxQACgkQgy51bQc2FFl9qwCgm1UI7A3hsN i3k7oezbjnAB5B
iyQAn3FmM75TRMOQDTbgNqbAcsSuvRGM
=Vg9S
-----END PGP SIGNATURE-----

Nov 12 '05 #3
Robert Creager <Ro************@LogicalChaos.org> writes:
But, I cannot turn of logging of the duration! It's set to
log_min_duration_statement = 0,


-1 turns it off now.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #4
When grilled further on (Sat, 18 Oct 2003 15:46:55 -0400),
Tom Lane <tg*@sss.pgh.pa.us> confessed:
Robert Creager <Ro************@LogicalChaos.org> writes:
But, I cannot turn of logging of the duration! It's set to
log_min_duration_statement = 0,


-1 turns it off now.


Thanks. Didn't even thing of doing another initdb to see if the setting may of
changed...

Cheers,
Rob

--
13:54:49 up 78 days, 6:20, 4 users, load average: 2.00, 2.00, 2.00

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (GNU/Linux)

iEYEARECAAYFAj+RmtoACgkQgy51bQc2FFkW3wCcCNk5ofwsHa xLDYSdsL/Y9FIS
1bMAoKJ7OnQeJ60M6g4cNFjk5rxSZ5cX
=YN/f
-----END PGP SIGNATURE-----

Nov 12 '05 #5
On Sat, Oct 18, 2003 at 01:40:04PM -0600, Robert Creager wrote:
But, I cannot turn of logging of the duration! It's set to
log_min_duration_statement = 0, but still logs the duration of every
statement. This didn't happen with 7.4b4... I've set it high to not
log most statements.


The value has a different meaning now. You have to set it to -1 to
disable logging IIRC.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"In a specialized industrial society, it would be a disaster
to have kids running around loose." (Paul Graham)

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

23 posts views Thread by phpfrizzle | last post: by
7 posts views Thread by andy vandenberghe | last post: by
1 post views Thread by alexqa2003 | last post: by
4 posts views Thread by Rob Panosh | last post: by
reply views Thread by lannsjo | last post: by
4 posts views Thread by DBC User | last post: by
3 posts views Thread by Alex | last post: by
3 posts views Thread by Tcs | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.