[GENERAL] select from table1 and table3 where (how table1 and table3 are related) is stored in table2?

see http://sqlfiddle.com/#!15/e30d9/8/0 for schema and sql. http://stackoverflow.com/questions/12238621/sql-subquery-has-too-many-columns -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] how would you speed up this long query?

select sub_query_1.pid, sub_query_1.tit, sub_query_1.num, sub_query_3.cid, sub_query_3.id, sub_query_3.c, sub_query_3.s, sub_query_3.z, sub_query_3.cy, sub_query_3.cd, sub_query_3.cr, org.id as org__id, org.pid as org__pi

Re: [GENERAL] how would you speed up this long query?

> Version of PostgreSQL? 9.3 > Operating system? win > Hardware configuration? 8 gb ram. takes about 7000 ms to retrieve about 7000 rows. max_connections = 200 shared_buffers = 512mb effective_cache_size = 6gb work_mem = 13107kb maintenance_work_mem = 512mb checkpoint_segments = 32 checkpoint_com

[GENERAL] better architecture?

i need some advice: 1. for our small business, i have a master (dev) - slave (prod) setup. i develop using the master. i get data from other people's dev (mssql) databases. i also get data from their prod (mssql) databases. i replicate everything on slave. apps using the master can connect only t

Re: [GENERAL] better architecture?

combining replies for the list: On 11/19/14, Charles Zaffery wrote: > 2 and 3 can be covered by this: > http://clusterlabs.org/wiki/PgSQL_Replicated_Cluster does something similar exist for windows? On 11/20/14, Michael Paquier wrote: > On Thu, Nov 20, 2014 at 10:58 AM, zach crui

Re: [GENERAL] better architecture?

On 11/20/14, Adrian Klaver wrote: > On 11/20/2014 08:00 AM, zach cruise wrote: >> combining replies for the list: >> >> >> On 11/19/14, Charles Zaffery wrote: >>> 2 and 3 can be covered by this: >>> http://clusterlabs.org/wiki/PgSQL_Replicated_Cluste

Re: [GENERAL] better architecture?

On 11/20/14, Adrian Klaver wrote: > On 11/20/2014 11:02 AM, zach cruise wrote: >> On 11/20/14, Adrian Klaver wrote: >>> On 11/20/2014 08:00 AM, zach cruise wrote: >>>> combining replies for the list: >>>> > >>> Well it would depend on your se

Re: [GENERAL] better architecture?

On 11/20/14, Adrian Klaver wrote: > On 11/20/2014 12:30 PM, zach cruise wrote: >>> >>> For more info see: >>> >>> http://www.postgresql.org/docs/9.3/interactive/continuous-archiving.html >> to be clear- i change my 2 VMs setup {"1. master (dev)

Re: [GENERAL] better architecture?

On 11/20/14, Adrian Klaver wrote: > On 11/20/2014 04:57 PM, zach cruise wrote: >> On 11/20/14, Adrian Klaver wrote: >>> On 11/20/2014 12:30 PM, zach cruise wrote: >>>>> >>>>> For more info see: >>>>> >>>>> http://www.

Re: [GENERAL] better architecture?

On 11/22/14, Adrian Klaver wrote: > On 11/21/2014 07:38 PM, zach cruise wrote: >> On 11/20/14, Adrian Klaver wrote: >>> On 11/20/2014 04:57 PM, zach cruise wrote: >>>> On 11/20/14, Adrian Klaver wrote: >>>>> On 11/20/2014 12:30 PM, zach cr

[GENERAL] which is better- storing data as array or json?

for indexing, accessing, filtering and searching? as simple array- first name | last name | nicknames tom | jerry | {cat}, {mouse} as multi-dimensional array- first name | last name | nicknames tom | jerry | {cat, kat}, {mouse, mice} as simple json- first name | last name | nickna

Re: [GENERAL] which is better- storing data as array or json?

i can't keep creating tables or adding columns every time i need to add a nickname- this happens a lot. so i want to put everything in an array or json. remember rows can have different number of nicknames. On 2/17/15, David G Johnston wrote: > zach cruise wrote >> for index

[GENERAL] select where true, or select where input = '$var'

i want to select based on input, but if input is not provided or if input is empty, then i want to select all rows. 1 select * 2 from table 3 if input = '' then 4 where true 5 else 6 where input = '$sanitized_variable' 7 end if; (syntax error at 3) i also looked at 'case' but i don't think it a

Re: [GENERAL] select where true, or select where input = '$var'

empty, so select only matching rows/records where input is a column/field 7 end if; On 2/19/15, John R Pierce wrote: > On 2/19/2015 12:39 PM, zach cruise wrote: >> i want to select based on input, but if input is not provided or if >> input is empty, then i want to select all rows. &g

[GENERAL] upgrading to 9.3

moving from 8.1 to 9.3, and redesigning at the same time (using navicat and psql). have access to both 8.1 and 9.3. and by redesigning i mean, going from multiple databases to multiple schemas. so what's the best approach?

Re: [GENERAL] upgrading to 9.3

thanks andy! a little off-topic, but about database reorganization - is it recommended to group all sequences and domains under the public schema? or is a sequence tied to one table and is better in its separate schema? what about replication options for x64 systems since slony is not an option?

[GENERAL] database redesign

