[GENERAL] Invalid client charset when using TDS_FDW

Hi, I am trying to connect to a MSSQL database via the tds_fdw extension.  I've installed the extension version 1.0.8 from BIGSQL on a Windows machine. I get a client charset invalid though, and I'm not sure where it is set or what to set it to: config.c:886:Setting tds version to 7.3

Re: [GENERAL] Migrating money column from MS SQL Server to Postgres

On 11/9/2017 8:19 AM, Merlin Moncure wrote: On Thu, Nov 9, 2017 at 8:22 AM, Adam Brusselback wrote: Since you are migrating data into a staging table in PostgreSQL, you may set the field data type as TEXT for each field where you have noticed or anticipate issues.

Re: [GENERAL] Postgresql and github

On 11/9/2017 3:27 AM, Poul Kristensen wrote: How come that Postgresql is not present in a github with latest release? What do you mean?  Is this not what you're referring to:     https://github.com/postgres/postgres/releases ? Igal Sapir Lucee Core Developer Lucee.org

Re: [GENERAL] Migrating money column from MS SQL Server to Postgres

On 11/8/2017 6:25 PM, Igal @ Lucee.org wrote: On 11/8/2017 5:27 PM, Allan Kamau wrote: Maybe using NUMERIC without explicitly stating the precision is recommended. This would allow for values with many decimal places to be accepted without truncation. Your field may need to capture very small

Re: [GENERAL] Migrating money column from MS SQL Server to Postgres

On 11/8/2017 5:27 PM, Allan Kamau wrote: Maybe using NUMERIC without explicitly stating the precision is recommended. This would allow for values with many decimal places to be accepted without truncation. Your field may need to capture very small values such as those in bitcoin trading or

Re: [GENERAL] Migrating money column from MS SQL Server to Postgres

