6 ms·

Generally, I make use of file_fdw module of Postgres to achieve this. https://www.postgresql.org/docs/11/file-fdw.html https://www.postgresql.org/docs/11/file-

Generally, I make use of file_fdw module of Postgres to achieve this. https://www.postgresql.org/docs/11/file-fdw.html https://www.postgresql.org/docs/11/file-fdw.html

cube2222 7y ago
Didn't know about that, thanks! Is there a way to connect this to other databases, to basically have views of your redis/MySQL/other databases in postgres?
- truth_seeker 7y ago
  Yes sir, that too is supported. https://www.postgresql.org/docs/11/contrib-dblink-function.html https://www.postgresql.org/docs/11/contrib-dblink-function.h...
  - cube2222 7y ago
    Seems like it's solving part of the problems we're having really well, I'll have to read up more on it, thanks!
    - truth_seeker 7y ago
      Sure. You might also wanna check this: PG community extensions - https://pgxn.org/ https://pgxn.org/ There are a lot of open-source or free extensions to make PG as a data platform rather than just a RDBMS.
- jon-wood 7y ago
  Postgres Foreign Data Wrappers are an incredibly powerful tool. I've in the past built a basic data warehouse for a distributed system by pulling in read slaves from assorted service's databases as FDW sources to a central Postgres instance, after which Postgres allows you to treat them all as one big database.
brunoqc 7y ago
I wonder if you or the other Postgres users here could give me some tips. What would be the best way to use the data from a huge Excel file into my web apps? Currently I'm converting it to CSV and use `BULK INSERT` with MSSQL. I know that with MSSQL I can also use Excel files as "external database" but AFAIK, Excel files are not indexed and are super slow when used as an "external database". I wouldn't mind switching to Postgres. I actually prefer Postgres.
- michelpp 7y ago
  THe process is similar, you would convert to csv and then use the \copy command to bulk insert the data. In pg 12, the copy command has grown a lot of very useful features like WHERE filtering, so if you get the chance go for 12: https://www.cybertec-postgresql.com/en/tech-preview-improving-copy-and-bulkloading-in-postgresql-12/ https://www.cybertec-postgresql.com/en/tech-preview-improvin...
- deleted 7y ago
  [deleted]
- truth_seeker 7y ago
  This link covers it in detail: http://www.postgresqltutorial.com/import-csv-file-into-posgresql-table/ http://www.postgresqltutorial.com/import-csv-file-into-posgr...
- tempguy9999 7y ago
  As an MSSQL guy who rarely connects it to excel, if your workflow allows it consider pulling the entirety of the excel sheet into a table in the db and working on it just from there. What's huge for excel isn't for MSSQL. As a decades-long user of MSSQL guy who is getting sick of being rpeatedly done over by MS, could you explain why you prefer postgres? That would be incredibly interesting, TIA
  - sixdimensional 7y ago
    Not OP but, in my mind (having used both extensively), Postgres is basically the closest I feel you can get to MSSQL in the open source world, in terms of completeness of a fully functional relational database that scales - not to mention Postgres has tons of features that MSSQL has been playing catch up with for years. I like both of them personally. Postgres didn't used to have as strong of a showing on Windows, and not as fancy tools as Management Studio/Query Analyzer/Azure Data Studio, but I think today Postgres is absolutely viable to replace SQL Server if you want to switch to open source.
riku_iki 7y ago
Do fdw/standard connectors support pushdown predicates well? Documentation doesn't say much about that.
- truth_seeker 7y ago
  Yep. PG 9.5 and later supports it. Use "EXPLAIN VERBOSE" before your query to get more info.
just_myles 7y ago
My god. I didn't know postgres can do this. Thank you!
sixdimensional 7y ago
This is a helpful utility for sure - but beware, joins between foreign data sources and local ones can be slow and very un-optimized, especially with bigger data sources. I used to work for a company that made a data federation software platform. I am always surprised that people forget that management of external data is part of the SQL standard actually - SQL/MED ("SQL Management of External Data") [1]! [1] https://en.wikipedia.org/wiki/SQL/MED https://en.wikipedia.org/wiki/SQL/MED
- greggyb 7y ago
  Completely unrelated, but that URL really threw me for a loop. I am not used to seeing a slash in the article topic (section after '/wiki/').

