Re: [help] Error in database import
On 4/19/24 20:58, Tu Ho wrote: Hi, I am currently having a project where I need to combine 2 large database. I was not however able to import an excel file .csv into the There are Excel files(.xls/.xlsx) and there are *.csv files. database. The error was ERROR: syntax error at or near "OIDS" LINE 1: ...ing Site" , "International Name(s)" ) FROM STDIN OIDS DELI... ^" I have no idea how to fix this because I used the "upload file" option. What should I do? Use a recent version of whatever client you are using as: https://www.postgresql.org/docs/current/sql-copy.html "If 1, OIDs are included in the data; if 0, not. Oid system columns are not supported in PostgreSQL anymore, but the format still contains the indicator." Your client is using old syntax as the last version of Postgres that allowed FROM STDIN OIDS was: https://www.postgresql.org/docs/11/sql-copy.html "where option can be one of: FORMAT format_name OIDS [ boolean ] " And Postgres 11 is ~6 months past EOL. -- Adrian Klaver adrian.kla...@aklaver.com
Re: [help] Error in database import
On Fri, Apr 19, 2024 at 11:58 PM Tu Ho wrote: > Hi, > > I am currently having a project where I need to combine 2 large database. > I was not however able to import an excel file .csv into the database. The > error was ERROR: syntax error at or near "OIDS" LINE 1: ...ing Site" , > "International Name(s)" ) FROM STDIN OIDS DELI... > ^" > > I have no idea how to fix this because I used the "upload file" option. > Presumably that means you used "PgAdmin"?
[help] Error in database import
Hi, I am currently having a project where I need to combine 2 large database. I was not however able to import an excel file .csv into the database. The error was ERROR: syntax error at or near "OIDS" LINE 1: ...ing Site" , "International Name(s)" ) FROM STDIN OIDS DELI... ^" I have no idea how to fix this because I used the "upload file" option. What should I do?
Re: Foreign Key error between two partitioned tables
On Fri, 19 Apr 2024 at 05:48, Michael Corey wrote: > ALTER TABLE ONLY par_log_file > ADD CONSTRAINT plf_pk PRIMARY KEY (par_file_id); > ALTER TABLE par_log_definition > ADD CONSTRAINT pld_fk FOREIGN KEY (par_file_id) REFERENCES > par_log_file(par_file_id); > I receive the following error when creating a foreign key between two > partitioned tables. > ERROR: there is no unique constraint matching given keys for referenced > table "par_log_file" > Version Postgres 13.13 No problems running that script here on 13.13. I imagine you've probably got a partition attached to par_log_file already and since your "plf_pk" constraint is on ONLY par_log_file, then the supporting index is likely invalid. I'd suggest checking if this is the case with: select indexrelid::regclass,indisvalid from pg_index where indrelid = 'par_log_file'::regclass; The correct way to create the PK constraint is with: ALTER TABLE par_log_file ADD CONSTRAINT plf_pk PRIMARY KEY (par_file_id); You might want to create supporting unique indexes on each partition CONCURRENTLY before doing that so that the ALTER TABLE becomes a meta-data-only operation. David
Re: Can you refresh a materialized view from a materialized view on another server?
On 4/18/24 19:49, Michael Nolan wrote: My production server has a materialized view that is refreshed from a mysql_fdw several times a day. What I'd like to be able to do is refresh the materialized view on the testbed server from the one on the production server so that they are the same. (Refreshing it from the MySQL server will result in one that has records that have been added or updated rather than an exact copy of the one on the production serve Use postgres_fdw to connect the test bed to the production server? Mike Nolan htf...@gmail.com -- Adrian Klaver adrian.kla...@aklaver.com
Re: Not able to grant access on pg_signal_backend on azure flexible server
On 4/18/24 22:48, Saksham Joshi wrote: Hi, We have created an azure postgresql flexible server and we have added an ad admin as a user and Created our database using this admin user.However,When are running this command: 'Grant pg_signal_backend To adminUser' we are getting an error that says 'permission denied to grant role "pg_signal_backend".While this is strange the admin user is infact the owner of the said database and we don't have any other user that have the said privileges. https://www.postgresql.org/docs/current/predefined-roles.html "PostgreSQL provides a set of predefined roles that provide access to certain, commonly needed, privileged capabilities and information. Administrators (including roles that have the CREATEROLE privilege) can GRANT these roles to users and/or other roles in their environment, providing those users with access to the specified capabilities and information." -- Adrian Klaver adrian.kla...@aklaver.com
Re: Not able to grant access on pg_signal_backend on azure flexible server
Saksham Joshi writes: > We have created an azure postgresql flexible server and we have added an ad > admin as a user and Created our database using this admin user.However,When > are running this command: 'Grant pg_signal_backend To adminUser' we are > getting an error that says 'permission denied to grant role > "pg_signal_backend".While this is strange the admin user is infact the > owner of the said database and we don't have any other user that have the > said privileges. Being the owner of a database isn't an especially privileged thing in Postgres. In particular, it does not grant you any powers over installation-wide objects such as roles. regards, tom lane
Re: SSPI Feature Request
On 2024-04-19 11:53, Buoro, John wrote: SSPI Kerberos\NTLM authentication (Windows environment) currently only authenticates users, however, it does not authenticate a user against an LDAP \ Active Directory group. Can you please look at making this possible? Sounds like it'd be pretty useful. :) Is this something that Harvey Norman would be interested in sponsoring? ie. hiring a suitable PostgreSQL developer (not me!) to implement it There are quite a few skilled PostgreSQL developers around these days, so (in theory) it shouldn't be *too hard* find someone the right person. ? Regards and best wishes, Justin Clift
SSPI Feature Request
Hi, SSPI Kerberos\NTLM authentication (Windows environment) currently only authenticates users, however, it does not authenticate a user against an LDAP \ Active Directory group. This makes administration complex because an administrator would need to add\remove each user to\from an instance or if a user changes role then their permissions would need to be altered. If you have many instances and many users then this becomes a long process which can be prone to error. Industry best practices would be to define group(s) and assign permissions and roles to these and have SSPI authenticate users against these groups. The responsibility of granting or altering permissions is at the LDAP \ Active Directory level which is its prime purpose. This is something that other RDBMS can do and it would make PostgreSQL a far more attractive solution from that perspective. Can you please look at making this possible? This has been raised before (below) but nothing has been progressed further... https://www.postgresql.org/message-id/20201016160029.GO19056%40tamriel.snowman.net Many thanks. John. Disclaimer The information contained in this communication from the sender is confidential. It is intended solely for use by the recipient and others authorized to receive it. If you are not the recipient, you are hereby notified that any disclosure, copying, distribution or taking action in relation of the contents of this information is strictly prohibited and may be unlawful. This email has been scanned for viruses and malware, and may have been automatically archived by Mimecast, a leader in email security and cyber resilience. Mimecast integrates email defenses with brand protection, security awareness training, web security, compliance and other essential capabilities. Mimecast helps protect large and small organizations from malicious activity, human error and technology failure; and to lead the movement toward building a more resilient world. To find out more, visit our website.
Re: Why does it sort rows after a nested loop that uses already-sorted indexes?
> That's a level of analysis that it doesn't do... Great. I suspected that, but I needed a confirmation from a reliable source. Thank you! > ...and TBH I'm not even > entirely sure it's correct to assume that the output is sorted like > that. At minimum you'd need an additional assumption that the > outer side's join key is unique, which is a factor that we don't > currently track when reasoning about ordering. Ouch! I hadn't thought about that possibility! When I tried to mentally reproduce the nested loop, I always considered the values of the outer loop to be unique. I guess that was because, very often, I used unique indexes for my tests... But it doesn't have to be so, of course. Best regards. On 18/04/2024 16:53, Tom Lane wrote: negora writes: As you can see, the planner does detect that the outer loop returns the rows presorted by [sales_order.id]. However, it's unable to detect that the rows returned by the inner loop are also sorted by [sales_order.id] first, and then by [order_line.id]. That's a level of analysis that it doesn't do, and TBH I'm not even entirely sure it's correct to assume that the output is sorted like that. At minimum you'd need an additional assumption that the outer side's join key is unique, which is a factor that we don't currently track when reasoning about ordering. regards, tom lane
