Questions tagged [citext]
The citext tag has no usage guidance.
11 questions
1
vote
1
answer
2k
views
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 ...
4
votes
3
answers
969
views
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.
...
- 909
7
votes
1
answer
6k
views
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 ...
1
vote
0
answers
586
views
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);
...
- 359
3
votes
1
answer
2k
views
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 ...
- 41
1
vote
1
answer
2k
views
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 ...
- 133
4
votes
2
answers
5k
views
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 ...
- 255
3
votes
2
answers
4k
views
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 ...
7
votes
1
answer
7k
views
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 "...
- 355
0
votes
1
answer
2k
views
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 ...
- 615
5
votes
1
answer
1k
views
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
...
- 64.7k