Re: [SQL] enum data type vs table
On Tue, May 17, 2011 at 11:23 PM, Seb splu...@gmail.com wrote: Are there any guidelines for deciding whether to 1) create an enum data type or 2) create a table with the set of values and then have foreign keys referencing this table? Some fields in a database take a small number of values, and I'm not sure which of these routes to take. The enum data type seems like a clean way to handle this without creating a constellation of tables for all these values, but if one wants to add a new label to the enum or make changes to it at some point, then the tables using it have to be recreated, so it's quite rigid. Have I got this right? Thanks. I think your choice depends on a few things: 1 - How do you want to interact with the tables? What I mean is, are you planning on querying, inserting, or updating data to those tables via text or will you need to join to your reference table? If you don't want to join, you'll either need to use enum types, use views (which can be a pain if you want to update a view), or duplicate/reference the text directly (which is slow and a bad idea for several reasons). 2 - How much can you tolerate downtime or a busy database? Changing types is a single transaction and requires an exclusive lock. On small tables this is negligible, but on big tables it can require downtime. 3 - How often do you really expect changes to the enum type? If adding a new value to an enum type is truly a rare event, it's . If it's frequent or regular, you should probably have a table. I've used both of these approaches and I've found enum types to be well worth any trouble to drop/recreate types. The changes I've made have been rare, and I've been able to schedule downtime pretty easily, so it made the most sense for me. Also, Postgres 9.1 allows adding values to enum types, so you could always use that when it is finally released. Hope this helps, Cheers, Peter -- 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] Domains, casts, and MS Access
On Wed, Aug 4, 2010 at 8:49 PM, Richard Broersma richard.broer...@gmail.com wrote: On Wed, Aug 4, 2010 at 3:41 PM, Peter Koczan pjkoc...@gmail.com wrote: Yep, that's the stumbling block we're running into. ODBC and these fields' assumptions of true/false are at odds. I'm trying a few other things with casts in the meantime to see if they'll work. Well there is a solution that I've been toying around with. In PostgreSQL, there are many data-types that cannot be expressed directly in an MS-Access Linked table. For example, composite types, arrays, range types, hstores, postgis types et.al. However, most of these types can be decomposed in to base types that can be express in linked tables. The key is using update-able views to decompose the data for Access and re-assemble it before it transmitted back to the base table. The same can be done for boolean datatype. Does anyone know if another product, like OpenOffice Base with its native postgres driver, does any better? From my limited experience, I believe is does do better. The following blogs as a few entries about using Base: http://www.postgresonline.com/journal/archives/167-Importing-data-into-PostgreSQL-using-Open-Office-Base-3.2.html The goal of this is to be as straight a port as possible (as part of a larger project that's a pretty straight port). The update-able views and using Open Office are good ideas for when we finally get around to redesigning the database, whenever that will happen. Anyway, we got this mostly working. There were a couple other quirks we found that we have since fixed. - Access does not like LongVarChar types to be primary keys. If you are keying on text types, set TextAsLongVarchar=0. It's probably not the best idea to have text as a primary key in general, but sometimes that's what the legacy gives you to work with. - To fix the incompatibility in the bit/boolean type, we mapped the drop-down menu input to have Yes == 1 instead of -1. We had to do that for each input. It was tedious, but workable. Again, this was necessary because of legacy and the other workarounds we put in to account for it. Thanks for all your help. Peter -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Domains, casts, and MS Access
Hi all, I'm working on porting an old MS Access form application from Sybase to postgres/ODBC as part of a larger database port project. One of the snags that's popped up is that there's some incompatibility between data types. Specifically, many fields are the Sybase type bit, which is basically a boolean, but it accepts and displays bareword 1 and 0 instead of 't' and 'f'. For the sake of compatibility (especially bareword integers in queries), I've defined a 'sybit' type in postgres to be a domain. = create domain sybit as smallint check ( value in (0,1) ); That is compatible behavior for most applications, but Access gets confused since it wants to map it to an integer instead of a boolean (it does the right thing for a native Sybase driver). I thought that creating casts between sybit and boolean might help, but that won't work so much it seems. =# create cast (sybit as smallint) without function as implicit; CREATE CAST =# create cast (sybit as integer) with function int4(smallint) as implicit; CREATE CAST =# create cast (sybit as boolean) with function bool(integer) as assignment; ERROR: argument of cast function must match or be binary-coercible from source data type Is there a way to tell Access to do the right thing, or is there a better way to define the type/domain, or is there some better product to use? Thanks much, Peter P.S. In case people are interested in the specifics of the sybase bit type, you can look at http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.blocks/html/blocks/blocks54.htm. -- 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] Domains, casts, and MS Access
On Wed, Aug 4, 2010 at 12:47 PM, Richard Broersma richard.broer...@gmail.com wrote: On Wed, Aug 4, 2010 at 10:31 AM, Peter Koczan pjkoc...@gmail.com wrote: One of the snags that's popped up is that there's some incompatibility between data types. Specifically, many fields are the Sybase type bit, which is basically a boolean, but it accepts and displays bareword 1 and 0 instead of 't' and 'f'. For the sake of compatibility (especially bareword integers in queries), I've defined a 'sybit' type in postgres to be a domain. One thought would be see if ODBC configuration options will achieve this for you. Have you already exhausted this option? This is one of my first forays into ODBC, so I didn't know that was a possibility. Is there any place where these are documented? Searching for ODBC options yields info on connection options, but none on behavior that I could find. Peter -- 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] Domains, casts, and MS Access
On Wed, Aug 4, 2010 at 4:40 PM, Richard Broersma richard.broer...@gmail.com wrote: On Wed, Aug 4, 2010 at 1:24 PM, Justin Graf jus...@magwerks.com wrote: My memory is fuzzy but there are some additional settings in Access that allows data type mapping... My experience is that PostgreSQL Integer types work the best for MS-Access bit datatype considering the fact that in Access -1 = true. I know that there is a setting in the ODBC driver for true = -1 but it doesn't work well. I also remember that filters didn't work on mapped boolean columns. Yep, that's the stumbling block we're running into. ODBC and these fields' assumptions of true/false are at odds. I'm trying a few other things with casts in the meantime to see if they'll work. Does anyone know if another product, like OpenOffice Base with its native postgres driver, does any better? Peter -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] tsearch2 and wildcards/pattern matching?
Hi all, Is there any sort of pattern matching or partial matching capability in tsearch2? I'd like to be able search for a common prefix (like for order numbers). For instance, tsearch2 will index 'zvt123456' or 'zvt55', but I'd like to be able to find both of them in one query using something akin to 'zvt%', like select * from attachments where textsearchable @@ 'zvt%'; I've been unable to find anything in documentation. If there's anything in tsearch2 that can do this and is faster than LIKE clauses on full-text, that would be fantastic. Cheers, Peter -- 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] tsearch2 and wildcards/pattern matching?
On Wed, Jul 7, 2010 at 5:44 PM, Oleg Bartunov o...@sai.msu.su wrote: it's called prefix search: =# select 'zvt123456'::tsvector @@ 'zvt:*'; ?column? -- t Ah, that works. Excellent. Thanks, Peter -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] ascii-betical sort order?
Is there some way to do ascii-betical sort ordering in postgres (i.e. capital letters come before lowercase)? It appears that text ordering is dictionary-alphabetical. It's useful, but it's different from the DBMS I'm porting some applications from (SQL Server, in case you were curious). It may not be terribly important, but it'd be useful to know in case it actually is an issue. I couldn't find any clear answer searching online. Thanks, Peter -- 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] ascii-betical sort order?
On Fri, May 8, 2009 at 12:57 PM, Tom Lane t...@sss.pgh.pa.us wrote: You're apparently using a non-C locale ... you need to re-initdb in C locale. On Fri, May 8, 2009 at 12:58 PM, Kenneth Marshall k...@rice.edu wrote: Try looking under Localization in the manual (Chapter 22). Excellent, just what I was looking for. I'll look at this when I have a bit more time. Thanks, Peter -- 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] pl/pgsql or control structures outside of a function?
On Fri, Apr 3, 2009 at 11:28 PM, John DeSoi de...@pgedit.com wrote: Is there any way to use PL/pgSQL code outside of a function? No. I kinda figured, but it doesn't hurt to ask. The reason I'm asking is that I'm porting some code from sybase/isql/SQR, and it allows some control code structures to be used in an sql script. For instance, CASE might work for you. http://www.postgresql.org/docs/8.3/interactive/functions-conditional.html Thanks for the suggestion. I think that for what I'm trying to do, adding a bunch of case statements would very quickly become unmaintainable. Wrapper scripts are probably the way to go for me. Thanks again, Peter -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] pl/pgsql or control structures outside of a function?
Hi all,
Is there any way to use PL/pgSQL code outside of a function?
The reason I'm asking is that I'm porting some code from
sybase/isql/SQR, and it allows some control code structures to be used
in an sql script. For instance,
begin
if ((select count(*) from users where login = 'foo') = 0)
begin
print 'Login foo does not exist.'
end
else
begin
print 'Adding account for foo.'
insert into accounts values ('foo', 'bar')
end
end
PL/pgSQL looks like it would make this port rather easy, but all the
docs and examples I found never had an example of PL/pgSQL outside of
a function. For the purposes of this port I'd really prefer not to
create functions for all this. I searched through the PL/pgSQL docs
and even several Google searches but couldn't find a definitive
answer. It's fine if the answer is no, I'm just curious if I should
pursue this path or look for a different one. And if there's a way to
do this or something like it besides create scripts in
Perl/Python/etc. that you know of, I'd appreciate any information.
Thanks,
Peter
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] New datestyle(s)
Anyway, this is in response to a really old request of mine to easily
and automatically get a datestyle for a different DBMS as part of an
ongoing port process
(http://archives.postgresql.org/pgsql-sql/2008-05/msg00048.php).
I patched this a while ago and I finally got a chance to test it.
Overall, it was pretty easy once I figured out how existing datestyles
were coded (lots of grep'ing was involved). So, I figured I'd share
what I did and also share the source patch in case anyone would either
like a Sybase/SQL Server datestyle or a model on how to add your own
custom datestyles.
You need to edit the following files:
src/backend/commands/variable.c - Add in your new datestyle to the
list to allow it to be a valid option for SET datestyle TO x
src/backend/utils/adt/datetime.c - Define your output format. A little
hack-ish but pretty straightforward overall.
src/include/miscadmin.h - Define your new datestyle.
src/bin/psql/tab-complete.c - Not necessary to add it, but having it
in the list of tab completions for datestyle is nice.
I don't use the ecpg interface, so I didn't bother patching that. It
seems like it would be analogous to what's been done already.
Peter
Index: src/backend/commands/variable.c
===
RCS file:
/s/postgresql-8.3.3/src/CVSROOT/postgresql-8.3.3/src/backend/commands/variable.c,v
retrieving revision 1.1
retrieving revision 1.2
diff -u -r1.1 -r1.2
--- src/backend/commands/variable.c 3 Sep 2008 18:30:21 - 1.1
+++ src/backend/commands/variable.c 3 Sep 2008 18:52:50 - 1.2
@@ -100,6 +100,13 @@
if (!have_order)
newDateOrder = DATEORDER_DMY;
}
+ else if (pg_strcasecmp(tok, SYBASE) == 0)
+ {
+ if (have_style newDateStyle != USE_SYBASE_DATES)
+ ok = false; /* conflicting styles */
+ newDateStyle = USE_SYBASE_DATES;
+ have_style = true;
+ }
else if (pg_strcasecmp(tok, YMD) == 0)
{
if (have_order newDateOrder != DATEORDER_YMD)
@@ -200,6 +207,9 @@
case USE_GERMAN_DATES:
strcpy(result, German);
break;
+ case USE_SYBASE_DATES:
+ strcpy(result, Sybase);
+ break;
default:
strcpy(result, Postgres);
break;
Index: src/backend/utils/adt/datetime.c
===
RCS file:
/s/postgresql-8.3.3/src/CVSROOT/postgresql-8.3.3/src/backend/utils/adt/datetime.c,v
retrieving revision 1.1
retrieving revision 1.2
diff -u -r1.1 -r1.2
--- src/backend/utils/adt/datetime.c3 Sep 2008 18:30:26 - 1.1
+++ src/backend/utils/adt/datetime.c3 Sep 2008 18:53:29 - 1.2
@@ -3233,6 +3233,15 @@
sprintf(str + 5, .%04d %s, -(tm-tm_year -
1), BC);
break;
+ case USE_SYBASE_DATES:
+ /* Sybase date format */
+ sprintf(str, %s %2d, months[tm-tm_mon - 1],
tm-tm_mday);
+ if (tm-tm_year 0)
+ sprintf(str + 6, %04d, tm-tm_year);
+ else
+ sprintf(str + 6, %04d %s, -(tm-tm_year -
1), BC);
+ break;
+
case USE_POSTGRES_DATES:
default:
/* traditional date-only style for Postgres */
@@ -3302,6 +3311,8 @@
EncodeDateTime(struct pg_tm * tm, fsec_t fsec, int *tzp, char **tzn,
int style, char *str)
{
int day;
+ boolmeridian;
+ int temp_hour;
/*
* Why are we checking only the month field? Change this to an assert...
@@ -3452,6 +3463,32 @@
sprintf(str + strlen(str), BC);
break;
+ case USE_SYBASE_DATES:
+ /* Sybase date format */
+ meridian = true; // true = AM, false = PM
+ temp_hour = tm-tm_hour;
+
+ if (temp_hour 12)
+ {
+ meridian = true;
+ if (temp_hour == 0) temp_hour = 12;
+ }
+ else
+ {
+ meridian = false;
+ if (temp_hour 12) temp_hour -= 12;
+ }
+
+ sprintf(str, %s %2d %04d %2d:%02d%s,
+ months[tm-tm_mon - 1],
tm-tm_mday,
+
[SQL] Auto-formatting timestamps?
Hi all, I'm undergoing a port from an old Sybase database to Postgres. It's going surprisingly well, but I have a question regarding formatting of timestamps. In Sybase, we get: : select date from checkoutdate; date 'May 1 2001 12:00AM' ... In Postgres: = select date from checkoutdate; date - 2001-05-01 00:00:00 ... I can properly format it using to_char: = select to_char(date, 'Mon DD HH:MIAM') as date from checkoutdate; date - May 01 2001 12:00AM ... Short of creating a wrapper type for timestamp (which seems like overkill just for modifying the output function), is there a way to output the Sybase format automatically (i.e. without a call to to_char)? I've found some code that actually somewhat depends on this format, and one of my goals in this port is to change as little client code as possible. Is it possible to automatically change the output like this, preferably on a per-connection basis? I found stuff regarding the datestyle parameter in the docs, but that doesn't quite do what I'd like. Thanks much, Peter -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
