date:20161220

Re: [GENERAL] How well does PostgreSQL 9.6.1 support unicode?

2016-12-20 Thread Kyotaro HORIGUCHI

Hello,

At Tue, 20 Dec 2016 16:41:51 -0800, James Zhou  wrote in 

> Unicode has evolved from version 1.0 with 7,161 characters released in 1991
> to version 9.0 with 128,172 characters released in June 2016. My questions
> are
> - which version of Unicode is supported by PostgreSQL 9.6.1?
> - what does "supported" exactly mean? simply store it? comparison? sorting?
> substring? etc.
...
> /* characters from BMP,  -  */
> insert into unicode(id, string) values(1, U&'\0041');  -- 'A'
...
> insert into unicode(id, string) values(5, U&'\6211\4EEC'); -- a string of two 
> Chinese characters

These shouldn't be a problem.

> /* Below are unicode characters with code points beyond , aka planes 1 - 
> F */
> insert into unicode(id, string) values(100, U&'\1F478'); -- a mojo character, 
> https://unicodelookup.com/#0x1f478/1

https://www.postgresql.org/docs/9.6/static/sql-syntax-lexical.html

> Unicode characters can be specified in escaped form by writing a
> backslash followed by the four-digit hexadecimal code point
> number or alternatively a backslash followed by a plus sign
> followed by a six-digit hexadecimal code point number.

So this is parsed as U+1f47 + '8' as you seen. This should be as
the following. '+' is needed just after the backslash.

insert into unicode(id, string) values(100, U&'\+01F478');

The six-digit form accepts up to U+10 so the whole space in
Unicode is usable.

> Observations
> 
>- BMP characters (id <= 10)
>   -  they are stored and fetched correctly.
>   - their lengths in char are correct, although some of them take 3
>   bytes (id = 4, 6, 7)
>   - *But their sorting order seems to be undefined. Can anyone comment
>   the sorting rules?*
>- Non-BMP characters (id >= 100)
>   - they take 2 - 4 bytes.
>   - Their lengths in character are not correct
>   - they are not retrieved correctly, judged by the their fetched ascii
>   value (column 5 in the table above)
>   - substring is not correct

> 
> Specifically, the lack of support for emojo characters 0x1F478, 0x1F479 is
> causing a problem in my application.

'+' would resolve the problem.

> My conclusion:
> - PostgreSQL 9.6.1 only supports a subset of unicode characters in BMP.  Is
> there any documents defining which subset is fully supported?

A PostgreSQL database with encoding=UTF8 just accepts the whole
range of Unicode, regardless that a character is defined for the
code or not.

> Are any configuration I can change so that more unicode characters are
> supported?

For the discussion on sorting, categorize is described in Tom's
mail.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center




-- 
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] How well does PostgreSQL 9.6.1 support unicode?

2016-12-20 Thread James Zhou

both lc_type and lc_collate are en_US.UTF-8. Sorry for missing them in the
original post.

I understand that collate has impact on sorting order, but the fact that
char_length() is not returning the correct length in char for certain
characters (non-BMP) is an indication that unicode is not fully supported.
If char_length() is not working properly, I'd expect that substring() won't
work either.

The PostgreSQL I am using is an AWS PostgreSQL RDS. I can check with AWS,
but presumably that they are running PostgreSQL RDS on some flavor of lunix.

My client is PgAdmin 4 running on a Windows 7 machine. I understand that
some client tools may not be able to display all unicode chars, but I do
expect that the function ascii() return correct values  of the stored
chars.

For me the primary requirement is storing and retrieving all unicode
characters as they are, and char_length() returns the correct values for
all supported unicode chars. Correct sorting is nice-to-have.

Any help to get unicode chars, particularly the mojos (0x1F478, 0x1F479), in
and out of pg correctly is much appreciated. Thank you!

James

On Tue, Dec 20, 2016 at 9:24 PM, Tom Lane  wrote:

