Questions tagged [citext]
11 questions
7
votes
1 answer
Where do you find the citext module in postgres 9.3 on ubuntu 14.04?
So I'm trying to use citext, but it appears to be MIA from ubuntu 14.04 postgres install. Where can you find this mythical beast called citext for postgresql 9.3?
project_test=> CREATE TABLE "customers_addresstype" (
project_test(> "id" serial…
boatcoder
- 355
- 4
- 12
7
votes
1 answer
Can I change CITEXT columns to VARCHAR without encountering any surprise difficulties?
I went overboard on CITEXT columns on a particularly large table in my application. I would like to back some of these out as it's confusing how to trigger lookups on the desired indexes.
My question is, can I do this without encountering any major…
RubyRedGrapefruit
- 345
- 9
- 18
5
votes
2 answers
Why does a comparison between CITEXT and TEXT fail?
As expected, when doing an equality test like this, the comparison succeeds:
CREATE TABLE citext_test (
value citext PRIMARY KEY
);
INSERT INTO citext_test VALUES ('one');
INSERT INTO citext_test VALUES ('two');
SELECT * FROM citext_test…
antsyawn
- 265
- 1
- 3
- 7
5
votes
1 answer
How do I resolve Postgresql error, 'no collation was derived for column "foo" with collatable type citext'?
Just recently I upgraded from Postgresql 8.4.11 to 9.1.3. I used pg_dump in the process. Now I'm getting an error:
ERROR: no collation was derived for column "vin" with collatable type citext
HINT: Use the COLLATE clause to set the collation…
Evan Carroll
- 65,432
- 50
- 254
- 507
4
votes
3 answers
Expression index on a citext column ignored, why?
Running on RDS with about 32M rows.
PostgreSQL 11.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit
Also testing locally on macOS with about 8M rows.
PostgreSQL 11.5 on x86_64-apple-darwin16.7.0, compiled by…
Morris de Oryx
- 939
- 6
- 18
3
votes
1 answer
PostgreSQL performance citext
I'm trying to determine the performance characteristics of citext. In Microsoft SQL Server, the nvarchar(max) has performance hits when there is excessive memory grants required to use it versus something like a varchar(50). There are other…
cdm
- 41
- 3
3
votes
2 answers
can't install citext extension: could not access file "$libdir/citext": No such file or directory
I'm trying to get citext available as a type in a specific database. The Postgres database server is 9.4. I see it on the server in the pg_catalog's pg_available_extensions view.
From the psql console I try to install it and get this error:
create…
superbAfterSemperPhi
- 131
- 1
- 4
1
vote
1 answer
Can I modify a column's data type against a live table in Postgres?
I have a sparse matrix table where every attribute is citext. There are some indexes built against a few of the fields.
I want to change these columns back to varchar.
Can I do this against a live production table, or do I need to bring down…
RubyRedGrapefruit
- 345
- 9
- 18
1
vote
0 answers
Postgresql citext datatype unique constraint fail
I need to create a unique constraint on citext column, but it fails.
Here is the example:
create table test (name citext);
ALTER TABLE ONLY test
ADD CONSTRAINT test_name_uq UNIQUE (name);
insert into test(name) values ('SIMIT');…
Banu Akkus
- 389
- 1
- 3
- 11
1
vote
1 answer
How does PostgreSQL's citext type affect string comparisons made using LOWER()?
The impetus for my question is that I had hoped that PostgreSQL would behave consistently when selecting from citext columns, regardless of whether or not the string to be matched is wrapped in one or more instances of lower() (any such wrapping is…
Ben Johnson
- 133
- 6
0
votes
1 answer
WorkAround for PHP PDO(with libpq V 9.1.4) binding for use of CITEXT?
The scenario
Two systems(not server) running PHP and PostgreSQL with the following versions
Fedora 15:
PHP
PHP 5.3.13 (cli) (built: May 9 2012 14:38:35)
Copyright (c) 1997-2012 The PHP Group
Zend Engine v2.3.0, Copyright (c) 1998-2012 Zend…
ThinkingMonkey
- 615
- 2
- 7
- 19