for schema in 'public' 'firstschema' 'secondschema'
do
for tbl in `psql -h hostname -U postgres -qAt -c "select tablename from pg_tables where schemaname = '$schema';" bddname`
do
psql -h hostname -U postgres -c "alter table ${schema}.$tbl owner to username" bddname
done
done
for schema in 'public' 'firstschema' 'secondschema'
do
for ...SELECT cprov, array_to_string(ARRAY(SELECT cine FROM ciudades.ciudades where cprov = c.cprov),',') FROM ciudades.ciudades c WHERE poblacion > 14999 GROUP BY cprov
copy <tablename> (field1, field2, ... ) from '/path/file' with csv header delimiter E'\t' quote E'\'';
#!/bin/bash
for bdd in 'dbname01' 'dbname02' 'dbname03'
do
`echo "Procesando $bdd" >> salida.log`
`echo "Procesando $bdd" >> error.log`
`pg_dump -C -Fp -h hostname -U postgres ${bdd} | gzip -c > ${bdd}_dump.sql.gz `
#`gunzip < ${bdd}_dump.sql.gz | psql -h hostname -U postgres postgres -f - > salida_restore.log`
done
For performing ...
more ...The COALESCE function returns the first of its arguments that is not null. Null is returned only if all arguments are null. It is often used to substitute a default value for null values when data is retrieved for display, for example:
COALESCE(value [, ...])
Like a CASE expression, COALESCE only ...
more ...#!/bin/bash
for table in `echo "SELECT relname FROM pg_stat_all_tables;" | psql dbname | grep -v "pg_" | grep "^ "`;
do
echo "GRANT ALL ON TABLE $table to username;"
echo "GRANT ALL ON TABLE $table to username;" | psql dbname
done
Stop the postgres service
Create the folder for the new cluster and assing as proprietary to the user and group postgres [/home/postgres/data]
Run as postgres user
$ initdb -D /home/postgres/data
Change several things in /etc/postgresql/9.1/main/postgres.cnf:
SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary DESC) FROM empsalary;
Result:
depname | empno | salary | rank -----------+-------+--------+------ develop | 8 | 6000 | 1 develop | 10 | 5200 | 2 develop | 11 | 5200 | 2 develop | 9 | 4500 | 4 develop | 7 | 4200 | 5 personnel | 2 | 3900 | 1 personnel | 5 | 3500 | 2 sales | 1 ...more ...
To convert numbers to text:
SELECT to_char(1, 'FM09');
Using FM to avoid the system adding a blank space before the number
more ...For using crosstab in table pivoting it is recommended that all categories have at least one value, for garanting this we use $$VALUES ()$$ defining the category descripcion
SELECT *
FROM crosstab(
'select mil_cagr
, case when extract(year from age(mil_f_nac)) < 26 THEN 1
WHEN extract(year from age(mil_f_nac)) > 25 AND ...