[SQL] matching a timestamp field
Hello, Why is my sql below accepted in 8.1.19 and refused in 8.4.9 ??? Is there something I have missed in the doc ? Welcome to psql 8.1.19, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit ansroc=# select * from s12hwdb where record ~'2012-09-20' limit 5; host | exchange | rit | board | var | lceid | pceid | mnem | eq | rtyp | rv | cetype | record| type | zone --+--+-+--+--+---+---+---++--++--+-+--+-- and5032t | and5032t | 01a0301 | 21122994 | ebjb | | 000c | con3a | e | ef03 | b1 | plce#xfx | 2012-09-20 11:50:02 | H| a1 and5032t | and5032t | 01a0307 | 21406298 | aaca | | 000c | mmca | e | ef03 | b1 | plce#xfx | 2012-09-20 11:50:02 | H| a1 and5032t | and5032t | 01a0309 | 21406298 | aaca | | 000c | mmca | s | ef03 | b1 | plce#xfx | 2012-09-20 11:50:02 | H| a1 and5032t | and5032t | 01a0311 | 21407930 | | | 000c | mmcb | e | ef03 | b1 | plce#xfx | 2012-09-20 11:50:02 | H| a1 and5032t | and5032t | 01a0313 | 21407932 | abca | | 000c | mcud | e | ef03 | b1 | plce#xfx | 2012-09-20 11:50:02 | H| a1 (5 rows) ansroc=# \q psql (8.4.9) Type "help" for help. ansroc=# select * from s12hwdb where record ~'2012-09-20' limit 5; ERROR: operator does not exist: timestamp without time zone ~ unknown LINE 1: select * from s12hwdb where record ~'2012-09-20' limit 5; ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. ansroc=# Pierre. +32 471 68 12 23 * Disclaimer * http://www.belgacom.be/maildisclaimer
Re: [SQL] matching a timestamp field
BACHELART PIERRE (CIS/SCC) wrote: > Hello, > > > > > > Why is my sql below accepted in 8.1.19 and refused in 8.4.9 ??? > > Welcome to psql 8.1.19, the PostgreSQL interactive terminal. > > ansroc=# select * from s12hwdb where record ~'2012-09-20' limit 5; > > > psql (8.4.9) > > > ERROR: operator does not exist: timestamp without time zone ~ unknown > > LINE 1: select * from s12hwdb where record ~'2012-09-20' limit 5; > Because of the dropped implicid casts since IIRC 8.2. You have to rewrite your query to: select * from s12hwdb where record::date = '2012-09-20'::date limit 5; (assuming record is a TIMESTAMP-Field) Short example: test=# select now() ~ '2012-09-22'; ERROR: operator does not exist: timestamp with time zone ~ unknown LINE 1: select now() ~ '2012-09-22'; ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. Time: 0,156 ms test=!# rollback; ROLLBACK Time: 0,079 ms test=# select now()::date = '2012-09-22'::date; ?column? -- t (1 row) Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Problem with committing the update
On 2012-09-13, BeeBee wrote: > Hi all, > > I have a problem updating the record using store procedure (LANGUAGE > plpgsql).I have attach the query. it all looks good to me. -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] matching a timestamp field
Hello 2012/9/20 BACHELART PIERRE (CIS/SCC) : > Hello, > > > > > > Why is my sql below accepted in 8.1.19 and refused in 8.4.9 ??? > > Is there something I have missed in the doc ? > you cannot use ~ operator for timestamp, it is nonsense - use '=' instead see 8.3 release notes http://www.postgresql.org/docs/9.1/static/release-8-3.html A dump/restore using pg_dump is required for those wishing to migrate data from any previous release. Observe the following incompatibilities: E.51.2.1. General Non-character data types are no longer automatically cast to TEXT (Peter, Tom) Regards Pavel Stehule > > > > > Welcome to psql 8.1.19, the PostgreSQL interactive terminal. > > Type: \copyright for distribution terms > >\h for help with SQL commands > >\? for help with psql commands > >\g or terminate with semicolon to execute query > >\q to quit > > > > ansroc=# select * from s12hwdb where record ~'2012-09-20' limit 5; > >host | exchange | rit | board | var | lceid | pceid | mnem | > eq | rtyp | rv | cetype | record| type | zone > > --+--+-+--+--+---+---+---++--++--+-+--+-- > > and5032t | and5032t | 01a0301 | 21122994 | ebjb | | 000c | con3a | e > | ef03 | b1 | plce#xfx | 2012-09-20 11:50:02 | H| a1 > > and5032t | and5032t | 01a0307 | 21406298 | aaca | | 000c | mmca | e > | ef03 | b1 | plce#xfx | 2012-09-20 11:50:02 | H| a1 > > and5032t | and5032t | 01a0309 | 21406298 | aaca | | 000c | mmca | s > | ef03 | b1 | plce#xfx | 2012-09-20 11:50:02 | H| a1 > > and5032t | and5032t | 01a0311 | 21407930 | | | 000c | mmcb | e > | ef03 | b1 | plce#xfx | 2012-09-20 11:50:02 | H| a1 > > and5032t | and5032t | 01a0313 | 21407932 | abca | | 000c | mcud | e > | ef03 | b1 | plce#xfx | 2012-09-20 11:50:02 | H| a1 > > (5 rows) > > > > ansroc=# \q > > > > > > > > psql (8.4.9) > > Type "help" for help. > > ansroc=# select * from s12hwdb where record ~'2012-09-20' limit 5; > > ERROR: operator does not exist: timestamp without time zone ~ unknown > > LINE 1: select * from s12hwdb where record ~'2012-09-20' limit 5; > >^ > > HINT: No operator matches the given name and argument type(s). You might > need to add explicit type casts. > > ansroc=# > > > > > > > > > > Pierre. > > +32 471 68 12 23 > > > > > > > * Disclaimer * > http://www.belgacom.be/maildisclaimer -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] matching a timestamp field
Hello, The solution I just found on the Net (Thanks to Samuel Gendler) ansroc=# select * from s12hwdb where record::text ~ '2012-09-20 11:50:02' limit 5; host | exchange | rit | board | var | lceid | pceid | mnem | eq | rtyp | rv | cetype | record| type | zone --+--+-+--+--+---+---+---++--++--+-+--+-- and5032t | and5032t | 01a0301 | 21122994 | ebjb | | 000c | con3a | e | ef03 | b1 | plce#xfx | 2012-09-20 11:50:02 | H| a1 and5032t | and5032t | 01a0307 | 21406298 | aaca | | 000c | mmca | e | ef03 | b1 | plce#xfx | 2012-09-20 11:50:02 | H| a1 and5032t | and5032t | 01a0309 | 21406298 | aaca | | 000c | mmca | s | ef03 | b1 | plce#xfx | 2012-09-20 11:50:02 | H| a1 and5032t | and5032t | 01a0311 | 21407930 | | | 000c | mmcb | e | ef03 | b1 | plce#xfx | 2012-09-20 11:50:02 | H| a1 and5032t | and5032t | 01a0313 | 21407932 | abca | | 000c | mcud | e | ef03 | b1 | plce#xfx | 2012-09-20 11:50:02 | H| a1 (5 rows) But I still can not find this in the doc. From: BACHELART PIERRE (CIS/SCC) [mailto:[email protected]] Sent: Thursday 20 September 2012 13:01 To: [email protected] Subject: matching a timestamp field Hello, Why is my sql below accepted in 8.1.19 and refused in 8.4.9 ??? Is there something I have missed in the doc ? Welcome to psql 8.1.19, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit ansroc=# select * from s12hwdb where record ~'2012-09-20' limit 5; host | exchange | rit | board | var | lceid | pceid | mnem | eq | rtyp | rv | cetype | record| type | zone --+--+-+--+--+---+---+---++--++--+-+--+-- and5032t | and5032t | 01a0301 | 21122994 | ebjb | | 000c | con3a | e | ef03 | b1 | plce#xfx | 2012-09-20 11:50:02 | H| a1 and5032t | and5032t | 01a0307 | 21406298 | aaca | | 000c | mmca | e | ef03 | b1 | plce#xfx | 2012-09-20 11:50:02 | H| a1 and5032t | and5032t | 01a0309 | 21406298 | aaca | | 000c | mmca | s | ef03 | b1 | plce#xfx | 2012-09-20 11:50:02 | H| a1 and5032t | and5032t | 01a0311 | 21407930 | | | 000c | mmcb | e | ef03 | b1 | plce#xfx | 2012-09-20 11:50:02 | H| a1 and5032t | and5032t | 01a0313 | 21407932 | abca | | 000c | mcud | e | ef03 | b1 | plce#xfx | 2012-09-20 11:50:02 | H| a1 (5 rows) ansroc=# \q psql (8.4.9) Type "help" for help. ansroc=# select * from s12hwdb where record ~'2012-09-20' limit 5; ERROR: operator does not exist: timestamp without time zone ~ unknown LINE 1: select * from s12hwdb where record ~'2012-09-20' limit 5; ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. ansroc=# Pierre. +32 471 68 12 23 * Disclaimer * http://www.belgacom.be/maildisclaimer
[SQL] transactions and ecpg
I'm looking at some code that a coworker ported from Informix to Postgres 9.0. The Informix database did not have transaction logging enabled thus no begins/aborts/commits/rollbacks where in the original code. The way the code has been ported there are still no begin statements in the code, only commit and rollback after data changing statements are executed. Question 1: After a commit/rollback does the next SQL statement implicitly begin a transaction? Question 2: If we're just examining data via SELECT statements should we be issuing COMMIT statements after or are they only required for data modifying operations? Thanks in advance, Wayne -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL]
On Sep 22, 2012, at 20:15, JORGE MALDONADO wrote: > I have the following query: > > SELECT > sem_clave, > to_char(secc_esp_media.sem_fechareg,'TMMon-DD-') as sem_fechareg, > sem_seccion, > sem_titulo, > sem_enca, > tmd_nombre, > tmd_archivo, > tmd_origen, > gen_nombre, > smd_nombre, > prm_urlyoutube, > prm_prmyoutube, > prm_urlsoundcloud, > prm_prmsoundcloud > FROM secc_esp_media > INNER JOIN cat_tit_media ON tmd_clave = sem_titulo > INNER JOIN cat_secc_media ON smd_clave = sem_seccion > INNER JOIN cat_generos ON gen_clave = tmd_genero > INNER JOIN parametros ON 1 = 1 > WHERE > smd_nombre = 'SOMETHING' AND > sem_fipub <= 'SOME DATE' > ORDER BY sem_fipub DESC, sem_ffpub DESC > > I thought it was working fine until I noticed I needed to include a DISTINCT > clause as follows: > > SELECT DISTINCT ON (sem_clave) ..(the rest of the query is exactly the > same as above) > > But, when I run it, I get a message telling me that I need an ORDER BY the > field "sem_clave" which is the field in the DISTINCT clause. How can I solve > this issue without affecting the ORDER BY it already has ? > > Regards, > Jorge Maldonado Since you are forced to include the ON field(s) first in the ORDER BY if you want a different final sort order you will have to use either a sub-select or a CTE/WITH to execute the above query then in the outer/main query you can perform a second sort. David J.