Thank you all for your help: On 11/8/2017 4:45 PM, Tom Lane wrote: "Igal @ Lucee.org" <i...@lucee.org> writes: The value in the offending insert is:  0.0 Why does Postgres decide that 0.0 is "double precision" (which is a weird name in my opinion -- why can't it jus

[GENERAL] Migrating money column from MS SQL Server to Postgres

Hello, I am migrating a database from MS SQL Server to Postgres. I have a column named "discount" of type money in SQL Server.  I created the table in Postgres with the same name and type, since Postgres has a type named money, and am transferring the data by using PDI (Pentaho Data

Re: [GENERAL] Building tds_fdw Extension for Windows 64bit

On 11/2/2017 8:40 AM, Tom Lane wrote: It looks to me like MSVC is complaining about the PGDLLEXPORT markings that tds_fdw.c has on the function definitions (not the extern declarations). In the core code we only put PGDLLEXPORT in extern declarations ... so try keeping it in the externs and

Re: [GENERAL] Building tds_fdw Extension for Windows 64bit

On 11/2/2017 1:05 AM, Laurenz Albe wrote: Igal @ Lucee.org wrote: After reading Craig's excellent blog post at https://blog.2ndquadrant.com/compiling-postgresql-extensions-visual-studio-windows/ I decided to try and build a real extension - tds_fdw. I've set it up in Visual Studio Community

[GENERAL] Building tds_fdw Extension for Windows 64bit

Hello, After reading Craig's excellent blog post at https://blog.2ndquadrant.com/compiling-postgresql-extensions-visual-studio-windows/ I decided to try and build a real extension - tds_fdw. I've set it up in Visual Studio Community 2017, but am getting the following errors: Error   

Re: [GENERAL] tgrm index for word_similarity

On 10/21/2017 5:01 AM, Arthur Zakirov wrote: PostgreSQL doesn't use index scan with functions within WHERE clause. So you always need to use operators instead. You can try <% operator and pg_trgm.word_similarity_threshold variable: =# SET pg_trgm.word_similarity_threshold TO 0.1; =# SELECT

Re: [GENERAL] tgrm index for word_similarity

On 10/19/2017 4:54 PM, Igal @ Lucee.org wrote: I want to use Postgres for a fuzzy auto-suggest search field.  As the user will be typing their search phrase, Postgres will show a list of items that fuzzy-matches what they typed so far, ordered by popularity (ntile(20)) and distance, i.e. 1

[GENERAL] tgrm index for word_similarity

Hello, I want to use Postgres for a fuzzy auto-suggest search field.  As the user will be typing their search phrase, Postgres will show a list of items that fuzzy-matches what they typed so far, ordered by popularity (ntile(20)) and distance, i.e. 1 - word_similarity(). I created a

Re: [GENERAL] Is it OK to create a directory in PGDATA dir

On 10/19/2017 1:25 PM, Tomas Vondra wrote: On 10/19/2017 09:58 PM, rakeshkumar464 wrote: In the container world, sometime the only persistent storage path (that is, storage outside container world) is PGDATA.> I don't want to be the "You're doing it wrong!" guy, but you're doing it wrong. If a

Re: [GENERAL] Using Variables in Queries

On 10/19/2017 12:14 PM, Tom Lane wrote: "Igal @ Lucee.org" <i...@lucee.org> writes: My real query is for similarity here, so I'm testing different functions with the same value, e.g. SELECT item_name , similarity('red widget', item_name) , similarity(item_n

Re: [GENERAL] Using Variables in Queries

On 10/19/2017 8:44 AM, David G. Johnston wrote: ​PREPARE sqlquery AS​ SELECT * FROM products WHERE col1 LIKE $1 OR col2 LIKE $1;  EXECUTE sqlquery('red widget'); This works, but requires `DEALLOCATE sqlquery` when you want to update it from what I've seen which is not very friendly.  

[GENERAL] Using Variables in Queries

Hello, In other database servers, which I'm finally dropping in favor of Postgres, I can do the following (mind you that this is for illustration only, I do not actually write queries like that): DECLARE @query varchar(64) = 'red widget'; SELECT * FROM products WHERE col1 LIKE @query    OR

Re: [GENERAL] Finally upgrading to 9.6!

On 10/18/2017 7:45 AM, Ron Johnson wrote: On 10/18/2017 09:34 AM, Igal @ Lucee.org wrote: A bit off-topic here, but why upgrade to 9.6 when you can upgrade to 10.0? There's no way we're going to put an x.0.0 version into production. Then think of it as 9.7.0 but with an easier name

Re: [GENERAL] Finally upgrading to 9.6!

On 10/18/2017 6:24 AM, Ron Johnson wrote: On 10/17/2017 11:17 AM, Tom Lane wrote: Ron Johnson writes: Where can I look to see (roughly) how much more RAM/CPU/disk needed when moving from 8.4 and 9.2? It's entirely possible you'll need *less*, as you'll be absorbing

Re: [GENERAL] Delete Duplicates with Using

FYI, On 10/16/2017 8:58 AM, Igal @ Lucee.org wrote: test=*# with keep as (select max(ctid) as ctid from dubletten group by c1,c2,c3) delete from dubletten where ctid not in (select ctid from keep); I like this solution, but would using a subquery be much slower than the implicit join

Re: [GENERAL] Delete Duplicates with Using

Andreas, On 10/15/2017 11:53 PM, Andreas Kretschmer wrote: other solution, using the CTID-column: (rows with (1,1,1) and (5,5,5) are identical) test=*# select * from dubletten ;  c1 | c2 | c3 ++   1 |  1 |  1   1 |  1 |  1   1 |  2 |  3   2 |  3 |  4   3 |  4 |  5   4 |  5 |  5  

Re: [GENERAL] Adding identity column to a non-empty table

On 10/15/2017 6:42 PM, Melvin Davidson wrote: On Sun, Oct 15, 2017 at 9:09 PM, Igal @ Lucee.org <i...@lucee.org <mailto:i...@lucee.org>> wrote: Melvin, On 10/15/2017 5:56 PM, Melvin Davidson wrote: On 10/15/2017 4:01 PM, Igal @ Lucee.org wrote:

Re: [GENERAL] Adding identity column to a non-empty table

Melvin, On 10/15/2017 5:56 PM, Melvin Davidson wrote: On Sun, Oct 15, 2017 at 8:24 PM, Igal @ Lucee.org <i...@lucee.org <mailto:i...@lucee.org>> wrote: On 10/15/2017 4:01 PM, Igal @ Lucee.org wrote: Hello, I'm trying to add an identity column to a ta

Re: [GENERAL] Adding identity column to a non-empty table

On 10/15/2017 4:01 PM, Igal @ Lucee.org wrote: Hello, I'm trying to add an identity column to a table that has records (previously had a bigserial column which I removed):   ALTER TABLE event_log ADD COLUMN r_id BIGINT GENERATED BY DEFAULT AS IDENTITY; But I'm getting an error `column r_id

[GENERAL] Adding identity column to a non-empty table

Hello, I'm trying to add an identity column to a table that has records (previously had a bigserial column which I removed):   ALTER TABLE event_log ADD COLUMN r_id BIGINT GENERATED BY DEFAULT AS IDENTITY; But I'm getting an error `column r_id contains null values`. How can I add the column

Re: [GENERAL] Delete Duplicates with Using

On 10/14/2017 12:32 AM, legrand legrand wrote: DELETE FROM table_with_duplicates AS T1 USING table_with_duplicates AS T2 WHERE T1.column_1 = T2.column_1 AND T1.column_2 = T2.column_2 AND T1.column_3 = T2.column_3 AND T1.row_num < T2.row_num Thank you, I

[GENERAL] Delete Duplicates with Using

Hello, I run the SQL query below to delete duplicates from a table.  The subquery is used to identify the duplicated rows (row_num is a BIGSERIAL column). /** delete older copies of duplicates */ DELETE FROM table_with_duplicatesAS T WHERE row_num IN (     SELECT     T1.row_num     FROM   

Re: [GENERAL] Multiple Schemas vs. Multiple Databases

On 10/13/2017 12:47 PM, John R Pierce wrote: On 10/13/2017 12:29 PM, Igal @ Lucee.org wrote: The main things that I need to do is:   a) Be able to backup/restore each "part" separately.  Looks like pg_dump allows that for schemas via the --schema=schema argument.   b) Be abl

[GENERAL] Multiple Schemas vs. Multiple Databases

Hello, I have read quite a few articles about multiple schemas vs. multiple databases, but they are all very generic so I wanted to ask here for a specific use case: I am migrating a Web Application from MS SQL Server to PostgreSQL.  For the sake of easier maintenance, on SQL Server I have

Re: [GENERAL] Permissions for Web App

On 10/13/2017 11:21 AM, David G. Johnston wrote: On Fri, Oct 13, 2017 at 11:03 AM, Igal @ Lucee.org <i...@lucee.org <mailto:i...@lucee.org>>wrote: You mean that if I execute the ALTER DEFAULT command above as user `postgres` then only tables created by user `postgre

Re: [GENERAL] Permissions for Web App

Stephen, On 10/10/2017 6:14 AM, Stephen Frost wrote: For future reference and for the benefit of others, the command that I ran is:   ALTER DEFAULT PRIVILEGES IN SCHEMA public     GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO webapp; Note that DEFAULT PRIVILEGES apply to a specific

Re: [GENERAL] Permissions for Web App

On 10/9/2017 10:51 AM, David G. Johnston wrote: On Mon, Oct 9, 2017 at 9:44 AM, Igal @ Lucee.org <i...@lucee.org <mailto:i...@lucee.org>>wrote: But I want to give that role permissions on future tables since I add new tables and drop/recreate current ones. ​ALTER DEFAUL

[GENERAL] Permissions for Web App

Hello, I created a role named `webapp` as follows:   CREATE ROLE webapp WITH LOGIN PASSWORD 'changeme'; While in development, I want to give that role permissions on all tables in schema public.  So far I've been using the following command, which works on existing tables:   GRANT ALL

Re: [GENERAL] Functions and Parentheses

On 10/6/2017 3:10 PM, Joshua D. Drake wrote: On 10/06/2017 02:33 PM, Tom Lane wrote: "Igal @ Lucee.org" <i...@lucee.org> writes: How come `current_date` has no parenthesis but `clock_timestamp()` does? Because the SQL standard says that CURRENT_DATE doesn't h

[GENERAL] Functions and Parentheses

Hi, Is current_date a function?  It's a bit puzzling to me since there are no parentheses after it, i.e.   SELECT current_date; And not   SELECT current_date();  -- syntax error How come `current_date` has no parenthesis but `clock_timestamp()` does? Thanks, Igal Sapir Lucee Core

Re: [GENERAL] Download 9.6.3 Binaries

Hi, On 6/23/2017 11:47 AM, Joshua D. Drake wrote: Looks like EnterpriseDB is behind. You can try BigSQL: https://www.bigsql.org/postgresql/installers/ Thanks, but I prefer the archive version rather than the installer. Contrary to "rumors" on the internet, it is very easy to install Postgres

[GENERAL] Download 9.6.3 Binaries

Hello, I expected to find binaries for 9.6.3 at https://www.enterprisedb.com/download-postgresql-binaries but I only see 9.6.2. Am I looking at the wrong place? Thanks, Igal Sapir Lucee Core Developer Lucee.org

Re: [GENERAL] BST Time Zone Discrepancy

Thank you, Steve and Adrian, for clarifying. Igal Sapir Lucee Core Developer Lucee.org

Re: [GENERAL] BST Time Zone Discrepancy

Tom, Thank you for your reply: On 2/6/2017 12:18 PM, Tom Lane wrote: This is controlled by the timezone_abbreviations file, which if you haven't changed it lists: # CONFLICT! BST is not unique # Other timezones: # - BST: Bougainville Standard Time (Papua New Guinea) BST 3600 D #

[GENERAL] BST Time Zone Discrepancy

I've been having some issues with using BST as a timezone, so I checked the Postgres catalogs and found a discrepancy. I expected it to be British Summer Time, which is a Daylight Saving Time in England and has a 1-hour UTC offset. In pg_timezone_abbrevs I see a 1-hr offset and is_dst set to

[GENERAL] RPM Package of PostgreSQL 9.6.1 for CentOS

Hi, I see the package for 9.6.0 at https://yum.postgresql.org/9.6/redhat/rhel-7-x86_64/ pgdg-centos96-9.6-3.noarch.rpm Should there be one for 9.6.1? When I try to install postgresql96-9.6.1-1PGDG.rhel7.x86_64.rpm I get dependency errors, so I'm looking for a package like the one above

Re: [GENERAL] recordings of pgconf us 2016

Funny. I was just looking for that myself. I would expect it to go on their channel at https://www.youtube.com/channel/UCsJkVvxwoM7R9oRbzvUhbPQ but so far nothing from this year. PGCon has published their 2016 recordings on their channel:

Re: [GENERAL] Cannot start the PostgreSQL service

On 1/30/2016 12:06 PM, Adrian Klaver wrote: On 01/30/2016 11:01 AM, David Unsworth wrote: Ccing list, so more eyes can see. Assuming this is a Windows machine, what version of the OS? Windows 7 Home Premium Did you check the Windows Event Viewer? When a service fails to start you should

Re: [GENERAL] Cannot start the PostgreSQL service

On 1/30/2016 12:10 PM, Igal @ Lucee.org wrote: Did you check the Windows Event Viewer? When a service fails to start you should see the details there in the Application log errors. Sorry, I just realized that you had this: 2016-01-25 18:45:19 GMTFATAL: the database system is starting up

Re: [GENERAL] Tutorial on How to Compile PostgreSQL 9.5 for Windows 64bit

On 1/25/2016 10:51 PM, Thomas Kellerer wrote: Very helpful, thanks. Great, thank you for the feedback. Is there any tutorial on how to compile extensions for Windows? There are many interesting extensions where no Windows binaries are available, so it would be really helpful if I could

[GENERAL] Tutorial on How to Compile PostgreSQL 9.5 for Windows 64bit

Hi Everybody, I have posted a video tutorial on How to Compile PostgreSQL 9.5 for Windows 64bit https://www.youtube.com/watch?v=-BJmuZT5IPE It was quite difficult for me to figure it out, so hopefully it will make life easier for the next guy (or gal). -- Igal Sapir Lucee Core Developer

Re: [GENERAL] plv8 binaries for PostgreSQL 9.5 on Windows

On 1/19/2016 2:56 PM, Regina Obe wrote: On 01/11/2016 05:50 PM, Igal @ Lucee.org wrote: Where can I find binaries for plv8 for PostgreSQL 9.5 for Windows? I've posted PostgreSQL 9.5 Windows binaries for PLV8 http://www.postgresonline.com/journal/archives/360-PLV8-binaries-for-Postgre SQL-9.5

Re: [GENERAL] plv8 binaries for PostgreSQL 9.5 on Windows

On 1/13/2016 8:25 AM, Igal @ Lucee.org wrote: On 1/12/2016 12:57 PM, Adrian Klaver wrote: On 01/11/2016 05:50 PM, Igal @ Lucee.org wrote: Where can I find binaries for plv8 for PostgreSQL 9.5 for Windows? The only Windows binaries I could find where for 9.4, here: http

Re: [GENERAL] plv8 binaries for PostgreSQL 9.5 on Windows

On 1/12/2016 12:57 PM, Adrian Klaver wrote: On 01/11/2016 05:50 PM, Igal @ Lucee.org wrote: Where can I find binaries for plv8 for PostgreSQL 9.5 for Windows? The only Windows binaries I could find where for 9.4, here: http://www.postgresonline.com/journal/archives/341-PLV8-binaries

[GENERAL] Building PostgreSQL in Eclipse on Windows

Can anyone help me figure out how to run/debug PostgreSQL in Eclipse on Windows? This is a cross post with http://stackoverflow.com/questions/34733991/building-postgresql-in-eclipse-on-windows : I'm trying to build|PosgreSQL|in Eclipse CDT Mars on Windows using the MinGW-64 gcc 5.3.0 I

[GENERAL] plv8 binaries for PostgreSQL 9.5 on Windows

Where can I find binaries for plv8 for PostgreSQL 9.5 for Windows? Thanks, -- Igal Sapir Lucee Core Developer Lucee.org

Re: [GENERAL] Building PostgreSQL in Eclipse on Windows

On 1/11/2016 6:21 PM, John R Pierce wrote: wait, are you trying to USE postgres on Eclipse on Windows, or BUILD it ?I would use the enterprisedb build of postgres-for-windows, and then you just need to install the appropriate postgresql jdbc jar file into Eclipse to be able to use it from

[GENERAL] pg_upgrade on Windows

hi guys, I am having issues with pg_upgrade on Windows. I have posted a question on StackOverflow -- at http://stackoverflow.com/questions/34664236/pg-upgrade-on-windows-cannot-write-to-log-file-pg-upgrade-internal-log -- copied below for convenience: I'm trying to run pg_upgrade on