my response hasn't shown up on http://postgresql.1045698.n5.nabble.com/upgrading-to-9-3-td5777291.html so trying again. sorry if both show up. anyway, on database reorganization - is it recommended to group all sequences and domains under one public schema? or is a sequence tied to a table as its

[GENERAL] file system level backup

so i installed 9.3 x64 on two windows 2008 servers from one set of installation files. then i created databases on postgresql a, shutdown all the servers, copied /data/ (from "show data_directory") from postgresql a to postgresql b. in short, i did a full file system level backup ( http://www.postg

Re: [GENERAL] file system level backup

Questions: 1) Both servers are x64? > yes 2) What installation files(Graphical installer, source,etc)? > postgresql-9.3.0-1-windows-x64 from enterprisedb.com 3) Does the data directory have the config files? > yes however, postgresql b won't start ("not listening")? 4) How are you start

Re: [GENERAL] file system level backup

Questions: 1) Both servers are x64? > yes 2) What installation files(Graphical installer, source,etc)? > postgresql-9.3.0-1-windows-x64 from enterprisedb.com 3) Does the data directory have the config files? > yes however,

Re: [GENERAL] file system level backup

I am still not understanding. You have a 2008 server at a and one at b. You copied the Postgres data files from 2008 server a to 2008 server b. This did not work, so you say you restored the data directory on server b from an old copy of the data directory at b. So where did t

Re: [GENERAL] file system level backup

I am not sure if you have already answered it and I have somehow missed it: - Are these 'a' and 'b' on two different servers? ( I think they are on different servers) > yes - Did you stop the server on 'b' before you replaced the files and attempted a startup? > yes > thanks. it doesn't help. i

[GENERAL] design question: how to geocode multiple dynamic "city, country"?

i accept multiple "city, country" from users on-the-fly, and want to dynamically map them. i could create a table where i insert their multiple entries, and then geocode that table for display. but i also want to avoid giving write permission to the web user. i could create a schema and restrict

[GENERAL] what happens to postmaster?

...when i am importing a table (from oracle, or updating it), and a user queries that same table? is it ok to be concerned about corruption etc. using 8.1. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailp

Re: [GENERAL] what happens to postmaster?

, Jun 22, 2010 at 2:21 PM, Bill Moran wrote: > In response to zach cruise : > >> ...when i am importing a table (from oracle, or updating it), and a >> user queries that same table? > > I depends on a lot of information you haven't provided ... Is the > update insid

Re: [GENERAL] MySQL versus Postgres

john, you're running up against a culture here, and trying to answer the question: how to make a nerd cool? answer: it can't be done. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] ease of use sync

on projects where i ended up selecting oracle, *my* main reasons were (1) clustering/replication (2) cross-database query (3) promise of drcp in that order for (1), actually more for synchronization/transfer, i got a simple suggestion: while installing postgresql, why not ask the user to give ip o

[GENERAL] how to look for duplicate rows?

i have to clean a table that looks like so: create table test (sn integer, fname varchar(10), lname varchar(10)); insert into test values (1, 'adam', 'lambert'); insert into test values (2, 'john', 'mayer'); insert into test values (3, 'john', 'mayer'); insert into test values (4, 'mary', 'kay');

[GENERAL] (odbc) multiple step ole db generated error - date/timestamp column

when importing from oracle 10g, i get "multiple step ole db generated error". i narrowed this down to a date/timestamp column - actually to about 100 rows within that column, all = '01-JAN-01' (never null). there is another date/timestamp column that gets imported error-free, and other tables also

Re: [GENERAL] (odbc) multiple step ole db generated error - date/timestamp column

On Sun, Mar 22, 2009 at 3:39 AM, Craig Ringer wrote: > zach cruise wrote: >> when importing from oracle 10g > > Importing how? CSV dump and load? DB link of some sort? odbc (see email) specifically Microsoft OLE DB Provider for Oracle > Operating system and version? Oracle ver

Re: [GENERAL] (odbc) multiple step ole db generated error - date/timestamp column

ok i brought it in as varchar and cast as date. On Sun, Mar 22, 2009 at 12:27 PM, zach cruise wrote: > On Sun, Mar 22, 2009 at 3:39 AM, Craig Ringer > wrote: >> zach cruise wrote: >>> when importing from oracle 10g >> >> Importing how? CSV dump and load? DB

[GENERAL] ...where 'value' = array[]

i have table, like so: group.group_name (varchar) | group.group_array (varchar[]) - west coast | {CA,WA} east coast | {NY,MA} i can do this: select group_name from group where 'CA' = any(array['CA','WA']); but i need to select group_name where state_abbreviation is in group_array, something like:

[GENERAL] how to search for relation by name?

when i try to copy database (into another database), i get "relation does not exist" errors for 'super objects' like sequences. (that is fine since i am using pg_dump, not pg_dumpall) but there is one relation i can't find to recreate in the new database. how can i search database for relation by

Re: [GENERAL] how to search for relation by name?

ible(c.oid) > replace dt with your sequence name > pg_catalog has the information. > Thanks > Deepak > > On Wed, Apr 22, 2009 at 2:36 PM, zach cruise wrote: >> >> when i try to copy database (into another database), i get "relation >> does not exist" errors fo

[GENERAL] utf8 encoding for template0 and template1?

is it recommended to change encodings for template0 and template1 to utf8 (by recreating databases) for 8.1? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general