> James Zhou  writes:
> >   - *But their sorting order seems to be undefined. Can anyone
> comment
> >   the sorting rules?*
>
> Well, it would depend on lc_collate, which you have not told us, and
> it would also depend on how well your platform's strcoll() function
> implements that collation; but you have not told us what platform this
> is running on.
>
> Most of the other behaviors you mention are also partly or wholly
> dependent on which software you use with Postgres and whether you've
> correctly configured that software.  So it's pretty hard to answer
> this usefully with only this much info.
>
> regards, tom lane
>

Re: [GENERAL] pg_repack and Postgres versions > 9.4

2016-12-20 Thread Kyotaro HORIGUCHI

At Tue, 20 Dec 2016 20:32:19 +, Gaetano Mendola  wrote 
in 
> I wonder why this is not a VACUUM option.
> 
> On Fri, 16 Dec 2016 at 15:30 Kenneth Marshall  wrote:
> 
> > On Fri, Dec 16, 2016 at 09:15:51AM -0500, Bill Moran wrote:
> > >
> > > Does anyone have experience using pg_repack on Postgres versions > 9.4?
> > > Specifically 9.5, but probably 9.6 at some point.
> > >
> > > The documentation claims it supports up to 9.4. I haven't looked at it
> > > closely enough to guess whether there might be changes in 9.5/9.6 to
> > > cause it not to work any more.
> > >
> > > Anyone know? Or, alternatively, anyone have another option to get the
> > > same job done?
> > >
> > > --
> > > Bill Moran 
> >
> > Hi Bill,
> >
> > We are currently using it with 9.5.5 and it works well. I would expect
> > that it would work with 9.6 as well, but we have not tested it.

As more official statement, this page says that it supports PG9.5
as of 1.3.3.

http://pgxn.org/dist/pg_repack/doc/pg_repack.html

> pg_repack 1.3.3
> Added support for PostgreSQL 9.5

Latest version seems 1.3.4.

The requirements on the page looks somewhat stale.

> Requirements
>   PostgreSQL versions
>   PostgreSQL 8.3, 8.4, 9.0, 9.1, 9.2, 9.3, 9.4


regards,

-- 
Kyotaro Horiguchi
NTT Open Source Software Center




-- 
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] How well does PostgreSQL 9.6.1 support unicode?

2016-12-20 Thread Tom Lane

James Zhou  writes:
>   - *But their sorting order seems to be undefined. Can anyone comment
>   the sorting rules?*

Well, it would depend on lc_collate, which you have not told us, and
it would also depend on how well your platform's strcoll() function
implements that collation; but you have not told us what platform this
is running on.

Most of the other behaviors you mention are also partly or wholly
dependent on which software you use with Postgres and whether you've
correctly configured that software.  So it's pretty hard to answer
this usefully with only this much info.

regards, tom lane

-- 
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] How well does PostgreSQL 9.6.1 support unicode?

2016-12-20 Thread Peter Devoy

The comments in here may be of help:
https://github.com/postgres/postgres/blob/master/src/include/mb/pg_wchar.h

Kind regards


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] How well does PostgreSQL 9.6.1 support unicode?

2016-12-20 Thread John R Pierce


On 12/20/2016 4:41 PM, James Zhou wrote:
- PostgreSQL 9.6.1 only supports a subset of unicode characters in 
BMP.  Is there any documents defining which subset is fully supported?


I believve its support is based on what the OS native runtime libraries 
support.



--
john r pierce, recycling bits in santa cruz

[GENERAL] How well does PostgreSQL 9.6.1 support unicode?

2016-12-20 Thread James Zhou

Unicode has evolved from version 1.0 with 7,161 characters released in 1991
to version 9.0 with 128,172 characters released in June 2016. My questions
are
- which version of Unicode is supported by PostgreSQL 9.6.1?
- what does "supported" exactly mean? simply store it? comparison? sorting?
substring? etc.

Below is a test I did which reveals some unexpected behaviors.

My test database 'gsdb' is using UTF8 encoding, confirmed by

select datname, datcollate, datctype, pg_encoding_to_char(encoding)
from pg_database
where datname = 'gsdb';

which returned UTF8.

Here is a simple test table:

