Differences

This shows you the differences between two versions of the page.

--- myhelp:sql [2022/12/01 09:37] – created - external edit 127.0.0.1
+++ myhelp:sql [2024/10/05 07:51] (current) – [mySQL / MariaDB] ulrich
@@ Line 11: / Line 11: @@
 sqlite> UPDATE T_names SET birthyear=1997 WHERE name = 'Jack Doe';
 sqlite> SELECT * FROM T_names LIMIT 1;
+</code>
+====== mySQL / MariaDB ======
+<code>
+CREATE DATABASE IF NOT EXISTS meineDB;
+CREATE USER 'benutzer'@'localhost' IDENTIFIED BY 'passwort';
+GRANT ALL PRIVILEGES ON meineDB . * TO 'benutzer'@'localhost';
+</code>
+Grant Access from remote IP (10.11.12.13)
+<code>
+GRANT ALL ON meineDB.* to 'benutzer'@'10.11.12.13' IDENTIFIED BY 'passwort' WITH GRANT OPTION;
 </code>
@@ Line 32: / Line 44: @@
 SELECT something, anything FROM T_table WHERE something != 'thing' AND anything LIKE '%any%' AND anything NOT LIKE '%stuff%' AND anything NOT LIKE '%other%';
 SELECT something, anything FROM T_table WHERE something LIKE ANY (ARRAY['%som%', '%thing', 'any%', 'stuff%'])
+</code>
+Create a new DB and User and change DB-Owner
+<code>
+CREATE DATABASE mynewdb;
+CREATE USER myuser WITH PASSWORD 'Aw5s0m4Secur3';
+ALTER DATABASE mynewdb OWNER TO myuser;
+</code>
+Copying PostgreSQL database to another server
+<code>
+pg_dump -C mynewdb | psql -h db.server.host -U myuser mynewdb
 </code>
+Original command from [[https://stackoverflow.com/questions/1237725/copying-postgresql-database-to-another-server|stackoverflow]].
+===== Enable remote connections =====
+  - Find your postgresql.conf <code>sudo -u postgres psql -c "SHOW config_file;" </code>
+  - Open the postgresql.conf <code>#listen_addresses = 'localhost'
+listen_addresses = '*' # OR a specific IP instead of the wildcard for all IPs</code>
+  - Find your pg_hba.conf (on RHEL based distros: /var/lib/pgsql/data/pg_hba.conf)
+  - Add the following lines <code># Allow external
+host    all             all             10.11.12.0/24              scram-sha-256 # for a network
+host    all             all             192.168.1.111/32           scram-sha-256 # for a single IP</code>
+  - Restart the DBMS <code>systemctl restart postgresql.service</code>
+===== A method to get the lastest timestamp =====
+From my point of view I've a good and a bad example
+The good example:
+<code>SELECT something FROM table
+WHERE time_stamp = (SELECT MAX(time_stamp) FROM table);</code>
+The bad example:
+<code>SELECT something FROM table
+WHERE time_stamp = (SELECT time_stamp FROM table ORDER BY time_stamp DESC NULLS LAST LIMIT 1);</code>
+===== Other usefull stuff =====
+Switch off the pages from CLI(shell)
+<code>sudo -u postgres psql -P pager=off -d database -c "SELECT something FROM table;"</code>
+Copy a SELECT to a CSV from CLI(shell)
+<code>
+sudo -u postgres psql -d database -c "COPY (SELECT  something, other_stuff FROM table
+GROUP BY something) TO '/tmp/result.csv' WITH CSV HEADER;"
+</code>
+{{tag>[Good2Know]}}