[GENERAL] How to retrieve Comment text using SQL, not psql?
Using pgAdmin3 I've tried this and variations on it. All are rejected. select COMMENT ON TABLE articlestats No answer here, http://www.postgresql.org/docs/9.3/static/sql-comment.html pgAdmin3 had no problem with entering a comment: COMMENT ON TABLE articlestats IS 'Comprehensive data for every article.' - Bob Futrelle
Re: [GENERAL] Re: Stuck trying to backup large database - best practice? How about a cloud service?
Many worthwhile things cost money. I never suggested you wouldn't have to pay. - Bob On Tue, Jan 13, 2015 at 12:27 AM, Michael Nolan htf...@gmail.com wrote: On Mon, Jan 12, 2015 at 7:46 PM, Bob Futrelle bob.futre...@gmail.com wrote: You should be able to find a cloud provider that could give you many TB. Or so they like to claim. Nope, but you probably find one willing to SELL you access to many TB. -- Mike Nolan
[GENERAL] Re: Stuck trying to backup large database - best practice? How about a cloud service?
You should be able to find a cloud provider that could give you many TB. Or so they like to claim. - Bob
[GENERAL] Installed postgres.app 9.3.1.0. pgadmin doesn't appear to see it
I uninstalled 9.2 before installing 9.3.1.0. The app is called Postgres93, it is version 9.3.1.0 I downloaded the latest pgAdmin, it is pgAdmin3 version 1.18.1 I have a database MiniServer which is supposed to use postgres as its Maintenance database. But there is no such database. Perhaps it can't find the DBs that were there? pg_upgrade requires pg_upgrade -b oldbindir -B newbindir -d olddatadir -D newdatadir [option...] but I'm not sure what the 'old' values should be (since I uninstalled 9.2 - maybe I shouldn't have?) In my /Library/PostgreSQL/9.2/data/global I see 42 files fom 8K to 25K in size, most created last March - my data must be there. So that may be telling me to create an empty folder, /Library/PostgreSQL/9.3/ In my Postgres93 bundle there's a Contents/MacOS/postgres, a small unix executable. Is that the binary? But again there's no binary for 9.2. I was rolling along with 9.2 getting lots of good work done via Eclipse/JDBC. But now I'm stuck. Sorry for being so dense. But I've never been afraid to ask questions. The answers I get increase my understanding of whatever the topic is. Thanks in advance for helping a tyro. - Bob
Re: [GENERAL] I need more specific instructions for switching to digest mode for this list
Me bad. It was right there in front me. I'm now in Digest Mode. - Bob On Wed, Oct 9, 2013 at 12:00 PM, Igor Neyman iney...@perceptron.com wrote: -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- ow...@postgresql.org] On Behalf Of Raymond O'Donnell Sent: Wednesday, October 09, 2013 11:54 AM To: Bob Futrelle Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] I need more specific instructions for switching to digest mode for this list On 09/10/2013 16:47, Bob Futrelle wrote: Trying to switch to the digest didn't work. How do I find more specific details about switching? On Wed, Oct 9, 2013 at 12:27 AM, Bob Futrelle bob.futre...@gmail.com mailto:bob.futre...@gmail.com wrote: set pgsql-general digest -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general It says it all right at the bottom: To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] I need more specific instructions for switching to digest mode for this list
Trying to switch to the digest didn't work. How do I find more specific details about switching? On Wed, Oct 9, 2013 at 12:27 AM, Bob Futrelle bob.futre...@gmail.comwrote: set pgsql-general digest
[GENERAL]
set pgsql-general digest
[GENERAL] How to restore some DBs to a new server?
Running on my MacBook Pro, pgAdmin3 tells me I have four servers in Server Groups, all local, no network involved. I have dumped two of my DBs from a current server using pg_dump. Then in pgAdmin3 I created another server, let's call it New. I would like to look at these earlier DBs while not disturbing my current DBs. So I would like to reload both the dumps into New, but I can't find the magic invocation that does this, in pgAdmin3 or using pg_restore or directly in psql. I realize that this isn't rocket science, so I'll blush a bit when the simplicity of it is revealed. - Bob
[GENERAL] pgAdmin shows two servers with the identical data
I have two PG servers with the same data. I know the data is the same, because if I change a value in a table on one server, it changes the value in a table with the same name in the other server. in pgAdmin III: Properties for server Local (localhost:5432): Name: Local Host: localhost Port: 5432 Maintenance DB: postgres UserName: robertfutrelle Properties for server Local(local:.s.PGSQL.55432) Name: Local Host: Port: 5432 Maintenance DB: postgres UserName: robertfutrelle The second one is odd - no Host is listed. What is going on? Since they appear to be identical in most all respects, would be nice to remove one of them. Is that safe? If so, which? All the above in Mac Mountain Lion OS. All my PG work is being done entirely locally, on my MacBook. I intend to work in that mode for at least the next two years. My son suggested I move to PostgreSQL. I started with it late last year and have never looked back. PG is definitely one cool DB system. Thanks guys. My data is 120,000 biology papers, HTML, and a great deal of secondary data I generate from them. - Bob Futrelle
[GENERAL] JDBC - Need to declare variables for values in insert statement
Do the declare statements and insert all have to be done in one statement execute()? That is, what is the scope of variables I declare? I see a variety of syntax examples, some for older versions? I'm using pg 9.2.2, so what are the rules/syntax for declaring and using variables? Use case: I collect metadata from XML articles such as title, journalName, volume, year. For each article I need to create a pg table record and insert the values for the various metadata items in the article. This is my first post to this list. Thanks, - Bob
Re: [GENERAL] JDBC - Need to declare variables for values in insert statement
Here's a small, but complete code example - Bob
package us.tsos.dbs.pg;
import java.sql.*;
/**
* This is an effort to get a computed value from a Java function
* (or data object) included properly in the VALUES entries.
* So, how can I declare an SQL variable and set its value to some Java
value?
* Results viewed in pgAdmin3 with query 'select * from public.hello'.
*
* Jar in classpath is postgresql-9.2-1002.jdbc4.jar
*
* @version 0.1 Mon Jan 28 EST 2013
* @author Bob Futrelle
*/
public class JDBCVariableTest {
Connection db;
Statement st;
Boolean boo;
public static void main(String[] args) throws SQLException {
JDBCVariableTest testIt = new JDBCVariableTest();
testIt.helloVariables();
}
public int f1() { return 3;}
public void helloVariables() throws SQLException {
int intVar = f1(); // OK in Java, but SQL/JDBC?
try {
db = DriverManager.getConnection(jdbc:postgresql:Articles,
robertfutrelle, my pw);
st = db.createStatement();
boo = st.execute(CREATE TABLE IF NOT EXISTS hello ( Name VARCHAR NOT NULL
PRIMARY KEY, value int));
// Declare ??
// INSTEAD OF THE LITERAL 4 VALUE (which works)
// how do I declare a variable and assign the f1() return value to it
// and then include it so the value 3 appears in the inserted record?
//st.execute(insert into hello values('aKey',4));
st.execute(insert into hello values('bKey',4));
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
On Mon, Jan 28, 2013 at 8:39 AM, Dave Cramer p...@fastcrypt.com wrote:
Bob,
Can you provide a snippet of code so I can understand what you mean by
declare ?
Dave Cramer
dave.cramer(at)credativ(dot)ca
http://www.credativ.ca
On Mon, Jan 28, 2013 at 7:11 AM, Bob Futrelle bob.futre...@gmail.comwrote:
Do the declare statements and insert all have to be done in one statement
execute()?
That is, what is the scope of variables I declare?
I see a variety of syntax examples, some for older versions?
I'm using pg 9.2.2, so what are the rules/syntax for declaring and using
variables?
Use case: I collect metadata from XML articles such as title,
journalName, volume, year. For each article I need to create a pg table
record and insert the values for the various metadata items in the article.
This is my first post to this list.
Thanks,
- Bob
Re: [GENERAL] database design best pratice help
Yes. The general rules are: Many normalized tables. OK. Denormalizing simply to reduce the number of tables. Not OK. - Bob On Mon, Jan 28, 2013 at 1:47 PM, Kevin Grittner kgri...@ymail.com wrote: Jose Soares jose.soa...@sferacarta.com wrote: In my db I have about one hundred tables like this: code description To avoid to have a so great number of similar tables in the db I wonder if it is a good idea to unify all these tables in one big table like this: id code table_ name description Could this be a way to enhance db performance? No. It could easily hurt performance, and will create a mess of your data. Is there any negative point that I don't see? For starters you can't use foreign keys to ensure data integrity. I would not only leave the separate tables, but I would create a separate domain for the code column of each, to be used everywhere a code is of that nature. I recommend reading up on concepts for normalizing a relational database. If you mix different logical types of data in one column, you don't even meet the requirements of first normal form, and reduce the ability to cleanly apply relational concepts to your data. -Kevin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] JDBC - Need to declare variables for values in insert statement
Thanks to Edson and Dave for lightning responses.
I'm confident that your answers will do the job.
I'll follow up on the advice AFTER I get my coffee ;-)
I'm really focused on the NLP content of my research,
but I need a DB infrastructure to do it right.
(Not a bunch of files as in ancient times.)
--- Bob
On Mon, Jan 28, 2013 at 1:59 PM, Edson Richter edsonrich...@hotmail.comwrote:
You have two options:
st.execute(insert into hello values ('bKey', +f1()+));
or
PreparedStatement st = db.prepareStatement(insert into hello values
('bKey', ?));
st.setInteger(1, f1());
where 1 is the first parameter, 2 is the second parameter, and so on.
Regards,
Edson Richter
Em 28/01/2013 16:50, Bob Futrelle escreveu:
Here's a small, but complete code example - Bob
package us.tsos.dbs.pg;
import java.sql.*;
/**
* This is an effort to get a computed value from a Java function
* (or data object) included properly in the VALUES entries.
* So, how can I declare an SQL variable and set its value to some Java
value?
* Results viewed in pgAdmin3 with query 'select * from public.hello'.
*
* Jar in classpath is postgresql-9.2-1002.jdbc4.jar
*
* @version 0.1 Mon Jan 28 EST 2013
* @author Bob Futrelle
*/
public class JDBCVariableTest {
Connection db;
Statement st;
Boolean boo;
public static void main(String[] args) throws SQLException {
JDBCVariableTest testIt = new JDBCVariableTest();
testIt.helloVariables();
}
public int f1() { return 3;}
public void helloVariables() throws SQLException {
int intVar = f1(); // OK in Java, but SQL/JDBC?
try {
db = DriverManager.getConnection(jdbc:postgresql:Articles,
robertfutrelle, my pw);
st = db.createStatement();
boo = st.execute(CREATE TABLE IF NOT EXISTS hello ( Name VARCHAR NOT
NULL PRIMARY KEY, value int));
// Declare ??
// INSTEAD OF THE LITERAL 4 VALUE (which works)
// how do I declare a variable and assign the f1() return value to it
// and then include it so the value 3 appears in the inserted record?
//st.execute(insert into hello values('aKey',4));
st.execute(insert into hello values('bKey',4));
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
On Mon, Jan 28, 2013 at 8:39 AM, Dave Cramer p...@fastcrypt.com wrote:
Bob,
Can you provide a snippet of code so I can understand what you mean by
declare ?
Dave Cramer
dave.cramer(at)credativ(dot)ca
http://www.credativ.ca
On Mon, Jan 28, 2013 at 7:11 AM, Bob Futrelle bob.futre...@gmail.comwrote:
Do the declare statements and insert all have to be done in one
statement execute()?
That is, what is the scope of variables I declare?
I see a variety of syntax examples, some for older versions?
I'm using pg 9.2.2, so what are the rules/syntax for declaring and using
variables?
Use case: I collect metadata from XML articles such as title,
journalName, volume, year. For each article I need to create a pg table
record and insert the values for the various metadata items in the article.
This is my first post to this list.
Thanks,
- Bob
Re: [GENERAL] JDBC - Need to declare variables for values in insert statement
I had read 'through' the JDBC material,
but now reading more deeply with more insight.
The API is useful too.
Anyhoo, PreparedStatement works like a charm, viz.,
PreparedStatement pstmt= db.prepareStatement(insert into hello values
('cKey', ?));
pstmt.setInt(1,intVar);
pstmt.execute();
This also worked, include the function directly, skip the java field. Nice.
pstmt.setInt(1,f1());
Makes sense, because the setInt() API says that the value resides in the
Java world.
Sets the designated parameter to the given Java int value.
I have about 250 source files on my machine that use PreparedStatements.
I wrote a few of them, but usually by grabbing code from the web.
The rest are demos or parts of systems I used for some of my work.
Used embedded Derby = JavaDB back then - history now.
The fact that the code contained a few PreparedStatements was not what I
focused on.
The code just worked, doing what I needed.
My new understanding now allows me to do a ton of good NLP stuff.
Back to the future.
Thanks all,
- Bob
On Mon, Jan 28, 2013 at 2:22 PM, Edson Richter edsonrich...@hotmail.comwrote:
I would recommend the reading of the excellent The Java Tutorial, that
has a very well explained section about JDBC:
http://docs.oracle.com/javase/tutorial/jdbc/index.html
and the chapter about PreparedStatements:
http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html
Regards,
Edson Richter
Em 28/01/2013 17:09, Bob Futrelle escreveu:
Thanks to Edson and Dave for lightning responses.
I'm confident that your answers will do the job.
I'll follow up on the advice AFTER I get my coffee ;-)
I'm really focused on the NLP content of my research,
but I need a DB infrastructure to do it right.
(Not a bunch of files as in ancient times.)
--- Bob
On Mon, Jan 28, 2013 at 1:59 PM, Edson Richter
edsonrich...@hotmail.comwrote:
You have two options:
st.execute(insert into hello values ('bKey', +f1()+));
or
PreparedStatement st = db.prepareStatement(insert into hello values
('bKey', ?));
st.setInteger(1, f1());
where 1 is the first parameter, 2 is the second parameter, and so on.
Regards,
Edson Richter
Em 28/01/2013 16:50, Bob Futrelle escreveu:
Here's a small, but complete code example - Bob
package us.tsos.dbs.pg;
import java.sql.*;
/**
* This is an effort to get a computed value from a Java function
* (or data object) included properly in the VALUES entries.
* So, how can I declare an SQL variable and set its value to some Java
value?
* Results viewed in pgAdmin3 with query 'select * from public.hello'.
*
* Jar in classpath is postgresql-9.2-1002.jdbc4.jar
*
* @version 0.1 Mon Jan 28 EST 2013
* @author Bob Futrelle
*/
public class JDBCVariableTest {
Connection db;
Statement st;
Boolean boo;
public static void main(String[] args) throws SQLException {
JDBCVariableTest testIt = new JDBCVariableTest();
testIt.helloVariables();
}
public int f1() { return 3;}
public void helloVariables() throws SQLException {
int intVar = f1(); // OK in Java, but SQL/JDBC?
try {
db = DriverManager.getConnection(jdbc:postgresql:Articles,
robertfutrelle, my pw);
st = db.createStatement();
boo = st.execute(CREATE TABLE IF NOT EXISTS hello ( Name VARCHAR NOT
NULL PRIMARY KEY, value int));
// Declare ??
// INSTEAD OF THE LITERAL 4 VALUE (which works)
// how do I declare a variable and assign the f1() return value to it
// and then include it so the value 3 appears in the inserted record?
//st.execute(insert into hello values('aKey',4));
st.execute(insert into hello values('bKey',4));
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
On Mon, Jan 28, 2013 at 8:39 AM, Dave Cramer p...@fastcrypt.com wrote:
Bob,
Can you provide a snippet of code so I can understand what you mean by
declare ?
Dave Cramer
dave.cramer(at)credativ(dot)ca
http://www.credativ.ca
On Mon, Jan 28, 2013 at 7:11 AM, Bob Futrelle bob.futre...@gmail.comwrote:
Do the declare statements and insert all have to be done in one
statement execute()?
That is, what is the scope of variables I declare?
I see a variety of syntax examples, some for older versions?
I'm using pg 9.2.2, so what are the rules/syntax for declaring and
using variables?
Use case: I collect metadata from XML articles such as title,
journalName, volume, year. For each article I need to create a pg table
record and insert the values for the various metadata items in the article.
This is my first post to this list.
Thanks,
- Bob
