There’s three ways to get this sort of count, each with their own tradeoffs. If you want a true count, you have to execute the SELECT statement like the one you used against each table. This is because PostgreSQL keeps row visibility information in the row itself, not anywhere else, so any accurate count can … Read more
uuid-ossp is a contrib module, so it isn’t loaded into the server by default. You must load it into your database to use it. For modern PostgreSQL versions (9.1 and newer) that’s easy: CREATE EXTENSION IF NOT EXISTS “uuid-ossp”; but for 9.0 and below you must instead run the SQL script to load the extension. … Read more
Or ask your database: $ psql -U postgres -c ‘SHOW config_file’ or, if logged in as the ubuntu user: $ sudo -u postgres psql -c ‘SHOW config_file’
Grant usage/select to a single table If you only grant CONNECT to a database, the user can connect but has no other privileges. You have to grant USAGE on namespaces (schemas) and SELECT on tables and views individually like so: GRANT CONNECT ON DATABASE mydb TO xxx; — This assumes you’re actually connected to mydb.. … Read more
To log in without a password: sudo -u user_name psql db_name To reset the password if you have forgotten: ALTER USER user_name WITH PASSWORD ‘new_password’;
If I remember correctly the user postgres has no DB password set on Ubuntu by default. That means, that you can login to that account only by using the postgres OS user account. Assuming, that you have root access on the box you can do: sudo -u postgres psql If that fails with a database … Read more
Postgres 9.5 (released since 2016-01-07) offers an “upsert” command, also known as an ON CONFLICT clause to INSERT: INSERT … ON CONFLICT DO NOTHING/UPDATE It solves many of the subtle problems you can run into when using concurrent operation, which some other answers propose.
Of course, you will get a fatal error for authenticating, because you do not include a user name… Try this one, it is OK for me 🙂 psql -U username -d myDataBase -a -f myInsertFile If the database is remote, use the same command with host psql -h host -U username -d myDataBase -a -f … Read more
Yes, SERIAL is the equivalent function. CREATE TABLE foo ( id SERIAL, bar varchar ); INSERT INTO foo (bar) VALUES (‘blah’); INSERT INTO foo (bar) VALUES (‘blah’); SELECT * FROM foo; +———-+ | 1 | blah | +———-+ | 2 | blah | +———-+ SERIAL is just a create table time macro around sequences. You … Read more
The UPDATE syntax is: [ WITH [ RECURSIVE ] with_query [, …] ] UPDATE [ ONLY ] table [ [ AS ] alias ] SET { column = { expression | DEFAULT } | ( column [, …] ) = ( { expression | DEFAULT } [, …] ) } [, …] [ FROM from_list … Read more