Test PostgreSQL API Beta

AttentionThis page documents an earlier version. Go to the latest (v2.1)version.

After creating a local cluster, follow the instructions below to test YugabyteDB’s PostgreSQL API.

psql is a command line shell for interacting with PostgreSQL. For ease of use, YugabyteDB ships with the 10.3 version of psql in its bin directory.

1. Connect with psql

  • Run psql to connect to the service.

You can do this as shown below.

  1. $ ./bin/psql --host localhost --port 5433 -U $USER
  1. Database 'username' does not exist
  2. psql (10.3, server 0.0.0)
  3. Type "help" for help.
  5. username=>
  • Run psql to connect to the service.

You can do this as shown below.

  1. $ ./bin/psql --host localhost --port 5433 -U $USER
  1. Database 'username' does not exist
  2. psql (10.3, server 0.0.0)
  3. Type "help" for help.
  5. username=>
  • Install the psql client inside docker
  1. $ docker exec -it yb-tserver-n3 yum install postgresql -y
  • Run psql to connect to the service.

You can do this as shown below.

  1. $ docker exec -it yb-tserver-n3 /usr/bin/psql --host yb-tserver-n3 --port 5433
  1. Database 'username' does not exist
  2. psql (10.3, server 0.0.0)
  3. Type "help" for help.
  5. username=>
  • Install the psql client inside the container
  1. $ kubectl exec -it yb-tserver-2 yum install postgresql
  • Run psql to connect to the service.
  1. $ kubectl exec -it yb-tserver-2 bash 
  1. $ psql --host localhost --port 5433 
  1. Database 'username' does not exist
  2. psql (10.3, server 0.0.0)
  3. Type "help" for help.
  5. username=>

2. Create a table

Create a database called ‘sample’.

  1. username=> CREATE DATABASE sample;

Connect to the database we just created.

  1. username=> \c sample
  1. psql (10.3, server 0.0.0)
  2. You are now connected to database "sample" as user "username".
  3. sample=>

Create a table named ‘stock_market’ which can store stock prices at various timestamps for different stock ticker symbols.

  1. sample=> CREATE TABLE sample.stock_market (
  2.   stock_symbol text,
  3.   ts text,
  4.   current_price float,
  5.   PRIMARY KEY (stock_symbol, ts)
  6. );

3. Insert data

Let us insert some data for a few stock symbols into our newly created ‘stock_market’ table. You can copy-paste these values directly into your cqlsh shell.

  1. INSERT INTO sample.stock_market (stock_symbol,ts,current_price) VALUES ('AAPL','2017-10-26 09:00:00',157.41);
  2. INSERT INTO sample.stock_market (stock_symbol,ts,current_price) VALUES ('AAPL','2017-10-26 10:00:00',157);
  3. INSERT INTO sample.stock_market (stock_symbol,ts,current_price) VALUES ('FB','2017-10-26 09:00:00',170.63);
  4. INSERT INTO sample.stock_market (stock_symbol,ts,current_price) VALUES ('FB','2017-10-26 10:00:00',170.1);
  5. INSERT INTO sample.stock_market (stock_symbol,ts,current_price) VALUES ('GOOG','2017-10-26 09:00:00',972.56);
  6. INSERT INTO sample.stock_market (stock_symbol,ts,current_price) VALUES ('GOOG','2017-10-26 10:00:00',971.91);

4. Query the table

Query all the values we have inserted into the table.

  1. sample=> SELECT * FROM sample.stock_market;
  1.  stock_symbol |         ts          | current_price
  2. --------------+---------------------+---------------
  3.  AAPL         | 2017-10-26 09:00:00 |    157.410004
  4.  AAPL         | 2017-10-26 10:00:00 |    157.000000
  5.  FB           | 2017-10-26 09:00:00 |    170.630005
  6.  FB           | 2017-10-26 10:00:00 |    170.100006
  7.  GOOG         | 2017-10-26 09:00:00 |    972.559998
  8.  GOOG         | 2017-10-26 10:00:00 |    971.909973
  9. (6 rows)