[GENERAL] creating a table based on a table in stored in another database
Hi list, in a database I have different kind of tables. I would like to take the meta data from one of those tables and create the same type of table (but empty) in another database. Can anyone, please, tell me how to do this? Kind regards, Paul -- 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] creating a table based on a table in stored in another database
In response to Malm Paul : > Hi list, > in a database I have different kind of tables. I would like to take the meta > data from one of those tables and create the same type of table (but empty) > in another database. > Can anyone, please, tell me how to do this? Create a schema-only dump and restore it into the other database. Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] creating a table based on a table in stored in another database
I can add that I would like to do this in my java application.
E.i. just create an empty table in database1 which has the same column names
and types as a table stored in database2.
I tried this for a start:
stmnt = dbConnection.prepareStatement("select dblink_connect('myconn',
'dbname=gemaps')");
resultset = stmnt.executeQuery();
but got ERROR: function dblink_connect(unknown, unknown) does not exist
/P
-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of A. Kretschmer
Sent: den 18 maj 2010 10:16
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] creating a table based on a table in stored in another
database
In response to Malm Paul :
> Hi list,
> in a database I have different kind of tables. I would like to take the meta
> data from one of those tables and create the same type of table (but empty)
> in another database.
> Can anyone, please, tell me how to do this?
Create a schema-only dump and restore it into the other database.
Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] postgreSQL enquiry
Hi I was on your website trying to look up information on PostgreSQL and the information that I needed was hard to get or just couldnt find it. Can you please clarify whether these requirements are covered by postgreSQL product: - data stored in the database includes text, numbers, pictures and even video clips - avoids vendor 'lock ins' --> open DBMS - allows for a web-based end (web 2.0) - fully searchable by anyone from anywhere and allows for heavy traffic - compatible with web-based applications on mobile devices etc - allows for a custom version of this site for managers where they can perform internal functions (e.g. billing, commission etc) - 2 main users in different cities - with approximately $250,000 budget for hardware and software Thankyou _ Looking for a place to manage all your online stuff? Download the new Windows Live http://download.live.com
[GENERAL] postgres installation
respected sir/mam, i am having laptop TOSHIBA satellite L505 which is of 64 bit and i am not able to install postgres on that. i think their is problem of selecting ENCODING parameter. so please help me out as early as possible. thanks virendra -- 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] postgres installation
On 18 May 2010 08:00, VIRENDRA MAGDUM wrote: > respected sir/mam, > i am having laptop TOSHIBA satellite L505 which is of > 64 bit and i am not able to install postgres on that. i think their is > problem of selecting ENCODING parameter. > so please help me out as early as possible. > > thanks > virendra Which operating system are you attempting to install it on, and how are you installing it? Thom -- 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] postgreSQL enquiry
On Tue, May 18, 2010 at 2:12 PM, Charo Carino wrote: > - data stored in the database includes text, numbers, pictures and even > video clips > - avoids vendor 'lock ins' --> open DBMS > - allows for a web-based end (web 2.0) > - fully searchable by anyone from anywhere and allows for heavy traffic > - compatible with web-based applications on mobile devices etc > - allows for a custom version of this site for managers where they can > perform internal functions (e.g. billing, commission etc) > - 2 main users in different cities > - with approximately $250,000 budget for hardware and software That looks like requirements specification for a web application. You can use PostgreSQL as the DBMS for this application. 1. text, numbers, pictures can be stored 2. No vendor lock in (open DBMS) Other requirements are not directly related to database. Best regards, Ma Sivakumar மா சிவகுமார் எல்லோரும் எல்லாமும் பெற வேண்டும் http://masivakumar.blogspot.com -- 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] postgreSQL enquiry
On 18 May 2010 09:42, Charo Carino wrote: > Hi > I was on your website trying to look up information on PostgreSQL and the > information that I needed was hard to get or just couldnt find it. Can you > please clarify whether these requirements are covered by postgreSQL product: > > - data stored in the database includes text, numbers, pictures and even > video clips Please see: http://www.postgresql.org/docs/current/static/datatype.html > - avoids vendor 'lock ins' --> open DBMS http://www.postgresql.org/docs/current/static/intro-whatis.html > - allows for a web-based end (web 2.0) That's less to do with the database system and more to do with whatever you're building your web-based interface with. Pretty much all languages have support for PostgreSQL. > - fully searchable by anyone from anywhere and allows for heavy traffic Again, who it's searchable by and from where isn't really to do with the database system. You'd be the one setting that up. And "heavy traffic" is a bit vague, but it is certainly used by many companies in high traffic environments. > - compatible with web-based applications on mobile devices etc > - allows for a custom version of this site for managers where they can > perform internal functions (e.g. billing, commission etc) Nothing to do with databases themselves really. They'll all do this if you write something to do it. > - 2 main users in different cities > - with approximately $250,000 budget for hardware and software PostgreSQL is completely free and runs extremely well when configured appropriately for your hardware and the types of data/queries you'll store/use in it. Regards Thom -- 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] Workqueue performance
Thank-you for the tips about this issue. I found two things: 1. NOTIFY/LISTEN was causing performance to degrade badly over time. I have reworked my code to poll the database instead. 2. There was a further function trigger that I didn't include in the description. I wanted to keep a status of the jobs in the workqueue. I created two triggers on the job table, that would update a 'status' table: > create table fileworkqueue.status(filetype_id smallint not null, num integer > not null default 0 check (num >= 0)); > CREATE FUNCTION fileworkqueue.add_status() RETURNS TRIGGER AS $$ BEGIN UPDATE fileworkqueue.status SET num=num+1 WHERE filetype_id=NEW.filetype_id; return NULL; END; $$ LANGUAGE plpgsql; > CREATE FUNCTION fileworkqueue.del_status() RETURNS TRIGGER AS $$ BEGIN UPDATE fileworkqueue.status SET num=num-1 WHERE filetype_id=OLD.filetype_id; RETURN NULL; END; $$ LANGUAGE plpgsql; CREATE TRIGGER fileworkqueue_add_trigger AFTER INSERT ON fileworkqueue.job FOR EACH ROW EXECUTE PROCEDURE fileworkqueue.add_status(); CREATE TRIGGER fileworkqueue_del_trigger AFTER DELETE ON fileworkqueue.job FOR EACH ROW EXECUTE PROCEDURE fileworkqueue.del_status(); So there were actually two triggers in the original design: log.file -> fileworkqueue.job -> fileworkqueue.status When I removed the second trigger to the 'status' table, performance jumped tenfold. But now I had no means of monitoring how my workqueue was performing. I decided to do this in application code instead, via IPC. -- Jason Armstrong -- 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] postgreSQL enquiry
On 18/05/2010 4:42 PM, Charo Carino wrote: Hi I was on your website trying to look up information on PostgreSQL and the information that I needed was hard to get or just couldnt find it. Can you please clarify whether these requirements are covered by postgreSQL product It sounds to me like you want a web content management / document management system. While such a product will *use* a database, tht's only a part of what it does. You can build something like that with PostgreSQL (though I'd store big files in the file system and only paths in Pg) but PostgreSQL does not do all those things by its self. -- Craig Ringer -- 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] Workqueue performance
Jason Armstrong writes: > 1. NOTIFY/LISTEN was causing performance to degrade badly over time. I > have reworked my code to poll the database instead. FWIW, you need to ensure pg_listener gets vacuumed pretty aggressively in order to prevent degradation in a high-traffic NOTIFY application. PG 9.0 will have a completely rewritten LISTEN/NOTIFY implementation that avoids use of a table and should scale a lot better, as well as not needing vacuuming support. That doesn't help you right now, but depending on what your development timescale is, you might want to plan to go back to LISTEN/NOTIFY later. regards, tom lane -- 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] creating a table based on a table in stored in another database
On Tue, May 18, 2010 at 4:45 AM, Malm Paul wrote:
> I can add that I would like to do this in my java application.
> E.i. just create an empty table in database1 which has the same column
> names and types as a table stored in database2.
>
> I tried this for a start:
> stmnt = dbConnection.prepareStatement("select dblink_connect('myconn',
> 'dbname=gemaps')");
> resultset = stmnt.executeQuery();
>
> but got ERROR: function dblink_connect(unknown, unknown) does not exist
>
>
Do you:
A) Install the dblink contrib module?
B) Run the dblink SQL file?
--Scott
/P
> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:
> pgsql-general-ow...@postgresql.org] On Behalf Of A. Kretschmer
> Sent: den 18 maj 2010 10:16
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] creating a table based on a table in stored in
> another database
>
> In response to Malm Paul :
> > Hi list,
> > in a database I have different kind of tables. I would like to take the
> meta data from one of those tables and create the same type of table (but
> empty) in another database.
> > Can anyone, please, tell me how to do this?
>
> Create a schema-only dump and restore it into the other database.
>
>
> Regards, Andreas
> --
> Andreas Kretschmer
> Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
> GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make
> changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
[GENERAL] pg_restore to log file
Hi All - Is there a way to direct the message generated during pg_restore to a log file? Regards
Re: [GENERAL] pg_restore to log file
On 18 May 2010 17:03, akp geek wrote: > Hi All - > Is there a way to direct the message generated during > pg_restore to a log file? > Regards > I think you can use "-f outputfile". Regards Thom -- 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] pg_restore to log file
pg_restore -h 10.xx.xx.xxx -d dbName -n schemaName -v -U postgres testDB_20100414.txt-f outputifile I got the following error "pg_restore: options -d/--dbname and -f/--file cannot be used together" Regards On Tue, May 18, 2010 at 12:07 PM, Thom Brown wrote: > On 18 May 2010 17:03, akp geek wrote: > > Hi All - > > Is there a way to direct the message generated during > > pg_restore to a log file? > > Regards > > > > I think you can use "-f outputfile". > > Regards > > Thom >
Re: [GENERAL] pg_restore to log file
On 18 May 2010 17:11, akp geek wrote: > pg_restore -h 10.xx.xx.xxx -d dbName -n schemaName -v -U postgres > testDB_20100414.txt -f outputifile > > I got the following error "pg_restore: options -d/--dbname and -f/--file > cannot be used together" > > Regards > That's odd. I can't see why those settings should conflict, although I notice you haven't specified your input file as the last parameter. It shouldn't appear before the other arguments. Anyone else know? Thom -- 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] pg_restore to log file
On Tue, May 18, 2010 at 12:11 PM, akp geek wrote: > pg_restore -h 10.xx.xx.xxx -d dbName -n schemaName -v -U postgres > testDB_20100414.txt-f outputifile > > > I got the following error "pg_restore: options -d/--dbname and -f/--file > cannot be used together" > I think you would either capture stdout or stderr (I'm honestly not sure which is required) pg_restore -all options >output.txt 2>&1 --Scott > > > Regards > > > > > On Tue, May 18, 2010 at 12:07 PM, Thom Brown wrote: > >> On 18 May 2010 17:03, akp geek wrote: >> > Hi All - >> > Is there a way to direct the message generated during >> > pg_restore to a log file? >> > Regards >> > >> >> I think you can use "-f outputfile". >> >> Regards >> >> Thom >> > >
Re: [GENERAL] pg_restore to log file
Thanks a lot pg_restore -all options >output.txt 2>&1 worked Regards On Tue, May 18, 2010 at 12:15 PM, Scott Mead wrote: > > On Tue, May 18, 2010 at 12:11 PM, akp geek wrote: > >> pg_restore -h 10.xx.xx.xxx -d dbName -n schemaName -v -U postgres >> testDB_20100414.txt-f outputifile >> >> >> I got the following error "pg_restore: options -d/--dbname and -f/--file >> cannot be used together" >> > > I think you would either capture stdout or stderr (I'm honestly not sure > which is required) > > > pg_restore -all options >output.txt 2>&1 > > --Scott > >> >> >> Regards >> >> >> >> >> On Tue, May 18, 2010 at 12:07 PM, Thom Brown wrote: >> >>> On 18 May 2010 17:03, akp geek wrote: >>> > Hi All - >>> > Is there a way to direct the message generated during >>> > pg_restore to a log file? >>> > Regards >>> > >>> >>> I think you can use "-f outputfile". >>> >>> Regards >>> >>> Thom >>> >> >> >
Re: [GENERAL] pg_restore to log file
On Tue, May 18, 2010 at 12:15 PM, Thom Brown wrote: > On 18 May 2010 17:11, akp geek wrote: > > pg_restore -h 10.xx.xx.xxx -d dbName -n schemaName -v -U postgres > > testDB_20100414.txt-f outputifile > > > > I got the following error "pg_restore: options -d/--dbname and -f/--file > > cannot be used together" > > > > Regards > > > > That's odd. I can't see why those settings should conflict, although > I notice you haven't specified your input file as the last parameter. > It shouldn't appear before the other arguments. > > Yeah, the output file isn't for messages, it's for the data. You can restore a compressed dump to two places, either a text file (-f) or a database (-d). --Scott > Anyone else know? > > Thom > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
[GENERAL] use of IN() with literals
I'm trying to use the following script: (to give command line ability to change grant on all tables in public in a database) psql -t -c “SELECT ‘GRANT $1 ON public.’ || t.relname || ‘ TO $2;’ from pg_class t, pg_namespace s WHERE t.relkind IN (‘r’, ‘v’, ‘S’) AND t.relnamespace=s.oid AND s.nspname=’public’;” $3 | psql $3 and it always fails at the "IN(‘r’, ‘v’, ‘S’)" part. psql won't accept the literals in the IN clause. Is this normal? What could fix this? I've tried just doing: ( after logging in to psql connected to a specific database) select * from pg_class where relkind IN IN (‘r’, ‘v’, ‘S’); and that doesn't work either. Dennis Gearon Signature Warning EARTH has a Right To Life, otherwise we all die. Read 'Hot, Flat, and Crowded' Laugh at http://www.yert.com/film.php -- 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] use of IN() with literals
Dennis Gearon wrote on 18.05.2010 19:05:
select * from pg_class where relkind IN IN (‘r’, ‘v’, ‘S’);
^^ ^ ^
You repeated the keyword IN, and you are using the wrong quotes (unless this is a
copy & paste problem of a broken email client)
select *
from pg_class
where relkind IN ('r', 'v', 'S');
should work
--
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] use of IN() with literals
On Tue, 18 May 2010 10:05:49 -0700 (PDT), Dennis Gearon wrote about [GENERAL] use of IN() with literals: >I'm trying to use the following script: (to give command line ability >to change grant on all tables in public in a database) > >psql -t -c “SELECT ‘GRANT $1 ON public.’ || t.relname || ‘ TO $2;’ >from pg_class t, pg_namespace s WHERE t.relkind IN (‘r’, ‘v’, ‘S’) AND >t.relnamespace=s.oid AND s.nspname=’public’;” $3 | psql $3 > >and it always fails at the "IN(‘r’, ‘v’, ‘S’)" part. psql won't accept >the literals in the IN clause. Is this normal? What could fix this? It works for me, using 8.4.2. >I've tried just doing: >( >after logging in to psql connected to a specific database) > >select * from pg_class where relkind IN IN (‘r’, ‘v’, ‘S’); You have the word "IN" twice. -- Regards, Dave [RLU #314465] == dwn...@ntlworld.com (David W Noon) == signature.asc Description: PGP signature
[GENERAL] Connection lost
Hello! I am using postgress 8.4. I am trying to save to my DB a Large Binary
Object, in localhost, let's say 1.4MB. I read that LargeObjectAPI should be
used.
I have a problem that sometimes i can store the file and some others i get
an exception of
>>org.postgresql.util.PSQLException: An I/O error occured while sending to
the backend.
>>java.net.SocketException: Socket closed
I try to create a connection for each object but the errors persist with big
files. For smaller files like 13KB this example works great and several in
simultaneous. Does anybody have this problem??? I debug the applcation and I
am getting the socket close exception during obj.write () here is a bit
of the code
mDbConnector.setAutoCommit(false);
// Get the Large Object Manager to perform operations with
LargeObjectManager lobj =
((org.postgresql.PGConnection)mDbConnector).getLargeObjectAPI();
// Create a new large object
int oid = lobj.create(LargeObjectManager.READ | LargeObjectManager.WRITE);
// Open the large object for writing
LargeObject obj = lobj.open(oid, LargeObjectManager.WRITE);
byte []data = cont.getData();
obj.write(data,0,data.length);
obj.close();
PreparedStatement statement = mDbConnector.prepareStatement("INSERT INTO
publicitiescontent (IdPublicities,MimeType,FileName,Title,Data) VALUES
(?,?,?,?,?)");
statement.setBigDecimal(1, new BigDecimal(publicityId));
statement.setString(2, cont.getMimeType());
statement.setString(3, cont.getFileName());
statement.setString(4, cont.getNombre());
statement.setInt(5, oid);
statement.execute();
statement.close();
mDbConnector.commit();
Thanks in advance,
Hernan
[GENERAL] How to return an Int4 when subtracting dates/timestamps
Hi, I need to return an Int4 when I do this king of select [code] select CURRENT_DATE - '2009-12-31' from tbl_sometable [/code] This select returns an Interval. How can I return an Integer? Like '138' Sorry my bad english. Best Regards,
Re: [GENERAL] How to return an Int4 when subtracting dates/timestamps
Andre Lopes wrote: > I need to return an Int4 when I do this king of select > [code] > select CURRENT_DATE - '2009-12-31' from tbl_sometable > [/code] > This select returns an Interval. How can I return an Integer? Like '138' That expression returning an interval would be contradictory to the documentation and a simple test: | tim=# SELECT CURRENT_DATE - '2009-12-31'; | ?column? | -- | 138 | (1 Zeile) | tim=# SELECT CURRENT_DATE::TIMESTAMP - '2009-12-31'; | ?column? | -- | 138 days | (1 Zeile) | tim=# So how far away from "this kind of select" is your actual query? Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] ERROR: unrecognized time zone name: "UTC"
I am experiencing this error trying to set the connection time zone to UTC on PostgreSQL 8.4.4: postgres=# set time zone 'UTC'; ERROR: unrecognized time zone name: "UTC" I have read the documentation for timezone_abbreviations, and everything looked in order, at least as far as my limited knowledge takes me: postgres=# show timezone_abbreviations; timezone_abbreviations Default (1 row) $ grep UTC `pg_config --sharedir`/timezonesets/Default UTC 0# Coordinated Universal Time Setting the time zone to 'CST6CDT' appears to work fine: postgres=# set time zone 'CST6CDT'; SET Is there something I'm doing wrong here? I have a slightly odd configuration (Gentoo Prefix Portage on Mac OS X, with a custom ebuild) so the problem may well be with my system setup. But I'd appreciate any pointers folks may have as to where to troubleshoot. Thanks! -be -- 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] use of IN() with literals
Yep bad scraping from one site to another. Probably encoding. Thanks for telling me what (should) have been obvious about the two INs. The gobbledy gook was bad encoding between the two web pages. select Dennis Gearon Signature Warning EARTH has a Right To Life, otherwise we all die. Read 'Hot, Flat, and Crowded' Laugh at http://www.yert.com/film.php -- 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] ERROR: unrecognized time zone name: "UTC"
On Tue, May 18, 2010 at 8:15 PM, Brad Ediger wrote: > I am experiencing this error trying to set the connection time zone to > UTC on PostgreSQL 8.4.4: > > postgres=# set time zone 'UTC'; > ERROR: unrecognized time zone name: "UTC" Answered my own question -- it was my Prefix Portage install missing the zoneinfo database ($EPREFIX/usr/share/zoneinfo/UTC). Working great now. -be -- 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] Connection lost
On Tue, May 18, 2010 at 1:18 PM, Hernan Danielan wrote: > Hello! I am using postgress 8.4. I am trying to save to my DB a Large Binary > Object, in localhost, let's say 1.4MB. I read that LargeObjectAPI should be > used. > I have a problem that sometimes i can store the file and some others i get > an exception of >>>org.postgresql.util.PSQLException: An I/O error occured while sending to >>> the backend. >>>java.net.SocketException: Socket closed Do the PostgreSQL logs include any useful information? -- Joshua Tolley / eggyknap End Point Corporation -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] default ordering of query result - are they always guarantee
I have the following table : CREATE TABLE measurement_1 ( measurement_id serial NOT NULL, fk_unit_id int NOT NULL, "value" double precision, measurement_type text NOT NULL, measurement_unit text NOT NULL ); When I want to retrieve the query. By default, the query result are ordered in ascending order, by using measurement_id. SELECT measurement_type, value, measurement_unit FROM measurement_1 This is the default behavior I want. However, I am not sure whether this is always guarantee? Or shall I explicitly make the query in the following form? Will this have performance impact on row with millions? SELECT measurement_type, value, measurement_unit FROM measurement_1 ORDER BY measurement_id ASC Thanks. by ensuring measurement_1 result are ordered in ascending Thanks and Regards Yan Cheng CHEOK -- 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] default ordering of query result - are they always guarantee
Le 19/05/2010 05:06, Yan Cheng CHEOK a écrit : > I have the following table : > > CREATE TABLE measurement_1 > ( > measurement_id serial NOT NULL, > fk_unit_id int NOT NULL, > "value" double precision, > measurement_type text NOT NULL, > measurement_unit text NOT NULL > ); > > When I want to retrieve the query. By default, the query result are ordered > in ascending order, by using measurement_id. > > SELECT measurement_type, value, measurement_unit > FROM > measurement_1 > > This is the default behavior I want. However, I am not sure whether this is > always guarantee? Or shall I explicitly make the query in the following form? An you're right. It's not guaranted. The only guaranted way is to use ORDER BY your_column. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] metadata on a table
Hi,
I'm trying to read the metadata from table to create the same sort of table in
another database, using java.
This is how I do it today
quwstion = "SELECT * FROM table"
stmnt = dbConnection.prepareStatement(question);
rs = stmnt.executeQuery();
ResultSetMetaData rsMetaData = rs.getMetaData();
int numberOfColumns = rsMetaData.getColumnCount();
System.out.println("resultSet MetaData column Count=" + numberOfColumns);
int precision = rsMetaData.getPrecision(1);
String columnTypeName = rsMetaData.getColumnTypeName(1);
String name = rsMetaData.getColumnName(1);
String classname = rsMetaData.getColumnClassName(1);
The first object is the primary key with the name "fid",
columnTypeName = int4
I can see in the original table that fid has the type serial, which is an int4
(pgAdmin).
But when I create the table in the other database I would like to create it as
serial not just int4, how can I know that it is a serial?
Kind regards,
Paul
