Re: [GENERAL] unexpected EOF on client connection vs 9.0.3
On Tue, 08 Mar 2011 11:30:10 +0800, Craig Ringer wrote: On 08/03/11 02:49, Piotr Czekalski wrote: I've checked and verified that all connections are closed within the code, what's more, the problem has appeared just as I've moved server from Fedora Linux x86_64 running Postgres 8.4.2 to the Windows and 9.0.3 (details above) thus I conclude this is not a client problem indeed (the failure didn't occure on Linux). Windows firewall? You can also see these error reports when the connections are closed uncleanly, without a proper backend close message. Perhaps you have client processes crashing? Or doing hard shutdowns where the client code doesn't get a chance to run any cleanup/dtors/etc? -- Craig Ringer I think in 8.x releases is no need to make any spacial close operation, or at least You do not get notice, about it. Closing socket is enough. E.g. JDBC driver closes connection, by closing socket. In 9 this changed and I see many of such notices in log. Probably Your driver, as many others, is written to close socket without backanad message. Regards, Radek -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Why count(*) doest use index?
On 7 Mar 2011, at 22:16, Glenn Maynard wrote: The stats system only helps for the most basic case--counting the number of rows in a table. In my experience that's not very common; most of the time it's counting total results from some more interesting query, eg. for pagination. In my particular case, I'm caching results for SELECT COUNT(*), expr2 FROM table WHERE expr GROUP BY expr2 (for a very limited set of expressions). It's not uncommon to track your own statistics on your data. Often this doesn't go beyond tracking COUNT(*) on various combinations of conditions. If your data approaches a normal distribution though, I think you can go a step further by tracking the distribution of values in one column for a given value in another. I'm not a mathematician, but I'm pretty sure you could do something like this (with the example given down-thread) to describe the distributions of values in your main table: CREATE TABLE user_event_time ( userinteger UNIQUE REFERENCES events (user), count integer, min date, max date, avg date, stddev date ); CREATE TABLE event_time_user ( event_time date UNIQUE REFERENCES events (event_time), count integer, min integer, max integer, avg integer, stddev integer ); Now, given a user ID, the first table gives you the chance of a specific event_time occurring - which with a normal distribution should be very close to the percentage of the total number of rows that match the set. Say you have 1000 rows and there's 23% chance that there's an event involving user 50 at '2011-01-01', then that means 230 rows match those conditions. You can do the same query the other way around base on the event time and the distribution of users at that date. Combining both will give you better accuracy. Whether this is practical to do is another question entirely, I just thought of this while reading this thread ;) Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4d760ff7235881825915661! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] unexpected EOF on client connection vs 9.0.3
Indeed those messages appear when the client closes the socket connection only. Npgsql has connection pooling and if the app domain is unloaded and there are connections in the pool, those connections don't get a chance to have the Close message sent to backend server. So, even when you are correctly closing your connection, you still will see those messages. You can prevent that from appearing by disabling the connection pooling. Which I advise against. Also, you may want to check if your domain app is being unloaded for some reason. I never see any harm being done because the connection exited without sending the close message. So I think you could (safely?) ignore this message on this case. I hope it helps. On Tue, Mar 8, 2011 at 05:50, rsmogura rsmog...@softperience.eu wrote: On Tue, 08 Mar 2011 11:30:10 +0800, Craig Ringer wrote: On 08/03/11 02:49, Piotr Czekalski wrote: I've checked and verified that all connections are closed within the code, what's more, the problem has appeared just as I've moved server from Fedora Linux x86_64 running Postgres 8.4.2 to the Windows and 9.0.3 (details above) thus I conclude this is not a client problem indeed (the failure didn't occure on Linux). Windows firewall? You can also see these error reports when the connections are closed uncleanly, without a proper backend close message. Perhaps you have client processes crashing? Or doing hard shutdowns where the client code doesn't get a chance to run any cleanup/dtors/etc? -- Craig Ringer I think in 8.x releases is no need to make any spacial close operation, or at least You do not get notice, about it. Closing socket is enough. E.g. JDBC driver closes connection, by closing socket. In 9 this changed and I see many of such notices in log. Probably Your driver, as many others, is written to close socket without backanad message. Regards, Radek -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Regards, Francisco Figueiredo Jr. Npgsql Lead Developer http://www.npgsql.org http://fxjr.blogspot.com http://twitter.com/franciscojunior -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] NULL value vs. DEFAULT value.
version = postgresql-8.4.4-2PGDG.el5.src.rpm I am seeing this problem and I cannot explain why it is happening. Evidently I misapprehend something about the interaction of NOT NULL and DEFAULT. If someone could tell me what the actual case is I would appreciate it very much. The table definition looks like this: CREATE TABLE ca_customs_shipments ( id integer NOT NULL, . . . weight_mass_gross numeric(14,4) DEFAULT 0.0 NOT NULL, weight_mass_gross_uom character varying(3) DEFAULT ' '::character varying NOT NULL, weight_mass_net numeric(14,4) DEFAULT 0 NOT NULL, weight_mass_net_uom character varying(3) DEFAULT ' '::character varying NOT NULL, . . . ); My question is: Why am I getting a NULL exception? Should I only specify DEFAULT and drop the NOT NULL constraint? -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] NULL value vs. DEFAULT value.
On Mar 8, 2011, at 7:54 AM, James B. Byrne wrote: My question is: Why am I getting a NULL exception? Because you're trying to insert NULL explicitly? -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] NULL value vs. DEFAULT value.
On 8 March 2011 20:24, James B. Byrne byrn...@harte-lyne.ca wrote: version = postgresql-8.4.4-2PGDG.el5.src.rpm I am seeing this problem and I cannot explain why it is happening. Evidently I misapprehend something about the interaction of NOT NULL and DEFAULT. If someone could tell me what the actual case is I would appreciate it very much. The table definition looks like this: CREATE TABLE ca_customs_shipments ( id integer NOT NULL, . . . weight_mass_gross numeric(14,4) DEFAULT 0.0 NOT NULL, weight_mass_gross_uom character varying(3) DEFAULT ' '::character varying NOT NULL, weight_mass_net numeric(14,4) DEFAULT 0 NOT NULL, weight_mass_net_uom character varying(3) DEFAULT ' '::character varying NOT NULL, . . . ); My question is: Why am I getting a NULL exception? Should I only specify DEFAULT and drop the NOT NULL constraint? If you pass in a NULL to a column with a NOT NULL and a DEFAULT, the DEFAULT won't take effect as you've already passed the value, even though it's NULL, and it would produce the error. Can you provide an example of a statement which you see this in? -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] NULL value vs. DEFAULT value.
On 08/03/2011 14:54, James B. Byrne wrote: My question is: Why am I getting a NULL exception? Should I only specify DEFAULT and drop the NOT NULL constraint? Good question. Are you trying to insert an explicit NULL? Can you show us your INSERT statement? Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Why count(*) doest use index?
-Original Message- From: Glenn Maynard [mailto:gl...@zewt.org] Sent: Monday, March 07, 2011 5:27 PM To: pgsql-general@postgresql.org Subject: Re: Why count(*) doest use index? An index on events(user, event_time::date) could optimize this, eg. effectively maintaining a count of matching rows for each (user, day) tuple--which is ultimately what I'm doing manually with triggers. Of course, it would have a significant cost, in some combination of complexity, index size and write concurrency, and couldn't be the default behavior for an index. -- Glenn Maynard Indexes don't maintain counts, indexes maintain pointers to the table records. What you need is materialized view storing aggregates. And it looks like you already have it with your triggers. Regards, Igor Neyman -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] NULL value vs. DEFAULT value.
I do not see any NULL exception.
A default value is used whenever a specific value for a column is not
specified. If whatever is trying to insert into this table is assigning a
NULL value to a field the DEFAULT no longer applies but the NOT NULL check
still does.
CREATE TABLE
Notnullfield DEFAULT 'value' NOT NULL,
Nullablefield NULL
;
INSERT INTO TABLE (notnullfield, nullablefield) VALUES (null, null); --
fails due to explicit null value for column notnullfield
INSERT INTO TABLE (nullablefield) VALUES (null); -- success with
notnullfield == 'value' since it was not explicitly set and thus the default
was used
Should I only specify DEFAULT and drop the NOT NULL constraint?
No way to answer the question with the provided information. DEFAULT and
NOT NULL are providing different features so whether either, both, or
neither are required depends on what behavior you require.
If you ALWAYS specify values for all columns in a table then DEFAULT is
pointless but NOT NULL insures you are not inserting NULL.
Looking at your model you do have an issue. It is impossible to have a
shipment with zero (0) mass (unknown yes, zero no). Personally I would
stick with tri-value logic here and allow NULL for both the value and the
uom. I would probably try and restrict uom to a domain or an enumerated
type as well - or at least a CHECK IN ('lbs','kg'). You will need to take
extra caution with your queries that use these weights BUT trying to perform
calculations on records without a known weight is going to cause problems -
and by using NULL you are more likely to catch any bugs more quickly than if
you use a DEFAULT of 0.0
I did have a thought that maybe the contents weight is being calculated but
I would probably want to include a Boolean (isempty) to the model to
represent that (and then allow 0 to be a valid value for weight). But even
in that case you still should have some actual uom associated with the
zero. Of course the gross weight includes the contained so again for that a
zero weight is impossible so the above would only apply to the net-weight.
Just some thoughts.
David J.
-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of James B. Byrne
Sent: Tuesday, March 08, 2011 9:55 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] NULL value vs. DEFAULT value.
version = postgresql-8.4.4-2PGDG.el5.src.rpm
I am seeing this problem and I cannot explain why it is happening.
Evidently I misapprehend something about the interaction of NOT NULL and
DEFAULT. If someone could tell me what the actual case is I would
appreciate it very much.
The table definition looks like this:
CREATE TABLE ca_customs_shipments (
id integer NOT NULL,
. . .
weight_mass_gross numeric(14,4) DEFAULT 0.0 NOT NULL,
weight_mass_gross_uom character varying(3)
DEFAULT ' '::character varying NOT NULL,
weight_mass_net numeric(14,4) DEFAULT 0 NOT NULL,
weight_mass_net_uom character varying(3)
DEFAULT ' '::character varying NOT NULL,
. . .
);
My question is: Why am I getting a NULL exception? Should I only specify
DEFAULT and drop the NOT NULL constraint?
--
*** E-Mail is NOT a SECURE channel ***
James B. Byrnemailto:byrn...@harte-lyne.ca
Harte Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] NULL value vs. DEFAULT value.
On Tue, March 8, 2011 10:09, Scott Ribe wrote: On Mar 8, 2011, at 7:54 AM, James B. Byrne wrote: My question is: Why am I getting a NULL exception? Because you're trying to insert NULL explicitly? Yes, that is the problem. Evidently RoR's ActiveRecord helpfully converts a string containing nought but spaces to nil when a numeric value is required for the column type. The problem arises with a single unit record received from the government system that has a UOM code provided but the associated decimal value field is blank. Since the default is zero in our DB I have altered our load program to coerce a value of zero for strings containing only spaces destined for numeric columns. But, it feels ugly. I would really like to be able to coerce nils to some value on a column by column basis on the DBMS side. This is not really a DEFAULT value and I do not know what I would call it if such a thing did exist. I suppose a trigger and function is called for. Thanks for the help. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] #PgEast schedule is up
Hey, The schedule for #PgEast is up. It can be found here: https://www.postgresqlconference.org/files/east_2011_schedule.html As usually we have a increasingly wide selection of content. Sincerely, JD -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Using bytea field...
Hi, I'm using a bytea field to store small images in base64. I need to know if I can compare bytea rows for equality. To know for example if the file already exists in the database, this is possible with bytea? Best Regads, -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Using bytea field...
On 3/8/2011 12:28 PM, Andre Lopes wrote: Hi, I'm using a bytea field to store small images in base64. I need to know if I can compare bytea rows for equality. To know for example if the file already exists in the database, this is possible with bytea? Best Regads, You dont need to use both base64 and bytea. You can store base64 in text field... or just store the photo as-is into bytea. To answer your question: it would be faster if you computed an md5 (or sha or whatever) and stored it in the db, then you could check to see if an image exists by searching for the md5, which would be way faster, an send a lot less data over the wire. -Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Why count(*) doest use index?
On Tue, Mar 8, 2011 at 10:42 AM, Igor Neyman iney...@perceptron.com wrote: Indexes don't maintain counts, indexes maintain pointers to the table records. The whole point is that they don't, even if you can afford the costs. What you need is materialized view storing aggregates. And it looks like you already have it with your triggers. With cumbersome, awkward triggers, yes. -- Glenn Maynard
[GENERAL] @@ to_tsquery help
Hi all -
I have 2 tables A,B . Can I write a select statement as follows. My query
is running slow ( 7000 ms). I have created gin index on text_col and also
transactionid is PK on both tables. thanks for your help
B has a transactionid and tsvector columns
A has trasactionid and other columns
I have the query as
select b.col1, b.col2
from
b,a
where b.transactionid=a.transactionid
and b.text_col @@ to_tsquery('SOMETEXT')
Regards
Re: [GENERAL] Using bytea field...
Not sure if it is possible directly but have you considered (or you might have to) generating an MD5 hash of the data (possibly after encoding) and then comparing the hashes? For a small image it may not matter but if you plan on making the check with any frequency (and multiple times against the same record) doing a one-time hash generation is going to be quite a bit more efficient. Just make sure you know how you are going to keep the hash and the binary contents in-sync. You could avoid the synchronization issues by putting the hash in an index...in theory...I'd wait for someone else to opine on that particular option. David J. -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Andre Lopes Sent: Tuesday, March 08, 2011 1:29 PM To: postgresql Forums Subject: [GENERAL] Using bytea field... Hi, I'm using a bytea field to store small images in base64. I need to know if I can compare bytea rows for equality. To know for example if the file already exists in the database, this is possible with bytea? Best Regads, -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] equivalent of mysql's SET type?
I'm considering porting a MySQL database to PostgreSQL. That database uses
MySQL's SET type. Does anyone have advice about representing this type in
PostgreSQL?
MySQL DDL excerpt:
CREATE TABLE `transcript_variation` (
`transcript_variation_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`transcript_stable_id` varchar(128) NOT NULL,
...
`consequence_type`
set('ESSENTIAL_SPLICE_SITE','STOP_GAINED','STOP_LOST','COMPLEX_INDEL','SPLICE_SITE')
) ENGINE=MyISAM AUTO_INCREMENT=174923212 DEFAULT CHARSET=latin1;
I'm considering implementing this as a new type based on a bit vector, but I
wonder if anyone has a more flexible general solution.
Thanks,
Reece
Re: [GENERAL] equivalent of mysql's SET type?
On Mar 8, 2011, at 5:06 PM, Reece Hart wrote:
I'm considering porting a MySQL database to PostgreSQL. That database uses
MySQL's SET type. Does anyone have advice about representing this type in
PostgreSQL?
MySQL DDL excerpt:
CREATE TABLE `transcript_variation` (
`transcript_variation_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`transcript_stable_id` varchar(128) NOT NULL,
...
`consequence_type`
set('ESSENTIAL_SPLICE_SITE','STOP_GAINED','STOP_LOST','COMPLEX_INDEL','SPLICE_SITE')
) ENGINE=MyISAM AUTO_INCREMENT=174923212 DEFAULT CHARSET=latin1;
I'm considering implementing this as a new type based on a bit vector, but I
wonder if anyone has a more flexible general solution.
tsvector or intarray might be other options, depending on how you're going to
query it and whether those are really strings or more enum-ish.
The more SQLy way of doing it would be to have that stored in a separate table,
with a foreign key to this one.
Cheers,
Steve
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] equivalent of mysql's SET type?
Reece Hart wrote:
I'm considering porting a MySQL database to PostgreSQL. That database uses
MySQL's SET type. Does anyone have advice about representing this type in
PostgreSQL?
MySQL DDL excerpt:
CREATE TABLE `transcript_variation` (
`transcript_variation_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`transcript_stable_id` varchar(128) NOT NULL,
...
`consequence_type`
set('ESSENTIAL_SPLICE_SITE','STOP_GAINED','STOP_LOST','COMPLEX_INDEL','SPLICE_SITE')
) ENGINE=MyISAM AUTO_INCREMENT=174923212 DEFAULT CHARSET=latin1;
I'm considering implementing this as a new type based on a bit vector, but I
wonder if anyone has a more flexible general solution.
Try starting with an enum type to define the possible values:
CREATE TYPE Consequence_Type
AS ENUM ('ESSENTIAL_SPLICE_SITE','STOP_GAINED',
'STOP_LOST','COMPLEX_INDEL','SPLICE_SITE');
... and then you could try using ARRAY OF Consequence_Type or some such.
-- Darren Duncan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 9.1 - rewrite less alter table?
On Sat, Mar 05, 2011 at 12:55:18PM +0100, hubert depesz lubaczewski wrote: perhaps I misunderstood something from commits, but I assumed that in 9.1 this operation shouldn't rewrite the table: CREATE TABLE test ( x varchar(16) ); insert into test select i::text from generate_series(1,100) i; alter table test alter column x set data type varchar(32); but it does. The patch optimizing that case foundered. We may have it in 9.2. The current code only kicks in when the destination has no typmod. When the source/destination type pair are marked (binary coercible) in the output of \dC, the optimization applies. Alternately, it applies when one of the types is a constraint-free domain over the other. The practical use cases are a bit thin at present. The main interesting ones are varchar(N) - text and conversions between domains and their base types. We did these first because they required a proper subset of the code needed to support the more-common cases. In commit log I see information about binary coercible (which doesn't mean much to me) - so I assumed varchars() can work this way. The applicable definition of binary coercible appears in our CREATE CAST documentation. nm -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Using bytea field...
On Tue, Mar 8, 2011 at 8:00 PM, David Johnston pol...@yahoo.com wrote:
You could avoid the synchronization issues by putting the hash in an
index...in theory...I'd wait for someone else to opine on that particular
option.
Yes, a functional index on MD5(your_bytea_column) will work and is the
way to go for equality comparisons on bytea values. You can use the
built-in MD5() function or one of the various hash functions in
pgcrypto. You can also save some index size by only storing the
bytea-encoded md5 result in the index, something like:
CREATE INDEX bigcol_idx ON foo (decode(MD5(bigcol), 'hex'));
And then run formulate your queries similarly so they use the index:
test=# EXPLAIN ANALYZE SELECT * FROM foo
WHERE (decode(md5(bigcol), 'hex')) = (decode(md5('4'), 'hex'));
QUERY PLAN
---
-
Index Scan using bigcol_idx on foo (cost=0.00..8.28 rows=1 width=4) (actual t
ime=0.032..0.034 rows=1 loops=1)
Index Cond: (decode(md5(bigcol), 'hex'::text) = '\xa87ff679a2f3e71d9181a67b7
542122c'::bytea)
Total runtime: 0.095 ms
(3 rows)
Josh
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] equivalent of mysql's SET type?
On Tue, Mar 8, 2011 at 5:52 PM, Darren Duncan dar...@darrenduncan.netwrote:
Try starting with an enum type to define the possible values:
CREATE TYPE Consequence_Type
AS ENUM ('ESSENTIAL_SPLICE_SITE','STOP_GAINED',
'STOP_LOST','COMPLEX_INDEL','SPLICE_SITE');
... and then you could try using ARRAY OF Consequence_Type or some such.
Arrays occurred to me, but they don't give a set (i.e., a consequence type
can appear more than once) unless I write the code to dedupe the array.
However, if I were going to put that much effort into it, I might as well
represent the set directly and obviate the dedupe. Or, am I missing
something from your suggestion?
-Reece
Re: [GENERAL] equivalent of mysql's SET type?
On Tue, Mar 8, 2011 at 5:51 PM, Steve Atkins st...@blighty.com wrote: tsvector or intarray might be other options, depending on how you're going to query it and whether those are really strings or more enum-ish. The more SQLy way of doing it would be to have that stored in a separate table, with a foreign key to this one. Using a separate table is what I've got now in an experimental conversion. This works well enough and might save me the bother of an enum update. Perhaps I should just move on. Thanks Steve. -Reece
Re: [GENERAL] equivalent of mysql's SET type?
Reece Hart wrote:
On Tue, Mar 8, 2011 at 5:52 PM, Darren Duncan dar...@darrenduncan.netwrote:
Try starting with an enum type to define the possible values:
CREATE TYPE Consequence_Type
AS ENUM ('ESSENTIAL_SPLICE_SITE','STOP_GAINED',
'STOP_LOST','COMPLEX_INDEL','SPLICE_SITE');
... and then you could try using ARRAY OF Consequence_Type or some such.
Arrays occurred to me, but they don't give a set (i.e., a consequence type
can appear more than once) unless I write the code to dedupe the array.
However, if I were going to put that much effort into it, I might as well
represent the set directly and obviate the dedupe. Or, am I missing
something from your suggestion?
You also have another option, which is to split consequence_type into
a second table, and have a record in there for each value in the set. You would
maintain a lack of duplicates by having an ordinary unique/primary key
constraint on the second table. This second-table approach is logically
equivalent to your use of a set-valued field. -- Darren Duncan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] procedure in db
I use postgresql in red hat linux . I want to know how I can find out all the procedure stored in the database . Any SQL command to do it ??? -- View this message in context: http://postgresql.1045698.n5.nabble.com/procedure-in-db-tp3414995p3414995.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] equivalent of mysql's SET type?
On 03/08/11 5:06 PM, Reece Hart wrote:
I'm considering porting a MySQL database to PostgreSQL. That database
uses MySQL's SET type. Does anyone have advice about representing this
type in PostgreSQL?
MySQL DDL excerpt:
CREATE TABLE `transcript_variation` (
`transcript_variation_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`transcript_stable_id` varchar(128) NOT NULL,
...
`consequence_type`
set('ESSENTIAL_SPLICE_SITE','STOP_GAINED','STOP_LOST','COMPLEX_INDEL','SPLICE_SITE')
) ENGINE=MyISAM AUTO_INCREMENT=174923212 DEFAULT CHARSET=latin1;
why not just have a set of booleans in the table for these individual
on/off attributes? wouldn't that be simplest?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] procedure in db
Hello 2011/3/9 abcdef chuiking...@gmail.com: I use postgresql in red hat linux . I want to know how I can find out all the procedure stored in the database . Any SQL command to do it ??? you can try \df in psql or select * from pg_proc Regards Pavel Stehule -- View this message in context: http://postgresql.1045698.n5.nabble.com/procedure-in-db-tp3414995p3414995.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
