473,372 Members | 1,236 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 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 1713
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

23
by: phpfrizzle | last post by:
Hi there, I have a site with products on it. The site has a mysql backend. All products belong to certain series (table series). There can be up to 4 different products (table products)...
7
by: andy vandenberghe | last post by:
Hello everybody, i have the following table (agltransact), in which 2 fields are relevant: ex_inv_ref account 15 1512 15 6040 16 1512 16 1512 16 ...
1
by: alexqa2003 | last post by:
got a table with (class, name, exam, score) fields. To find max(score) for exam that starts with letter 'm' grouped by class and name,did: select max(score) from #temp where exam like 'm%'...
4
by: Rob Panosh | last post by:
Hello, This is probably a stupid question but I am going to ask it anyway because I don't know the answer to it. Both SQL Select Statements, below, execute and retuurn the same results. Why...
0
by: lannsjo | last post by:
I have a "userstuff" table like this: | uid | user_id | typedb | dbid | ----------------------------------- | 1 | 12 | boards | 3 | | 2 | 13 | boards | 3 | | 3 | ...
4
by: DBC User | last post by:
Hi, I feel so stupid to ask this question, but here it goes When I select a column from a table, if the column has a null value I want the select to return me a blank. I have done this before...
3
by: Alex | last post by:
Hi, I need to form a query where i can add some columns based on the result. Table A ColA, ColB ---------- 1 A 2 B
3
by: Tcs | last post by:
My backend is DB2 on our AS/400. While I do HAVE DB2 PE for my PC, I haven't loaded it yet. I'm still using MS Access. And no, I don't believe this is an Access question. (But who knows? I...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...

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.