Generally, I make use of file_fdw module of Postgres to achieve this. https://www.postgresql.org/docs/11/file-fdw.html https://www.postgresql.org/docs/11/file-fdw.html

cube2222 7y ago

Didn't know about that, thanks! Is there a way to connect this to other databases, to basically have views of your redis/MySQL/other databases in postgres?

truth_seeker 7y ago
Yes sir, that too is supported. https://www.postgresql.org/docs/11/contrib-dblink-function.html https://www.postgresql.org/docs/11/contrib-dblink-function.h...
- cube2222 7y ago
  Seems like it's solving part of the problems we're having really well, I'll have to read up more on it, thanks!
  - truth_seeker 7y ago
    Sure. You might also wanna check this: PG community extensions - https://pgxn.org/ https://pgxn.org/ There are a lot of open-source or free extensions to make PG as a data platform rather than just a RDBMS.
jon-wood 7y ago
Postgres Foreign Data Wrappers are an incredibly powerful tool. I've in the past built a basic data warehouse for a distributed system by pulling in read slaves from assorted service's databases as FDW sources to a central Postgres instance, after which Postgres allows you to treat them all as one big database.

brunoqc 7y ago

I wonder if you or the other Postgres users here could give me some tips. What would be the best way to use the data from a huge Excel file into my web apps? Currently I'm converting it to CSV and use `BULK INSERT` with MSSQL. I know that with MSSQL I can also use Excel files as "external database" but AFAIK, Excel files are not indexed and are super slow when used as an "external database". I wouldn't mind switching to Postgres. I actually prefer Postgres.

michelpp 7y ago
THe process is similar, you would convert to csv and then use the \copy command to bulk insert the data. In pg 12, the copy command has grown a lot of very useful features like WHERE filtering, so if you get the chance go for 12: https://www.cybertec-postgresql.com/en/tech-preview-improving-copy-and-bulkloading-in-postgresql-12/ https://www.cybertec-postgresql.com/en/tech-preview-improvin...
deleted 7y ago
[deleted]
truth_seeker 7y ago
This link covers it in detail: http://www.postgresqltutorial.com/import-csv-file-into-posgresql-table/ http://www.postgresqltutorial.com/import-csv-file-into-posgr...
tempguy9999 7y ago
As an MSSQL guy who rarely connects it to excel, if your workflow allows it consider pulling the entirety of the excel sheet into a table in the db and working on it just from there. What's huge for excel isn't for MSSQL. As a decades-long user of MSSQL guy who is getting sick of being rpeatedly done over by MS, could you explain why you prefer postgres? That would be incredibly interesting, TIA
- sixdimensional 7y ago
  Not OP but, in my mind (having used both extensively), Postgres is basically the closest I feel you can get to MSSQL in the open source world, in terms of completeness of a fully functional relational database that scales - not to mention Postgres has tons of features that MSSQL has been playing catch up with for years. I like both of them personally. Postgres didn't used to have as strong of a showing on Windows, and not as fancy tools as Management Studio/Query Analyzer/Azure Data Studio, but I think today Postgres is absolutely viable to replace SQL Server if you want to switch to open source.

riku_iki 7y ago

Do fdw/standard connectors support pushdown predicates well? Documentation doesn't say much about that.

truth_seeker 7y ago
Yep. PG 9.5 and later supports it. Use "EXPLAIN VERBOSE" before your query to get more info.

just_myles 7y ago

My god. I didn't know postgres can do this. Thank you!

sixdimensional 7y ago

This is a helpful utility for sure - but beware, joins between foreign data sources and local ones can be slow and very un-optimized, especially with bigger data sources. I used to work for a company that made a data federation software platform. I am always surprised that people forget that management of external data is part of the SQL standard actually - SQL/MED ("SQL Management of External Data") [1]! [1] https://en.wikipedia.org/wiki/SQL/MED https://en.wikipedia.org/wiki/SQL/MED

greggyb 7y ago
Completely unrelated, but that URL really threw me for a loop. I am not used to seeing a slash in the article topic (section after '/wiki/').