merge statement gives error
Hi, Trying to execute the following statement merge into ABC as n using dual on (n.id=123) when matched update set aaa=222, bbb=333 when not matched insert (id, aaa) values (NEXTVAL(id),555); but gives syntax error.What should be the proper syntax[ Parameter values are properly passed based on data type]. Thanks
Re: stored procedure call is not working with "select procedure()" option
On Tue, Feb 20, 2018 at 9:52 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 02/20/2018 08:17 AM, Abhra Kar wrote: > >> >> >> On Tue, Feb 20, 2018 at 9:21 PM, Adrian Klaver <adrian.kla...@aklaver.com >> <mailto:adrian.kla...@aklaver.com>> wrote: >> > > >> >> Please provide me the list email.I don't know about it. >> > > The list email is the one you used already: > > pgsql-gene...@postgresql.org > > I suspect the problem is that when you replied to my first post you did > Reply not Reply All. This picked up my email, but not the list email. > > >> Thanks >> > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > Postgres Version -- 9.5 JDBC Driver - org.postgresql.Driver JDVC driver version and relavant postgres logs didn't get. Regards, Abhra
stored procedure call is not working with "select procedure()" option
Hi ,
I have a stored procedure name "procedure()". Which I am calling
by --
Session sess = (Session)entityManager.getDelegate(); //entityManager is
javax.persistent.EntityManager[ Properly Initialise]
sess.createSQLQuery("select procedure()");
procedure is containing some truncate queries like -- EXECUTE('truncate
table abc');
It's not throwing any exception but not executing the procedure.Using
oracle query -- sess.createSQLQuery("{ call procedure()
}").executeUpdate(); procedure execution is working fine.
What's need to be change here.
Thanks and Regards,
Abhra
Re: postgres connection with port option in shell script
On Wed, Feb 14, 2018 at 9:03 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Wed, Feb 14, 2018 at 8:21 AM, Abhra Kar <abhra@gmail.com> wrote: > >> Hi, >> >> I want to get postgres connection in script file. I am executing >> below command and successfully getting connected --- >> >> >> psql postgresql://$USER:$PASSWORD@$HOST/$DATABASE <> >> >> > If all you are going to do is substitute environment variables into a URI > why not just identify the environment variables that psql uses directly, > set those, and call "psql" directly. > > select * from abc; >> >> >> This is executing with default 5432 port.If postgres is installed to any >> other port then port option need to be include here[Don't want to take port >> from .pgpass file]. >> > > You couldn't even if you wanted to. Did you maybe mean the > .pg_service.conf file? > > You should strongly consider using .pgpass instead of "$PASSWORD" - > especially depending upon where perform the export. > > >> How I can modify this command with PORT option. >> > > https://www.postgresql.org/docs/10/static/libpq-connect. > html#LIBPQ-CONNSTRING > > David J. > Hi David, I meant .pgpass file with in user home directory.And I followed the link you provided.It worked.Thanks a lot. Regards, Abhra
postgres connection with port option in shell script
Hi, I want to get postgres connection in script file. I am executing below command and successfully getting connected --- psql postgresql://$USER:$PASSWORD@$HOST/$DATABASE <
Found non-empty schema without metadata table error while migrating
Hi, Getting below error— exec] Flyway (Command-line Tool) v.1.1 [exec] [exec] Metadata table created: schema_version [exec] Schema initialized with version: 0 [exec] Flyway (Command-line Tool) v.1.1 [exec] [exec] ValidationException: Found non-empty schema without metadata table! Use init() first to initialise the metadata table. It stuck when going to execute below configuration in ant build.xml for migrate db(populate tables into schema abc)— abc schema is created properly registered with abc user. didn’t solve the problem What need to be change here. Thanks
Re: Create schema with in a specific database from a script file
*sudo **-u** postgres createdb ABC* *su **-c* *"**psql -d ABC**"* *if [* *$3* *==* *'x'* *]* *then* *su **-c* *"**psql -c **\"**grant all privileges on database ABC** to * *\"**"** postgres* *su **-c* *"**psql -c **\"**create schema authorization **\"**"** postgres* *else* *su **-c* *"**psql -c **\"**grant all privileges on database ABC to * *\"**"** postgres* *su **-c* *"**psql -c **\"**create schema authorization **\"**"* * postgres* *fi* Hi Melvin, I executed the above script but schema created with in postgres db not with in ABC db. And in terminal didn't show the message connected to ABC database. What is wrong I am doing here? Regards, Abhra On Fri, Feb 2, 2018 at 7:48 AM, Melvin Davidson <melvin6...@gmail.com> wrote: > > > On Thu, Feb 1, 2018 at 9:09 PM, Abhra Kar <abhra@gmail.com> wrote: > >> Hi, >> >>I have to write script for psql in a xyz.sh file which should create a >> schema in a specific data.In psql prompt I achieve it like -- >> >> >> postgres=# \c ABC >> >> ABC=# create schema authorization myschema >> >> >> >> In xyz.sh I executed the following script --- >> >> *su -c "psql -c \"\c ABC \"" postgres* >> >> *su -c "psql -c \"create schema authorization myschema\"" postgres* >> >> >> >> In the terminal got message “connected to ABC database”. But schema >> created with in postgres database not with in ABC database. >> >> What should be the proper script ? >> >> >> >> Thanks and Regards, >> >> Abhra >> >> > > *> But schema created with in postgres database not with in ABC database. * > > *Just specify the database in the command line:* > > *EG: su -c "psql -d ABC"* > > > -- > *Melvin Davidson* > I reserve the right to fantasize. Whether or not you > wish to share my fantasy is entirely up to you. >
Create schema with in a specific database from a script file
Hi, I have to write script for psql in a xyz.sh file which should create a schema in a specific data.In psql prompt I achieve it like -- postgres=# \c ABC ABC=# create schema authorization myschema In xyz.sh I executed the following script --- *su -c "psql -c \"\c ABC \"" postgres* *su -c "psql -c \"create schema authorization myschema\"" postgres* In the terminal got message “connected to ABC database”. But schema created with in postgres database not with in ABC database. What should be the proper script ? Thanks and Regards, Abhra
Re: CannotAcquireResourceException in Junit
Thanks Rob.
OS -- RHEL 6.7(Santiago)
Postgres Version -- 9.5
Location --*/usr/pgsql-9.5/bin/psql*
Can you please guide me , how to stop all those connection manually(From
psql prompt or linux shell or any .sh file with in postgres directory)
Regards,
Abhra
On Thu, Jan 25, 2018 at 9:36 AM, Rob Sargent <robjsarg...@gmail.com> wrote:
>
> > On Jan 24, 2018, at 9:57 AM, Abhra Kar <abhra@gmail.com> wrote:
> >
> >
> > Hi,
> > I am getting the following error while running bunch of Junit
> test cases through “ant test” command
> > Caused by: com.mchange.v2.resourcepool.CannotAcquireResourceException:
> A ResourcePool could not acquire a resource from its primary factory or
> source.
> > PFA full stack trace.
> >
> > After execution of 300 test cases I got this error. The setup() method
> code snippet is ---
> >
> > @Before
> > public void setUp() throws Exception {
> > entityManager = entityManagerFactory.
> createEntityManager();//javax.persistence.EntityManager
> > entityManager.getTransaction().begin();
> > }
> >
> > this method ran for all 300 test cases but didn’t get error .
> >
> > Another 900 test cases are remaining. I tried running single test cases
> from those test cases, getting same error for all.
> > What is root cause of this error.
> >
> > Thanks and Regards,
> > Abhra
> >
> Apparently Merlin is busy elsewhere. You need to name the OS, the
> postgres version, and the stack you’re using.
>
> But my guess is you’re not closing those connections and have hit a
> configuration limit for maximum connection.
>
>
>
missing FROM-clause entry for table bbbb
Hi I tried to modify below sql command in postgres syntax--- insert into (id, groupid, ele_id, ro_element_id) ") select .nextval, :groupid, gosp.neteleid, gosp.hodev from net_ele gos, net_gos_prop gosp " where gos.eid in ( :eids ) and gos.id = gosp.net_element_id and gos.id not in (select ealjs.element_id from ealjs where ealjs.groupid = :groupid) If I run this it gives error for ":".If I remove : then I will get error like missing FROM-clause entry for table . What is the issue here and what should be exact query in postgressql Thanks
