Re: [SQL] a wierd query
Sorry, I forgot one situation: if I run the query like so: select distinct on (task_id, begindate) task_id, workhour_id, begindate as date from workhour UNION select distinct on (task_id, enddate) task_id, workhour_id, enddate as date from workhour I get yet another value: 2961 rows. So I got 3 different result sets for 3 different ways to run the query. Even in this last case the UNION doesn't seem to only return unique values, and I will still need the top-level select. > -Original Message- > From: Stijn Vanroye > Sent: maandag 17 mei 2004 9:26 > To: [EMAIL PROTECTED] > Cc: 'Edmund Bacon' > Subject: RE: [SQL] a wierd query > > > Are you sure about that Edmund? > > I have the following query: > select distinct on (task_id, date) task_id, workhour_id, date from > ( > select task_id, workhour_id, begindate as date from workhour > UNION > select task_id, workhour_id, enddate as date from workhour > )as dist1 > which returns me 2763 rows in my case > > if I use the query without the top level select, like this: > select task_id, workhour_id, begindate as date from workhour > UNION > select task_id, workhour_id, enddate as date from workhour > I get 7146 rows. > > If I understand correctly there would be no need for the top > level select if UNION would be to only return unique values. > But given my test results this doesn't seem to be the case. > Am I missing something or am I misinterpreting something? I > mean I'm sure you get this information out of the > documentation, that's why this question has risen. > > > Kind regards, > > Stijn Vanroye > > > -Original Message- > > From: Edmund Bacon [mailto:[EMAIL PROTECTED] > > Sent: donderdag 13 mei 2004 17:28 > > To: sad > > Cc: [EMAIL PROTECTED] > > Subject: Re: [SQL] a wierd query > > > > > > sad wrote: > > > select distinct a as F from table > > > union > > > select distinct b as F from table; > > > > > > > Note that UNION only returns the unique values of the union > > You can get repeated values by using UNION ALL. > > > > > > -- > > Edmund Bacon <[EMAIL PROTECTED]> > > > > ---(end of > > broadcast)--- > > TIP 6: Have you searched our list archives? > > > >http://archives.postgresql.org > > > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] a wierd query
Are you sure about that Edmund? I have the following query: select distinct on (task_id, date) task_id, workhour_id, date from ( select task_id, workhour_id, begindate as date from workhour UNION select task_id, workhour_id, enddate as date from workhour )as dist1 which returns me 2763 rows in my case if I use the query without the top level select, like this: select task_id, workhour_id, begindate as date from workhour UNION select task_id, workhour_id, enddate as date from workhour I get 7146 rows. If I understand correctly there would be no need for the top level select if UNION would be to only return unique values. But given my test results this doesn't seem to be the case. Am I missing something or am I misinterpreting something? I mean I'm sure you get this information out of the documentation, that's why this question has risen. Kind regards, Stijn Vanroye > -Original Message- > From: Edmund Bacon [mailto:[EMAIL PROTECTED] > Sent: donderdag 13 mei 2004 17:28 > To: sad > Cc: [EMAIL PROTECTED] > Subject: Re: [SQL] a wierd query > > > sad wrote: > > select distinct a as F from table > > union > > select distinct b as F from table; > > > > Note that UNION only returns the unique values of the union > You can get repeated values by using UNION ALL. > > > -- > Edmund Bacon <[EMAIL PROTECTED]> > > ---(end of > broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] a wierd query
> I forgot one situation: > > if I run the query like so: > select distinct on (task_id, begindate) task_id, workhour_id, > begindate as date from workhour UNION > select distinct on (task_id, enddate) task_id, workhour_id, enddate > as date from workhour I get yet another value: 2961 rows. > > So I got 3 different result sets for 3 different ways to run the query. > Even in this last case the UNION doesn't seem to only return unique values, > and I will still need the top-level select. if we suppose this situation possible to program in SQL it causes data-loss in query (i mean unpredictable query result: if you have two records (f=1,b=2),(f=1,b=3) "distinct ON f" makes a value of b meaningless) So you MUST NOT select that way P.S. This situation means: you have wrong data structure. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] problem with spanish characters
Hello everybody, I’m not sure if this is a bug on the upper/lower command or anywhere else. This is the problem: I have a DB that uses ‘UNICODE’ encoding. PostgreSQL version is 7.4.2. I have a field that holds characters, including characters like ‘Ñ’ (lowercase version is ‘ñ’). The name of the field is ‘ccaa_ds_ccaa’ (table name is ‘ccaa’). When I execute the following SQL statement: select * from ccaa where ccaa_ds_ccaa like '%Ñ%' .. it returns one record (that’s the correct result). There is only one record that matches this criteria (the actual value is ‘CATALUÑA’). However, if I execute this one: select * from ccaa where lower(ccaa_ds_ccaa) like '%Ñ%' … it returns the very same record, which is WRONG, since the “lower” should make that query return no matching records. Similarly, I tried this: select * from ccaa where upper(ccaa_ds_ccaa) like upper(%ñ%') … and IT DOES NOT RETURN ANY MATCHING RECORDS (it should return the same record return by the previous query). It seems like the upper / lower commands do not work properly on UNICODE. I tested this using ‘LATIN9’ encoding an it works properly in all cases. Is this a known bug? Is there a bug fix for it? Where can I find it? What effect does using UNICODE / LATIN9 on BLOB fields (bytea)? (I believe there are issues with the JDBC drivers) Regards, Freddy.
Re: [SQL] a wierd query
First of all, I don't select distinct on 1 value, but on 2. Meaning I want each unique combination of task_id (or employee_id in this example) and date. That way both fields still have meaning. the workhour_id field is indeed redundant, but was still there from some pevious testing work. (It is now removed). both other fields are used. What I'm trying to achieve here is the following: for each task get all date's in wich that task has been performed (and each date only once per task). Since workhours have a begin date and time, as well as an end date and time. Begin- and enddate don't have to be the same day. But if a task is performed starting monday and lasting till tueseday, both days have to be included in the count. What I use now is this: select distinct on (date, employee_id) employee_id, date from ( select distinct on (begindate, employee_id) begindate as date, employee_id from workhour UNION select distinct on (enddate, employee_id) enddate as date, employee_id from workhour )as dist The workhour table looks something like this: workhour(workhour_id, task_id, employee_id, begindate, begintime, enddate, endtime) I Can't think of any other solution to achieve this. As far as I can tell, Im not missing something and I don't have meaningless fields (suggestions always welcome). Later on some grouping will be done to count the number of days worked on a certain task (or by a certain employee) in a given period. This still keeps my question open wether or not a UNION does only show unique values in the union. > -Original Message- > From: sad [mailto:[EMAIL PROTECTED] > Sent: maandag 17 mei 2004 9:13 > To: [EMAIL PROTECTED] > Subject: Re: [SQL] a wierd query > > > > I forgot one situation: > > > > if I run the query like so: > > select distinct on (task_id, begindate) task_id, workhour_id, > > begindate as date from workhour UNION > > select distinct on (task_id, enddate) task_id, > workhour_id, enddate > > as date from workhour I get yet another value: 2961 rows. > > > > So I got 3 different result sets for 3 different ways to > run the query. > > Even in this last case the UNION doesn't seem to only > return unique values, > > and I will still need the top-level select. > > if we suppose this situation possible to program in SQL > it causes data-loss in query > (i mean unpredictable query result: > if you have two records (f=1,b=2),(f=1,b=3) > "distinct ON f" makes a value of b meaningless) > > So you MUST NOT select that way > > P.S. This situation means: you have wrong data structure. > > > ---(end of > broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to > [EMAIL PROTECTED]) > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Unique Constraint with foreign Key
On Tue, Feb 10, 2004 at 12:03:36 -0500, [EMAIL PROTECTED] wrote: > Hi all i am using postgres and torque > I have a column x in Table A of type varchar and NOT NULL. I want to > impose a unique key constraint on that table and column y is the primary > key. > I have another Table B with column x. can i make this column x as a > foreign key on column x in table A. Yes. The reference will need to specify column x, since the primary key column (y) will be the default. > or Can i have varchar types of size 50 as primary keys in Postgres. Yes. But unless the 50 character limit comes from a business rule, you might want to use the type 'text'. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] a wierd query
On Mon, 17 May 2004, Stijn Vanroye wrote: > Are you sure about that Edmund? > > I have the following query: > select distinct on (task_id, date) task_id, workhour_id, date from > ( > select task_id, workhour_id, begindate as date from workhour > UNION > select task_id, workhour_id, enddate as date from workhour > )as dist1 This gets you first rows distincted by task_id, workhour_id and date and then only rows distincted by task_id and date (and an unpredictable workhour_id). > if I use the query without the top level select, like this: > select task_id, workhour_id, begindate as date from workhour > UNION > select task_id, workhour_id, enddate as date from workhour This gets rows distincted by task_id, workhour_id and date. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Max Columns in a SQL Select
SQL Guru's, Does anyone know what is the Column Limit in a SQL Query in Oracle RDB version 7.1? I've about 200 columns in my SQL query. Each column is a computed column -- Case when a = b Then c Else 0 End. It gives me a runtime error -- abnormal program termination -- upon execution. Thanks a lot in advance. __ Do you Yahoo!? SBC Yahoo! - Internet access at a great low price. http://promo.yahoo.com/sbc/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Unique Constraint with foreign Key
> > or Can i have varchar types of size 50 as primary keys in Postgres. > Yes. But unless the 50 character limit comes from a business rule, you > might want to use the type 'text'. And if that limit does come from a business rule you might want to think twice whether using columns constrained by business rules are good candidates for primary keys. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] view running query
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 There are two ways to do it. The server-side approach is to increase logging levels in the config file and then "pg_ctl reload". See http://www.postgresql.org/docs/7.4/interactive/runtime-config.html#RUNTIME-CONFIG-LOGGING for the stuff involved. The other way to do it is client side. In this case, you have to increase the logging level of your database connection. For example, if you have a perl DBD application, find the initilization of the database handle (by convention named $dbh) and then add $dbh->trace(2); After it. Drew Eric Anderson Vianet SAO wrote: | I run an application which connects to my pgsql DB. | | How could I see which query is sent to DB when, an example, i push some | application button (such ´find´). | | sds | | Eric Anderson | CPD Via Net SAO | 11-66432800 | | | ---(end of broadcast)--- | TIP 4: Don't 'kill -9' the postmaster -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFAqMzogfzn5SevSpoRAnDwAJ4+y6xBwD9hXQ2k7V4mJbUf26rKLQCeP74Q HdgdLOV8bpqh5z4hgcUX52M= =dVN9 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Max Columns in a SQL Select
PS PS wrote: SQL Guru's, Does anyone know what is the Column Limit in a SQL Query in Oracle RDB version 7.1? I've about 200 columns in my SQL query. Each column is a computed column -- Case when a = b Then c Else 0 End. You'll probably want an Oracle mailing list - this is for PostgreSQL. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Replace function ...
Hi, I need to write a function which has to replace a character with two or three other characters I have some data in the DB and when I retrieve this data, I have sorting problems because I use specific Croatian characters I know that the DB is not setup properly, but I cannot change anything now because some data could be lost So I need to change specific Croatian characters with some other standard characters in order to sort the data properly so, if anybody knows something or has such function, please, let me know thanks R.K.
Re: [SQL] [JDBC] problem with spanish characters
--- Freddy Villalba Arias
<[EMAIL PROTECTED]> escribió: > Hello
everybody,
>
>
>
> I'm not sure if this is a bug on the upper/lower
> command or anywhere else.
>
>
>
> This is the problem:
>
>
>
>
>
> I have a DB that uses 'UNICODE' encoding. PostgreSQL
> version is 7.4.2.
>
>
>
>
>
> I have a field that holds characters, including
> characters like 'Ñ' (lowercase version is 'ñ'). The
> name of the field is 'ccaa_ds_ccaa' (table name is
> 'ccaa').
>
Hola Freddy, he encontrado el mismo problema con psql,
ni sisquiera me permite insertar caracteres con Ñ en
bases de datos UNICODE.
Siempre he usado LATIN1 para mis bases de datos, lo
que parece ser el problema es el la codificación del
sistema operativo, en Fedora Core 1 en mi archivo
quite las referncias a UTF-8 o UNICODE en mi archivo
/etc/sysconfig/i18n
LANG="es_MX"
SUPPORTED="es_MX:es"
SYSFONT="latarcyrheb-sun16"
Me entro la duda e hice una prueba creando una tabla
con codificación UNICODE y tengo unos resultados
extraños:
Primero no puedo insertar datos con Ñ o ñ,
enes=# insert into enes(nombre) values('CATALUÑA');
ERROR: secuencia de bytes no válida para codificación
"UNICODE": 0xd141
Pero con LATIN1 nunca he tenido este problema
Segundo no puedo obtener el describe de la base de
datos:
enes=# \d
ERROR: secuencia de bytes no válida para codificación
"UNICODE": 0xed6e64
Voy a hacer la prueba con el archivo(fichero) i18n en
codificación UTF-8 y luego te digo que paso.
Nota: el postgres lo instale con RPMS, voy a tratar
tambien con el compilado del codigo fuente.
Saludos desde México.
=
--
Nahum Castro
Leon, Guanajuato, Mexico
e-mail: pedro1_72 at yahoo dot com
_
Do You Yahoo!?
La mejor conexión a internet y 25MB extra a tu correo por $100 al mes.
http://net.yahoo.com.mx
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Replace function ...
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 > I need to write a function which has to replace a character with two or three other > characters > You can use the replace function as specified below: replace(string text, from text, to text) text Replace all occurrences in @string of substring @from with substring @to. replace( 'abcdefabcdef', 'cd', 'XX') ==> abXXefabXXef Yasir -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (NetBSD) iQEVAwUBQKkT1+J7vYSSIbWdAQL/5QgAmCdJrYh4YjJFWKeWNk+OEuhpvRNYbRlM tH5dezmUrR4fkM4l2mfrmuIkTT+rqQNyRR3E5PhKyfkbnHdw7/WPalqGSbX1aIfP YZI/RdxH/Aqy+hO+zcUQzSjpYSEyKrmTVGLmRf+tyYg0QdZdAQFd6O9aKWMeCo28 TFKRFAlg+glT/YiN1JiwzF/QygBNmo3g+JDWZ+U8Au+y0fHh/3KeoyLx9ipMSqs/ uBiy8eNjOdAF3ihoydDw2uBpzjwtGv9eeR0Myh8m4Zvx9K239NVSz4s9a+2R1Onf Mr5PK7Te5TG8TLcjJyo35x8yC/57oufNvWoi+Q6Y7cmSxeiEcHzJiw== =xChX -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