create table unicode (id int, string varchar(100));

Then I insert some unicode characters by referencing their code points in
hexadecimal:

/* characters from BMP,  -  */
insert into unicode(id, string) values(1, U&'\0041');  -- 'A'
insert into unicode(id, string) values(2, U&'\00C4');   -- 'A' with umlaut,
German
insert into unicode(id, string) values(3, U&'\03B1');  -- Greek letter alpha
insert into unicode(id, string) values(4, U&'\6211');  -- a Chinese
character, https://unicodelookup.com/#0x6211/1
insert into unicode(id, string) values(5, U&'\6211\4EEC'); -- a string of
two Chinese characters
insert into unicode(id, string) values(6, U&'\30CF');  -- a Japanese
character
insert into unicode(id, string) values(7, U&'\306F');  -- a Japanese
character
insert into unicode(id, string) values(8, U&'\2B41');  --
https://unicodelookup.com/#0x2b41/1
insert into unicode(id, string) values(9, U&'\2B44');  --
https://unicodelookup.com/#0x2b44/1
insert into unicode(id, string) values(10, U&'\2B50');  --
https://unicodelookup.com/#0x2b50/1

/* Below are unicode characters with code points beyond , aka planes 1
- F */
insert into unicode(id, string) values(100, U&'\1F478'); -- a mojo
character, https://unicodelookup.com/#0x1f478/1
insert into unicode(id, string) values(101, U&'\1F479');  -- another mojo
insert into unicode(id, string) values(102, U&'\1D11F');  -- musical symbol
g clef ottava alta
insert into unicode(id, string) values(103, U&'\26000');   -- a very
infrequently used Chinese character
insert into unicode(id, string) values(104, U&'\26001');   -- another very
infrequently used Chinese character
insert into unicode(id, string) values(105, U&'\26000\26001');  -- a string
with 2 Chinese characters in the plane 2

The SELECT below shows what PostgreSQL has recorded:

select id, string,
  char_length(string),
  octet_length(string),
  ascii(string),
  substring(string, 1, 1) as firstChar,
  ascii(substring(string, 1, 1)) as unicodeInt
from unicode
order by string;

Here are the results:

[image: Inline image 1]

Observations

   - BMP characters (id <= 10)
  -  they are stored and fetched correctly.
  - their lengths in char are correct, although some of them take 3
  bytes (id = 4, 6, 7)
  - *But their sorting order seems to be undefined. Can anyone comment
  the sorting rules?*
   - Non-BMP characters (id >= 100)
  - they take 2 - 4 bytes.
  - Their lengths in character are not correct
  - they are not retrieved correctly, judged by the their fetched ascii
  value (column 5 in the table above)
  - substring is not correct


Specifically, the lack of support for emojo characters 0x1F478, 0x1F479 is
causing a problem in my application.

My conclusion:
- PostgreSQL 9.6.1 only supports a subset of unicode characters in BMP.  Is
there any documents defining which subset is fully supported?

Are any configuration I can change so that more unicode characters are
supported?

Thanks

James

Re: [GENERAL] pg_repack and Postgres versions > 9.4

2016-12-20 Thread Alvaro Herrera

Gaetano Mendola wrote:
> I wonder why this is not a VACUUM option.

Because nobody has written the patch.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] pg_repack and Postgres versions > 9.4

2016-12-20 Thread Gaetano Mendola

I wonder why this is not a VACUUM option.

On Fri, 16 Dec 2016 at 15:30 Kenneth Marshall  wrote:

> On Fri, Dec 16, 2016 at 09:15:51AM -0500, Bill Moran wrote:
> >
> > Does anyone have experience using pg_repack on Postgres versions > 9.4?
> > Specifically 9.5, but probably 9.6 at some point.
> >
> > The documentation claims it supports up to 9.4. I haven't looked at it
> > closely enough to guess whether there might be changes in 9.5/9.6 to
> > cause it not to work any more.
> >
> > Anyone know? Or, alternatively, anyone have another option to get the
> > same job done?
> >
> > --
> > Bill Moran 
>
> Hi Bill,
>
> We are currently using it with 9.5.5 and it works well. I would expect
> that it would work with 9.6 as well, but we have not tested it.
>
> Regards,
> Ken
>
>
> --
> 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] pgAdmin 4 - auto disconnect

