Re: [SQL] how to escape _ in select
Wes James, 28.07.2010 19:35: I'm trying to do this: select * from table where field::text ilike '%\_%'; but it doesn't work. How do you escape the _ and $ chars? The docs say to use \, but that isn't working. ( http://www.postgresql.org/docs/8.3/static/functions-matching.html ) The text between '%...%' can be longer, I'm just trying to figure out how to escape some things. I've found that ' works with '' and \ works with \\ To get around the somewhat quirky usage of backslashes, you can simply define a different esacpe character: select * from table where field::text ilike '%...@_%' escape '@'; Thomas -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] grouping subsets
Howdy Cristina, unfortunately things are more complicated. I have inserted an excerpt of the real data here: TableID MasterID dtBegin dtEnd idR idL idB consumption 4057312 295530 2010-07-01 00:59:21.077 2010-07-01 01:32:59.670 1726 3212 1428 279 4061043 295574 2010-07-01 01:59:31.137 2010-07-01 02:32:09.373 1726 3212 1428 183 4083397 295838 2010-07-01 07:57:51.327 2010-07-01 08:28:28.117 318 1846 1012 30 4090858 295920 2010-07-01 09:52:33.777 2010-07-01 10:31:34.393 318 1846 1012 487 4094589 295961 2010-07-01 10:47:59.370 2010-07-01 11:32:20.903 318 1846 1012 472 4098330 296013 2010-07-01 11:58:53.890 2010-07-01 12:31:35.730 318 1846 1012 195 4102069 296058 2010-07-01 12:36:19.170 2010-07-01 13:32:13.950 318 1846 1012 338 4105809 296102 2010-07-01 13:58:53.170 2010-07-01 14:02:57.710 318 1846 1012 105 4109555 296150 2010-07-01 14:59:11.663 2010-07-01 15:32:33.810 318 1846 1012 187 4113305 296194 2010-07-01 15:59:01.797 2010-07-01 16:02:27.260 318 1846 1012 108 4117048 296238 2010-07-01 16:20:47.997 2010-07-01 17:32:49.367 318 1846 1012 179 4120791 296282 2010-07-01 17:58:27.657 2010-07-01 18:29:01.733 318 1846 1012 256 4128291 296370 2010-07-01 19:54:17.687 2010-07-01 20:32:53.850 318 1846 1012 239 4132044 296413 2010-07-01 20:31:37.653 2010-07-01 21:29:13.497 318 1846 1012 39 4135797 296458 2010-07-01 21:59:13.983 2010-07-01 22:32:46.503 318 1846 1012 157 4139572 296506 2010-07-01 22:58:49.530 2010-07-01 23:32:22.543 318 1846 1012 218 4142941 296554 2010-07-01 23:59:13.857 2010-07-02 00:32:30.390 318 1846 1012 248 4146289 296598 2010-07-02 00:58:55.763 2010-07-02 01:32:41.983 318 1846 1012 204 4149616 296642 2010-07-02 01:46:57.357 2010-07-02 02:32:56.983 318 1846 1012 42 4152952 296686 2010-07-02 02:55:19.653 2010-07-02 03:32:28.013 318 1846 1012 135 4156289 296730 2010-07-02 03:43:52.777 2010-07-02 04:32:55.250 318 1846 1012 743 4159624 296774 2010-07-02 04:43:15.310 2010-07-02 05:32:44.547 318 1846 1012 277 4162961 296817 2010-07-02 05:58:59.483 2010-07-02 06:32:37.340 318 1846 1012 121 4166303 296862 2010-07-02 06:58:50.733 2010-07-02 07:32:39.113 318 1846 1012 239 4172981 296950 2010-07-02 07:28:55.293 2010-07-02 09:33:01.200 318 1846 1012 512 4176322 296993 2010-07-02 09:59:04.607 2010-07-02 10:33:01.903 318 1846 1012 139 4179667 297038 2010-07-02 10:55:27.760 2010-07-02 11:32:56.560 318 1846 1012 722 4183012 297082 2010-07-02 11:59:33.650 2010-07-02 12:32:14.700318 1846 1012 163 4186351 297126 2010-07-02 12:23:45.997 2010-07-02 13:32:59.500318 1846 1012 284 4189689 297169 2010-07-02 13:44:21.253 2010-07-02 14:18:05.080318 1846 1012 254 4196371 297258 2010-07-02 16:16:19.123 2010-07-02 16:32:53.437 1706 3541 1511 161 4199720 297301 2010-07-02 16:59:35.127 2010-07-02 17:32:57.950 1706 3541 1511 250 4203068 297346 2010-07-02 17:59:34.027 2010-07-02 18:32:54.337 1706 3541 1511 302 4206413 297389 2010-07-02 18:59:28.730 2010-07-02 19:32:37.950 1706 3541 1511 276 4209758 297434 2010-07-02 19:54:00.243 2010-07-02 20:32:57.433 1706 3541 1511 209 4213102 297473 2010-07-02 20:49:10.963 2010-07-02 21:30:44.540 1706 3541 1511 76 4216447 297511 2010-07-02 21:59:34.810 2010-07-02 22:33:00.603 1706 3541 1511 287 4219818 297569 2010-07-02 22:56:52.750
Re: [SQL] grouping subsets
Yes. This is somewhat more complicated because it has more constraints. I've noticed that a given combination doesn't appear with holes on a certain day. For ex, on a daily basis, we have every three key combinations together. We dont have things like 2010-7-01 1726 3212 1428 2010-7-01 1726 3212 1428 ...318 1846 1012 2010-7-01 1726 3212 1428 Can I assume that, for a certain day , the records for the same three combination are all together? There is just one set per day for a given combination? Or is it possible to have the same combination on one day with several sets? Best, Oliveiros - Original Message - From: "Rainer Stengele" Newsgroups: gmane.comp.db.postgresql.sql To: "Oliveiros d'Azevedo Cristina" Cc: <> Sent: Thursday, July 29, 2010 10:41 AM Subject: Re: grouping subsets Howdy Cristina, unfortunately things are more complicated. I have inserted an excerpt of the real data here: TableID MasterID dtBegin dtEnd idR idL idB consumption 4057312 295530 2010-07-01 00:59:21.077 2010-07-01 01:32:59.670 1726 3212 1428 279 4061043 295574 2010-07-01 01:59:31.137 2010-07-01 02:32:09.373 1726 3212 1428 183 4083397 295838 2010-07-01 07:57:51.327 2010-07-01 08:28:28.117 318 1846 1012 30 4090858 295920 2010-07-01 09:52:33.777 2010-07-01 10:31:34.393 318 1846 1012 487 4094589 295961 2010-07-01 10:47:59.370 2010-07-01 11:32:20.903 318 1846 1012 472 4098330 296013 2010-07-01 11:58:53.890 2010-07-01 12:31:35.730 318 1846 1012 195 4102069 296058 2010-07-01 12:36:19.170 2010-07-01 13:32:13.950 318 1846 1012 338 4105809 296102 2010-07-01 13:58:53.170 2010-07-01 14:02:57.710 318 1846 1012 105 4109555 296150 2010-07-01 14:59:11.663 2010-07-01 15:32:33.810 318 1846 1012 187 4113305 296194 2010-07-01 15:59:01.797 2010-07-01 16:02:27.260 318 1846 1012 108 4117048 296238 2010-07-01 16:20:47.997 2010-07-01 17:32:49.367 318 1846 1012 179 4120791 296282 2010-07-01 17:58:27.657 2010-07-01 18:29:01.733 318 1846 1012 256 4128291 296370 2010-07-01 19:54:17.687 2010-07-01 20:32:53.850 318 1846 1012 239 4132044 296413 2010-07-01 20:31:37.653 2010-07-01 21:29:13.497 318 1846 1012 39 4135797 296458 2010-07-01 21:59:13.983 2010-07-01 22:32:46.503 318 1846 1012 157 4139572 296506 2010-07-01 22:58:49.530 2010-07-01 23:32:22.543 318 1846 1012 218 4142941 296554 2010-07-01 23:59:13.857 2010-07-02 00:32:30.390 318 1846 1012 248 4146289 296598 2010-07-02 00:58:55.763 2010-07-02 01:32:41.983 318 1846 1012 204 4149616 296642 2010-07-02 01:46:57.357 2010-07-02 02:32:56.983 318 1846 1012 42 4152952 296686 2010-07-02 02:55:19.653 2010-07-02 03:32:28.013 318 1846 1012 135 4156289 296730 2010-07-02 03:43:52.777 2010-07-02 04:32:55.250 318 1846 1012 743 4159624 296774 2010-07-02 04:43:15.310 2010-07-02 05:32:44.547 318 1846 1012 277 4162961 296817 2010-07-02 05:58:59.483 2010-07-02 06:32:37.340 318 1846 1012 121 4166303 296862 2010-07-02 06:58:50.733 2010-07-02 07:32:39.113 318 1846 1012 239 4172981 296950 2010-07-02 07:28:55.293 2010-07-02 09:33:01.200 318 1846 1012 512 4176322 296993 2010-07-02 09:59:04.607 2010-07-02 10:33:01.903 318 1846 1012 139 4179667 297038 2010-07-02 10:55:27.760 2010-07-02 11:32:56.560 318 1846 1012 722 4183012 297082 2010-07-02 11:59:33.650 2010-07-02 12:32:14.700318 1846 1012 163 4186351 297126 2010-07-02 12:23:45.997 2010-07-02 13:32:59.500318 1846 1012 284 4189689 297169 2010-07-02 13:44:21.253 2010-07-02 14:18:05.080318 1846 1012 254 4196371 297258 2010-07-02 16:16:19.123 2010-07-02 16:32:53.437 1706 3541 1
Re: [SQL] grouping subsets
No. This is by accident. We have to assume that the combinations do change anytime, and many times per day. So "Or is it possible to have the same combination on one day with several sets?" YES! Rainer Am 29.07.2010 13:47, schrieb Oliveiros d'Azevedo Cristina: > Yes. This is somewhat more complicated because it has more constraints. > I've noticed that a given combination doesn't appear with holes on a certain > day. > > For ex, on a daily basis, we have every three key combinations together. > > We dont have things like > 2010-7-01 1726 3212 1428 > 2010-7-01 1726 3212 1428 > ...318 1846 1012 > 2010-7-01 1726 3212 1428 > > Can I assume that, for a certain day , the records for the same three > combination are all together? There is just one set per day for a given > combination? > > Or is it possible to have the same combination on one day with several sets? > > Best, > Oliveiros > > > - Original Message - From: "Rainer Stengele" > > Newsgroups: gmane.comp.db.postgresql.sql > To: "Oliveiros d'Azevedo Cristina" > Cc: <> > Sent: Thursday, July 29, 2010 10:41 AM > Subject: Re: grouping subsets > > >> Howdy Cristina, >> >> unfortunately things are more complicated. I have inserted an excerpt of the >> real data here: >> >> >> TableID MasterID dtBegin dtEnd idR >> idL idB consumption >> 4057312 295530 2010-07-01 00:59:21.077 2010-07-01 >> 01:32:59.670 1726 3212 1428 279 >> 4061043 295574 2010-07-01 01:59:31.137 2010-07-01 >> 02:32:09.373 1726 3212 1428 183 >> 4083397 295838 2010-07-01 07:57:51.327 2010-07-01 >> 08:28:28.117 318 1846 1012 30 >> 4090858 295920 2010-07-01 09:52:33.777 2010-07-01 >> 10:31:34.393 318 1846 1012 487 >> 4094589 295961 2010-07-01 10:47:59.370 2010-07-01 >> 11:32:20.903 318 1846 1012 472 >> 4098330 296013 2010-07-01 11:58:53.890 2010-07-01 >> 12:31:35.730 318 1846 1012 195 >> 4102069 296058 2010-07-01 12:36:19.170 2010-07-01 >> 13:32:13.950 318 1846 1012 338 >> 4105809 296102 2010-07-01 13:58:53.170 2010-07-01 >> 14:02:57.710 318 1846 1012 105 >> 4109555 296150 2010-07-01 14:59:11.663 2010-07-01 >> 15:32:33.810 318 1846 1012 187 >> 4113305 296194 2010-07-01 15:59:01.797 2010-07-01 >> 16:02:27.260 318 1846 1012 108 >> 4117048 296238 2010-07-01 16:20:47.997 2010-07-01 >> 17:32:49.367 318 1846 1012 179 >> 4120791 296282 2010-07-01 17:58:27.657 2010-07-01 >> 18:29:01.733 318 1846 1012 256 >> 4128291 296370 2010-07-01 19:54:17.687 2010-07-01 >> 20:32:53.850 318 1846 1012 239 >> 4132044 296413 2010-07-01 20:31:37.653 2010-07-01 >> 21:29:13.497 318 1846 1012 39 >> 4135797 296458 2010-07-01 21:59:13.983 2010-07-01 >> 22:32:46.503 318 1846 1012 157 >> 4139572 296506 2010-07-01 22:58:49.530 2010-07-01 >> 23:32:22.543 318 1846 1012 218 >> 4142941 296554 2010-07-01 23:59:13.857 2010-07-02 >> 00:32:30.390 318 1846 1012 248 >> 4146289 296598 2010-07-02 00:58:55.763 2010-07-02 >> 01:32:41.983 318 1846 1012 204 >> 4149616 296642 2010-07-02 01:46:57.357 2010-07-02 >> 02:32:56.983 318 1846 1012 42 >> 4152952 296686 2010-07-02 02:55:19.653 2010-07-02 >> 03:32:28.013 318 1846 1012 135 >> 4156289 296730 2010-07-02 03:43:52.777 2010-07-02 >> 04:32:55.250 318 1846 1012 743 >> 4159624 296774 2010-07-02 04:43:15.310 2010-07-02 >> 05:32:44.547 318 1846 1012 277 >> 4162961 296817 2010-07-02 05:58:59.483 2010-07-02 >> 06:32:37.340 318 1846 1012 121 >> 4166303 296862 2010-07-02 06:58:50.733 2010-07-02 >> 07:32:39.113 318 1846 1012 239 >> 4172981 296950 2010-07-02 07:28:55.293 2010-07-02 >> 09:33:01.200 318 1846 1012 512 >> 4176322 296993 2010-07-02 09:59:04.607 2010-07-02 >> 10:33:01.903 318 1846 1012 139 >> 4179667 297038 2010-07-02 10:55:27.760 2010-07-02 >> 11:32:56.560 318 1846 101
[SQL] How to get geometry enabled Tables form Postgresql/postgis
Dear All, How can i retrieve only spatial enabled tables form the database(Postgresql/PostGIS).Please let me know. I am waiting for your great response. Thanks and Regards, Venkat
Re: [SQL] grouping subsets
Fine. Please advice me, How long can your table be? Thousands? Millions of records? Do you really need it in pure SQL ? It seems to me that it might be possible, I'm just affraid that the query would become too complex and thus slow... Best, Oliveiros - Original Message - From: "Rainer Stengele" To: "Oliveiros d'Azevedo Cristina" Cc: Sent: Thursday, July 29, 2010 1:10 PM Subject: Re: grouping subsets No. This is by accident. We have to assume that the combinations do change anytime, and many times per day. So "Or is it possible to have the same combination on one day with several sets?" YES! Rainer Am 29.07.2010 13:47, schrieb Oliveiros d'Azevedo Cristina: Yes. This is somewhat more complicated because it has more constraints. I've noticed that a given combination doesn't appear with holes on a certain day. For ex, on a daily basis, we have every three key combinations together. We dont have things like 2010-7-01 1726 3212 1428 2010-7-01 1726 3212 1428 ...318 1846 1012 2010-7-01 1726 3212 1428 Can I assume that, for a certain day , the records for the same three combination are all together? There is just one set per day for a given combination? Or is it possible to have the same combination on one day with several sets? Best, Oliveiros - Original Message - From: "Rainer Stengele" Newsgroups: gmane.comp.db.postgresql.sql To: "Oliveiros d'Azevedo Cristina" Cc: <> Sent: Thursday, July 29, 2010 10:41 AM Subject: Re: grouping subsets Howdy Cristina, unfortunately things are more complicated. I have inserted an excerpt of the real data here: TableID MasterID dtBegin dtEnd idR idL idB consumption 4057312 295530 2010-07-01 00:59:21.077 2010-07-01 01:32:59.670 1726 3212 1428 279 4061043 295574 2010-07-01 01:59:31.137 2010-07-01 02:32:09.373 1726 3212 1428 183 4083397 295838 2010-07-01 07:57:51.327 2010-07-01 08:28:28.117 318 1846 1012 30 4090858 295920 2010-07-01 09:52:33.777 2010-07-01 10:31:34.393 318 1846 1012 487 4094589 295961 2010-07-01 10:47:59.370 2010-07-01 11:32:20.903 318 1846 1012 472 4098330 296013 2010-07-01 11:58:53.890 2010-07-01 12:31:35.730 318 1846 1012 195 4102069 296058 2010-07-01 12:36:19.170 2010-07-01 13:32:13.950 318 1846 1012 338 4105809 296102 2010-07-01 13:58:53.170 2010-07-01 14:02:57.710 318 1846 1012 105 4109555 296150 2010-07-01 14:59:11.663 2010-07-01 15:32:33.810 318 1846 1012 187 4113305 296194 2010-07-01 15:59:01.797 2010-07-01 16:02:27.260 318 1846 1012 108 4117048 296238 2010-07-01 16:20:47.997 2010-07-01 17:32:49.367 318 1846 1012 179 4120791 296282 2010-07-01 17:58:27.657 2010-07-01 18:29:01.733 318 1846 1012 256 4128291 296370 2010-07-01 19:54:17.687 2010-07-01 20:32:53.850 318 1846 1012 239 4132044 296413 2010-07-01 20:31:37.653 2010-07-01 21:29:13.497 318 1846 1012 39 4135797 296458 2010-07-01 21:59:13.983 2010-07-01 22:32:46.503 318 1846 1012 157 4139572 296506 2010-07-01 22:58:49.530 2010-07-01 23:32:22.543 318 1846 1012 218 4142941 296554 2010-07-01 23:59:13.857 2010-07-02 00:32:30.390 318 1846 1012 248 4146289 296598 2010-07-02 00:58:55.763 2010-07-02 01:32:41.983 318 1846 1012 204 4149616 296642 2010-07-02 01:46:57.357 2010-07-02 02:32:56.983 318 1846 1012 42 4152952 296686 2010-07-02 02:55:19.653 2010-07-02 03:32:28.013 318 1846 1012 135 4156289 296730 2010-07-02 03:43:52.777 2010-07-02 04:32:55.250 318 1846 1012 743 4159624 296774 2010-07-02 04:43:15.310 2010-07-02 05:32:44.547 318 1846 1012 277 4162961 296817 2010-07-02 05:58:59.483 2010-07-02 06:32:37.340 318 1846 1012 121 4166303 296862 2010-07-02 06:58:50.733 2010-07-02 07:32:39.113 318 1846 1012 239 4172981 296950 2010-07-02 07:28:55.293 2010-07-02 09:33:01.200 318 1846 1012 512 4176322 296993 2010-07-02 09:59:0
Re: [SQL] How to get geometry enabled Tables form Postgresql/postgis
All geometric columns in all spatially enabled tables appear in the table public.geometry_columns, defined as: CREATE TABLE public.geometry_columns ( f_table_catalog character varying(256) NOT NULL, f_table_schema character varying(256) NOT NULL, f_table_name character varying(256) NOT NULL, f_geometry_column character varying(256) NOT NULL, coord_dimension integer NOT NULL, srid integer NOT NULL, "type" character varying(30) NOT NULL, CONSTRAINT geometry_columns_pk PRIMARY KEY (f_table_catalog, f_table_schema, f_table_name, f_geometry_column) ) WITH ( OIDS=TRUE ); For tables only, a simple SELECT DISTINCT f_table_schema, f_table_name FROM geometry_columns; should do the trick. --Lee On 07/29/2010 08:58 AM, venkat wrote: Dear All, How can i retrieve only spatial enabled tables form the database(Postgresql/PostGIS).Please let me know. I am waiting for your great response. Thanks and Regards, Venkat -- Lee Hachadoorian PhD Student, Geography Program in Earth & Environmental Sciences CUNY Graduate Center
Re: [SQL] How to get geometry enabled Tables form Postgresql/postgis
Dear Lee, Perfect Its working fine Thanks alot.. Thanks and Regards, Venkat On Fri, Jul 30, 2010 at 1:43 AM, Lee Hachadoorian < lee.hachadoor...@gmail.com> wrote: > All geometric columns in all spatially enabled tables appear in the table > public.geometry_columns, defined as: > > CREATE TABLE public.geometry_columns > > ( > > f_table_catalog character varying(256) NOT NULL, > > f_table_schema character varying(256) NOT NULL, > > f_table_name character varying(256) NOT NULL, > > f_geometry_column character varying(256) NOT NULL, > > coord_dimension integer NOT NULL, > > srid integer NOT NULL, > > "type" character varying(30) NOT NULL, > > CONSTRAINT geometry_columns_pk PRIMARY KEY (f_table_catalog, > f_table_schema, f_table_name, f_geometry_column) > > ) > > WITH ( > > OIDS=TRUE > > ); > > > For tables only, a simple > > SELECT DISTINCT f_table_schema, f_table_name FROM geometry_columns; > > should do the trick. > > --Lee > > > On 07/29/2010 08:58 AM, venkat wrote: > > Dear All, > > > How can i retrieve only spatial enabled tables form the > database(Postgresql/PostGIS).Please let me know. > > I am waiting for your great response. > > Thanks and Regards, > > Venkat > > > -- > Lee Hachadoorian > PhD Student, Geography > Program in Earth & Environmental Sciences > CUNY Graduate Center > >
