Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t0l6F-00D359-4v for pgsql-admin@arkaria.postgresql.org; Tue, 15 Oct 2024 17:12:47 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1t0l6D-0097fx-E6 for pgsql-admin@arkaria.postgresql.org; Tue, 15 Oct 2024 17:12:45 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t0l6C-0097cI-Uc for pgsql-admin@lists.postgresql.org; Tue, 15 Oct 2024 17:12:45 +0000 Received: from mx0b-0039f802.pphosted.com ([205.220.176.45]) by makus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t0l69-0012Jl-GP for pgsql-admin@lists.postgresql.org; Tue, 15 Oct 2024 17:12:43 +0000 Received: from pps.filterd (m0209982.ppops.net [127.0.0.1]) by mx0b-0039f802.pphosted.com (8.18.1.2/8.18.1.2) with ESMTP id 49FGcDha004450 for ; Tue, 15 Oct 2024 10:12:40 -0700 Received: from mail-yw1-f200.google.com (mail-yw1-f200.google.com [209.85.128.200]) by mx0b-0039f802.pphosted.com (PPS) with ESMTPS id 427q82b9qd-1 (version=TLSv1.2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128 verify=NOT) for ; Tue, 15 Oct 2024 10:12:40 -0700 (PDT) Received: by mail-yw1-f200.google.com with SMTP id 00721157ae682-6e3d660a1afso3882947b3.0 for ; Tue, 15 Oct 2024 10:12:40 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1729012360; x=1729617160; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=7ktRJltWYflwq0mnNFufMC8wFf0jpqqTq4eOc+ScLBI=; b=KtPH51KV26/ssc0C8wrlgYE0S5IBItONv87WsAIZewwgJmwa+Pi/KC24iD7UTlvLFg 6Z/gsMz3HFewR5F6X5RRyw6PKzqH0lHos+bbkFjjKbbMO9XEf47/EKsAMQ4vjI3YUh1C FSMWptGtoQzd1M9WFieJXJv+aB4+hjbr8T6igsmVbSz/jtRiwMZNgwMzcQfoHPvxip82 wbY+ypl2xjXo2kHNnudWjX37gMa4kMyMPW7pUkqE60IpOiCJn/8U7h79bFe09kyoTzNr XoNKxOunkg25/3nE0n0dn2gl8y+KAldn28zKINlf6AD8eXGGO2m59lOf4p5Nf6E3Nzia Tr5A== X-Forwarded-Encrypted: i=1; AJvYcCUhrIgbQoq6NwRclggsWyYsFYWHMb/SKMGzimOuEjPZcGssBGY0e1xdjru57EumItb2NFeNU6+ffpXOdA==@lists.postgresql.org X-Gm-Message-State: AOJu0YxUL26u0emW1J5HIhCZZuuiywvyJgEGKbZJOWx1ORAftxIOf+RQ Bybg2J5BSZ0LXfd/ljL941Xm5tcM+b06TZCiCCHECU1iy/niC3Iz7QkTTVzDFiPss30izn2nEXw kHR4AYucHXAmAGkxKITVRPzMiDty6ALRMOal2OORBWE6/RBgg3Q6HBysRE3VbrXE/8d0eIyhISf +su8XhQa7KJ4UwMkg3ixsyGFIOZ30cJVFIPRQvhE5OYRcOWulvg8tWfw1YqFM9KT1Ke7M= X-Received: by 2002:a05:690c:3381:b0:6e3:26dd:1bf5 with SMTP id 00721157ae682-6e36434bfd2mr91563347b3.20.1729012359832; Tue, 15 Oct 2024 10:12:39 -0700 (PDT) X-Google-Smtp-Source: AGHT+IHEi/3NeyQJR9xGvQeIygsbR/0RqvlIaqw3658mie4JSHAEdS9Zxxs9b2mCSTUoj4fo1Grc34eMxevOqDaxmRg= X-Received: by 2002:a05:690c:3381:b0:6e3:26dd:1bf5 with SMTP id 00721157ae682-6e36434bfd2mr91562877b3.20.1729012359369; Tue, 15 Oct 2024 10:12:39 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Sam Stearns Date: Tue, 15 Oct 2024 10:12:28 -0700 Message-ID: Subject: Re: create foreign table To: vignesh kumar Cc: Guillaume Lelarge , Pgsql-admin Content-Type: multipart/alternative; boundary="0000000000007e37170624870f14" X-Proofpoint-Virus-Version: vendor=baseguard engine=ICAP:2.0.293,Aquarius:18.0.1051,Hydra:6.0.680,FMLib:17.12.62.30 definitions=2024-10-15_12,2024-10-15_01,2024-09-30_01 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000007e37170624870f14 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Thanks,Vignesh! On Tue, Oct 15, 2024 at 9:54=E2=80=AFAM vignesh kumar < vigneshkumar.venugopal@outlook.com> wrote: > Great and glad it worked for you.. it's always better to cache the foreig= n > server details onto postgres wrappers.. so that your calls to foreign > objects would be simpler and more secure.. > > Sent from Outlook for Android > ------------------------------ > *From:* Sam Stearns > *Sent:* Tuesday, October 15, 2024 10:09:39 PM > *To:* vignesh kumar > *Cc:* Guillaume Lelarge ; Pgsql-admin < > pgsql-admin@lists.postgresql.org> > *Subject:* Re: create foreign table > > I removed 'program' from the OPTIONS keyword which resolved it, as > suggested by Guillaume. > > On Tue, Oct 15, 2024 at 9:00=E2=80=AFAM vignesh kumar < > vigneshkumar.venugopal@outlook.com> wrote: > > First you need to create foreign server, foreign data wrapper then > followed by granting access to use the postgres_fdw.. then try accessing > it.. that's the optimal way.. I'm not getting what is being tried here > > Sent from Outlook for Android > ------------------------------ > *From:* Guillaume Lelarge > *Sent:* Monday, October 14, 2024 11:48:26 PM > *To:* Sam Stearns > *Cc:* Pgsql-admin > *Subject:* Re: create foreign table > > Hi, > > Le lun. 14 oct. 2024 =C3=A0 20:14, Sam Stearns a = =C3=A9crit : > > Howdy, > > Receiving an error without much detail when creating a foreign table: > > 2024-10-14 11:08:30.928 PDT [1177724] ERROR: conflicting or redundant > options > 2024-10-14 11:08:30.928 PDT [1177724] STATEMENT: CREATE FOREIGN TABLE > ext_invalids ( > validcargo char(8), > validauto char(8), > cllocid char(8), > insdid char(6), > memberid char(14), > attachdate char(12), > commonauthority char(1), > contractauthority char(1), > compname char(60), > docket_num char(9) > ) SERVER external_dat_dir OPTIONS(filename 'DISABLED', format > 'csv', delimiter ',', program ''); > > I'm not seeing much out there on the error. I'm also not seeing what are > valid values for the OPTIONS keyword in the Postgres documentation. > > Would someone be able to give advice here, please? > > > Saying which FDW you're using would help, but I guess it's file_fdw. > > You can't specify both filename and program. You have to choose one of > them. > > Regards. > > > > -- > Guillaume. > > > > -- > > *Samuel Stearns* > Lead Database Administrator > *c:* 971 762 6879 | *o:* 503 672 5115 | DAT.com > [image: DAT] > > --=20 *Samuel Stearns* Lead Database Administrator *c:* 971 762 6879 | *o:* 503 672 5115 | DAT.com [image: DAT] --0000000000007e37170624870f14 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Thanks,Vignesh!