2016-12-20 Thread Paolo Saudin

Thank you very much,
I will try this approach
Paolo

2016-12-19 15:30 GMT+01:00 Melvin Davidson :

>
> On Mon, Dec 19, 2016 at 7:28 AM, Paolo Saudin 
> wrote:
>
>> Hi,
>>
>> I deployed pgAdmin4 on a server and I have a script that every day
>> restore a database from a dump file. The problem I am facing at, is that if
>> somebody forget to diconnect from the database, the restore command fails
>> with an error "database Test is being accessed by other users".
>>
>> Is there a way to tell pgAdmin to auto disconnect from all databases?
>>
>> Thanks
>> Paolo Saudin
>>
>
> PgAdmin4 actually has nothing to do with it, as it is nothing more than a
> tool to monitor PostgreSQL.
> However, you can kill all user processes (except your own) by submitting
> the following query.
>
> SELECT pg_terminate_backend(pid)
> FROM pg_stat_activity
> WHERE pg_backend_pid() <> pid;
>
> CAVEAT EMPTOR: You must be a superuser for this to work properly.
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>

Re: [GENERAL] Replacing an existing unique constraint (not UNIQUE INDEX) with primary key

2016-12-20 Thread Adrian Klaver


On 12/20/2016 03:03 AM, Andreas Joseph Krogh wrote:

På tirsdag 20. desember 2016 kl. 11:42:56, skrev Achilleas Mantzios
>:

On 20/12/2016 12:27, Andreas Joseph Krogh wrote:

På tirsdag 20. desember 2016 kl. 11:02:27, skrev Achilleas
Mantzios >:

On 20/12/2016 11:43, Andreas Joseph Krogh wrote:

[snip]

BEGIN;
ALTER TABLE person ADD CONSTRAINT person_pk PRIMARY KEY
(entity_id);
alter table person drop constraint person_entity_id_key CASCADE;
alter table phone add CONSTRAINT phone_fk FOREIGN KEY
(person_entity_id) REFERENCES person(entity_id);
alter table address add CONSTRAINT address_fk FOREIGN KEY
(person_id) REFERENCES person(entity_id);
COMMIT;


Yea, I was hoping to avoid having to manually add the FK's to the
referencing tables (34).
Is there really no way to accomplish this without DROP CONSTRAINT
...  CASCADE, hacking the system-catalogs or something?


You may write a script to output those 34 FK constraints. Definitely
safer than hacking pg_constraint.conindid


Yes.

I'd still argue that what I'm trying to do should "just work" as PG
treats UNIQUE CONSTRAINT and UNIQUE INDEX the same wrt. the planner and
FK-enforcement.


Close as I can come:

test=# ALTER TABLE person
ADD CONSTRAINT person_pkey PRIMARY KEY (entity_id);
ALTER TABLE


test=# \d person
   Table "public.person"
  Column   |   Type| Modifiers
---+---+---
 entity_id | bigint| not null
 name  | character varying | not null
Indexes:
"person_pkey" PRIMARY KEY, btree (entity_id)
"person_entity_id_key" UNIQUE CONSTRAINT, btree (entity_id)
Referenced by:
TABLE "address" CONSTRAINT "address_person_id_fkey" FOREIGN KEY 
(person_id) REFERENCES person(entity_id)
TABLE "phone" CONSTRAINT "phone_person_entity_id_fkey" FOREIGN KEY 
(person_entity_id) REFERENCES person(entity_id)



Though you cannot DROP the original constraint index until you change 
what the FKs point to. It buys you time to do that though.


test=# ALTER TABLE person DROP CONSTRAINT person_entity_id_key;
ERROR:  cannot drop constraint person_entity_id_key on table person 
because other objects depend on it
DETAIL:  constraint phone_person_entity_id_fkey on table phone depends 
on index person_entity_id_key
constraint address_person_id_fkey on table address depends on index 
person_entity_id_key

