[GENERAL] Alternatives to a unique indexes with NULL
All, I have a Rails application on 9.3 in which I want to enforce a unique index on a set of fields, one of which includes a NULL-able column. According to http://www.postgresql.org/docs/9.3/static/indexes-unique.html, btree indexes can't handle uniqueness on NULL columns, so I'm looking for another way to achieve what I need. My initial thought is to replace the null with a single space (it's a character varying(1) column), which will require some changes to application code, but result in a cleaner process than the application enforcing the uniqueness constraint. Is there a better or cleaner way to do what I want? Peter -- 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] Alternatives to a unique indexes with NULL
On Sat, Jan 17, 2015 at 6:27 AM, Peter Hicks wrote: > All, > > I have a Rails application on 9.3 in which I want to enforce a unique > index on a set of fields, one of which includes a NULL-able column. > > According to http://www.postgresql.org/docs/9.3/static/indexes-unique.html, > btree indexes can't handle uniqueness on NULL columns, so I'm looking for > another way to achieve what I need. > > My initial thought is to replace the null with a single space (it's a > character varying(1) column), which will require some changes to > application code, but result in a cleaner process than the application > enforcing the uniqueness constraint. > > Is there a better or cleaner way to do what I want? I read the above. As I understand it, you can have a unique index on a column which is NULL-able. That will guarantee that all the non-NULL values are unique. What it will not guarantee is that there will be at most one NULL value in the indexed column. Are you saying that what you want is a column with a unique index where you cannot have two or more rows with NULL in the indexed column? If so, then you will need to have a value to indicate the equivalent of NULL. Personally, I use a zero length string "" instead of a single blank ' '. This is value since you say this column is a "character varying(1)". Which seems a bit strange to me, but I don't know your application. > > > > Peter > -- While a transcendent vocabulary is laudable, one must be eternally careful so that the calculated objective of communication does not become ensconced in obscurity. In other words, eschew obfuscation. 111,111,111 x 111,111,111 = 12,345,678,987,654,321 Maranatha! <>< John McKown
Re: [GENERAL] Alternatives to a unique indexes with NULL
Hi John On 17/01/15 12:39, John McKown wrote: I read the above. As I understand it, you can have a unique index on a column which is NULL-able. That will guarantee that all the non-NULL values are unique. What it will not guarantee is that there will be at most one NULL value in the indexed column. Are you saying that what you want is a column with a unique index where you cannot have two or more rows with NULL in the indexed column? That's correct - the application reads in a very old-format of fixed-length file and, if any field in there is just a single space, replaces it with a NULL, since a single space implies a null. However, only one of the records needs a constraint on one of these fields. If so, then you will need to have a value to indicate the equivalent of NULL. Personally, I use a zero length string "" instead of a single blank ' '. This is value since you say this column is a "character varying(1)". Which seems a bit strange to me, but I don't know your application. OK, that makes sense and it was more-or-less along the lines of what I expected. I like the idea of a zero-length string versus a single space, so I'll go implement that. I believe the column type a Rails-ism, which creates 'string' fields with a length constraint of 1 as 'character varying(1)'. Probably not ideal, but there's usually a trade-off somewhere. Thanks very much for your help and quick response! Peter -- 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] Alternatives to a unique indexes with NULL
Peter Hicks wrote: > All, > > I have a Rails application on 9.3 in which I want to enforce a unique > index on a set of fields, one of which includes a NULL-able column. > > According to > http://www.postgresql.org/docs/9.3/static/indexes-unique.html, btree > indexes can't handle uniqueness on NULL columns, so I'm looking for > another way to achieve what I need. somethink like that? : test=# create table peter_hicks (id int); CREATE TABLE Time: 1,129 ms test=*# create unique index idx_1 on peter_hicks ((case when id is null then 'NULL' else '' end)) where id is null; CREATE INDEX Time: 14,803 ms test=*# insert into peter_hicks values (1); INSERT 0 1 Time: 0,385 ms test=*# insert into peter_hicks values (2); INSERT 0 1 Time: 0,145 ms test=*# insert into peter_hicks values (null); INSERT 0 1 Time: 0,355 ms test=*# insert into peter_hicks values (null); ERROR: duplicate key value violates unique constraint "idx_1" DETAIL: Key (( CASE WHEN id IS NULL THEN 'NULL'::text ELSE ''::text END))=(NULL) already exists. Time: 0,376 ms test=*# 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-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] WAL supported extension
Hi, (Is this mailing list right mailing list for asking this question...?) Is there any plan to implement PostgreSQL API to implement WAL supported extension? Background: I'm writing an extension(*) that provides index module for CJK ready fast full-text search feature. (*) PGroonga: https://github.com/pgroonga/pgroonga (Sorry. README is written in Japanese for now.) I want to add WAL support to the extension. But PostgreSQL doesn't provide API to add an entry to RmgrTable. So I can't implement WAL support to the extension. Thanks, -- kou -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Can pg_restore produce create or replace commands
I often work with the output of pg_restore from a custom format dump file. For example a file produced by running pg_restore -s -1 -L listfile dumpfile where listfile has been edited to comment out most of the rows to leave only the data base objects I'm currently interested in. Most often, I'm refactoring functions and so don't really want to drop the function but rather want to do a "create or replace function" operation to implement the changes. Consequently I have to frequently do a global search and replace along the lines of sed -ie 's/CREATE FUNCTION/CREATE OR REPLACE FUNCTION/' I am not seeing in the documentation an option to generate the script with anything but straight "create function" commands. Is there a way for me to access this functionality (i.e., to generate "create or replace function" scripts) from the command line? I suppose I could pipe the pg_restore output through the sed command just as a matter of standard operating procedure, but the capability must exist because that is the way the scripts appear in pgadmin. I generally do not use the GUI tool and so would like it to happen automatically when using the command line tools. --- This email is free from viruses and malware because avast! Antivirus protection is active. http://www.avast.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] Can pg_restore produce create or replace commands
On 01/17/2015 10:05 AM, Berend Tober wrote: I often work with the output of pg_restore from a custom format dump file. For example a file produced by running pg_restore -s -1 -L listfile dumpfile where listfile has been edited to comment out most of the rows to leave only the data base objects I'm currently interested in. Most often, I'm refactoring functions and so don't really want to drop the function but rather want to do a "create or replace function" operation to implement the changes. Consequently I have to frequently do a global search and replace along the lines of sed -ie 's/CREATE FUNCTION/CREATE OR REPLACE FUNCTION/' I am not seeing in the documentation an option to generate the script with anything but straight "create function" commands. Is there a way for me to access this functionality (i.e., to generate "create or replace function" scripts) from the command line? Not that I know of. Though it should be noted that what you can do with CREATE OR REPLACE depends a good deal on what constitutes refactoring. Per the docs: http://www.postgresql.org/docs/9.3/interactive/sql-createfunction.html "To replace the current definition of an existing function, use CREATE OR REPLACE FUNCTION. It is not possible to change the name or argument types of a function this way (if you tried, you would actually be creating a new, distinct function). Also, CREATE OR REPLACE FUNCTION will not let you change the return type of an existing function. To do that, you must drop and recreate the function. (When using OUT parameters, that means you cannot change the types of any OUT parameters except by dropping the function.)" My solution to this is using scripts for objects and keeping them under version control. Lately I have been using Sqitch(sqitch.org/). There is a learning curve, but I am finding it useful. I suppose I could pipe the pg_restore output through the sed command just as a matter of standard operating procedure, but the capability must exist because that is the way the scripts appear in pgadmin. I generally do not use the GUI tool and so would like it to happen automatically when using the command line tools. --- This email is free from viruses and malware because avast! Antivirus protection is active. http://www.avast.com -- Adrian Klaver adrian.kla...@aklaver.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] Can pg_restore produce create or replace commands
Adrian Klaver wrote: On 01/17/2015 10:05 AM, Berend Tober wrote: I often work with the output of pg_restore from a custom format dump file... Most often, I'm refactoring functions and so don't really want to drop the function but rather want to do a "create or replace function"... I am not seeing in the documentation an option to generate the script with anything but straight "create function" commands. Is there a way for me to access this functionality (i.e., to generate "create or replace function" scripts) from the command line? Not that I know of. Though it should be noted that what you can do with CREATE OR REPLACE depends a good deal on what constitutes refactoring. ... "To replace the current definition of an existing function, use CREATE OR REPLACE FUNCTION. It is not possible to change the name or argument types... Indeed. I have run into that occasionally. But currently and most often it has not been much problem as the refactoring is generally internal to the function behavior ... in fact most of them are trigger functions, and since I have adopted a consistent naming convention there are practically never function interface changes. My solution to this is using scripts for objects and keeping them under version control. Lately I have been using Sqitch(sqitch.org/). There is a learning curve, but I am finding it useful. Oh sqitch is (looks to be) awesome and I wish so much to employ it, but I have not had success it getting it installed. I do use Wheeler's companion tool pgtap, and THAT is totally awesome as well and is making life SO much better. I cannot envision ever again doing data base development without it! I just wonder how PgAdmin creates the scripts, then. Is that a feature specific to the PgAdmin application then rather than the underlying system and tools? -- 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] Can pg_restore produce create or replace commands
On 01/17/2015 10:05 AM, Berend Tober wrote:
I often work with the output of pg_restore from a custom format dump
file. For example a file produced by running
pg_restore -s -1 -L listfile dumpfile
where listfile has been edited to comment out most of the rows to leave
only the data base objects I'm currently interested in.
Most often, I'm refactoring functions and so don't really want to drop
the function but rather want to do a "create or replace function"
operation to implement the changes. Consequently I have to frequently do
a global search and replace along the lines of
sed -ie 's/CREATE FUNCTION/CREATE OR REPLACE FUNCTION/'
I am not seeing in the documentation an option to generate the script
with anything but straight "create function" commands.
Is there a way for me to access this functionality (i.e., to generate
"create or replace function" scripts) from the command line?
I suppose I could pipe the pg_restore output through the sed command
just as a matter of standard operating procedure, but the capability
must exist because that is the way the scripts appear in pgadmin. I
generally do not use the GUI tool and so would like it to happen
automatically when using the command line tools.
Not sure how pgAdmin does it. Just remembered something though,
pg_get_functiondef(), available in 8.4+:
http://www.postgresql.org/docs/9.3/interactive/functions-info.html
"pg_get_functiondef returns a complete CREATE OR REPLACE FUNCTION
statement for a function. pg_get_function_arguments returns the argument
list of a function, in the form it would need to appear in within CREATE
FUNCTION. pg_get_function_result similarly returns the appropriate
RETURNS clause for the function. pg_get_function_identity_arguments
returns the argument list necessary to identify a function, in the form
it would need to appear in within ALTER FUNCTION, for instance. This
form omits default values."
So:
test=# SELECT pg_get_functiondef('ean_substr'::regproc);
pg_get_functiondef
CREATE OR REPLACE FUNCTION public.ean_substr(text)+
RETURNS boolean +
LANGUAGE plpgsql +
AS $function$ +
DECLARE +
offset integer := 0; +
-- Support UPCs. +
ean TEXT:= CASE WHEN length($1) = 12 THEN +
'0' || $1 +
ELSE +
$1 +
END; +
BEGIN +
-- Make sure we really have an EAN. +
IF ean !~ '^\\d{13}$' THEN RETURN FALSE; END IF; +
+
RETURN 10 - ( +
( +
-- Sum even numerals. +
substring(ean, 2 + offset, 1)::integer +
+ substring(ean, 4 + offset, 1)::integer +
+ substring(ean, 6 + offset, 1)::integer +
+ substring(ean, 8 + offset, 1)::integer +
+ substring(ean, 10 + offset, 1)::integer +
+ substring(ean, 12 + offset, 1)::integer +
) * 3 -- Multiply total by 3.+
-- Add odd numerals except for checksum (13).+
+ substring(ean, 3 + offset, 1)::integer+
+ substring(ean, 5 + offset, 1)::integer+
+ substring(ean, 7 + offset, 1)::integer+
+ substring(ean, 9 + offset, 1)::integer+
+ substring(ean, 11 + offset, 1)::integer+
-- Compare to the checksum. +
) % 10 = substring(ean, 12 + offset, 1)::integer; +
END; +
$function$+
(1 row)
---
This email is free from viruses and malware because avast! Antivirus
protection is active.
http://www.avast.com
--
Adrian Klaver
adrian.kla...@aklaver.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] Can pg_restore produce create or replace commands
Adrian Klaver wrote:
On 01/17/2015 10:05 AM, Berend Tober wrote:
I often work with the output of pg_restore from a custom format dump
file. ...
Most often, I'm refactoring functions and so don't really want to drop
the function but rather want to do a "create or replace function"...
Not sure how pgAdmin does it. Just remembered something though,
pg_get_functiondef(), available in 8.4+:...
test=# SELECT pg_get_functiondef('ean_substr'::regproc);
...
That has potential. However, in some instances the object I'm
refactoring will end up having dependencies, for instance for the case
of views oftentimes I will have to do the drop/create for it and all
dependent objects. In those situations, generating scripts from the
pg_restore output is very convenient, since it tells me all the
dependencies and I can adjust the listfile contents to get them all and
in the correct order.
At this point I'm thinking to wrap the pg_restore invocation in a script
that includes piping through sed to transform the create statements.
I would have thought that the functionality in PgAdmin and the command
line utilities would rely on the same underlying source code and so
support the same functionality. Alas.
---
This email is free from viruses and malware because avast! Antivirus protection
is active.
http://www.avast.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] Can pg_restore produce create or replace commands
Berend Tober wrote on 17.01.2015 19:05: I often work with the output of pg_restore from a custom format dump file. For example a file produced by running pg_restore -s -1 -L listfile dumpfile where listfile has been edited to comment out most of the rows to leave only the data base objects I'm currently interested in. Most often, I'm refactoring functions and so don't really want to drop the function but rather want to do a "create or replace function" operation to implement the changes. Consequently I have to frequently do a global search and replace along the lines of sed -ie 's/CREATE FUNCTION/CREATE OR REPLACE FUNCTION/' I am not seeing in the documentation an option to generate the script with anything but straight "create function" commands. Is there a way for me to access this functionality (i.e., to generate "create or replace function" scripts) from the command line? To me this sounds as if you are doing it the wrong way round. To manage (refactor) your functions, you should have the current code stored in a version control system, update the code there an then apply it to the target database. Extracting the code from the database in order to do refactoring is like disassembling a program each time you want to apply a bugfix. The code in the vcs would then contain the necessary "create or replace" (btw you still need to drop the function if you change the parameters) There are several tools that will help you manage the SQL scripts. Were are quite content with using Liquibase, but Flyways is another alternative. Thomas -- 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] Can pg_restore produce create or replace commands
Thomas Kellerer wrote: Berend Tober wrote on 17.01.2015 19:05: I often work with the output of pg_restore from a custom format dump file. ... Most often, I'm refactoring functions and so don't really want to drop the function but rather want to do a "create or replace function" ... To me this sounds as if you are doing it the wrong way round. Possibly. But if the revision control system and the production data base disagree, then which one do you believe? To manage (refactor) your functions, you should have the current code stored in a version control system, update the code there an then apply it to the target database. Extracting the code from the database in order to do refactoring is like disassembling a program each time you want to apply a bugfix. The code in the vcs would then contain the necessary "create or replace" (btw you still need to drop the function if you change the parameters) --- This email is free from viruses and malware because avast! Antivirus protection is active. http://www.avast.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] WAL supported extension
On Sat, Jan 17, 2015 at 11:18 PM, Kouhei Sutou wrote: > (Is this mailing list right mailing list for asking this > question...?) Hackers would have been fine as well. > Is there any plan to implement PostgreSQL API to implement > WAL supported extension? Not that I know of, the last discussion I recall on the matter being this one: http://www.postgresql.org/message-id/capphfdsxwzmojm6dx+tjnpyk27kt4o7ri6x_4oswcbyu1rm...@mail.gmail.com -- Michael -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
