From: | Igal Sapir <igal(at)lucee(dot)org> |
---|---|
To: | Brent Wood <pcreso(at)yahoo(dot)com> |
Cc: | pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Postgres for SQL Server users |
Date: | 2019-05-09 04:13:15 |
Message-ID: | CA+zig09LDTkT-dT-9oGzM4GpTWPjhXfxsaNBqaHRP+1muUKiyQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Brent,
On Tue, May 7, 2019 at 12:42 PM Brent Wood <pcreso(at)yahoo(dot)com> wrote:
> I have not used SS for spatial data, but I don't have a Postgres database
> without Postgis installed. The OSGEO ecosystem and synergies with other
> FOSS GIS tools is fantastic.
>
> And it does not stop with the Postgis extension. For time series data
> (anything from fleet management to sensor data) Postgres has the (new)
> TimescaleDB extension. I ran this very effectively with a 600,000,000
> record database of sensor readings from a research vessel - on a $400
> laptop (with an SSD) for testing/prototyping. The sensor data was stored in
> Timescaledb Hypertables & the location data in Postgis geometry columns in
> those tables. Significantly better performance than native Postgres.
>
Excellent information with impressive numbers.
>
> Also consider language support for database functions... pl/R supports
> some very nice capabilities, especially supporting websites. Instead if
> running a Postgres query to return the data to plot via the web page, or
> storing static plots in your CMS that need updating when you get new data,
> you can use Postgres functions in pl/R to render the plot of the data in a
> file, and return the name of the file. The web site does no rendering, just
> invokes the SQL & displays the file that is returned. So the DB can return
> the data and/or the graphic. Back up your database & back up your
> functions. This paradigm can work very effectively...
>
Is there any tutorial/example code for rendering pl/R images on a website?
Cool feature
>
> Generally, the FOSS ecosystem around Postgres offers an incredible array
> of tools and capabilities that I don't think any other db - FOSS or not -
> can provide. I have had limited exposure to Oracle, SQL Server, Sybase,
> Empress, Teradata, Netezza, DB2, Sqlite/Spatialite, Interbase & Informix.
> Of these, Postgres & Sqlite3 (which one depends on use cases) are all I use
> these days.
>
Yep. agreed.
Thanks,
Igal
>
>
> On Tuesday, May 7, 2019, 5:36:00 PM GMT+12, Tony Shelver <
> tshelver(at)gmail(dot)com> wrote:
>
>
> I have to agree on the geospatial (GIS) features.
> I converted from SQL Server to Postgresql for our extended tracking
> database. The SS geospatial feature set doesn't seem nearly as robust or
> complete or perfoirmant as that supplied by PostGIS.
> The PostGIS ecosystem of open source / 3rd party tools is also far bigger,
> for anything to do with mapping. Openstreetmaps.org stores their world
> dataset on Postgresql / PostGIS, and there a ton of mostly open
> source-based tools and organizations that work with it or any other PostGIS
> data to provide a complete GIS solution.
>
> My first sS implementation had me backing out of storing geographic points
> in the relevant SQL Server datatype as the performance hit during loading
> was just too big. Doing the same thing in Postgresql / PostGIS is nardly
> noticeable.
>
> Another feature in Postgres is that you are not restricted to just plpgsql
> as an internal procedural language.
>
> I am not an expert, but it also seems far easier to create, install and
> work with major extensions to Postgresql than SQL Server. I found
> installing the GIS featureset in SS to be a bit of a pain back oin the
> day..
>
> On Tue, 7 May 2019 at 00:53, Michel Pelletier <pelletier(dot)michel(at)gmail(dot)com>
> wrote:
>
> On Mon, May 6, 2019 at 2:49 PM Adam Brusselback <adambrusselback(at)gmail(dot)com>
> wrote:
>
> I think the main "gotcha" when I moved from SQL Server to Postgres was I
> didn't even realize the amount of in-line t-sql I would use to just get
> stuff done for ad-hoc analysis. Postgres doesn't have a good way to emulate
> this. DO blocks cannot return resultsets, so short of creating a function
> and dropping it, it's not possible to get the same workflow.
>
>
> Just ruminating here, and this has probably already been discussed in the
> past, but I've always wanted something like a 'SELECT DO [LANGUAGE ...]
> RETURNS rettype | TABLE (...) $$ RETURN [NEXT | QUERY] ... $$; but haven't
> had any serious problem with creating/dropping functions like you mentioned.
>
> -Michel
>
>
> The lack of GUI tooling was also a huge "whoa" moment for me, which I
> still grapple with.
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | M Tarkeshwar Rao | 2019-05-09 04:51:02 | integrate Postgres Users Authentication with our own LDAP Server |
Previous Message | Julie Nishimura | 2019-05-08 23:35:09 | Re: postgresql 9.4 restart |