[SQL] Determine length of numeric field
I'm altering datatypes in several tables from numeric to integer. In doing so, I get the following error: dw=# \d uniq_hits Table "support.uniq_hits" Column | Type | Modifiers +-+--- sourceid | numeric | hitdate| date| total | numeric | hitdate_id | integer | Indexes: "uniq_hits_hitdateid_idx" btree (hitdate_id), tablespace "support_idx" Tablespace: "support" esave_dw=# alter table uniq_hits alter sourceid type int; ERROR: integer out of range Sourceid should not be more than 5 digits long. I'm able to perform this query on Oracle and would like something similar on postgres 8.4: delete from uniq_hits where sourceid in (select sourceid from uniq_hits where length(sourceid) > 5); I haven't had much luck with the length or char_length functions on postgres. Thanks. Tony -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Determine length of numeric field
Hello probably you have to use a explicit cast postgres=# select length(10::numeric::text); length 2 (1 row) Regards Pavel Stehule 2011/2/15 Tony Capobianco : > I'm altering datatypes in several tables from numeric to integer. In > doing so, I get the following error: > > dw=# \d uniq_hits > Table "support.uniq_hits" > Column | Type | Modifiers > +-+--- > sourceid | numeric | > hitdate | date | > total | numeric | > hitdate_id | integer | > Indexes: > "uniq_hits_hitdateid_idx" btree (hitdate_id), tablespace > "support_idx" > Tablespace: "support" > > esave_dw=# alter table uniq_hits alter sourceid type int; > ERROR: integer out of range > > Sourceid should not be more than 5 digits long. I'm able to perform > this query on Oracle and would like something similar on postgres 8.4: > > delete from uniq_hits where sourceid in (select sourceid from uniq_hits > where length(sourceid) > 5); > > I haven't had much luck with the length or char_length functions on > postgres. > > Thanks. > Tony > > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Determine length of numeric field
In Oracle you never have to cast, in Postgres it's pretty common. See the doc on String Operators and Functions also: http://www.postgresql.org/docs/8.4/interactive/functions-string.html On Tue, Feb 15, 2011 at 3:48 PM, Tony Capobianco wrote: > I'm altering datatypes in several tables from numeric to integer. In > doing so, I get the following error: > > dw=# \d uniq_hits >Table "support.uniq_hits" > Column | Type | Modifiers > +-+--- > sourceid | numeric | > hitdate| date| > total | numeric | > hitdate_id | integer | > Indexes: >"uniq_hits_hitdateid_idx" btree (hitdate_id), tablespace > "support_idx" > Tablespace: "support" > > esave_dw=# alter table uniq_hits alter sourceid type int; > ERROR: integer out of range > > Sourceid should not be more than 5 digits long. I'm able to perform > this query on Oracle and would like something similar on postgres 8.4: > > delete from uniq_hits where sourceid in (select sourceid from uniq_hits > where length(sourceid) > 5); > > I haven't had much luck with the length or char_length functions on > postgres. > > Thanks. > Tony > > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Peter Steinheuser [email protected]
Re: [SQL] Determine length of numeric field
Pavel, That's perfect! Thanks. Tony On Tue, 2011-02-15 at 22:04 +0100, Pavel Stehule wrote: > Hello > > probably you have to use a explicit cast > > postgres=# select length(10::numeric::text); > length > > 2 > (1 row) > > Regards > > Pavel Stehule > > 2011/2/15 Tony Capobianco : > > I'm altering datatypes in several tables from numeric to integer. In > > doing so, I get the following error: > > > > dw=# \d uniq_hits > >Table "support.uniq_hits" > > Column | Type | Modifiers > > +-+--- > > sourceid | numeric | > > hitdate| date| > > total | numeric | > > hitdate_id | integer | > > Indexes: > >"uniq_hits_hitdateid_idx" btree (hitdate_id), tablespace > > "support_idx" > > Tablespace: "support" > > > > esave_dw=# alter table uniq_hits alter sourceid type int; > > ERROR: integer out of range > > > > Sourceid should not be more than 5 digits long. I'm able to perform > > this query on Oracle and would like something similar on postgres 8.4: > > > > delete from uniq_hits where sourceid in (select sourceid from uniq_hits > > where length(sourceid) > 5); > > > > I haven't had much luck with the length or char_length functions on > > postgres. > > > > Thanks. > > Tony > > > > > > -- > > Sent via pgsql-sql mailing list ([email protected]) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-sql > > > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Determine length of numeric field
Tony Capobianco writes: > I'm altering datatypes in several tables from numeric to integer. In > doing so, I get the following error: > dw=# \d uniq_hits > Table "support.uniq_hits" >Column | Type | Modifiers > +-+--- > sourceid | numeric | > hitdate| date| > total | numeric | > hitdate_id | integer | > Indexes: > "uniq_hits_hitdateid_idx" btree (hitdate_id), tablespace > "support_idx" > Tablespace: "support" > esave_dw=# alter table uniq_hits alter sourceid type int; > ERROR: integer out of range > Sourceid should not be more than 5 digits long. I'm able to perform > this query on Oracle and would like something similar on postgres 8.4: > delete from uniq_hits where sourceid in (select sourceid from uniq_hits > where length(sourceid) > 5); That seems like a pretty bizarre operation to apply to a number. Why not "where sourceid > 9"? Or maybe "where abs(sourceid) > 9" would be better. regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Determine length of numeric field
Tom, That's a frighteningly easy solution. Thanks. Tony On Tue, 2011-02-15 at 16:10 -0500, Tom Lane wrote: > Tony Capobianco writes: > > I'm altering datatypes in several tables from numeric to integer. In > > doing so, I get the following error: > > > dw=# \d uniq_hits > > Table "support.uniq_hits" > >Column | Type | Modifiers > > +-+--- > > sourceid | numeric | > > hitdate| date| > > total | numeric | > > hitdate_id | integer | > > Indexes: > > "uniq_hits_hitdateid_idx" btree (hitdate_id), tablespace > > "support_idx" > > Tablespace: "support" > > > esave_dw=# alter table uniq_hits alter sourceid type int; > > ERROR: integer out of range > > > Sourceid should not be more than 5 digits long. I'm able to perform > > this query on Oracle and would like something similar on postgres 8.4: > > > delete from uniq_hits where sourceid in (select sourceid from uniq_hits > > where length(sourceid) > 5); > > That seems like a pretty bizarre operation to apply to a number. Why > not "where sourceid > 9"? Or maybe "where abs(sourceid) > 9" > would be better. > > regards, tom lane > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] recommended data model for a search?
We have an application that manages RSS feeds for searches. Our first attempt at modeling this was to create a table for the searches with the standard integer primary key generated from a sequence, and then have one column per search key. Each row should represent a unique search. When a new search comes we need to look up the key/value pairs received and find the unique search ID that corresponds to it. ( IE, we need to know whether we have an "insert" case or an "update" case, without the benefit of being provided the primary key for the update case. ) The problem with this design is that there are 13 potential search terms, and the "unique" index we would like to create to avoid duplicates and speed-up searches would necessarily span all 13 columns. And further complicating the matter is the handling of nulls, which need to be considered as part of the unique signature of a search, but are not easily indexed that way. Having gotten this far, I'm taking a step back to consider if there's a better way to design this. Perhaps there's a standard solution for this, but I'm not sure where to find it. Here's one idea I've had for a refined design: Each search can be represented as a unique, sorted query string. It would be easy for the application to compute this and then make an MD5-hash of it (or similar). The MD5-hash would then be used as the unique key instead of a standard integer. This would then be easily indexable for quick look-ups, and would allow us to create a unique index that doesn't have a problem with null values, too. Is this a reasonable approach, or is there a better design I'm overlooking? Thanks, Mark -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