On Tue, Oct 15, 2024 at 9:54=E2=80=AFAM vig= nesh kumar <vignes= hkumar.venugopal@outlook.com> wrote:
Great and glad it worked for you.. it's always better= to cache the foreign server details onto postgres wrappers.. so that your = calls to foreign objects would be simpler and more secure..

From: = Sam Stearns <sa= m.stearns@dat.com>
Sent: Tuesday, October 15, 2024 10:09:39 PM
To: vignesh kumar <vigneshkumar.venugopal@outlook.com>
Cc: Guillaume Lelarge <guillaume@lelarge.info>; Pgsql-admin <pgsql-admin@= lists.postgresql.org>
Subject: Re: create foreign table
=C2=A0
I removed 'program' from the OPTIONS keyword which= resolved it, as suggested by Guillaume.

On Tue, Oct 15, 2024 at 9:00=E2=80=AFAM vignesh kumar <= vig= neshkumar.venugopal@outlook.com> wrote:
First you need to create foreign server, foreign data wra= pper then followed by granting access to use the postgres_fdw.. then try ac= cessing it.. that's the optimal way.. I'm not getting what is being= tried here

Sent from Outlook for Android
From: Guillaume Lelarge <guillaume@lelarge.info>
Sent: Monday, October 14, 2024 11:48:26 PM
To: Sam Stearns <sam.stearns@dat.com>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: create foreign table
=C2=A0
Hi,

Le=C2=A0lun. 14 oct. 2024 =C3=A0=C2=A020:14, Sam Stearns &= lt;sam.stearns@dat= .com> a =C3=A9crit=C2=A0:
Howdy,

Receiving an error without much detail when creating a foreign table:<= /div>

2024-10-14 11:08:30.928 PDT [1177724] ERROR: =C2=A0conflicting or redu= ndant options
2024-10-14 11:08:30.928 PDT [1177724] STATEMENT: =C2=A0CREATE FOREIGN =C2= =A0TABLE ext_invalids (
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 validcargo char(8),=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 validauto char(8),<= br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 cllocid char(8), =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 insdid char(6),
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 memberid char(14),<= br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 attachdate char(12)= ,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 commonauthority cha= r(1),
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 contractauthority c= har(1),
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 compname char(60),<= br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 docket_num char(9)<= br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 ) SERVER external_dat_dir OPTIONS(filename '= ;DISABLED', format 'csv', delimiter ',', program '&= #39;);

I'm not seeing much out there on the error.=C2=A0 I'm also not= seeing what are valid values for the=C2=A0OPTIONS keyword in the Postgres = documentation.

Would=C2=A0someone be able to give advice here, please?


Saying which FDW you're using would help, but I guess it's fil= e_fdw.

You can't specify both filename and program. You have to choose on= e of them.

Regards.


--
Guillaume.


--

Samuel Stearns
Lead Database Administrator
c:=C2=A0971 762 6879=C2=A0|=C2=A0o:=C2=A0503 672 5115=C2=A0|=C2=A0DAT.com

3D"DAT"


--

Samuel Stearns
Lead Database Administrat= or
c:=C2=A0971 762 6879=C2=A0|=C2=A0o:=C2=A0503 672 5115=C2=A0|=C2=A0DAT.com

3D=
--0000000000007e37170624870f14--