[SQL] new table with a select
Hello to all, Can anybody tell me the sql instruction to create a new table with a select of other table? I need to create a new table based on paralell of a table of lines. Can anybody post to me a example? Can?t be a buffer, must be lines. Thanks -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] new table with a select
create table foo as select * from bar; just add "where 1=2" if you just want the schema On 08/25/2011 11:44, pp...@sapo.pt wrote: Hello to all, Can anybody tell me the sql instruction to create a new table with a select of other table? I need to create a new table based on paralell of a table of lines. Can anybody post to me a example? Can?t be a buffer, must be lines. Thanks -- No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. <> -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] new table with a select
* Julien Cigar: > create table foo as select * from bar; > > just add "where 1=2" if you just want the schema There's also this: CREATE TABLE foo (LIKE bar); This gives you more control over what aspects of the table are duplicated; see the documentation for details. -- Florian Weimer BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Add one column to another
I have to deal with a table which contains: first_name surname email1 email2 ... and I would like to create a view which combines both email columns thus: first_name surname email It looks simple but I can't think of an obvious query. gvim -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Add one column to another
Use the concat || operator. On Thu, 2011-08-25 at 15:21 +0100, gvim wrote: > I have to deal with a table which contains: > > first_name > surname > email1 > email2 > > ... and I would like to create a view which combines both email columns thus: > > first_name > surname > email > > It looks simple but I can't think of an obvious query. > > gvim > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Add one column to another
Something like this...? SELECT first_name,surname, email1 || ';' || email2 FROM t_your_table; Best, Oliver - Original Message - From: "gvim" To: "pgsql sql" Sent: Thursday, August 25, 2011 3:21 PM Subject: [SQL] Add one column to another I have to deal with a table which contains: first_name surname email1 email2 ... and I would like to create a view which combines both email columns thus: first_name surname email It looks simple but I can't think of an obvious query. gvim -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Add one column to another
I have to deal with a table which contains: first_name surname email1 email2 ... and I would like to create a view which combines both email columns thus: first_name surname email It looks simple but I can't think of an obvious query. --- SELECT first_name, surname, ARRAY[email1, email2] AS email FROM [...] David J. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Add one column to another
(anonymous) wrote: > I have to deal with a table which contains: > first_name > surname > email1 > email2 > ... and I would like to create a view which combines both email columns thus: > first_name > surname > email > It looks simple but I can't think of an obvious query. Try: | SELECT first_name, surname, email1 AS email FROM testtable WHERE email1 IS NOT NULL UNION ALL | SELECT first_name, surname, email2 AS email FROM testtable WHERE email2 IS NOT NULL; Tim -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Add one column to another
On Thu, Aug 25, 2011 at 8:52 AM, Oliveiros d'Azevedo Cristina wrote: > Something like this...? > > SELECT first_name,surname, email1 || ';' || email2 > FROM t_your_table; If there's any nulls in email1 or email2 they'll need special handling with coalesce. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Add one column to another
Check this link. It's useful to resolve the issue. http://blog.ropardo.ro/2010/05/04/extending-postgresql-a-better-concat-operator/ - Vipul On Thu, Aug 25, 2011 at 12:54 PM, Scott Marlowe wrote: > On Thu, Aug 25, 2011 at 8:52 AM, Oliveiros d'Azevedo Cristina > wrote: > > Something like this...? > > > > SELECT first_name,surname, email1 || ';' || email2 > > FROM t_your_table; > > If there's any nulls in email1 or email2 they'll need special handling > with coalesce. > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
[SQL] st_offsetcurve
Hello I want to use the function st_offsetCurve but the pgadmin don't have and i don't find in the site of pgadmin. CAn anybody send me? thanks -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Need a little help with geometric query
This is my first shot at using postgresql's geometric functions I have a passel of lines defined by x1 y1 x2 y2 I created a table with type lseg and loaded it I start by finding the line that has an end closest to 0,0 Now I want to find the next line that has an end closest to the first line's other end and so on I want to find the shortest path I see there are a lot of geometric operators but I am a bit baffled Can someone offer suggestions or reading references Thanks Richard -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] st_offsetcurve
On Aug 25, 2011, at 1:17 PM, pp...@sapo.pt wrote: > > Hello > > > I want to use the function st_offsetCurve but the pgadmin don't have and i > don't find in the site of pgadmin. CAn anybody send me? > > thanks > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql I believe that function is part of PostGIS. You'll need to have that installed and your database PostGIS enabled. The PostGIS website has instructions on how to install it. http://postgis.refractions.net/ This needs to be done on the server end. pgAdmin will just pass the function call on to the server. Brent D. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Need a little help with geometric query
On Thu, Aug 25, 2011 at 3:56 PM, wrote: > This is my first shot at using postgresql's geometric functions > I have a passel of lines defined by x1 y1 x2 y2 > I created a table with type lseg and loaded it > I start by finding the line that has an end closest to 0,0 > Now I want to find the next line that has an end closest to the first > line's other end and so on > I want to find the shortest path > I see there are a lot of geometric operators but I am a bit baffled > Can someone offer suggestions or reading references > Forget about the built-in geometry types and operators in postgres. You really want to take a look at postGIS for doing geometric calculations. It has a much more diverse set of operators and functions and adheres to various GIS standards which will allow your db to interact with other GIS tools as well. It is quite easy to learn and there is an excellent book which, I believe, also has an electronic edition available so you can start reading immediately. Finding a geometry that is 'closest' to a particular point or to another 2D geometry (or even 3D in the upcoming version) is relatively trivial. I'm sure there is a function, or at least a common technique, for finding a shortest path between 2 points, since GIS is often used for mapping applications. It's not my specialty, so I'll leave the details to your research.
Re: [SQL] (pgsql8.4) DATA Corruption
On 20/08/2011 4:27 AM, Mik Rose wrote: Thanks for the suggestions Scott. I was looking at the zero_damage_page option and enabled it in my postgres.conf. I saw that it detected bad pages and zero'd them out during a number of reindex and a vacuum's. Now when i reindex though, I am getting this error. NOTICE: table "pg_foreign_server" was reindexedERROR: could not create unique index "pg_toast_57366_index" DETAIL: Table contains duplicated values. ERROR: could not create unique index "pg_toast_57366_index" DETAIL: Table contains duplicated values. You'll have to figure out what what values are duplicates within each table and DELETE one of them after determining which is the correct one - if you can. Then you should be able to re-create the index. If the DELETE fails you might find you have to DROP the index, do the DELETE(s), then CREATE the index again. If the index is a partial index (ie it has a WHERE clause) you need to find duplicates that match that WHERE clause, you can ignore ones that don't match. I hope you took a file-system-level backup of your database before starting to mess with it. People here will normally advise you to do that first and I'm kind of surprised nobody did. -- Craig Ringer
[SQL] new table with a select
Hello to all, Can anybody tell me the sql instruction to create a new table with a select of other table? I need to create a new table based on paralell of a table of lines. Can anybody post to me a example? Can?t be a buffer, must be lines. Thanks -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