HINT:  Use DROP ... CASCADE to drop the dependent objects too.




--
*Andreas Joseph Krogh*
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.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] Replacing an existing unique constraint (not UNIQUE INDEX) with primary key

2016-12-20 Thread Andreas Joseph Krogh

På tirsdag 20. desember 2016 kl. 11:42:56, skrev Achilleas Mantzios <
ach...@matrix.gatewaynet.com >:
On 20/12/2016 12:27, Andreas Joseph Krogh wrote:
På tirsdag 20. desember 2016 kl. 11:02:27, skrev Achilleas Mantzios <
ach...@matrix.gatewaynet.com >:
On 20/12/2016 11:43, Andreas Joseph Krogh wrote:
[snip]
 BEGIN;
 ALTER TABLE person ADD CONSTRAINT person_pk PRIMARY KEY (entity_id);
 alter table person drop constraint person_entity_id_key CASCADE;
 alter table phone add CONSTRAINT phone_fk FOREIGN KEY (person_entity_id) 
REFERENCES person(entity_id);
 alter table address add CONSTRAINT address_fk FOREIGN KEY (person_id) 
REFERENCES person(entity_id);
 COMMIT;  
Yea, I was hoping to avoid having to manually add the FK's to the referencing 
tables (34).
Is there really no way to accomplish this without DROP CONSTRAINT ... 
 CASCADE, hacking the system-catalogs or something?
 
 You may write a script to output those 34 FK constraints. Definitely safer 
than hacking pg_constraint.conindid   
Yes.
 
I'd still argue that what I'm trying to do should "just work" as PG treats 
UNIQUE CONSTRAINT and UNIQUE INDEX the same wrt. the planner and FK-enforcement.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com

Re: [GENERAL] Replacing an existing unique constraint (not UNIQUE INDEX) with primary key

2016-12-20 Thread Achilleas Mantzios


On 20/12/2016 12:27, Andreas Joseph Krogh wrote:

På tirsdag 20. desember 2016 kl. 11:02:27, skrev Achilleas Mantzios 
>:

On 20/12/2016 11:43, Andreas Joseph Krogh wrote:

[snip]

BEGIN;
ALTER TABLE person ADD CONSTRAINT person_pk PRIMARY KEY (entity_id);
alter table person drop constraint person_entity_id_key CASCADE;
alter table phone add CONSTRAINT phone_fk FOREIGN KEY (person_entity_id) 
REFERENCES person(entity_id);
alter table address add CONSTRAINT address_fk FOREIGN KEY (person_id) 
REFERENCES person(entity_id);
COMMIT;

Yea, I was hoping to avoid having to manually add the FK's to the referencing 
tables (34).
Is there really no way to accomplish this without DROP CONSTRAINT ...  CASCADE, 
hacking the system-catalogs or something?

You may write a script to output those 34 FK constraints. Definitely safer than 
hacking pg_constraint.conindid .

Thanks.
--
*Andreas Joseph Krogh*
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 




--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Re: [GENERAL] Replacing an existing unique constraint (not UNIQUE INDEX) with primary key

2016-12-20 Thread Andreas Joseph Krogh

På tirsdag 20. desember 2016 kl. 11:02:27, skrev Achilleas Mantzios <
ach...@matrix.gatewaynet.com >:
On 20/12/2016 11:43, Andreas Joseph Krogh wrote:
[snip]
 BEGIN;
 ALTER TABLE person ADD CONSTRAINT person_pk PRIMARY KEY (entity_id);
 alter table person drop constraint person_entity_id_key CASCADE;
 alter table phone add CONSTRAINT phone_fk FOREIGN KEY (person_entity_id) 
REFERENCES person(entity_id);
 alter table address add CONSTRAINT address_fk FOREIGN KEY (person_id) 
REFERENCES person(entity_id);
 COMMIT;  
Yea, I was hoping to avoid having to manually add the FK's to the referencing 
tables (34).
Is there really no way to accomplish this without DROP CONSTRAINT ... 
 CASCADE, hacking the system-catalogs or something?
 
