Thread: crosstab function
I run Postgres 10.5. I understand that there is something called tablefunc and it includes a crosstab function. On Stack Overflow I learn that you import this function. But from where and how? The Postgres documentation is quite clear and intelligible to a retired English professor like me, but there is nothing in the Postgres documentation about how to do the import, and a search for ‘import modules’ yields nothing.
I tried to emulate a Stack overflow query that does what I want to do, but got an error message saying that the crosstab function doesn’t exist. I tried
CREATE EXTENSION IF NOT EXISTS tablefunc;
but it did nothing. It doesn’t seem to work as import statements in Python do
-----
Martin Mueller
Professor emeritus of English and Classics
Northwestern University
On 2/26/19 5:51 PM, Martin Mueller wrote: > I run Postgres 10.5. I understand that there is something called > tablefunc and it includes a crosstab function. On Stack Overflow I learn > that you import this function. But from where and how? The Postgres > documentation is quite clear and intelligible to a retired English > professor like me, but there is nothing in the Postgres documentation > about how to do the import, and a search for ‘import modules’ yields > nothing. tablefunc is a contrib module so we need to know something information about your Postgres setup: 1) Your OS ? 2) How did you install Postgres? > > I tried to emulate a Stack overflow query that does what I want to do, > but got an error message saying that the crosstab function doesn’t > exist. I tried > > CREATEEXTENSION IFNOTEXISTStablefunc; > > but it did nothing. It doesn’t seem to work as import statements in > Python do The above depends on the module code actually existing where CREATE EXTENSION can find it. Whether the module exists and where it exists depends on the answers to the questions above. > > ----- > > Martin Mueller > Professor emeritus of English and Classics > > Northwestern University > -- Adrian Klaver adrian.klaver@aklaver.com
>>>>> "Martin" == Martin Mueller <martinmueller@northwestern.edu> writes: Martin> I run Postgres 10.5. I understand that there is something Martin> called tablefunc and it includes a crosstab function. On Stack Martin> Overflow I learn that you import this function. But from where Martin> and how? The Postgres documentation is quite clear and Martin> intelligible to a retired English professor like me, but there Martin> is nothing in the Postgres documentation about how to do the Martin> import, and a search for ‘import modules’ yields nothing. Martin> I tried to emulate a Stack overflow query that does what I want Martin> to do, but got an error message saying that the crosstab Martin> function doesn’t exist. I tried Martin> CREATE EXTENSION IF NOT EXISTS tablefunc; Martin> but it did nothing. It doesn’t seem to work as import Martin> statements in Python do CREATE EXTENSION causes the functions to be defined in the current database; that's the only "import" that is needed. In psql, you can do this: \df+ *.crosstab* to see what functions of that name are defined and what schema they are in (should be "public" by default). Likewise \dx lists installed extensions in the current database. Remember that you need to do the CREATE EXTENSION command actually in the database in which you want to use the functions, not in any other database. Errors about functions not existing are usually caused by a problem with the number or type of parameters, since function names aren't unique (overloaded functions are allowed). You didn't tell us the actual error you got, so we can't say exactly what the problem is there. -- Andrew (irc:RhodiumToad)
Thank you for the prompt and clear answer. I work with a Mac (OS 10.4.3) and I used the PostgresApp to install it. I am
avery primitive user of Postgres and think of it as Microsoft Access on Steroids. I access it via Aqua Data, Studio,
butI don't think that makes any difference
On 2/26/19, 8:04 PM, "Adrian Klaver" <adrian.klaver@aklaver.com> wrote:
On 2/26/19 5:51 PM, Martin Mueller wrote:
> I run Postgres 10.5. I understand that there is something called
> tablefunc and it includes a crosstab function. On Stack Overflow I learn
> that you import this function. But from where and how? The Postgres
> documentation is quite clear and intelligible to a retired English
> professor like me, but there is nothing in the Postgres documentation
> about how to do the import, and a search for ‘import modules’ yields
> nothing.
tablefunc is a contrib module so we need to know something information
about your Postgres setup:
1) Your OS ?
2) How did you install Postgres?
>
> I tried to emulate a Stack overflow query that does what I want to do,
> but got an error message saying that the crosstab function doesn’t
> exist. I tried
>
> CREATEEXTENSION IFNOTEXISTStablefunc;
>
> but it did nothing. It doesn’t seem to work as import statements in
> Python do
The above depends on the module code actually existing where CREATE
EXTENSION can find it. Whether the module exists and where it exists
depends on the answers to the questions above.
>
> -----
>
> Martin Mueller
> Professor emeritus of English and Classics
>
> Northwestern University
>
--
Adrian Klaver
adrian.klaver@aklaver.com
On 2/26/19 6:10 PM, Martin Mueller wrote: > Thank you for the prompt and clear answer. I work with a Mac (OS 10.4.3) and I used the PostgresApp to install it. I ama very primitive user of Postgres and think of it as Microsoft Access on Steroids. I access it via Aqua Data, Studio, butI don't think that makes any difference My guess is PostgresApp does not include the tablefunc extension. Per Andrews instructions it would helpful to know what error you got when you tried to CREATE EXTENSION tablefunc; > > On 2/26/19, 8:04 PM, "Adrian Klaver" <adrian.klaver@aklaver.com> wrote: > > On 2/26/19 5:51 PM, Martin Mueller wrote: > > I run Postgres 10.5. I understand that there is something called > > tablefunc and it includes a crosstab function. On Stack Overflow I learn > > that you import this function. But from where and how? The Postgres > > documentation is quite clear and intelligible to a retired English > > professor like me, but there is nothing in the Postgres documentation > > about how to do the import, and a search for ‘import modules’ yields > > nothing. > > tablefunc is a contrib module so we need to know something information > about your Postgres setup: > > 1) Your OS ? > > 2) How did you install Postgres? > > > > > I tried to emulate a Stack overflow query that does what I want to do, > > but got an error message saying that the crosstab function doesn’t > > exist. I tried > > > > CREATEEXTENSION IFNOTEXISTStablefunc; > > > > but it did nothing. It doesn’t seem to work as import statements in > > Python do > > The above depends on the module code actually existing where CREATE > EXTENSION can find it. Whether the module exists and where it exists > depends on the answers to the questions above. > > > > > ----- > > > > Martin Mueller > > Professor emeritus of English and Classics > > > > Northwestern University > > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com > > -- Adrian Klaver adrian.klaver@aklaver.com
On 2/26/19 7:51 PM, Martin Mueller wrote:
Extensions are short bits of sql that create functions for you, and point to "shared object" executable files. Quite literally, it's extending postgres.
Thus, the actual executable file needs to be somewhere on the computer's hard drive (specifically, where Postgres expects it to be for your OS).
In the Linux world, for example, there's typically a package you install named something like postgresql96-contrib-9.6.9-blahblah.rpm. You'd install that using the OS' package manager, restart postgres and then CREATE EXTENSION should work.
You'll have to determine whether PostgreApp comes with "contribs". If not, you'll have to use another source for Pg, like MacPorts.
I run Postgres 10.5. I understand that there is something called tablefunc and it includes a crosstab function. On Stack Overflow I learn that you import this function. But from where and how? The Postgres documentation is quite clear and intelligible to a retired English professor like me, but there is nothing in the Postgres documentation about how to do the import, and a search for ‘import modules’ yields nothing.
I tried to emulate a Stack overflow query that does what I want to do, but got an error message saying that the crosstab function doesn’t exist. I tried
CREATE EXTENSION IF NOT EXISTS tablefunc;
but it did nothing. It doesn’t seem to work as import statements in Python do
Extensions are short bits of sql that create functions for you, and point to "shared object" executable files. Quite literally, it's extending postgres.
Thus, the actual executable file needs to be somewhere on the computer's hard drive (specifically, where Postgres expects it to be for your OS).
In the Linux world, for example, there's typically a package you install named something like postgresql96-contrib-9.6.9-blahblah.rpm. You'd install that using the OS' package manager, restart postgres and then CREATE EXTENSION should work.
You'll have to determine whether PostgreApp comes with "contribs". If not, you'll have to use another source for Pg, like MacPorts.
--
Angular momentum makes the world go 'round.
Angular momentum makes the world go 'round.
On 2/26/19 8:10 PM, Martin Mueller wrote: > Thank you for the prompt and clear answer. I work with a Mac (OS 10.4.3) and I used the PostgresApp to install it. I ama very primitive user of Postgres and think of it as Microsoft Access on Steroids. I access it via Aqua Data, Studio, butI don't think that makes any difference Access is an application design tool with a built-in engine that's good for storing "small" amounts of data. Postgresql is a database management system suitable for storing and replicating billions and billions and billions of large amounts of data, and analyzing it in a myriad of ways. It requires "administration from the command line", as you're discovering. Sincerely, Ron -- Angular momentum makes the world go 'round.
Professor Mueller! I believe that we met, long ago. I graduated from your department in 1984 where I worked closely with the wonderful, late Prof. Dipple.
Postgres.app is a very easy way to work with Postgres, and it does include support for tablefunc. If you ever want to check which extensions are installed, run this line:
select * from pg_available_extensions order by name;
Your code looks correct on the face of it:
CREATE EXTENSION IF NOT EXISTS tablefunc;
Or, if you have set up schemas other than the default "public", you can install into a specific schema:
CREATE EXTENSION IF NOT EXISTS tablefunc WITH SCHEMA extensions;
If you aren't already using custom schemas...I'll leave it alone for now.
As noted, you're installing into a specific database, so make sure that you've connected where you expect and are in the database you mean. It's fairly easy for a tool to default to something other than your custom database. If it's not clear from the UI, or you just feel like testing by hand, run this line:
SELECT current_database();
It's worth knowing that a Postgres extension is a packaging system. An extension may include C code, setup scripts, straight SQL, a variety of resources. Sometimes, you can open one up and harvest little bits of SQL you want. For details:
After a quick googling, it looks like you may be interested in textual analysis. If so, Postgres has a *lot* of tools that can be of assistance. Within Postgres.app, I can see at least the following:
citext
If you haven't noticed, and care, Postgres' default varchar/text field type is case-sensitive. Ugh. The citext extension is searchable case-insensitively out of the box. I use this for alpha/text fields when I don't care about case-sensitive searches. For where that is, read "everywhere".
fuzzystrmatch
Basic, name/word-matching fuzzy algorithms. The "phonetic" ones are not so great, but Levenshtein is quite good, if a bit expensive to run.
Full Text Search
Huge subject, lots of options, modern versions of Postgres are quite strong here.
unaccent
The description reads, "text search dictionary that removes accents." I haven't needed it, and wonder if specifying a collation might not work better?
pg_pgtrgm
N-grams of length 3. This is a fantastic tool. N-grams have proven themselves down the years for fuzzy string matching in multiple domains. I've mostly used it historically on name data, but it works well on larger text blocks as well. This holds up with many languages other than English. It's pretty easy to use this extension.
There's another appealing extension named pg_similarity that includes a huge range of text comparison and fuzzy ranking tools, but I do not know how to compile it for macOS or get it to run with Postgres.app. If you are interested in a specific algorithm, many are easily implemented in a SQL statement or stored function. For example, Jaccard (and similar) ranking metrics are produced arithmetically, so they're easy to reimplement.