Where Postgres database files are saved in ubuntu?
In the postgres prompt, just execute this query: SHOW data_directory; Check also the Ubuntu manual: https://help.ubuntu.com/10.04/serverguide/C/postgresql.html
postgresql
Trying to add an Array Column to existing Postgres table
You need to specify a datatype. If you want an array of strings, use text: ALTER TABLE candidate ADD COLUMN blocked_companies text[]; if you want an array of numbers, use int: ALTER TABLE candidate ADD COLUMN blocked_companies int[]; More details can be found in the manual: http://www.postgresql.org/docs/current/static/arrays.html#ARRAYS-DECLARATION But in most cases using arrays is not … Read more
How to replace nulls with zeros in postgresql crosstabs
You can use coalesce: select account_number, coalesce(Attr1, 0) as Attr1, coalesce(Attr2, 0) as Attr2, etc
What is the CHARINDEX (SQL SERVER) equivalent in POSTGRESQL?
The equivalent function in postgresql is: strpos(string, substring) Or: position(substring in string) They are equivalent, just with different order in parameters. If you also need parameter start_location, you will need to pass a substring to strpos. You can find them in: https://www.postgresql.org/docs/current/functions-string.html
How to add numbers to grouped rows in postgresql group by clause
With help from this question and its answers: SELECT gid, capt, row_number() OVER (PARTITION BY capt ORDER BY gid) AS rnum FROM your_table_here ORDER BY gid; The row_number window function provides the count. The PARTITION BY statement in the OVER clause tells the database to restart its numbering with each change to capt. The ORDER … Read more
How long can PostgreSQL table names be?
According to the PostgreSQL documentation: identifiers…identify names of tables, columns, or other database objects.… The system uses no more than NAMEDATALEN-1 bytes of an identifier; longer names can be written in commands, but they will be truncated. By default, NAMEDATALEN is 64 so the maximum identifier length is 63 bytes. You can see this limit … Read more
pg_restore with -C option does not create the database
Exactly like @Eelke said – you’ve got in file wrote ‘create database’ so this database does not exist when you’re running script… That’s what for there is always ‘postgres’ database. Try this: pg_restore -C -d postgres -v -h xxhostxx -p 5432 -U xxuserxx test_pg_dump.dmp** And this should: connect to postgres database Create test database Disconnect … Read more
SqlAlchemy: count of distinct over multiple columns
The exact query can be produced using the tuple_() construct: session.query( func.count(distinct(tuple_(Hit.ip_address, Hit.user_agent)))).scalar()
Can’t use uuid and create an extension to use it
The operating system package that contains the extension is not installed. To install it: apt-get install postgresql-contrib-9.2
pg_restore toc error
this did the trick pg_dump database_name -c -Ft -f file_name.tar pg_restore -d database_name -c file_name.tar before this i was trying to restore with out including -c(clean) even though -c is included in pg_dump it is not used in pg_restore unless we say to use…