Thanks.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com

Re: [GENERAL] Replacing an existing unique constraint (not UNIQUE INDEX) with primary key

2016-12-20 Thread Achilleas Mantzios


On 20/12/2016 11:43, Andreas Joseph Krogh wrote:

Hi all.
For historical reasons I have a table which at first had an "id"-column (the PK) and 
later got an "entity_id"-column (which is a UNIQUE CONSTRAINT).
I'm now trying to get rid of the "id"-column and make the "entity_id"-column the new PK. The tricky part is that both of these columns are referenced as FK's from /many/ tables, so 
disabling/removing FKs is not so easy. I'm facing a problem when issuing:

ALTER TABLE personADD PRIMARY KEY USING INDEX person_entity_id_key;
ERROR:  index "person_entity_id_key" is already associated with a constraint
A full example of what I'm trying to do (replacing the PK of the 
"person"-table) is here:

DROP TABLE IF EXISTS phone;
DROP TABLE IF EXISTS address;
DROP TABLE IF EXISTS person;
CREATE TABLE person (
 idBIGINT PRIMARY KEY,
 entity_idBIGINT NOT NULL UNIQUE,
 name VARCHAR NOT NULL );

CREATE TABLE address (
 idBIGINT PRIMARY KEY,
 person_idBIGINT NOT NULL REFERENCES person (id)
);

CREATE TABLE phone (
 idBIGINT PRIMARY KEY,
 person_entity_idBIGINT NOT NULL REFERENCES person (entity_id),
 numberVARCHAR NOT NULL );

INSERT INTO person (id, entity_id,name)VALUES (1,101,'Andreas'), 
(2,102,'Santa');
INSERT INTO address (id, person_id)VALUES (1,1), (2,2);
INSERT INTO phone (id, person_entity_id, number)VALUES (1,101,'1800555123'), 
(2,102,'1800555456');

-- Drop the deprecated foreign key on address ALTER TABLE address
 DROP CONSTRAINT address_person_id_fkey;

-- Update address and make person_id point to person.entity_id instead of 
person.id UPDATE address a
SET person_id = p.entity_id
FROM person p
WHERE p.id = a.person_id;
ALTER TABLE address
 ADD FOREIGN KEY (person_id)REFERENCES person (entity_id);

-- Drop the deprecated id-column ALTER TABLE person
 DROP COLUMN id;

-- Try to make new PK using the UNIQUE CONSTRAINT person_entity_id_key ALTER 
TABLE person
 ADD PRIMARY KEY USING INDEX person_entity_id_key;
ERROR:  index "person_entity_id_key" is already associated with a constraint

BEGIN;
ALTER TABLE person ADD CONSTRAINT person_pk PRIMARY KEY (entity_id);
alter table person drop constraint person_entity_id_key CASCADE;
alter table phone add CONSTRAINT phone_fk FOREIGN KEY (person_entity_id) 
REFERENCES person(entity_id);
alter table address add CONSTRAINT address_fk FOREIGN KEY (person_id) 
REFERENCES person(entity_id);
COMMIT;



I see that if I had declared person.entity_id without the UNIQUE-keyword and 
instead created a UNIQUE INDEX:
create UNIQUE INDEX person_entity_id_keyon person(entity_id);
Then the ADD PRIMARY KEY USING INDEX command would have succeeded.
I have lots of queries which have GROUP BY person.id which now should use GROUP 
BY person.entity_id, and not having to also list all other columns selected 
from the person-table.
How do I proceed with this?
Thanks.
--
*Andreas Joseph Krogh*
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 




--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

[GENERAL] Replacing an existing unique constraint (not UNIQUE INDEX) with primary key

2016-12-20 Thread Andreas Joseph Krogh

Hi all.
 
For historical reasons I have a table which at first had an "id"-column (the 
PK) and later got an "entity_id"-column (which is a UNIQUE CONSTRAINT).
 
I'm now trying to get rid of the "id"-column and make the "entity_id"-column 
the new PK. The tricky part is that both of these columns are referenced as 
FK's from many tables, so disabling/removing FKs is not so easy. I'm facing a 
problem when issuing:
ALTER TABLE person ADD PRIMARY KEY USING INDEX person_entity_id_key; 
ERROR:  index "person_entity_id_key" is already associated with a constraint

 
A full example of what I'm trying to do (replacing the PK of the 
"person"-table) is here:

DROP TABLE IF EXISTS phone; DROP TABLE IF EXISTS address; DROP TABLE IF EXISTS 
person;CREATE TABLE person ( id BIGINT PRIMARY KEY, entity_id BIGINT NOT NULL 
UNIQUE, name VARCHAR NOT NULL ); CREATE TABLE address ( id BIGINT PRIMARY KEY, 
person_idBIGINT NOT NULL REFERENCES person (id) ); CREATE TABLE phone ( id 
BIGINT PRIMARY KEY, person_entity_id BIGINT NOT NULL REFERENCES person 
(entity_id), numberVARCHAR NOT NULL ); INSERT INTO person (id, entity_id, name) 
VALUES(1, 101, 'Andreas'), (2, 102, 'Santa'); INSERT INTO address (id, 
person_id)VALUES (1, 1), (2, 2); INSERT INTO phone (id, person_entity_id, 
number)VALUES (1, 101, '1800555123'), (2, 102, '1800555456'); -- Drop the 
deprecated foreign key on addressALTER TABLE address DROP CONSTRAINT 
address_person_id_fkey;-- Update address and make person_id point to 
person.entity_id instead of person.idUPDATE address a SET person_id = 
p.entity_idFROM person p WHERE p.id = a.person_id; ALTER TABLE address ADD 
FOREIGN KEY(person_id) REFERENCES person (entity_id); -- Drop the deprecated 
id-columnALTER TABLE person DROP COLUMN id; -- Try to make new PK using the 
UNIQUE CONSTRAINT person_entity_id_keyALTER TABLE person ADD PRIMARY KEY USING 
INDEXperson_entity_id_key;  ERROR:  index "person_entity_id_key" is already 
associated with a constraint 
  
  
I see that if I had declared person.entity_id without the UNIQUE-keyword and 
instead created a UNIQUE INDEX:
create UNIQUE INDEX person_entity_id_key on person(entity_id);  
Then the ADD PRIMARY KEY USING INDEX command would have succeeded.
 
I have lots of queries which have GROUP BY person.id which now should use 
GROUP BY person.entity_id, and not having to also list all other columns 
selected from the person-table.
 
How do I proceed with this?

 
Thanks.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com

Re: [GENERAL] How well does PostgreSQL 9.6.1 support unicode?

Re: [GENERAL] How well does PostgreSQL 9.6.1 support unicode?

Re: [GENERAL] pg_repack and Postgres versions > 9.4

Re: [GENERAL] How well does PostgreSQL 9.6.1 support unicode?

Re: [GENERAL] How well does PostgreSQL 9.6.1 support unicode?

Re: [GENERAL] How well does PostgreSQL 9.6.1 support unicode?

[GENERAL] How well does PostgreSQL 9.6.1 support unicode?

Re: [GENERAL] pg_repack and Postgres versions > 9.4

Re: [GENERAL] pg_repack and Postgres versions > 9.4

Re: [GENERAL] pgAdmin 4 - auto disconnect

Re: [GENERAL] Replacing an existing unique constraint (not UNIQUE INDEX) with primary key

Re: [GENERAL] Replacing an existing unique constraint (not UNIQUE INDEX) with primary key

Re: [GENERAL] Replacing an existing unique constraint (not UNIQUE INDEX) with primary key

Re: [GENERAL] Replacing an existing unique constraint (not UNIQUE INDEX) with primary key

Re: [GENERAL] Replacing an existing unique constraint (not UNIQUE INDEX) with primary key

[GENERAL] Replacing an existing unique constraint (not UNIQUE INDEX) with primary key

16 matches

Site Navigation

Mail list logo

Footer information